analysis and explanation of DB2 lock problem
Lock timeouts and deadlocks are often encountered in DB2 applications, so what is the cause of this phenomenon? In this paper, the phenomenon of lock waiting, lock time-out and deadlock is simulated in the form of experiment, and the root cause of these phenomena is given.
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 completedsuccessfully.
$ DB2 "UPDATE student set name= ' gu ' where age=1 '
db20000i the SQL command completedsuccessfully.
$ DB2 "Delete from student where age=2"
db20000i the SQL command completedsuccessfully.
----------------------------------------------------------------------------
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 delete and update operations on the other rows are not allowed
----------------------------------------------------------------------------
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 of a table, apply 2 can insert the table, but delete and update operations on the other rows are not allowed
----------------------------------------------------------------------------
The general conclusion is that:
When an insert operation is applied to a table, other operations are not affected or affected by other operations.
Other update and delete operations are affected when the update,delete operation is made.
To explain the reasons for this, let's first look at what locks are required for the above operation.
Session 1.
---------
$ DB2 Rollback
$ DB2 +c "INSERT into student values (7, ' Han ')"
db20000i the SQL command completed successfully.
$ db2pd-db Qsmiao-locks
Conclusion: An 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
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 and update,delete operations require the same lock (table-level IX lock, corresponding to row-level X-Lock), but the performance is not the same?
To solve this problem, take a look at their execution plan:
$ db2expln-d qsmiao-g-statement "insert into student values (5, ' Gao ')"-terminal
$ 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 execution 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 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. Session 2 then tries to update another row of the table, and when the full table is scanned, it tries to add U locks to the row used by a, but is powerless to
Lock timeout.
To verify this, you can grab the message of the lock wait,
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 point because it waits for the lock of 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
As you can see, the incompatibility between the U-Lock and X-lock causes the lock to wait and finally causes the lock to time out.
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.
As you can see, the lock timeout problem has been resolved through the index, and if the reader is interested, you can look at the Access plan after indexing.
Below simulates a deadlock phenomenon
Test 5: Simulate the deadlock, the process is as follows
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 parameter to 1 (which means always waiting)
Update db CFG using locktimeout-1
Then 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 age = 1" <--Apply lock Lock1,hang Live because LOCK1 is 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
A deadlock has occurred, and after 10s, one of the two sessions will report the following deadlock (reason code 2) error and the other session successfully executed
sql0911n the current transaction have been rolled back because of a deadlock
or timeout. Reason code "2". sqlstate=40001
Resources:
Standard table locking method and access scheme, here you can see the detailed 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
Attached, you can only use DB2PD to view the information of the lock when a deadlock or lock wait occurs. Here is how to use the event Monitor to monitor the deadlock/lock timeout. Event Monitor can crawl lock events over a period of 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 qsmiao-ue lockevmon-ftext-u e97q6c-p e97q6c > Deadlock.txt
More deadlock.txt can see the relevant SQL statement.
Please refer to
http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-1004lockeventmonitor/
Analysis and explanation of DB2 lock problem
