none
Key-range Lock疑问 RRS feed

  • 问题

  • 今天测试SET TRANSACTION ISOLATION LEVEL SERIALIZABLE(版本2008 R2 RTM),有几个疑问

      

     CREATE TABLE TEST (C1 INT,C2 VARCHAR(20))

     INSERT INTO test VALUES (1,'TEST'),(2,'TEST'),(3,'EST')

      --Create clustered index

     ALTER TABLE [dbo].[TEST] ADD  CONSTRAINT [PK_TEST] PRIMARY KEY CLUSTERED

    (

          [C1] ASC

    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

    GO

     --第一个窗口查询

     SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

     BEGIN TRAN

     SELECT * FROM test WHERE C1 BETWEEN 1 AND 3

     --第二个窗口执行插入

      INSERT INTO test VALUES (100,'TEST')

    插入的语句无法完成,一直在等待。如果我将第一条查询语句COMMIT第二条语句成功执行。 按照MSDN上的说法,应该只对RANG 1,2,3加锁,但是我插入的是100啊,为什么也被BLOCK。查了一下SP_LOCK发现查询还加了PAGE LOCK,为什么?




    2013年1月18日 2:05

答案

  • 查看 select * from [sys].[dm_tran_locks] 可以看到有一行的 resource_description 为 (ffffffffffff)  ,
    也就是说它的键范围锁是1,2,3到无穷大,而并不少单纯的1,2,3到。

    如果再插入一行   INSERT INTO test VALUES (4,'EST') ,再次运行上面的session2就不会被阻塞。

    键范围锁的实际范围为键范围+到下一个NEXT值之间的值(包含NEXT值);

    如果Key-range 有一个具体的next 值,则被锁定的范围为
          Key-range +到下一个NEXT值之间的值(包含NEXT值); ,
          否则为 Key-range 到无穷大。

    这可以解释你上面的为什么插入 session1插入 INSERT INTO test VALUES (4,'EST')  后就不会阻塞session2了



    Please click the Mark as Answer button if a post solves your problem!

    2013年1月19日 15:55

全部回复

  • MSDN上面,哪个地方说了这种情况会只用Key Lock的?

    你要是插入1000条记录,然后再做这个测试,insert就不会被锁定了。


    想不想时已是想,不如不想都不想。

    2013年1月18日 3:18
    版主
  • 那MSDN是怎么说的呢?能不能给指点一二,我只是想知道什么情况下会使用RANG LOCK,什么时候会升级到PAGE LOCK。
    2013年1月18日 5:27
  • SQL Server 数据库引擎使用动态锁定策略,这种策略能够在大多数情况下自动为查询选择最佳锁定粒度。这个算法在不同版本里面有细微的差别。 另外,Range lock其实比Page lock的范围要大。

    想不想时已是想,不如不想都不想。

    2013年1月18日 6:54
    版主
  •  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
     
     BEGIN TRAN 
    
     SELECT * FROM test WHERE C1 BETWEEN 1 AND 3
    

    当执行查询的时候SQL已经在表上加了一个意向共享锁,除非你提交事务,否则其他事务都要等待

    另外不知道LZ在哪里看到的range lock


    给我写信: QQ我:点击这里给我发消息

    2013年1月18日 17:12
  • --表 数据库引擎可以锁定的资源
    --资源                               说明
    --RID                        用于锁定堆(heap)中的某一行
    --KEY                        用于锁定索引上的某一行,或者某个索引键
    --PAGE                       锁定数据库中的一个8KB页,例如数据页或索引页
    --EXTENT                     一组连续的8页(区)
    --HOBT                       锁定整个堆或B树的锁
    --TABLE                      锁定包括所有数据和索引的整个表
    --FILE                       数据库文件
    --APPLICATION                应用程序专用的资源
    --METADATA                   元数据锁
    --ALLOCATION_UNIT            分配单元
    --DATABASE                   整个数据库


    --在此基础上,SQL使用不同的锁模式锁定资源,这些锁模式确定了并发事务访问资源
    --的方式

    --表 数据库引擎使用的资源锁模式
    --锁模式                                 说明
    --共享(S)                         用于不更改或不更新数据的读取操作 ,如select语句  在可重复读或可序列化事务中,一个修改需要先读取数据[获取资源(页或行)的共享锁]  
                      
    --更新(U)                         用于可更新的资源中,防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁
    --排他(X)                         用于数据修改操作,例如insert update delete,确保不会同时对一个资源进行多重更新                
    --意向 (I)intent                  用于建立锁的层次结构,意向锁包含三种类型:意向共享(IS)意向排他(IX)意向排他共享(SIX)
    --架构   稳定stable                 在执行依赖于表架构的操作时使用,架构锁包含两种类型:架构修改(sch-m)和架构稳定性(sch-s)
    --大容量更新(BU) bulk insert ,bcp 在向表进行大容量数据复制并且指定了tablock提示时使用
    --键范围                            当使用可序列化事务隔离级别时保护查询读取的行的范围,确保再次运行查询时其他
    --事务无法插入符合可序列化事务的查询的行


    --共享锁:共享锁(S锁)允许并发事务在封闭式并发控制下读取select资源。资源上存在共享锁(S锁)时,任何其他事务都不能修改数据


    --更新锁:在可重复读或可序列化事务中,一个修改需要先读取数据[获取资源(页或行)的共享锁(S锁)],
    --然后修改数据[此操作要求锁转换为排他锁(X锁)。]如果两个事务获得了同一个资源上的共享模式锁,然后试图
    --同时更新数据,则事务会把共享锁转换为排他锁。由于两个事务都要转换为排他锁(X锁),并且每个事务都必须等待
    --另一个事务释放共享锁之后才能得到排他锁,以至于两个事务都无法完成转换,因此发生死锁
    --为了避免这种潜在的死锁问题,SQL使用更新锁(U锁)。一次只有一个事务可以获得资源的更新锁。事务真正修改数据
    --时,将更新锁(U锁)转换为排他锁(X锁)


    --排他锁:排他锁(X锁)可以防止并发事务对资源进行访问。使用排他锁(X锁)时,任何其他事务都无法读取或者修改数据;仅在
    --使用NOLOCK提示或未提交读隔离级别时才会进行读取操作
    --数据修改语句(如insert,update,delete)合并了修改和读取select操作。语句在执行所需的修改操作之前首先执行读取操作以获取数据。
    --因此,数据修改语句通常请求共享锁和排他锁。例如:update语句可能根据与一个表的联接修改另一个表中的行。在此情况下,除了请求
    --更新行上的排他锁之外,update语句还将请求在联接表中读取的行上的共享锁


    --意向锁:数据库引擎使用意向锁来保护锁层次结构的底层资源,以防止其他事务对自己锁住的资源造成伤害,提高锁冲突检测性能
    --例如:当读取表里的页面时,在请求页共享锁(S锁)之前,事务在表级请求共享意向锁。这样可以防止其他事务随后在表上获取
    --排他锁(X锁),修改整个表格。意向锁可以提高性能,因为数据库引擎仅在表级检查意向锁,确定事务是否能安全地获取该表上
    --的锁,而不需要检查表中的每行或每页上的锁以确定事务是否可以锁定整个表


    --架构锁:数据库引擎在表数据定义语言(DDL)操作(例如添加列或删除表)的过程中使用架构修改(sch-m)锁,阻止其他用户
    --对这个表格的访问
    --数据库引擎在编译和执行查询时使用架构稳定(sch-s)锁,稳定stable。sch-s锁不会阻止其他事务访问表格里的数据。但是,
    --会阻止对表格做修改性的DDL操作和DML操作


    --大容量更新锁:数据库引擎在将大容量复制到表中时使用大容量更新(BU)锁,并指定tablelock提示或使用sp_tableoption
    --设置table lock on bulk load表选项。大容量更新锁(BU)锁允许多个线程将数据并发地大容量加载到同一张表,同时
    --防止其他不进行大容量加载数据的进程访问该表 bulk insert ,bcp


    --键范围锁:在使用可序列化(serializable)事务隔离级别时,对于TSQL语句读取的记录集,键范围锁可以隐式包含该记录集中
    --包含的行范围。键范围锁可防止幻读。通过保护行之间键的范围,他还可防止对事务访问的记录集进行幻插入或删除


    给我写信: QQ我:点击这里给我发消息

    2013年1月18日 17:12
  • LZ你哪个操作SQL应该会申请这几种锁
    --KEY                        用于锁定索引上的某一行,或者某个索引键
    --PAGE                       锁定数据库中的一个8KB页,例如数据页或索引页
    --TABLE                      锁定包括所有数据和索引的整个表
    --DATABASE                   整个数据库

    在SSMS窗口一里执行

    --查询一:
    USE [AdventureWorks]
    GO
    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
    GO
    SET STATISTICS PROFILE ON
    GO
    --以下查询使用了聚集索引查找 ctrl+l
    BEGIN TRAN
    SELECT [EmployeeID],[LoginID],[Title] FROM [dbo].[Employee_Demo_BTree] WHERE [EmployeeID] IN(3,30,200)

    在窗口二里执行

    --查看DMV看一下有多少个锁-----------------------------------------------------------
    USE [AdventureWorks] --要查询申请锁的数据库
    GO
    SELECT
    [request_session_id],
    c.[program_name],
    DB_NAME(c.[dbid]) AS dbname,
    [resource_type],
    [request_status],
    [request_mode],
    [resource_description],OBJECT_NAME(p.[object_id]) AS objectname, 
    p.[index_id]
    FROM sys.[dm_tran_locks] AS a LEFT JOIN sys.[partitions] AS p
    ON a.[resource_associated_entity_id]=p.[hobt_id] 
    LEFT JOIN sys.[sysprocesses] AS c ON a.[request_session_id]=c.[spid]
    WHERE c.[dbid]=DB_ID('AdventureWorks')  ----要查询申请锁的数据库
    ORDER BY [request_session_id],[resource_type]


    给我写信: QQ我:点击这里给我发消息

    2013年1月18日 17:19
  • 感谢桦仔的热心参与,不过Range Lock确实是存在的,而且是很重要的。楼主的查询由于范围太小,所以不会用到Range lock。

    详情我就不多介绍了,有兴趣的话自己看联机丛书。


    想不想时已是想,不如不想都不想。

    2013年1月19日 13:52
    版主
  • 查看 select * from [sys].[dm_tran_locks] 可以看到有一行的 resource_description 为 (ffffffffffff)  ,
    也就是说它的键范围锁是1,2,3到无穷大,而并不少单纯的1,2,3到。

    如果再插入一行   INSERT INTO test VALUES (4,'EST') ,再次运行上面的session2就不会被阻塞。

    键范围锁的实际范围为键范围+到下一个NEXT值之间的值(包含NEXT值);

    如果Key-range 有一个具体的next 值,则被锁定的范围为
          Key-range +到下一个NEXT值之间的值(包含NEXT值); ,
          否则为 Key-range 到无穷大。

    这可以解释你上面的为什么插入 session1插入 INSERT INTO test VALUES (4,'EST')  后就不会阻塞session2了



    Please click the Mark as Answer button if a post solves your problem!

    2013年1月19日 15:55
  • 根据jackie shen大侠所说的确是这样 明白key range lock 什么意思就是锁定键值的范围


    给我写信: QQ我:点击这里给我发消息

    2013年1月19日 18:00
  • 我把他改为

    SELECT [EmployeeID],[LoginID],[Title] FROM [dbo].[Employee_Demo_BTree] WHERE [EmployeeID] BETWEEN 3 AND 200

    一眼看上去有100多个key ,但是没有发现resource_description 为 (ffffffffffff) 的  不知道怎样才能出现,LZ的情况应该跟我一样,只有3个key 吧  BETWEEN 1 AND 3


    给我写信: QQ我:点击这里给我发消息


    2013年1月19日 18:02
  • 不好意思,LZ

    应该分堆表和聚集表来考虑

    你的是聚集表,所以不受意向共享锁的影响

    无论是update insert delete 受影响的是所在数据键值的key  page


    给我写信: QQ我:点击这里给我发消息

    2013年1月20日 3:09
  • 今天 测试了一下 没出现resource_description 为 (ffffffffffff) 的情况

    如果jackie shen大侠能能给出一个例子就最好了


    给我写信: QQ我:点击这里给我发消息

    2013年1月20日 3:12
  • 今天 测试了一下 没出现resource_description 为 (ffffffffffff) 的情况

    如果jackie shen大侠能能给出一个例子就最好了


    给我写信: QQ我:点击这里给我发消息


    一楼不就是例子嘛


    Please click the Mark as Answer button if a post solves your problem!

    2013年1月20日 6:02
  • 查看 select * from [sys].[dm_tran_locks] 可以看到有一行的 resource_description 为 (ffffffffffff)  ,
    也就是说它的键范围锁是1,2,3到无穷大,而并不少单纯的1,2,3到。

    如果再插入一行   INSERT INTO test VALUES (4,'EST') ,再次运行上面的session2就不会被阻塞。

    键范围锁的实际范围为键范围+到下一个NEXT值之间的值(包含NEXT值);

    如果Key-range 有一个具体的next 值,则被锁定的范围为
          Key-range +到下一个NEXT值之间的值(包含NEXT值); ,
          否则为 Key-range 到无穷大。

    这可以解释你上面的为什么插入 session1插入 INSERT INTO test VALUES (4,'EST')  后就不会阻塞session2了



    Please click the Mark as Answer button if a post solves your problem!

    是不是这句话啊:

    注意注意

    包含的 RangeS-S 锁数量为 n+1,此处 n 是满足查询条件的行数。

    http://msdn.microsoft.com/zh-cn/library/ms191272(v=sql.105).aspx

    2013年1月22日 4:25
  • 你再看一下我上面给出的那篇文章讲的很详细,为什么不能插入100,另外Jackie Shen的解释也是正确的。
    2013年1月23日 1:01