SQL Server，误导性的 XLOCK &优化

Question

从我最近所做的一些测试和阅读来看，XLOCK 的“X”（独占）名称部分似乎具有误导性。事实上，它并不比 UPDLOCK 多加锁。如果它是独占的，它将阻止外部 SELECT，但事实并非如此。

我无法从阅读或测试中看出两者之间的区别。

XLOCK 唯一一次创建独占锁是与 TABLOCK 一起使用时。 我的第一个问题是“为什么只在这个粒度？”

此外，我遇到了博客 声明以下内容：

但是，请注意 XLOCK 提示。 SQL Server 将有效地忽略 XLOCK 提示！有一个优化，SQL Server 检查自最旧的打开事务以来数据是否已更改。如果不是，则忽略 xlock。这使得 xlock 提示基本上毫无用处，应该避免。

有人遇到过这种现象吗？

根据我所看到的，似乎应该忽略这个提示。

Answer 1

X 锁与 U 锁的排他性

在 可以看出，X锁仅兼容schema稳定性和Insert Range-Null锁类型。 U 与以下附加共享锁类型兼容 S/IS/RS-S/RI -S/RX-S

锁兼容性矩阵 http://i.msdn.microsoft.com/ms186396.LockConflictTable(en-us,SQL.105).gif

X 锁的粒度

这些各级都被罚款。下面的脚本和探查器跟踪演示了它们已在行级别成功取出。

CREATE TABLE test_table (id int identity(1,1) primary key, col char(40))

INSERT INTO test_table
SELECT NEWID() FROM sys.objects

select * from test_table with (rowlock,XLOCK) where id=10

但行仍然可以读取！

事实证明，在已提交读隔离级别，SQL Server 并不总是会取出S锁，如果不存在读取未提交数据的风险，它将跳过此步骤。这意味着不能保证永远发生锁冲突。

但是，如果初始选择是with (paglock,XLOCK)，则此将停止读取事务，因为页面上的X锁将阻止读取事务。 IS 读者始终需要的页面锁定。这当然会对并发产生影响。

其他注意事项

即使您锁定行/页，这并不意味着您阻止对表中该行的所有访问。聚集索引中的行上的锁不会阻止查询从覆盖非聚集索引中的相应行读取数据。

Answer 2

这不是一个警告，而是对 SELECT 中发生的情况的误解。

如果页面不包含脏数据，则单纯的 SELECT 不会请求共享锁，因此不会被 XLOCK 阻塞。

要被 XLOCK 阻止，需要在 REPEATABLE READ 隔离级别下运行。有两件事可以触发：

1. 通过 INSERT/UPDATE/DELETE 修改数据。更新的表不必是 XLOCK 所在的表。
2. 通过事务隔离级别或表提示显式请求 REPEATABLE READ。

Answer 3

根据@Martin的回答中的评论，这是小脚本（在不同的 SSMS 窗口中运行不同的部分来测试阻止 SELECT 的锁定：

--
--how to lock/block a SELECT as well as UPDATE/DELETE on a particular row
--

--drop table MyTable
--set up table to test with
CREATE TABLE MyTable (RowID int primary key clustered
                     ,RowValue int unique nonclustered not null) 

--populate test data
;WITH InsertData AS
(
    SELECT 4321 AS Number
    UNION ALL
    SELECT Number+1
        FROM InsertData
        WHERE Number<9322
)
INSERT MyTable
        (RowID,RowValue)
    SELECT
        Number, 98765-Number
        FROM InsertData
        ORDER BY Number
    OPTION (MAXRECURSION 5001)

-----------------------------------------------------------------------------
-- #1
--OPEN A NEW SSMS window and run this
--
--create lock to block select/insert/update/delete
DECLARE @ID int

BEGIN TRANSACTION

SELECT @ID=RowID FROM MyTable WITH (ROWLOCK, XLOCK, HOLDLOCK) WHERE RowID=6822
PRINT @ID

--COMMIT  --<<<only run the commit when you want to release the lock
          --<<<adfter opening the other new windows and running the SQL in them



-----------------------------------------------------------------------------
-- #2
--OPEN A NEW SSMS window and run this
--
--shows how a select must wait for the lock to be released
--I couldn't get SSMS to output any text while in the trnasaction, even though
--it was completing those commands (possibly buffering them?) so look at the
--time to see that the statements were executing, and the SELECT...WHERE RowID=6822
--was what was where this script is blocked and waiting
SELECT GETDATE() AS [start of run]
SELECT '1 of 2, will select row',* FROM MyTable Where RowID=6822
go
DECLARE @SumValue int
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT GETDATE() AS [before transaction, shouldn't be nuch difference]
BEGIN TRANSACTION
SELECT @SumValue=SUM(RowID) FROM MyTable WHERE ROWID<6000
SELECT GETDATE() AS [in transaction, shouldn't be much difference]
    , @SumValue AS SumValue
--everything to here will run immediately, but the select below will wait for the
-- lock to be removed
SELECT '2 of 2, will wait for lock',* FROM MyTable Where RowID=6822
SELECT GETDATE() AS [in transaction after lock was removed, should show a difference]
COMMIT


-----------------------------------------------------------------------------
-- #3
--OPEN A NEW SSMS window and run this
--
--show how an update must wait
UPDATE MyTable SET RowValue=1111 WHERE RowID=5000  --will run immediately
GO
UPDATE MyTable SET RowValue=1111 WHERE RowID=6822 --waits for the lock to be removed

-----------------------------------------------------------------------------
-- #4
--OPEN A NEW SSMS window and run this
--
--show how a delete must wait
DELETE MyTable WHERE RowID=5000 --will run immediately
go
DELETE MyTable WHERE RowID=6822  --waits for the lock to be removed