[db2]锁问题求教

操作系统：

/opt/ibm/db2/V9.1/bin>uname -a
Linux yon 2.6.9-67.EL #1 Wed Nov 7 13:43:31 EST 2007 x86_64 x86_64 x86_64 GNU/Linux

复制代码

数据库：

/opt/ibm/db2/V9.1/bin>db2level
DB21085I Instance "db2inst1" uses "64" bits and DB2 code release "SQL09013"
with level identifier "01040107".
Informational tokens are "DB2 v9.1.0.3", "s070719", "MI00203", and Fix Pack
"3".
Product is installed at "/opt/ibm/db2/V9.1".

复制代码

准备：

/tmp> db2 "create table x (x int not null primary key)"
DB20000I The SQL command completed successfully.
/tmp> db2 "insert into x values (1)"
DB20000I The SQL command completed successfully.
/tmp> db2 "insert into x values (2)"
DB20000I The SQL command completed successfully.
/tmp> db2 "insert into x values (3)"
DB20000I The SQL command completed successfully.
/tmp> db2 "insert into x values (4)"
DB20000I The SQL command completed successfully.
/tmp> db2 "insert into x values (5)"
DB20000I The SQL command completed successfully.
/tmp> db2 "insert into x values (6)"
DB20000I The SQL command completed successfully.
/tmp> db2 "insert into x values (7)"
DB20000I The SQL command completed successfully.
/tmp> db2 "select * from x"
X
-----------
1
2
3
4
5
6
7
7 record(s) selected.

复制代码

启动三个窗口。依次进行如下操作。
窗口1：

/tmp> db2 +c +p
lock table x in share mode
DB20000I The SQL command completed successfully.

复制代码

窗口2：

/tmp> db2 +c +p
lock table x in exclusive mode

复制代码

此时，窗口2执行语句被阻塞

窗口3：

/home/fex> db2 "select x from x where x = 1"

复制代码

此时，窗口3的语句也被阻塞。

问题：窗口2语句显然没有能够锁定表x，为何窗口3语句还会被共享锁阻塞？

如果您要回复我窗口2语句没有完成，但是仍然持有了一个internal varriable lock。
我的主要疑问是：数据库使用何种方法来保证acid，从使用者角度来说，应该是不用关心的。
问题在于，在“原子性”的约束下，窗口2的执行应该是“要么排他锁定x，要么什么都不锁定”。
对于出现上述现象，我是不是可以理解成DB2的一个BUG？

作者: ivhb 发布时间: 2011-03-22

回复 ivhb

窗口2持有了IX锁，把他终止掉看看？

作者: 3sane 发布时间: 2011-03-23

[db2]锁问题求教

热门频道