SQL Server，误导性的XLOCK和优化
从我最近做的一些测试和阅读中，似乎XLOCK的“X”（独占）名称部分是误导性的。 它实际上并不比UPDLOCK锁定更多。 如果它是独占的，它会阻止外部的SELECT，它不会。
我无法从阅读或测试中看到两者之间的差异。
XLOCK创建独占锁的唯一时间是与TABLOCK一起使用的时间。 我的第一个问题是“为什么只在这个粒度？”
此外，我遇到一个博客，其中指出以下内容：
但是，请注意XLOCK提示。 SQL Server将有效地忽略XLOCK提示！ 有一个优化，SQL Server检查自最早的打开事务以来数据是否已更改。 如果不是，则会忽略一个xlock。 这使得xlock提示基本上没用，应该避免。
有没有人遇到过这种现象？
根据我所看到的，似乎这个提示应该被忽略。
X锁与U锁的排他性
在下面的锁兼容性矩阵中，可以看出X锁只与模式稳定性和Insert Range-Null锁类型兼容。 U与以下附加的共享锁类型S / IS / RS-S / RI-S / RX-S兼容
锁定兼容性矩阵http://i.msdn.microsoft.com/ms186396.LockConflictTable（zh-cn，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
但行仍然可以阅读！
事实证明，在read committed隔离级别时，SQL Server不会始终取出S锁，如果没有读取未提交的数据的风险，它将跳过此步。 这意味着不能保证曾经发生过锁冲突。
但是，如果初始选择with (paglock,XLOCK)那么这将停止读取事务，因为页面上的X锁将阻止阅读器始终需要的IS页面锁定。 这当然会影响并发性。
其他警告
即使您锁定了行/页面，这并不意味着您会阻止对表格中该行的所有访问。 对聚簇索引中的行进行锁定不会阻止查询读取覆盖非聚簇索引中相应行的数据。
这不是一个警告，它是对SELECT中发生的事情的误解。
如果页面不包含脏数据，则SELECT不会要求共享锁定，因此不会被XLOCK阻止。
要被XLOCK阻止，您需要运行REPEATABLE READ隔离级别。 有两件事可以触发这一点：
根据@ 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
上一篇: SQL Server, the misleading XLOCK & optimizations
下一篇: Why does row level locking not appear to work correctly in SQL server?