sql-server-2005 - 在 SQL Server 中的存储过程开始时获取更新表锁

Question

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

这是我的第一次尝试:

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 时，我看到表确实被锁定了。但是，它是用排他锁而不是更新锁锁定的:

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

我怎样才能获得更新 (U) 锁？

Answer 1

你说:

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。

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

为什么你认为你需要更新锁？

HOLDLOCK or SERIALIZABLE

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 的用武之地。

来自 "Lock Modes"

Shared Locks

...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.

因此:共享锁不允许写入，并且可以通过使其可串行化来持久化