DB2鎖問題分析與解釋
DB2 鎖問題分析與解釋
DB2 應用中經常會遇到鎖超時與死鎖現象，那麼這種現象產生的原因是什麼呢。本文以試驗的形式模擬鎖等待、鎖超時、死鎖現象，並給出這些現象的根本原因。
試驗環境：
DB2 v9.7.0.6
AIX 6.1.0.0
采用默認的隔離級別CS
STUDENT表的DDL與初始內容
------------------------------------------------
-- 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
試驗1：驗證insert操作與其他操作的鎖等待問題
session 1中發出insert操作，在session 2中觀察insert,update,delete操作是否會鎖超時。
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.
----------------------------------------------------------------------------
結論1：當session 1對表作insert操作時，session 2對該表的insert及其他行的update,delete操作都不會有問題
----------------------------------------------------------------------------
試驗2：驗證update操作與其他操作的鎖等待問題
session 1中發出update操作，在session 2中觀察insert,update,delete操作是否會鎖超時。
--------------
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 a
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 a
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
----------------------------------------------------------------------------
結論2：當session 1對表某一行做update操作時，session 2可以對該表作insert操作，但不允許對其他行的delete和update操作
----------------------------------------------------------------------------
試驗3：驗證delete操作與其他操作的鎖等待問題
session 1中發出delete操作，在session 2中觀察insert,update,delete操作是否會鎖超時。
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 a
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 a
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
----------------------------------------------------------------------------
結論3：當應用1對表某一行做delete操作時，應用2可以對該表作insert操作，但不允許對其他行的delete和update操作
----------------------------------------------------------------------------
總的結論是：
應用對表作insert操作時，其他操作不受影響,也不受其他操作影響。
作update，delete操作時，其他的update和delete操作受影響。
為了解釋以上現象的原因，我們首先看一下上面的操作需要什麼樣的鎖。
session 1.
---------
$ db2 rollback
$ db2 +c "insert into student values(7,'han')"
DB20000I The SQL command completed successfully.
$ db2pd -db qsmiao -locks
結論：insert操作需要表級的IX鎖和行級的X鎖。
注：IX鎖，該鎖的擁有者在擁有相應行的X鎖時可以更改該行的數據。
$ db2 rollback
$ db2 +c "update student set name='yan' where age=5"
DB20000I The SQL command completed successfully.
$ db2pd -db qsmiao -locks
結論：update操作需要表級的IX鎖和行級的X鎖。
$ db2 rollback
$ db2 +c "delete from student where age=6"
DB20000I The SQL command completed successfully.
$ db2pd -db qsmiao -locks
結論：update操作需要表級的IX鎖和對應的行級的X鎖（這裡因為3條記錄的age都為6,因此需要3個行級鎖）。
現在的問題是：為什麼insert和update,delete操作需要的鎖一樣（表級的IX鎖，對應行級的X鎖），但是表現的效果卻不一樣呢？
為了解決這個問題，看一下他們的執行計劃吧：
$ 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
從上面的執行計劃中可以看到原因：insert操作不需要表掃描，而update和delete操作都需要全表掃描，而且會在掃描的時候試圖對每一行加U鎖。
導致鎖超時的原因就是表掃描
例如session 1要更新表的某一行，會在該行加上X鎖。之後, session 2試圖更新該表的另一行，進行全表掃描時，就會試圖對A占用的那一行加上U鎖，但無能為力，最終導
致鎖超時。
為了驗證該說法，可以抓取鎖等待的消息，
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"
<-------這時會hang住，因為它在等session 1的鎖
session 3
---------
$ db2pd -db qsmiao -wlocks <---在鎖超時發生之前，抓取鎖等待的消息
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
可以看到，是因為U鎖和X鎖的不兼容導致鎖等待，最後導致鎖超時。
為了解決該鎖等待問題，可以在查詢謂詞所涉及的列age上建立索引，避免全表掃描
試驗4：通過建立索引，消除鎖等待現象
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" <--沒有發生鎖等待現象，直接成功
DB20000I The SQL command completed successfully.
可以看到，已經通過索引解決了該鎖超時問題，如果讀者有興趣的話，可以看下建立索引之後的訪問計劃。
下面模擬一個死鎖現象
試驗5:模擬死鎖，過程如下
第一步：session 1 獲得 鎖 LOCK1
第二步：session 2 獲得 鎖 LOCK2
第三步：session 2 申請 鎖 LOCK1
第四步：session 1 申請 鎖 LOCK2
為了避免死鎖之前產生鎖超時，先將鎖超時控制參數設為-1（表示永遠等待）
update db cfg using locktimeout -1
之後重啟數據庫
session 1
---------
$ db2 +c "update student set name = 'an' where age = 1" <--獲得鎖LOCK1,成功
DB20000I The SQL command completed successfully.
session 2
---------
$ db2 +c "update student set name = 'two' where age = 4" <--獲得鎖LOCK2,成功
DB20000I The SQL command completed successfully.
$ db2 +c "update student set name = 'four' where age = 1" <--申請鎖LOCK1，hang住，因為LOCK1被session 1持有
session 1
---------
$ db2 +c "update student set name = 'three' where age = 4" <--申請鎖LOCK2，hang住，因為LOCK2被session 2持有
這時已經發生了死鎖，10s之後，這兩個session有一個會報出如下死鎖（reason code 2）錯誤，另一個session成功執行
SQL0911N The current transaction has been rolled back because of a deadlock
or timeout. Reason code "2". SQLSTATE=40001
參考資料：
標准表的鎖定方式和存取方案，這裡您可以看到詳細的加鎖方式
http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.perf.doc/doc/r0005275.html?lang=zh
附，只能在發生死鎖或者鎖等待的時候才能用db2pd查看鎖的信息。下面附上如何采用事件監控器監控死鎖/鎖超時。事件監控器可以抓取一段時間內的鎖事件
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
重現問題
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 可以看到有關的SQL語句。
請參考
http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-1004lockeventmonitor/
