什么时候UPDLOCK在SQL服务器上发布？

Recently I have gone through with Hints and Locks in SQL server. While google about this topic I have read one blog where some query have been written which I am not bale to understand. Here it is

最近我在SQL服务器中使用了Hints和Locks。虽然谷歌关于这个话题，我已经阅读了一个博客，其中一些查询已经写入，我不是很难理解。这里是

BOL states: Use update locks instead of shared locks while reading a table, and hold locks until the end of the statement or transaction. I have some trouble translating this. Does this mean that the update locks are released after the execution of the SELECT statement, unless the SELECT statement in within a transaction?

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

I other words, are my assumptions in the following 2 scenario's correct?

换句话说，我在以下2个场景中的假设是正确的吗？

Scenario 1: no transaction

场景1：没有交易

SELECT something FROM table WITH (UPDLOCK)

/* update locks released */

Scenario 2: with transaction

场景2：使用交易

BEGIN TRANSACTION 
SELECT something FROM table WITH (UPDLOCK)

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

/* update locks released */

Example for scenario 2 (referred for * blog)

场景2的示例（参考*博客）

BEGIN TRAN

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

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

COMMIT TRAN

Please help to understand the above query.

请帮助理解上面的查询。

My second question is: once execution of select statement completed at same time UPDLOCK get released or not?

我的第二个问题是：一旦执行select语句同时UPDLOCK被释放了吗？

2 个解决方案

#1

Your assumption in scenario 2 is correct.

您在方案2中的假设是正确的。

To answer your second question, no. The Update locks are held on the selected row(s) until the transaction ends, or until converted to exclusive locks when the update statement modifies those row(s). Step through each statement one at a time using SSMS to verify.

要回答你的第二个问题，不。更新锁保留在所选行上，直到事务结束，或者直到更新语句修改这些行时转换为排它锁。使用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.

As for what is going on in scenario 1, all T-SQL statements exist either in an implicit or explicit transaction. Senario 1 is implicitly:

至于方案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.

#2

Does this mean that the update locks are released after the execution of the SELECT statement, unless the SELECT statement in within a transaction?

这是否意味着在执行SELECT语句后释放更新锁，除非事务中的SELECT语句？

The locks will be released as soon as the row is read..but the lock hold will be U lock,so any parallel transaction trying to modify this will have to wait

一旦读取行就会释放锁定。但是锁定保持将是U锁定，因此任何试图修改它的并行事务都必须等待

if you wrap above select in a transaction,locks will be released only when the transaction is committed,so any parallel transaction acquiring locks incompatible with U lock will have to wait

如果你在事务中选择上面的选择，只有在提交事务时才会释放锁，因此任何与U锁不兼容的并行事务获取锁必须等待

begin tran
select * from t1 with (updlock)

for the below second scenario

对于以下第二种情况

BEGIN TRANSACTION 
SELECT something FROM table WITH (UPDLOCK)

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

Imagine, if your select query returned 100 rows,all will use U lock and imagine the update in same transaction affects 2 rows,the two rows will be converted to x locks .so now your query will have 98 u locks and 2 xlocks until the transaction is committed

想象一下，如果您的选择查询返回100行，则所有将使用U锁并想象同一事务中的更新会影响2行，这两行将转换为x锁。现在您的查询将具有98 u锁和2 xlocks直到交易已提交

I would like to think Updlock as repeatable read,any new rows can be added,but any parallel transaction can't delete or update existing rows

我想将Updlock视为可重复读取，可以添加任何新行，但任何并行事务都无法删除或更新现有行

#1