analysis and explanation of DB2 lock problem
DB2 applications often encounter lock timeouts and deadlocks, so what is the cause of this phenomenon? In this paper, we simulate the lock waiting, lock timeout and deadlock phenomenon in the form of experiment. and give the root cause of these phenomena.
Test environment:
DB2 v9.7.0.6
AIX 6.1.0.0
Using the default Isolation level CS
DDL and initial contents of the student table
------------------------------------------------
--DDL statements for table "e97q6c". " STUDENT "
------------------------------------------------
CREATE TABLE "e97q6c". " STUDENT "(
"Age" INTEGER,
"NAME" CHAR (8))
In "USERSPACE1";
$ DB2 "SELECT * FROM Student"
Age NAME
----------- --------
3 Xu
5 Gao
2 Liu
1 gu
Test 1: Verifying lock-wait issues with insert operations and other operations
The insert operation is issued in Session 1, in session 2 to see if the insert,update,delete operation will lock timeout.
Session 1
---------
$ DB2 +c "INSERT into student values (4, ' Miao ')"
db20000i the SQL command completed successfully.
Session 2
---------
$ DB2 "INSERT into student values (6, ' mu ')"
db20000i the SQL command completed successfully.
$ DB2 "UPDATE student set name= ' gu ' where age=1 '
db20000i the SQL command completed successfully.
$ DB2 "Delete from student where age=2"
db20000i the SQL command completed successfully.
----------------------------------------------------------------------------
Conclusion 1: When session 1 inserts the table, session 2 does not have any problem with the update,delete operation of the insert and other rows of the table.
----------------------------------------------------------------------------
Test 2: Verifying lock-wait issues with update operations and other operations
The update operation is issued in Session 1, in session 2 to see if the insert,update,delete operation will lock timeout.
--------------
Session 1
---------
$ DB2 Commit
$ DB2 "SELECT * FROM Student"
Age NAME
----------- --------
3 Xu
5 Gao
6 mu
4 Miao
1 gu
5 record (s) selected.
$ DB2 +c "UPDATE student set name = ' Qing ' where age=4 '
db20000i the SQL command completed successfully.
Session 2
---------
$ DB2 "INSERT into student values (6, ' mu ')"
db20000i the SQL command completedsuccessfully.
$ DB2 "UPDATE student set name= ' gu ' where age=1 '
db21034e the command is processed as an SQL statement because it is not a
Valid command line Processor command. During SQL Processing It returned:
sql0911n the current transaction have been rolled back because of a deadlock
OrTimeout. Reason code "68". sqlstate=40001
$ DB2 "Delete from student where age=2"
db21034e the command is processed as an SQL statement because it is not a
Valid command line Processor command. During SQL Processing It returned:
sql0911n the current transaction have been rolled back because of a deadlock
OrTimeout. Reason code "68". sqlstate=40001
----------------------------------------------------------------------------
Conclusion 2: When session 1 makes an update operation on a table row, session 2 can insert the table, but does not agree to delete and update operations on the other rows
----------------------------------------------------------------------------
Test 3: Verifying lock-wait issues with delete operations and other operations
The delete operation is issued in Session 1 and the Insert,update,delete operation is observed in session 2 to see if the lock timeout occurs.
Session 1
---------
$ DB2 Commit
$ DB2 +c "Delete from student where age=4"
db20000i the SQL command completed successfully.
Session 2
---------
$ DB2 "INSERT into student values (6, ' mu ')"
db20000i the SQL command completedsuccessfully.
$ DB2 "UPDATE student set name= ' gu ' where age=1 '
db21034e the command is processed as an SQL statement because it is not a
Valid command line Processor command. During SQL Processing It returned:
sql0911n the current transaction have been rolled back because of a deadlock
OrTimeout. Reason code "68". sqlstate=40001
$ DB2 "Delete from student where age=2"
db21034e the command is processed as an SQL statement because it is not a
Valid command line Processor command. During SQL Processing It returned:
sql0911n the current transaction have been rolled back because of a deadlock
OrTimeout. Reason code "68". sqlstate=40001
----------------------------------------------------------------------------
Conclusion 3: When applying 1 to delete a row in a table, application 2 can insert the table. But does not agree to delete and update operations on other rows
----------------------------------------------------------------------------
The general conclusion is that:
When an insert operation is applied to a table. Other operations are unaffected and unaffected by other operations.
Other update and delete operations are affected when the update,delete operation is made.
To explain the reasons for the above, let's first look at what kind of lock the above operation requires.
Session 1.
---------
$ DB2 Rollback
$ DB2 +c "INSERT into student values (7, ' Han ')"
db20000i the SQL command completed successfully.
$ db2pd-db Qsmiao-locks
Conclusion: The insert operation requires a table-level IX lock and a row-level x lock.
Note: IX lock. The owner of the lock can change the row's data when it has an X lock on the corresponding row.
$ DB2 Rollback
$ DB2 +c "update student set Name= ' Yan ' where age=5 '
db20000i the SQL command completed successfully.
$ db2pd-db Qsmiao-locks
watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvcwluz3nvbmczmzmz/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/ Dissolve/70/gravity/southeast ">
Conclusion: The update operation requires a table-level IX lock and a row-level x lock.
$ DB2 Rollback
$ DB2 +c "Delete from student where age=6"
db20000i the SQL command completed successfully.
$ db2pd-db Qsmiao-locks
Conclusion: The update operation requires a table-level IX lock and a corresponding row-level X-Lock (this is because the age of 3 records is 6, so 3 row-level locks are required).
The question now is: Why insert is the same lock as the update,delete operation (table-level IX lock. The corresponding row-level x lock), but the performance is not the same?
In order to solve the problem. Take a look at their running plans:
$ db2expln-d qsmiao-g-statement "insert into student values (5, ' Gao ')"-terminal
watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvcwluz3nvbmczmzmz/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/ Dissolve/70/gravity/southeast ">
$ db2expln-d qsmiao-g-statement "update student set Name= ' Qing ' where age=4 '-terminal
$ db2expln-d qsmiao-g-statement "Delete from student where age=6"-terminal
You can see the reason from the running plan above: the insert operation does not require a table scan, and both the update and delete operations require a full table scan, and you will try to lock each row with a U when scanning.
The cause of the lock timeout is the table scan
For example session 1 to update a row of a table, an X lock is added to the row. After that, session 2 tries to update the table with another row, when the full table scan, will try to a occupy the row with a U lock, but helpless, and finally guide
Lock timeout.
In order to verify the assertion, the message of the lock wait can be crawled.
Session 1
---------
$ DB2 +c "update student set name= ' hehe ' where age = 4"
db20000i the SQL command completed successfully.
Session 2
---------
$ DB2 +c "Delete from student where age=6"
<-------will hang at this time. Because it's in the lock waiting session 1
Session 3
---------
$ db2pd-db Qsmiao-wlocks <---Grabbing a lock wait message before the lock timeout occurs
Locks being waited on:
Apphandl [Nod-index] tranhdl lockname Type Mode Conv Sts cooredu AppName AuthID AppID
15393 [000-15393] 2 00020004000000000000000952Row.. X G7818 db2bp e97q6c *local.e97q6c.141016035113
15408 [000-15408] 16 00020004000000000000000952Row.. U W10153 db2bp e97q6c *local.e97q6c.141016035219
It is possible to see that the lock waits due to the incompatibility of the U-Lock and X-lock, which finally leads to a lock timeout.
To resolve this lock wait problem, you can index the column age involved in the query predicate to avoid a full table scan
Test 4: Eliminate the lock wait by establishing an index
Session 1
---------
$ DB2 Rollback
$ DB2 +c "lock table student in Share mode"
$ DB2 +c "CREATE INDEX STU_IDX on student (age)"
$ DB2 Commit
$ DB2 +c "update student set name= ' hehe ' where age = 4"
db20000i the SQL command completed successfully.
Session 2
---------
$ DB2 +c "Delete from student where age=6" <--no lock-wait phenomenon, direct success
db20000i the SQL command completed successfully.
Can see that the lock timeout problem has been overcome by indexing. If the reader is interested, be able to look at the post-indexing interview plan.
The following simulates a deadlock phenomenon
Test 5: Simulate deadlocks, steps such as the following
First step: Session 1 Get lock LOCK1
Step Two: Session 2 get Lock LOCK2
Step Three: Session 2 application Lock LOCK1
Fourth Step: Session 1 Application Lock LOCK2
To avoid a lock timeout before a deadlock, set the lock timeout control number to 1 (which means to wait forever)
Update db CFG using locktimeout-1
After you restart the database
Session 1
---------
$ DB2 +c "UPDATE student set name = ' A ' where age = 1" <--get lock LOCK1, success
db20000i the SQL command completed successfully.
Session 2
---------
$ DB2 +c "UPDATE student set name = '" Where age = 4 "<--get lock LOCK2, successful
db20000i the SQL command completed successfully.
$ DB2 +c "UPDATE student set name = ' Four ' where the age = 1" <--Apply for lock Lock1,hang live. Since LOCK1 was held by session 1
Session 1
---------
$ DB2 +c "UPDATE student set name = ' Three ' where age = 4" <--Apply lock Lock2,hang Live, because LOCK2 is held by session 2
At this point, a deadlock has occurred, and after 10s, the two sessions will have a report such as the following deadlock (reason code 2) error. There's another session running successfully
sql0911n the current transaction have been rolled back because of a deadlock
or timeout. Reason code "2". sqlstate=40001
References:
Standard table locking method and access scheme, here you can see the specific lock mode
Http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.perf.doc/doc/r0005275.html?lang=zh
Report. You can only use DB2PD to view the information of a lock in the event of a deadlock or lock waiting. Here is how to use the event Monitor to monitor the deadlock/lock timeout. Event Monitor is able to crawl lock events over time
DB2 Update DB CFG for sample using mon_lockwait hist_and_values mon_deadlock hist_and_values mon_locktimeout Hist_and_valu ES mon_lw_thresh 10000
DB2 "CREATE event MONITOR Lockevmon for LOCKING WRITE to unformatted EVENT table (table Lockevmon)"
DB2 Set Event Monitor Lockevmon state=1
Reproduce the problem
DB2 Flush Event Monitor Lockevmon
DB2 Set Event Monitor Lockevmon state=0
Cp/home/db2users/e97q6c/sqllib/samples/java/jdbc/db2evmonfmt.java./
Cp/home/db2users/e97q6c/sqllib/samples/java/jdbc/db2evmonlocking.xsl./
Export Path=/home/db2users/e97q6c/sqllib/java/jdk64/bin: $PATH
Javac Db2evmonfmt.java
Java db2evmonfmt-d sample-ue lockevmon-ftext-u e97q6c-p e97q6c > Deadlock.txt
More deadlock.txt can see the relevant SQL statement.
Please take a test.
http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-1004lockeventmonitor/
http://blog.csdn.net/qingsong3333/article/details/51206329
Analysis and explanation of DB2 lock problem
