sql-server - 何时在 SQL Server 中释放 UPDLOCK？

Question

最近我研究了 SQL Server 中的提示和锁。在谷歌搜索这个主题时，我读了一个博客，其中写了一些我不太理解的查询。在这里

BOL 状态:在读取表时使用更新锁而不是共享锁，并持有锁直到语句或事务结束。我在翻译这个时遇到了一些麻烦。这是否意味着更新锁在 SELECT 语句执行后被释放，除非 SELECT 语句在一个事务中？

换句话说，我在以下两种情况下的假设是否正确？

场景一:无交易

SELECT something FROM table WITH (UPDLOCK)

/* update locks released */

场景二:有交易

BEGIN TRANSACTION 
SELECT something FROM table WITH (UPDLOCK)

/* some code, including an UPDATE */
COMMIT TRANSACTION

/* update locks released */

场景 2 的示例(引用 stackoverflow 博客)

BEGIN TRAN

SELECT Id FROM Table1 WITH (UPDLOCK)
WHERE AlertDate IS NULL;

UPDATE Table1 SET AlertDate = getutcdate() 
WHERE AlertDate IS NULL;

COMMIT TRAN 

请帮助理解上面的查询。

我的第二个问题是:一旦 select 语句的执行同时完成，UPDLOCK 是否被释放？

Answer 1

您在场景 2 中的假设是正确的。

回答你的第二个问题，没有。更新锁一直保留在选定的行上，直到事务结束，或者直到更新语句修改这些行时转换为独占锁。使用 SSMS 一次单步执行每条语句以进行验证。

BEGIN TRAN
    -- execute sp_lock in second session - no locks yet
    SELECT Id FROM Table1 WITH (UPDLOCK) WHERE AlertDate IS NULL;
    -- execute sp_lock in second session - update locks present
    UPDATE Table1 SET AlertDate = getutcdate() WHERE AlertDate IS NULL;
    -- execute sp_lock in second session - update (U) locks are replace by exclusive locks (X) for all row(s) returned by SELECT and modified by the UPDATE (Lock Conversion).
    -- Update locks (U) continue to be held for any row(s) returned by the SELECT but not modified by the UPDATE
    -- exclusive locks (X) are also held on all rows not returned by SELECT but modified by UPDATE. Internally, lock conversion still occurs, because UPDATE statements must read and write.
COMMIT TRAN 

    -- sp_lock in second session - all locks gone.

至于场景 1 中发生的情况，所有 T-SQL 语句都存在于隐式或显式事务中。 Senario 1 是隐含的:

BEGIN TRAN
     SELECT something FROM table WITH (UPDLOCK)
     -- execute sp_lock in second session - update locks (U) will be present
     COMMIT TRAN;
     -- execute sp_lock in second session - update locks are gone.