|
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 s |