DB2锁问题分析与解释(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