Multi threaded INSERT result in deadlock and transaction rollback

[former_member212487]

Working on porting a J2EE application from Oracle to HANA

I’ve hit a deadlock situation on INSERT-ing data to one of the appplication tables, a thing that didn’t happen in Oracle.

The log show the following deadlock on INSERT statements

Questions:

1. How can INSERT statement cause a deadlock on a table ?

2. How can I configure the HANA log system to show exactly what resource is being held by the relevant threads at the time of the deadlock ?

"...

This is not an HDB error. User or application may cause a deadlock due to incorrect access sequences on shared objects.

current tx CONNECTION_ID=408576, LOGICAL_CONNECTION_ID=408576, THREAD_ID=62668, CLIENT_HOST=127.0.0.1, CLIENT_IP=10.26.181.205, CLIENT_PID=12316, DB USER=AMI, TRANSACTION_ID=77, UPDATE_TRANSACTION_ID=441346, TRANSACTION_TYPE=USER, TRANSACTION_STATE=ACTIVE, TRANSACTION_ISOLATION_LEVEL=RC, TRANSACTION_CACHING_FLAG=0, QUERY_BUFFER_SIZE=48104, NUM_OPENED_CURSORS=0, SQL_STATEMENT="INSERT INTO COMPOUND_REQUEST_INSTANCES(COMPOUND_REQUEST_INSTANCE_ID, COMPOUND_REQUEST_INSTANCE_NAME, start_time, end_time, instance_count, successful_instance_count, IGNORED_BY_RULE, EXCEEDED_INSTANCES_PER_MINUTE, EXCEEDED_BP_LIMIT, BUSINESS_PROCESS_TYPE_ID) VALUES(?,?,?,?,1,?, ?, ?, ?, ?)"

LOCK_TYPE=RECORD_LOCK, LOCK_MODE=EXCLUSIVE, CONTAINER_ID=0, TABLE_ID=163946, TABLE_NAME=OPTIER_TRUNK:COMPOUND_REQUEST_INSTANCES, RECORD_ID=[CS:OID=0x00000354, PARTID=0x0, OFFSET=0xa5cb]

blocked by CONNECTION_ID=408609, LOGICAL_CONNECTION_ID=408609, THREAD_ID=62634, CLIENT_HOST=127.0.0.1, CLIENT_IP=10.26.181.205, CLIENT_PID=12316, DB USER=AMI, TRANSACTION_ID=71, UPDATE_TRANSACTION_ID=441353, TRANSACTION_TYPE=USER, TRANSACTION_STATE=ACTIVE, TRANSACTION_ISOLATION_LEVEL=RC, TRANSACTION_CACHING_FLAG=0, QUERY_BUFFER_SIZE=48104, NUM_OPENED_CURSORS=0, SQL_STATEMENT="INSERT INTO COMPOUND_REQUEST_INSTANCES(COMPOUND_REQUEST_INSTANCE_ID, COMPOUND_REQUEST_INSTANCE_NAME, start_time, end_time, instance_count, successful_instance_count, IGNORED_BY_RULE, EXCEEDED_INSTANCES_PER_MINUTE, EXCEEDED_BP_LIMIT, BUSINESS_PROCESS_TYPE_ID) VALUES(?,?,?,?,1,?, ?, ?, ?, ?)"

LOCK_TYPE=RECORD_LOCK, LOCK_MODE=EXCLUSIVE, CONTAINER_ID=0, TABLE_ID=163946, TABLE_NAME=OPTIER_TRUNK:COMPOUND_REQUEST_INSTANCES, RECORD_ID=[CS:OID=0x00000354, PARTID=0x0, OFFSET=0xa5d5]

blocked by current transaction (TRANSACTION_ID=77, see above for the details)

..."

[lbreddemann]

1. How can INSERT statement cause a deadlock on a table ?

It's not a single INSERT that causes this.

As the trace output clearly shows (reformatting goes a long way here), session 408576 wants to insert into table COMPOUND_REQUEST_INSTANCES  a record that should get this record_id:

RECORD_ID=[CS:OID=0x00000354, PARTID=0x0, OFFSET=0xa5cb]

This exact record ID however has already been taken by a second insert statement issues by session 408609.

So up to here it's simply a common life-lock situation.

Maybe there exists a second pair of update/insert/delete statements from those two sessions that are also intertwined like that - this would then trigger the deadlock resolution.

2. How can I configure the HANA log system to show exactly what resource is being held by the relevant threads at the time of the deadlock ?

Well, the trace already shows that. Other than that, you may want to have a look into m_record_locks/m_object_locks system tables.

From what I see up to here, the very same thing would have happened with Oracle - there must still be some other change in the application behavior.

- Lars

[former_member197029]

Hi ,

Can you please let me know if there is any document explaining each of the terms in the deadlock alert? I know this link http://help.sap.com/hana/sap_hana_troubleshooting_and_performance_analysis_guide_en.pdf

describes the alerts but not in detail.

[lbreddemann]

The "lock name" (or record_id as it is named in the trace file) is a representation of internal locking data structures that are not documented for use outside the SAP core HANA development.

If you face issues for which the analysis requires the decoding of this lock name, please open a support incident.

- Lars

[former_member197029]

Okay. Thanks for the quick response.