SQL Server，误导性的XLOCK&最佳化 [英] SQL Server, the misleading XLOCK & optimizations

问题描述

根据我最近做的一些测试和阅读，似乎XLOCK的"X"(专有)名称部分具有误导性.实际上，它的锁定范围不只是UPDLOCK.如果它是排他性的，它将阻止外部SELECT，而不会.

From some recent testing and reading I've done, it seems the "X" (exclusive) name part of XLOCK is misleading. It in fact doesn't lock any more than UPDLOCK. If it were exclusive, it would prevent external SELECTs, which it doesn't.

从阅读或测试中我都看不到两者之间的差异.

I cannot see either from reading or from testing and difference between the two.

XLOCK唯一创建排他锁的时间是与TABLOCK一起使用时. 我的第一个问题是为什么只在这种粒度下?"

The only time XLOCK creates an exclusive lock is when used with TABLOCK. My first question is "why only at this granularity?"

此外，我遇到了博客，其中指出以下内容:

Further, I came across a blog that states the following:

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

However, watch out for XLOCK hint. SQL Server will effectively ignore XLOCK hint! There's an optimization where SQL Server check whether the data has changed since the oldest open transaction. If not, then an xlock is ignored. This makes xlock hints basically useless and should be avoided.

有人遇到过这种现象吗?

Has anyone run across this phenomenon?

根据我所看到的，似乎应该忽略此提示.

Based on what I'm seeing, it seems this hint should be ignored.

推荐答案

X锁与U锁的排他性

在下面的锁兼容性列表中，可以看到X锁仅与架构稳定性和插入范围-空锁类型兼容. U与以下其他共享锁类型兼容S/IS/RS-S/RI-S/RX-S

Exclusivity of X locks vs U locks

In the lock compatibility matrix below it can be seen that the X lock is only compatible with the schema stability and Insert Range-Null lock types. U is compatible with the following additional shared lock types S/IS/RS-S/RI-S/RX-S

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

这些在各个级别上都可以正常删除.下面的脚本和事件探查器跟踪演示了它们已在行级别成功删除.

These are taken out fine at all levels. The script and profiler trace below demonstrates them being successfully taken out at row level.

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锁，

It turns out that at read committed isolation level SQL Server will not always take out S locks, it will skip this step if there is no risk of reading uncommitted data without them. This means that there is no guarantee of a lock conflict ever occurring.

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

However if the initial select is with (paglock,XLOCK) then this will stop the reading transaction as the X lock on the page will block the IS page lock that will always be needed by the reader. This will of course have an impact on concurrency.

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

Even if you lock the row/page this does not mean that you block all accesses to that row in the table. A lock on a row in the clustered index will not prevent queries reading data from the corresponding row in a covering non clustered index.

这篇关于SQL Server，误导性的XLOCK&最佳化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！