在SQL Server中的存储过程开始时获取更新表锁 [英] Obtain Update Table Lock at start of Stored Procedure in SQL Server

问题描述

我正在编写一个SQL Server存储过程，在该存储过程中，我想在执行存储过程的主体之前锁定表以进行更新.我不想阻止其他进程读取该表，但确实希望阻止其他进程更新该表.

I'm writing a SQL Server stored procedure in which I want to lock a table for update before executing the body of the stored procedure. I don't want to prevent other processes from reading the table, but I do want to prevent other processes updating the table.

这是我的第一次尝试:

CREATE PROCEDURE someProcedure
BEGIN
   SET TRANSACTION ISOLATION LEVEL READ COMITTED
   BEGIN TRANSANCTION
     SELECT COUNT(*) FROM TheTable WITH (UPDLOCK, TABLOCK)

     -- Pause procedure so that we can view the locks with sp_lock
     WAITFOR DELAY '00:15'

     -- Do stuff
   COMMIT
END

当我执行存储过程并调用sp_lock时，我看到该表确实被锁定了.但是，它是用排他锁而不是更新锁来锁定的:

When I execute the stored procedure, and invoke sp_lock, I see that the table is indeed locked. However, it's locked with an Exclusive lock instead of an update lock:

spid | dbid | ObjId     | IndId | Type | Resource | Mode | Status
------------------------------------------------------------------
63   | 10   | 233208031 | 0     | TAB  |          | X    | GRANT

我该如何获取更新(U)锁?

How can I get an update (U) lock instead?

推荐答案

您说的是

我不想阻止其他人 从读取表开始的过程，但是 我确实想防止其他进程 更新表格.

I don't want to prevent other processes from reading the table, but I do want to prevent other processes updating the table.

您只需要在TXN期间共享读取锁即可.这意味着没有其他进程可以与TABLOCK一起获得写"锁.而且您也不需要COUNT.

You simply need a shared read lock for the duration of the TXN. This means no other process can get a "write" lock, in conjunction with a TABLOCK. And you don't need COUNT either.

...
   BEGIN TRANSANCTION
     SELECT TOP 1 KeyCol FROM TheTable WITH (TABLOCK, HOLDLOCK)
...

为什么您认为需要更新锁?

Why do you think you want an UPDATE LOCK?

保持锁定或可序列化

等效于SERIALIZABLE.欲了解更多 信息，请参阅后面的SERIALIZABLE 这个话题. HOLDLOCK仅适用于 所要使用的表或视图 指定且仅在 交易定义的 使用它的声明. ... 通过以下方式使共享锁更具限制性 持有它们直到交易完成 完成，而不是释放 必要时尽快共享锁 表或数据页不再 是否需要交易 完成与否.

Is equivalent to SERIALIZABLE. For more information, see SERIALIZABLE later in this topic. HOLDLOCK applies only to the table or view for which it is specified and only for the duration of the transaction defined by the statement that it is used in. ... Makes shared locks more restrictive by holding them until a transaction is completed, instead of releasing the shared lock as soon as the required table or data page is no longer needed, whether the transaction has been completed or not.

在评论后进行

• 排他锁"的意思是只有一个进程使用数据".
• "SERIALIZABLE"基本上是指将锁定(共享，独占或其他)锁定的时间更长.

您不能指定排他锁" ，并且允许其他进程读取.这些概念是互斥的.您想防止写入整个表，而持久化共享/读取锁将这样做.这是SERIALIZABLE出现的地方.

You can't specify "exclusive lock" and allow other processes to read. The concepts are mutually exclusive. You want to prevent writes to the entire table, which a persisted shared/read lock will do. This is where SERIALIZABLE comes in.

来自锁定模式"

共享锁

...没有其他 交易可以在修改数据的同时 共享(S)锁存在于 资源.共享(S)锁 资源会在 读取操作完成，除非 事务隔离级别设置为 可重复阅读或更高，或 锁定提示用于保留 共享(S)锁的持续时间为 交易.

...No other transactions can modify the data while shared (S) locks exist on the resource. Shared (S) locks on a resource are released as soon as the read operation completes, unless the transaction isolation level is set to repeatable read or higher, or a locking hint is used to retain the shared (S) locks for the duration of the transaction.

因此:共享锁禁止写入，并且可以通过使其可序列化来使其持久化

这篇关于在SQL Server中的存储过程开始时获取更新表锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！