Analysis and Interpretation of DB2 lock problems
Analysis and Interpretation of DB2 lock problems
DB2 applications often encounter lock timeouts and deadlocks. What is the cause of this phenomenon. This article simulates the lock wait, lock timeout, and deadlock phenomena in the form of experiments, and gives the root causes of these phenomena.
Test environment:
DB2 v9.7.0.6
AIX 6.1.0.0
Use the default CS isolation level
DDL and initial content 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: Verify lock waits for insert and other operations
Execute the insert operation in session 1. In session 2, check whether the insert, update, and delete operations 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 performs an insert operation on the table, session 2 does not have any problems with the insert and update and delete operations on the table.
----------------------------------------------------------------------------
Test 2: Verify lock waits for update and other operations
Execute the update operation in session 1. In session 2, check whether the insert, update, and delete operations 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 completed successfully.
$ Db2 "update student set name = 'GU' where age = 1"
DB21034E The command was processed as an SQL statement because it was not
Valid Command Line Processor command. During SQL processing it returned:
SQL0911N The current transaction has been rolled back because of a deadlock
Or timeout. Reason code "68". SQLSTATE = 40001
$ Db2 "delete from student where age = 2"
DB21034E The command was processed as an SQL statement because it was not
Valid Command Line Processor command. During SQL processing it returned:
SQL0911N The current transaction has been rolled back because of a deadlock
Or timeout. Reason code "68". SQLSTATE = 40001
----------------------------------------------------------------------------
Conclusion 2: When session 1 performs an update operation on a table row, session 2 can perform an insert operation on the table, but does not allow the delete and update operations on other rows.
----------------------------------------------------------------------------
Test 3: Verify the lock wait problem between the delete operation and other operations
The delete operation is performed in session 1. In session 2, check whether the insert, update, and delete operations will lock timeout.
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 completed successfully.
$ Db2 "update student set name = 'GU' where age = 1"
DB21034E The command was processed as an SQL statement because it was not
Valid Command Line Processor command. During SQL processing it returned:
SQL0911N The current transaction has been rolled back because of a deadlock
Or timeout. Reason code "68". SQLSTATE = 40001
$ Db2 "delete from student where age = 2"
DB21034E The command was processed as an SQL statement because it was not
Valid Command Line Processor command. During SQL processing it returned:
SQL0911N The current transaction has been rolled back because of a deadlock
Or timeout. Reason code "68". SQLSTATE = 40001
----------------------------------------------------------------------------
Conclusion 3: When application 1 performs the delete operation on a row of the table, Application 2 can perform the insert operation on the table, but does not allow the delete and update operations on other rows.
----------------------------------------------------------------------------
The general conclusion is:
When an application performs an insert operation on a table, other operations are not affected or affected.
When performing update and delete operations, other update and delete operations are affected.
To explain the cause of the above phenomenon, Let's first look at the locks required by 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: The insert operation requires table-level IX locks and row-Level X locks.
Note: The IX lock. The owner of the lock can change the data of the row when it owns the X lock of 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 table-level IX locks and row-Level X locks.
$ 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 the table-level IX lock and the corresponding row-Level X lock (here, three row-level locks are required because the age of the three records is 6 ).
The question is: why does the insert, update, and delete operations require the same locks (Table-level IX locks, corresponding to row-Level X locks), but the performance is different?
To solve this problem, let's 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
From the preceding execution plan, we can see the reason: the insert operation does not require table scanning, while the update and delete operations both require full table scanning, in addition, the system tries to apply the U Lock to each row during scanning.
The cause of lock timeout is table scan.
For example, if session 1 is to update a row of the table, the X lock will be applied to the row. Later, session 2 tries to update another row of the table. During full table scan, it will try to add the U lock to the row occupied by A, but there is nothing to do with it.
Cause lock timeout.
To verify this statement, you can capture the lock wait message,
Session 1
---------
$ Db2 + c "update student set name = 'hei' where age = 4"
DB20000I The SQL command completed successfully.
Session 2
---------
$ Db2 + c "delete from student where age = 6"
<------- At this time, it will hang because it is waiting for the lock of session 1.
Session 3
---------
$ Db2pd-db qsmiao-wlocks <--- capture the 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 00020004000000000000000952 Row.. x g 7818 db2bp E97Q6C * LOCAL. e97q6c. 141016035113
15408 [000-15408] 16 00020004000000000000000952 Row .. u w 10153 db2bp E97Q6C * LOCAL. e97q6c. 141016035219
As you can see, the U Lock and X lock are incompatible, resulting in lock wait, and finally cause lock timeout.
To solve the lock wait problem, you can create an index on the column age involved in the query predicate to avoid full table scan.
Test 4: Eliminate lock wait through index creation
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 = 'hei' where age = 4"
DB20000I The SQL command completed successfully.
Session 2
---------
$ Db2 + c "delete from student where age = 6" <-- no lock wait occurs and the operation succeeds directly.
DB20000I The SQL command completed successfully.
We can see that the lock timeout problem has been solved through the index. If you are interested, you can refer to the access plan after the index is created.
The following is a deadlock simulation.
Test 5: simulate a deadlock. The process is as follows:
Step 1: session 1 acquires LOCK1
Step 2: session 2 get lock LOCK2
Step 3: Apply for LOCK1 in session 2
Step 4: Apply for LOCK2 in session 1
To avoid lock timeout before a deadlock occurs, set the lock timeout control parameter to-1 (indicating waiting forever)
Update db cfg using locktimeout-1
Then restart the database.
Session 1
---------
$ Db2 + c "update student set name = 'A' where age = 1" <-- get LOCK1, successful
DB20000I The SQL command completed successfully.
Session 2
---------
$ Db2 + c "update student set name = 'two' where age = 4" <-- get LOCK2, successful
DB20000I The SQL command completed successfully.
$ Db2 + c "update student set name = 'four 'where age = 1" <-- apply for LOCK1, hang, because LOCK1 is held by session 1
Session 1
---------
$ Db2 + c "update student set name = 'three 'where age = 4" <-- apply for LOCK2 and hang because LOCK2 is held by session 2.
A deadlock has occurred. After 10 s, one of the two sessions will report the following deadlock (reason code 2) error, and the other session will be successfully executed.
SQL0911N The current transaction has been rolled back because of a deadlock
Or timeout. Reason code "2". SQLSTATE = 40001
References:
Lock methods and access solutions for standard tables. Here you can see detailed locking methods
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 use db2pd to view the lock information only when a deadlock or lock wait occurs. The following describes how to use the event monitor to monitor deadlock/lock timeout. The event monitor can capture lock events for 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_values 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
You can view the related SQL statements in more deadlock.txt.
See
Http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-1004lockeventmonitor/
