首页 > 代码库 > DB2 锁问题分析与解释
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.
----------------------------------------------------------------------------
试验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操作
----------------------------------------------------------------------------
---------
$ 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.
结论：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/
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操作是否会锁超时。
---------
$ 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/
DB2 锁问题分析与解释
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。