关于事务和锁定记录集的问题

孤烟

是的，在1楼所建的表中，你执行
begin tran
  update tb with (ROWLOCK)
   set A='aa'
   where B='b2'
   waitfor delay '00:01:00'  --等待1分钟
commit tran
在控制台的锁/进程ID中都是RID,PAG,TAB三个锁一起出现！说明3个锁一起加了！


实际上当有行锁出现时，页和表都会出现意向锁。
SQL SERVER 2000的解释：
意向锁
意向锁表示 SQL Server 需要在层次结构中的某些底层资源上获取共享 (S) 锁或排它 (X) 锁。例如，放置在表级的共享意向锁表示事务打算在表中的页或行上放置共享 (S) 锁。在表级设置意向锁可防止另一个事务随后在包含那一页的表上获取排它 (X) 锁。意向锁可以提高性能，因为 SQL Server 仅在表级检查意向锁来确定事务是否可以安全地获取该表上的锁。而无须检查表中的每行或每页上的锁以确定事务是否可以锁定整个表。

意向锁包括意向共享 (IS)、意向排它 (IX) 以及与意向排它共享 (SIX)。

但现在发现：页和表的意向排它 (IX)锁会堵塞别的事务企图在该表中获得行锁，意义同表锁类似呀！


虽然
SQL SERVER 2000的资料说：
说明  意向排它 (IX) 锁与 IX 锁模式兼容，因为 IX 表示打算更新一些行而不是所有行。还允许其它事务读取或更新部分行，只要这些行不是其它事务当前所更新的行即可。
但实际上好象不是这样。

enhydraboy

行锁应该是跳过锁定的行==>不是说，让SQL Server忽略被lock的行。而是你的事务操作的行如果被锁的话，就需要等待释放。

enhydraboy

最初由 孤烟 发布
[B]行锁的意义就在于锁定的记录对其他事务不可见，不会导致其他事务的挂起而影响其他事务
拿售票系统来作为例子，当一个售票点锁定一条记录的时候，完全可能导致其他售票点无法访问所有的记录！类似问题很多。
[/B]

关于这个问题，我以前在csdn上也回答过。其实这就是oracle和sql server在这一点上的差别。
ORACLE采用了ROLLBACK的机制，保证了在READ COMMITTED模式下行记录锁定不会影响其他事务的读取（更新还是会被LOCK住的）。因此，ORACLE提供了更强的并发度。
显然，SQL SERVER简化了这个架构，自然就只能这样了。
不同数据库间，有很多差别，这些表面现象其实在于体系架构上的差别。
需要指出的一点是：
作为应用系统，应该是在编程开发上应该去适应数据库，而不是让数据库来适应编程开发。因为数据库的选型方案是不会来考虑编程的方便与否。

举个例子：
有时候，在实际应用中ORACLE的这个特性你也会发现有问题，如你的售票管理系统，一个事务已经开始处理这张票，而另外的事务却没有意识到这张票已经是ON SALE了，也会有问题。
此时，你需要在系统设计上考虑这个问题，不能只依靠数据库系统的锁定机制来解决你应用系统的逻辑问题。

enhydraboy

最初由 孤烟 发布
[B]是的，在1楼所建的表中，你执行
begin tran
  update tb with (ROWLOCK)
   set A='aa'
   where B='b2'
   waitfor delay '00:01:00'  --等待1分钟
commit tran
在控制台的锁/进程ID中都是RID,PAG,TAB三个锁一起出现！说明3个锁一起加了！


实际上当有行锁出现时，页和表都会出现意向锁。
SQL SERVER 2000的解释：
意向锁
意向锁表示 SQL Server 需要在层次结构中的某些底层资源上获取共享 (S) 锁或排它 (X) 锁。例如，放置在表级的共享意向锁表示事务打算在表中的页或行上放置共享 (S) 锁。在表级设置意向锁可防止另一个事务随后在包含那一页的表上获取排它 (X) 锁。意向锁可以提高性能，因为 SQL Server 仅在表级检查意向锁来确定事务是否可以安全地获取该表上的锁。而无须检查表中的每行或每页上的锁以确定事务是否可以锁定整个表。

意向锁包括意向共享 (IS)、意向排它 (IX) 以及与意向排它共享 (SIX)。

但现在发现：页和表的意向排它 (IX)锁会堵塞别的事务企图在该表中获得行锁，意义同表锁类似呀！


虽然
SQL SERVER 2000的资料说：
说明  意向排它 (IX) 锁与 IX 锁模式兼容，因为 IX 表示打算更新一些行而不是所有行。还允许其它事务读取或更新部分行，只要这些行不是其它事务当前所更新的行即可。
但实际上好象不是这样。 [/B]

对于这个问题，我的理解是这样的：
EXAMPLE:
BEGIN TRAN
DELETE FROM Test where c1=1
通过sp_lock查看：
spid   dbid   ObjId       IndId  Type Resource         Mode     Status
------ ------ ----------- ------ ---- ---------------- -------- ------ ------------------------------------
53     7      789577851   1      PAG  1:126                    IX       GRANT
53     7      789577851   1      KEY  (010086470766)   X        GRANT
53     7      789577851   1      PAG  1:127                    IX       GRANT
53     7      789577851   2      KEY  (090041892960)   X        GRANT
53     7      789577851   0      TAB                               IX       GRANT

我分别解释如下：
id 789577851就是表Test,可以查询sysobjects。
首先，关于TAB的IX，是表结构的意向排他锁[/COLOR] 。此时，如果你执行ALTER TABLE命令来改变表结构(会对表结构上X锁)是会被挂住的。
其次，PAG是页锁，就是索引页锁，此时为什么会有两个呢？显然1:126是索引树的中间页节点页面，而1:127是叶节点页，也就是数据页（聚集索引的表存储结构）。因此，任何对索引页上X锁的操作都会被挂住，而上IX,S不会，SQL Server会进一步判断行级锁。
最后， KEY  (010086470766)  ,KEY  (090041892960)  的两个X最明显了，就是行级独占锁。一个是索引中间页上的行级锁，一个是叶节点（数据页）上的行级锁。

enhydraboy

最初由 孤烟 发布
[B]谢谢enhydraboy的解答
我这里给出的例子是一个测试，实际应用中使用的是insert语句，并且在表中没有主键和索引的情况，一个事务进行insert操作，导致另外一个事务挂起，就我的理解，行锁应该是跳过锁定的行，而不是挂起事务。在enhydraboy的解答中也验证了其他事务访问锁定行时会导致事务挂起。
如何避免出现事务挂起，实现真正的read commited？？ [/B]

你的这种情况，相当于一个session作了insert 为提交，而另外一个事物作了select * from。

呵呵。SQL Server和ORACLE对于read committed的理解不同，SQL Server对于read committed的理解，是读取正式提交的数据（如果你的查询中包括了其他事务为提交的数据），SQL SERVER将让你等待其他提交。
ORACLE对于read committed的理解，其它事务未提交的数据对于我当前的查询是透明的，我只关心可以看到的提交后的数据。
这种差别，在select，尤其是select *上面可以反映出来。
但是，两者对于同一条数据的更新，还是采取相同的策略，等待资源释放。

孤烟

谢谢enhydraboy的热心解答，我基本上明白了SQL SERVER2000的处理方式
刚才发现有人在问sybase的相同的问题，把这个帖子顶起来。