none
在默认事务隔离等级下,在获得X LOCK的资源上既然还能获得 S LOCK,奇了怪了。 RRS feed

  • 问题

  • 1,我创建如下脚本,并且插入数据,

    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [dbo].[Table_2](
    [a] [int] NOT NULL,
    [b] [nchar](10) NULL,
    CONSTRAINT [PK_Table_2] PRIMARY KEY CLUSTERED
    (
    [a] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    go

    insert into Table_2 values(1,1)

    insert into Table_2 values(2,2)

    2,session A

    begin tran
    update Table_2 set b=b

    通过 select * from sys.dm_tran_locks 查询,确实发现有2个KEY 持有X LOCK,

    然后我在session B 运行 select * from Table_2,

    既然没有被blocked住。。。。。

    这不是明显违反了 S LOCK跟 X LOCK 不兼容的基本原则嘛, 我知道尽管b自己更新自己,值没有变,但是它确实已经持有了X LOCK了啊,为什么其他的session还能获得S LOCK啊?


    2011年11月23日 15:50

答案

  • 我记得好像很早以前讨论过类似问题,不过结论忘记了。。

    刚试了下,在我的sql server 2008 sp2下的用户库是不能锁住的。

    但在tempdb下可以锁住,不过过一段时间,tempdb也可以了。。。

    这可能和sql server设计有关,和脏读有关。

    大概与这个问题类似:

    http://www.cnblogs.com/nzperfect/archive/2010/02/25/1673667.html

     

     



    “这里就涉及到SQL Server在锁管理中的一种特殊情况,当连接64已经获得ID=10这行所在页上的IS锁时(IS和IX兼容),SQL Server会首先检查改页是否为脏页(dirty page),即该页自上一次写入磁盘后(通常是在checkpoint时发生)是否被更新过。如果该页非脏页的话,则SQL Server不需要再在行上获得S锁就可以直接去读取ID=10这行的数据,因为这时已可以保证该页中的所有数据都未在其他任何活动事务中被更新过(即不会出现脏读的问题)。这样对于这个查询语句来说,加行锁这步就被省略掉了(即使加了rowlock锁提示也是如此)。“

    谢谢,这是我真正想要的答案。不过请问这个结论是哪里得来的,官方文档上有这样的说明吗?

     

    这段话是微软工程师给我的回复。

    • 已标记为答案 Jacky_shen 2011年11月27日 11:15
    • 取消答案标记 Jacky_shen 2011年11月27日 11:17
    • 已标记为答案 Jacky_shen 2011年11月27日 11:42
    2011年11月27日 9:51

全部回复

  • Because session B didn't access that key, it did table scan. You can try with 'select * from Table_2 where b = ...'.
    2011年11月23日 16:10
  •  

     谢谢RMIAO,

    但是下面2个语句也都没有被阻塞。

    select * from Table_2 where A=1

      |--Clustered Index Seek(OBJECT:([AdventureWorks].[dbo].[Table_2].[PK_Table_2]), SEEK:([AdventureWorks].[dbo].[Table_2].[a]=CONVERT_IMPLICIT(int,[@1],0)) ORDERED FORWARD)

    很明显此query plan 访问了那个KEY (1)了。

     


      select * from Table_2 where B=1

    |--Clustered Index Scan(OBJECT:([AdventureWorks].[dbo].[Table_2].[PK_Table_2]), WHERE:(CONVERT_IMPLICIT(int,[AdventureWorks].[dbo].[Table_2].[b],0)=CONVERT_IMPLICIT(int,[@1],0)))


    2011年11月23日 16:33
  • Did you see x lock on whole key that including A and/or B or just some rows of that key? You should be able to query rows not locked.

    2011年11月23日 17:22
  • SQL Server可不傻,它根本就不执行set b=b这样的操作。
    想不想时已是想,不如不想都不想。
    2011年11月24日 3:26
    版主
  • SQL Server可不傻,它根本就不执行set b=b这样的操作。
    想不想时已是想,不如不想都不想。


    谢谢怡红公子的解答,但是我还是有几点疑问。

    1 ,如果它根本就不执行set b=b这样的操作的话,为什么我执行set b=b这样的操作后,通过select * from sys.dm_tran_locks 还是可以看到下面三个的 LOCK呢,? 如果不执行set b=b这样的操作,那它加三个LOCK上去的意图是什么呢?

    KEY  9 (8194443284a0)       72057594055426048 0 X
    PAGE  9 1:29888               72057594055426048 0 IX
    OBJECT  9                       39671189 0 IX

    2 ,并且PLAN为:  |--Clustered Index Update(OBJECT:([AdventureWorks].[dbo].[Table_2].[PK_Table_2]), SET:([AdventureWorks].[dbo].[Table_2].[b] = [AdventureWorks].[dbo].[Table_2].[b]), WHERE:([AdventureWorks].[dbo].[Table_2].[a]=CONVERT_IMPLICIT(int,[@1],0))),

    这似乎还是执行了一下这个赋值操作,当然它内部的source code我是看不到了:-(,所以我不知道它内部有没有作了什么特殊处理

     
     3,另外,我在Read Committed下执行select * from Table_2 where A=1却没有被阻塞,但是在REPEATABLE READ下却阻塞了, 通过 sys.dm_os_waiting_tasks查看,是在等待 SLOCK,这都是符合逻辑的,可我不明白,为什么在Read Committed下却没有被阻塞呢,难道Read Committed下读取数据前不需要KEY SLOCK吗?这让人很郁闷啊













    2011年11月24日 10:38
  • Did you see x lock on whole key that including A and/or B or just some rows of that key? You should be able to query rows not locked.


    是这个key的某一行上(where A=1,其中A为PRIMARY KEY CLUSTERED )获得了XLOCK,因为是CLUSTERED  index,所以这个KEY ROW应该包含 A andB吧。但是在我另外的SESSION 里查询select * from Table_2 where A=1,既然没有被阻塞,当然如果我把set b=b改为set b=b+1还是会被阻塞的。

    2011年11月24日 10:48
  • 我发现另外一个牛人也发现了我同样的问题,http://blog.csdn.net/zjcxc/article/details/4383113

    另外一个人也帮我测试了下,他说在SQL 2005跟SQL 2012上没有这样的问题,不过我的测试环境是SQL2008 R2。http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/d099d3b4-68ac-45f7-bb72-5402ba0f75c1/#1f7e8f0a-8485-4872-a943-65bf0813f7c2


    难到是SQL SERVER的一个BUG?

    2011年11月24日 11:18
  • 简单的说,SQL对于这种实际上没改数据的写操作,不会有锁。REPEATABLE READ阻塞,是因为读数据就锁住了。设计如此,不是bug。
    想不想时已是想,不如不想都不想。
    2011年11月24日 13:55
    版主
  • When you update column B, sql will not put lock on column A unless update statement filters data by column A. Therefore your update shouldn't block select that has search condition on column A.
    2011年11月24日 19:15
  • 我记得好像很早以前讨论过类似问题,不过结论忘记了。。

    刚试了下,在我的sql server 2008 sp2下的用户库是不能锁住的。

    但在tempdb下可以锁住,不过过一段时间,tempdb也可以了。。。

    这可能和sql server设计有关,和脏读有关。

    大概与这个问题类似:

    http://www.cnblogs.com/nzperfect/archive/2010/02/25/1673667.html

     

     


    • 已编辑 nicofer 2011年11月25日 9:54
    • 已标记为答案 Jacky_shen 2011年11月25日 15:18
    • 取消答案标记 Jacky_shen 2011年11月26日 3:46
    2011年11月25日 9:45
  • 我记得好像很早以前讨论过类似问题,不过结论忘记了。。

    刚试了下,在我的sql server 2008 sp2下的用户库是不能锁住的。

    但在tempdb下可以锁住,不过过一段时间,tempdb也可以了。。。

    这可能和sql server设计有关,和脏读有关。

    大概与这个问题类似:

    http://www.cnblogs.com/nzperfect/archive/2010/02/25/1673667.html

     

     



    “这里就涉及到SQL Server在锁管理中的一种特殊情况,当连接64已经获得ID=10这行所在页上的IS锁时(IS和IX兼容),SQL Server会首先检查改页是否为脏页(dirty page),即该页自上一次写入磁盘后(通常是在checkpoint时发生)是否被更新过。如果该页非脏页的话,则SQL Server不需要再在行上获得S锁就可以直接去读取ID=10这行的数据,因为这时已可以保证该页中的所有数据都未在其他任何活动事务中被更新过(即不会出现脏读的问题)。这样对于这个查询语句来说,加行锁这步就被省略掉了(即使加了rowlock锁提示也是如此)。“

    谢谢,这是我真正想要的答案。不过请问这个结论是哪里得来的,官方文档上有这样的说明吗?

    2011年11月25日 15:17
  • Don't think so, sql doesn't put s lock for reading only when you use nolock hint or set session to read uncommitted isolation.
    2011年11月25日 17:56
  • 谢谢,我同意你的观点,问题是我是在默认事务隔离登记下做的操作,也没有使用nolock hint 的情况下读取数据,却也没有发生HOLD SLOCK的事件,所以才感觉奇怪,是通过TRACE LOCK观察到的 http://blog.csdn.net/zjcxc/article/details/4383113
    2011年11月26日 3:08
  • 简单的说,SQL对于这种实际上没改数据的写操作,不会有锁。REPEATABLE READ阻塞,是因为读数据就锁住了。设计如此,不是bug。
    想不想时已是想,不如不想都不想。

    谢谢,
    问题是有锁,如果没锁的话那一切好理解了,更离谱是这个XLOCK不会阻碍别的PROCESS 去获取SLOCK(默认事务隔离等级)

    我为什么说有锁?是通过下面的测试获知的。

    执行update Table_2 set b=b where A=1 这样的操作后,通过select * from sys.dm_tran_locks 还是可以看到下面三个的 LOCK的。

    KEY 9 (8194443284a0) 72057594055426048 0 X
    PAGE 9 1:29888 72057594055426048 0 IX
    OBJECT 9 39671189 0 IX

    我认为对于这种没有改变数据的写操作,要么SQL SERVER 就不应该加XLOCK上去,如果加了XLOCK,那么其他想获取那个数据的process就应该像往常的锁获取过程一样,在获取不到SLOCK的情况下就应该被阻塞,而不是像现在那样,不需要获取SLOCK直接读取那个有XLOCK的数据了。

    我为什么说这种情况下不需要获取SLOCK直接读取那个有XLOCK的数据了?

    是通过下面 TRACE LOCK知道的。

    http://blog.csdn.net/zjcxc/article/details/4383113

     当然说BUG的话可能也谈不上,我只是认为这种设计非常的不make sense.

     顺便说一下,这里的现象是在默认事务隔离等级下做的操作















    2011年11月26日 3:13
  • 谢谢,我同意你的观点,问题是我是在默认事务隔离登记下做的操作,也没有使用nolock hint 的情况下读取数据,却也没有发生HOLD SLOCK的事件,所以才感觉奇怪,是通过TRACE LOCK观察到的 http://blog.csdn.net/zjcxc/article/details/4383113
    Unless update statement puts x lock on whole table, user still can query from table for non-locked rows.
    • 已编辑 rmiao 2011年11月26日 4:12
    2011年11月26日 4:12
  • 谢谢,我同意你的观点,问题是我是在默认事务隔离登记下做的操作,也没有使用nolock hint 的情况下读取数据,却也没有发生HOLD SLOCK的事件,所以才感觉奇怪,是通过TRACE LOCK观察到的 http://blog.csdn.net/zjcxc/article/details/4383113
    Unless update statement puts x lock on whole table, user still can query from table for non-locked rows.


    我确认那个操作没有产生table级别的XLOCK,但产生ROW级别XLOCK,因为我执行update Table_2 set b=b where A=1 后,看到了如下3个锁。

    KEY 9 (8194443284a0) 72057594055426048 0 X
    PAGE 9 1:29888 72057594055426048 0 IX
    OBJECT 9 39671189 0 IX

    2011年11月26日 4:41
  • You can try 'select b from table_2 where a = 1' and see what happens.
    2011年11月26日 5:39
  • step1 ,

    SESSION 1

    执行

    begin transaction

    update Table_2 set b=b where A=1

     

    step2 ,

    执行 select * from sys.dm_tran_locks

    产生的结果如下

    PAGE 12 1:118 72057594042056704 0 IX
    OBJECT 12 1093578934 0 IX
    KEY 12 (61a06abd401c) 72057594042056704 0 X

    step3,

    SESSION 2

    执行 select b from table_2 where a=1 跟 select * from Table_2 where a=1 都没有被阻塞。

     

     RMIAO,你方便测试一下吗?因为有些人做了这样的测试后还是发现被阻塞的,但是我在SQL2008 R2 SESSION2就是没有被阻塞,当然还有我上面提到的一些其他的人也发现没有被阻塞。


    2011年11月26日 6:18
  • 我记得好像很早以前讨论过类似问题,不过结论忘记了。。

    刚试了下,在我的sql server 2008 sp2下的用户库是不能锁住的。

    但在tempdb下可以锁住,不过过一段时间,tempdb也可以了。。。

    这可能和sql server设计有关,和脏读有关。

    大概与这个问题类似:

    http://www.cnblogs.com/nzperfect/archive/2010/02/25/1673667.html

     

     



    “这里就涉及到SQL Server在锁管理中的一种特殊情况,当连接64已经获得ID=10这行所在页上的IS锁时(IS和IX兼容),SQL Server会首先检查改页是否为脏页(dirty page),即该页自上一次写入磁盘后(通常是在checkpoint时发生)是否被更新过。如果该页非脏页的话,则SQL Server不需要再在行上获得S锁就可以直接去读取ID=10这行的数据,因为这时已可以保证该页中的所有数据都未在其他任何活动事务中被更新过(即不会出现脏读的问题)。这样对于这个查询语句来说,加行锁这步就被省略掉了(即使加了rowlock锁提示也是如此)。“

    谢谢,这是我真正想要的答案。不过请问这个结论是哪里得来的,官方文档上有这样的说明吗?

     

    这段话是微软工程师给我的回复。

    • 已标记为答案 Jacky_shen 2011年11月27日 11:15
    • 取消答案标记 Jacky_shen 2011年11月27日 11:17
    • 已标记为答案 Jacky_shen 2011年11月27日 11:42
    2011年11月27日 9:51
  • paul_white对这种现象做了更加详细的解释。

    “If I hold an exclusive lock on a row, can another transaction running at the default read committed isolation level read it?”

    http://sqlblog.com/blogs/paul_white/archive/2010/11/01/read-committed-shared-locks-and-rollbacks.aspx

    2011年11月27日 11:17
  • That's correct and it's locking range related as I said above but not 'sql doesn't put shared lock on committed rows'.
    2011年11月27日 19:06
  • That's correct and it's locking range related as I said above but not 'sql doesn't put shared lock on committed rows'.

    Thank you
    2011年11月28日 2:38