带 UPDLOCK 的 HOLDLOCK [英] HOLDLOCK with UPDLOCK

问题描述

在一个事务(例如 T1)中使用 HOLDLOCK 或 UPDLOCK 似乎不会阻止来自另一个事务(例如 T2)的读取访问.

It appears using either HOLDLOCK or UPDLOCK in a transaction (say T1), will not block read access from another transaction (say T2).

据我所知，在 T1 完成之前，HOLDLOCK 将阻止 T2 更新/删除；并且 UPDLOCK 将阻止 T2 更新/删除/插入.在这两个 T2 中，都可以读取这些记录.

As I understand, until T1 completes, HOLDLOCK will prevent updates/deletes by T2; and a UPDLOCK will prevent updates/deletes/inserts by T2. In both these T2 will have read access to these records.

但是，使用两者(例如:HOLDLOCK、UPDLOCK)甚至会阻止 T2 进行读取访问.当我们同时使用它们时会发生什么?

But, using both (like: HOLDLOCK, UPDLOCK) blocks T2 from even a read access. What exactly happens when we use both of them?

感谢您的见解

这不是我所看到的:

例如:

在查询 1 中:

begin tran

select * from tblTest WITH (UPDLOCK, HOLDLOCK)

WAITFOR DELAY '00:00:10'

commit tran

在查询 2 中:

select * from tblTest 

在查询 1 完成之前，查询 2 不会产生结果.

Query 2 does not yield results until Query 1 finishes.

推荐答案

UPDLOCK 影响锁的类型.这意味着对于 SELECT 语句将采用 U 锁而不是 S 锁.在默认读取提交级别，它们将在读取数据后立即释放.

UPDLOCK affects the type of lock. It means for a SELECT statement that U locks will be taken rather than an S lock. At default read committed level they will be released as soon as the data is read.

以上适用于行锁和页锁.对于表级锁 BOL 状态

The above applies to row and page locks. For table level locks BOL states

如果UPDLOCK和TABLOCK结合使用，或者是采取了表级锁由于某些其他原因，将采用排他 (X) 锁.

If UPDLOCK is combined with TABLOCK, or a table-level lock is taken for some other reason, an exclusive (X) lock will be taken instead.

HOLDLOCK 意味着您获得了可序列化的隔离语义，因此直到事务结束才会释放锁，并且至少您的查询所涵盖的整个范围都将被锁定以防止插入幻影.

HOLDLOCK means that you get serializable isolation semantics so the locks won't be released until the end of the transaction and at least the whole range covered by your query will be locked to prevent insert of phantoms.

U 锁与其他 S 锁兼容，但与其他 U 锁不兼容(请参阅 锁兼容性矩阵)，因此如果在行或页级别取出锁，则不会阻止其他读者除非他们也使用 UPDLOCK 提示.

A U lock is compatible with other S locks but not other U locks (See Lock compatibility matrix) so if the locks were taken out at row or page level this will not block other readers unless they too use the UPDLOCK hint.

如果对象级别的 X 锁因 UPDLOCK 而被解除，那么读者将在尝试获取 IS 时被阻止 锁在桌子上.在您的示例查询中，尝试查看 sys.dm_tran_locks 而第二个查询被阻止以查看两个事务拥有/正在等待的锁.

If an object level X lock is taken out due to UPDLOCK however then readers will be blocked trying to acquire an IS lock on the table. In your example query try looking at sys.dm_tran_locks whilst the second query is blocked to see what locks both transactions have / are waiting for.

对于您问题中的查询

SELECT *
FROM   tblTest WITH (UPDLOCK, HOLDLOCK) 

如果查询计划显示对堆进行扫描，您将始终获得对象上的 X 锁.如果是索引扫描，则取决于使用的锁定粒度(通常在 至少 5,000 个较低级别的锁被采用).

You will always get an X lock on the object if the query plan shows a scan on a heap. If it is an index scan it depends upon the locking granularity used (lock escalation to table level is generally attempted after at least 5,000 lower level locks are taken).

这篇关于带 UPDLOCK 的 HOLDLOCK的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！