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"

Source: Internet
Author: User
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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.