db2 与 Oracle 的锁的区别和比较

beebear

db2的优化器非常依赖索引，我的优化经验是避免所有的全表扫描，那怕牺牲空间，多建几个略带重复的索引，至于什么是“合适”的索引可以使用“设计顾问向导”对语句进行索引分析。
当然，基于业务的语句结构合理设计也很重要。
还有就是实在没有太好的进一步优化的办法可以考虑在语句或会话级修改隔离级别为未落实的读，牺牲“读一致性”换取性能及“少”的锁等待

macrozeng

今天做了个实验
过程如下：
在一个 db2cmd  窗口(A)中执行
db2 create table testlock (id num(10),name varchar(20))
db2 insert into testlock values (1,'a')
db2 insert into testlock values (2,'b')
db2 insert into testlock values (3,'c')
db2 commit
db2 +c update testlock set  name='a1' where id=1

另起一个 db2cmd  (B) 窗口执行查询
db2 select * from  testlock where id=2

结果是 B窗口没返回，只到在 A 窗口执行 commit 或者 rollback 以后，B 窗口才能查询出结果！

macrozeng

在 ID 字段上加一个索引
在 A 窗口执行 db2 "create index inx_testlock on testlock(ID)"
然后再执行
db2 +c update testlock set name='a1' where id=1
同样在 B 窗口执行
db2 select * from testlock where id=2
可以顺利得到结果，没有被锁住 ：）

macrozeng

在没加索引之前，
在执行完了
db2 +c update testlock set  name='a1' where id=1 以后
testlock 表上的锁的情况如下：


锁定列表
锁定名称                       = 0x020006000400E0090000000052
锁定属性                       = 0x00000000
发行版标志                     = 0x40000000
锁定计数                       = 1
挂起计数                       = 0
锁定对象名                     = 165675012
对象类型                       = 行
表空间名                       = USERSPACE1
表模式               = ZENGH
表名                 = TESTLOCK
方式                           = X

锁定名称                       = 0x53514C4332463041F12CF8E241
锁定属性                       = 0x00000000
发行版标志                     = 0x40000000
锁定计数                       = 1
挂起计数                       = 0
锁定对象名                     = 0
对象类型                       = 内部方案锁定
方式                           = S

锁定名称                       = 0x02000600000000000000000054
锁定属性                       = 0x00000000
发行版标志                     = 0x40000000
锁定计数                       = 1
挂起计数                       = 0
锁定对象名                     = 6
对象类型                       = 表
表空间名                       = USERSPACE1
表模式               = ZENGH
表名                 = TESTLOCK
方式                           = IX

在 testlock 上面加了一个排它锁。
而在执行
db2 select * from  testlock where id=2   以后，又新增了下面一个锁

挂起锁定的代理程序标识                   = 7
保留锁定的应用程序标识                   = *LOCAL.DB2.070622071909
锁定名称                                 = 0x020006000400E0090000000052
锁定属性                                 = 0x00000000
发行版标志                               = 0x00000001
锁定对象类型                             = 行
锁定方式                                 = 互斥锁定（X）
请求的锁定方式                           = 下一个键共享（NS）
挂起锁定的表空间名                       = USERSPACE1
挂起锁定的表模式                         = ZENGH
挂起锁定的表名                           = TESTLOCK
挂起锁定的表的数据分区标识  = 0
锁定等待启动时间戳记                     = 2007-06-22 16:34:41.916449


锁定列表
锁定名称                       = 0x01000000010000000200210056
锁定属性                       = 0x00000000
发行版标志                     = 0x40000000
锁定计数                       = 1
挂起计数                       = 0
锁定对象名                     = 0
对象类型                       = 内部变化锁定
方式                           = S

锁定名称                       = 0x53514C4332463041F12CF8E241
锁定属性                       = 0x00000000
发行版标志                     = 0x40000000
锁定计数                       = 1
挂起计数                       = 0
锁定对象名                     = 0
对象类型                       = 内部方案锁定
方式                           = S

锁定名称                       = 0x02000600000000000000000054
锁定属性                       = 0x00000000
发行版标志                     = 0x00000001
锁定计数                       = 1
挂起计数                       = 0
锁定对象名                     = 6
对象类型                       = 表
表空间名                       = USERSPACE1
表模式               = ZENGH
表名                 = TESTLOCK
方式                           = IS

新增了一个读的共享锁，但是由于前面加上了个排他锁，所以造成了锁定。

macrozeng

在加索引以后，
db2 +c update testlock set name='a1' where id=1
语句依然会加一个排他锁，但是在执行
db2 select * from testlock where id=2  的时候不会产生锁定，很快就能得到结果
这里应该是因为 ID 字段上有索引，所以不必再对 testlock 表进行全表扫描，不需要在 id=1 这行上加共享读锁。
但是如果 where 条件换成 id=1 的话，执行
db2 select * from testlock where id=1
同样会造成锁定，因为这条语句即使通过索引不必对全表加读锁还是需要对  id=1 这行加读锁，如果 update 的事务没有结束，就会产生锁定！
但是不幸的是无论是加不加索引，在执行 db2 select * from testlock 都是被锁定的 ：（

macrozeng

但是 Oracle 在上述的实验中都不会造成互相锁定，因为它是“写不影响读的”即使在最后一种情况下， Oracle 一样可以返回结果，返回的是 update 事务开始时的结果，这也是因为 Oracle 具有回滚段才具有的特性。
请各位指教！

南来一味凉

最初由 macrozeng 发布
[B]今天做了个实验
过程如下：
在一个 db2cmd  窗口(A)中执行
db2 create table testlock (id num(10),name varchar(20))
db2 insert into testlock values (1,'a')
db2 insert into testlock values (2,'b')
db2 insert into testlock values (3,'c')
db2 commit
db2 +c update testlock set  name='a1' where id=1

另起一个 db2cmd  (B) 窗口执行查询
db2 select * from  testlock where id=2

结果是 B窗口没返回，只到在 A 窗口执行 commit 或者 rollback 以后，B 窗口才能查询出结果！ [/B]

佩服楼主动手实验的精神。只是要考虑隔离级。
db2 select * from testlock where id=2 with ur

或者
db2set DB2_EVALUNCOMMITTED=ON

南来一味凉

最初由 macrozeng 发布
[B]但是 Oracle 在上述的实验中都不会造成互相锁定，因为它是“写不影响读的”即使在最后一种情况下， Oracle 一样可以返回结果，返回的是 update 事务开始时的结果，这也是因为 Oracle 具有回滚段才具有的特性。
请各位指教！ [/B]

有没有看偶贴的文章，看完了再讨论吧。

beebear

最初由 南来一味凉 发布
[B]


佩服楼主动手实验的精神。只是要考虑隔离级。
db2 select * from testlock where id=2 with ur

或者
db2set DB2_EVALUNCOMMITTED=ON [/B]

1、同样佩服实践动手精神。
2、db2 虽然通过执行隔离级别选项能避免select等待，但由于其需要在语句级进行显性指定，所以导致实际应用中语句通用性不好。况且什么语句需要指定，何时指定也很难判断
3、DB2_EVALUNCOMMITTED=ON  很有作用，能避免不必要的锁等待，但满足谓词的数据仍然会因锁定而导致其它selec进程 访问该数据库产生锁等待。
应用中如何恰当使用隔离级别尚需根据实际情况制定，目前还没有总结出太好的办法。希望有经验的朋友分享经验，谢谢！

macrozeng

最初由 南来一味凉 发布
[B]

有没有看偶贴的文章，看完了再讨论吧。 [/B]

谢谢，我只是觉得自己动手记忆会更深刻些 ：）
文章我大概看了一下，大概意思是说 Oracle 的想法比较陈旧，而且不符合国际标准 ：）由于 undo 的问题还会经常出现 ORA-01555 。而 DB2 是遵循标准。
其实，我无意对  DB2 和 Oracle 的好坏做出讨论，这个问题实在是太无聊，因为我本身是从 Oracle 转到 Db2 上了的，所以有很多 Oracle 的思维，在使用 Db2 的时候会去犯错误，我只想知道他们到底有什么区别，是因为什么原因造成这样的区别的？以后在使用不同的数据库的时候应该去注意点什么 ：）