Com. IBM. db2.jdbc. db2exception: [IBM] [CLI driver] [DB2/NT] sql0911n has rolled back the current transaction due to a deadlock or timeout. Cause code "2"

Com. IBM. db2.jdbc. db2exception: [IBM] [CLI driver] [DB2/NT] sql0911n has rolled back the current transaction due to a deadlock or timeout. Cause code "2 ". Sqlstate = 40001
Solutions provided by IBM for this problem

This problem may be caused by the DB2 deadlock caused by the application, especially when accessing the DB2 data source, an error similar to the following occurs:

Error code:-911 com. IBM. db2.jdbc. db2exception: [IBM] [CLI driver] [DB2/NT] sql0911n has rolled back the current transaction due to a deadlock or timeout. Cause code "2 ". Sqlstate = 40001

To diagnose this problem:

1. Run these DB2 commands:
   1. DB2 update monitor switches using lock on
   2. DB2 get snapshot for locks on dbname>

   Now,Directory_name\ Lock_snapshot.logHas DB2 lock information.

2. Run the following command to disable the lock monitor:DB2 update monitor switchesusing lock off

To verify whether you have a deadlock:

1. Find the application handle with the lock wait state, and then find the agent ID that is locked to verify the agent identity.
2. Go to the handle to verify whether it has the lock Wait Status and the agent ID that keeps the handle locked. If it has the same agent ID as the previous handle, you know that you have a loop lock (deadlock ).

To solve this problem:

1. If concurrent access is not required, check the application and use a smaller isolation level.
2. When changedAccessintentBe careful when changing the value to a lower isolation level. This change may cause data integrity problems:
3. For DB2/UDB v7.2 and earlier releases, you can set the db2_rr_to_rs flag from the DB2 command line window to eliminate unnecessary deadlocks, for example, there are too many restrictions on accessintent defined on bean methods (for example, pessimisticupdate ). DB @ _ rr_to_rs settings have two influences:
   • If RR is the isolation level you selected, it is effectively reduced to Rs.
   • If another isolation level is selected and db2_rr_to_rs is set to enabled, the row that has been deleted but not implemented will be scanned, even if this row may be eligible for scanning. The hop behavior affects the RR, read stability (RS), and cursor stability (CS) isolation level.

   For example, consider the situation where transaction a deletes rows with column1 = 10 and transaction B scans on column1> 8 and column1 <12. When db2_rr_to_rs is disabled, transaction B waits for transaction a to implement or roll back. If transaction a rolls back, column1 = 10 rows are included in the result set of transaction B's query. When db2_rr_to_rs is enabled, transaction B does not wait for transaction a to be implemented or rolled back. Transaction B immediately receives query results that do not contain deleted rows. Set db2_rr_to_rs to effectively change the locking behavior, so as to avoid deadlocks.