本文介绍了SQL Server，误导性的XLOCK &优化的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
从我最近所做的一些测试和阅读来看，XLOCK 的X”(专有)名称部分似乎具有误导性.事实上，它不会比 UPDLOCK 锁定更多.如果它是独占的，它将阻止外部 SELECT，但它不会.
XLOCK 创建排他锁的唯一时间是与 TABLOCK 一起使用时.我的第一个问题是为什么只有这个粒度?”
with (paglock,XLOCK) 那么这个 将 停止读取事务作为页面上的
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.
X locks vs
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
lock compatibility matrix http://i.msdn.microsoft.com/ms186396.LockConflictTable(en-us,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
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.
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 &优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，