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 sample -ue LOCKEVMON -ftext -u e97q6c -p e97q6c > deadlock.txt

more deadlock.txt 可以看到有關的SQL語句。

請參考

http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-1004lockeventmonitor/

以下文章點擊率最高