SQL Server : select的锁和独占类型

Question

假设我们有一个只有3列的表Users。

UserId - primary key clustered int
Username - nvarchar(50)
CityId - int, which has a non-unique non-clustered index ( mouthful ).

如果我在一个选项卡中使用排它锁和begin tran执行select and：

declare @CityId int = 10

begin tran
    SELECT * 
    FROM [dbo].Users WITH (XLOCK,INDEX (MyCityIndex))  
    WHERE CityId = @CityId 

请注意，我并没有提交或回滚事务。

然后在另一个选项卡中(是的，另一个tab，否则SQL将执行一些优化，但将不起作用)，我运行：

declare @CityId int = 10

begin tran
    SELECT * 
    FROM [dbo].Users WITH (XLOCK, INDEX (MyCityIndex))  
    WHERE CityId = @CityId 

如果我使用相同的CityId (10)，它显然会阻塞。但我猜如果我使用CityId = 11会发生什么

declare @CityId int = 11

看起来不错。

但如果我执行索引提示搜索，它就会工作，否则它就不会工作，即使我放入CityId = 11，它也会阻塞并等待其他tran，它将失败。

这里发生什么事情？

当它使用排它锁读取数据时，执行全表扫描，然后一旦它命中一个被锁定的记录，它会被阻塞吗？真的？

如果索引碎片化，并命中不包含其'id‘的实际被阻塞的行，会发生什么情况。这也会阻塞吗？

在进行测试时，请使用两个不同的选项卡来选择，因为这里有SQL Server优化。

http://sqlblog.com/blogs/louis_davidson/archive/2006/12/13/does-xlock-always-prevent-reads-by-others.aspx

我使用的是SQL Server 2014

Answer 1

不同的行为意味着不同的执行计划。如果许多用户具有相同的城市，SQL Server可能会选择聚集索引扫描，因为这样做效率更高，因为您选择的是所有列。当扫描在另一个会话的行上遇到不兼容的锁时，扫描将被阻止。

我认为，如果显式地INCLUDE MyCityIndex定义中的其他2列，就可以避免这个提示。或者，在CityId之后将其他3列添加到MyCityIndex键列表中。

下面的示例使用MyCityIndex查找，即使没有提示也不阻塞，因为它只涉及请求的CityId值。如果删除INCLUDE子句，则会执行聚集索引扫描，并且第二个查询将被阻塞。

CREATE TABLE dbo.Users(
      CityId int
    , UserID int
        CONSTRAINT PK_Users PRIMARY KEY CLUSTERED
    , OtherData int
    );
CREATE INDEX MyCityIndex ON dbo.Users(CityId) INCLUDE(UserID, OtherData);
GO

INSERT INTO dbo.Users VALUES(1,1,1);
INSERT INTO dbo.Users VALUES(1,2,1);
INSERT INTO dbo.Users VALUES(1,3,1);
INSERT INTO dbo.Users VALUES(10,4,1);
INSERT INTO dbo.Users VALUES(10,5,1);
INSERT INTO dbo.Users VALUES(10,6,1);
INSERT INTO dbo.Users VALUES(11,7,1);
INSERT INTO dbo.Users VALUES(11,8,1);
INSERT INTO dbo.Users VALUES(11,9,1);
UPDATE STATISTICS dbo.Users;
GO

--run this on session 1
DECLARE @CityId int = 10;
BEGIN TRAN
      --SELECT * FROM dbo.Users with (XLOCK,INDEX (MyCityIndex))  where CityId= @CityId 
    SELECT * FROM dbo.Users with (XLOCK) WHERE CityId= @CityId;
--ROLLBACK;
GO

--run this on session 2
DECLARE @CityId int = 11;
BEGIN TRAN
      --SELECT * FROM dbo.Users with (XLOCK,INDEX (MyCityIndex))  where CityId= @CityId 
    SELECT * FROM dbo.Users with (XLOCK) WHERE CityId= @CityId;
--ROLLBACK;
GO