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/

以下文章点击率最高

Loading…

发表评论