SQL Server, the misleading XLOCK amp; optimizations(SQL Server，误导性的XLOCK amp;优化)

问题描述

从我最近所做的一些测试和阅读来看，XLOCK 的X"(专有)名称部分似乎具有误导性.事实上，它不会比 UPDLOCK 锁定更多.如果它是独占的，它将阻止外部 SELECT，但它不会.

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

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

此外，我发现了一个 .这意味着无法保证会发生锁冲突.

然而，如果初始选择是 with (paglock,XLOCK) 那么这个 将 停止读取事务作为页面上的 X 锁将阻止阅读器始终需要的 IS 页面锁定.这当然会对并发产生影响.

其他注意事项

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

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.

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:

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.

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

lock compatibility matrix http://i.msdn.microsoft.com/ms186396.LockConflictTable(en-us,SQL.105).gif

Granularity of X locks

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

But rows can still be read!

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.

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.

Other Caveats

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 &优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！