在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

你说过：

我不想阻止其他进程读取表，但我确实想阻止其他进程更新表。

在TXN期间，你只需要一个共享的读锁。这意味着任何其他进程都不能获得与TABLOCK相结合的“写”锁。而且你也不需要计数。

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

为什么你认为你想要一个更新锁？

HOLDLOCK or SERIALIZABLE

等同于SERIALIZABLE。有关详细信息，请参阅本主题后面的SERIALIZABLE。HOLDLOCK仅适用于为其指定了HOLDLOCK的表或视图，并且仅适用于使用它的语句定义的事务的持续时间。..。通过持有共享锁直到事务完成，而不是在不再需要所需的表或数据页时立即释放共享锁，而不管事务是否已完成，从而使共享锁具有更强的限制性。

编辑，注释后：

独占锁“

• ”的意思是“只有一个使用data".
• "SERIALIZABLE”的进程基本上意味着持有锁(共享、独占等)的时间更长。“

您不能指定“独占锁”并允许其他进程读取。这两个概念是相互排斥的。您希望防止对整个表进行写操作，持久化的共享/读锁定就可以做到这一点。这就是SERIALIZABLE的用武之地。

来自"Lock Modes"

共享锁

当资源上存在共享(S)锁时，...No其他事务可以修改数据。资源上的共享(S)锁在读取操作完成后立即释放，除非将事务隔离级别设置为可重复读取或更高级别，或者使用锁定提示在事务持续时间内保留共享(S)锁。

SERIALIZABLE So:共享锁不允许写入，可以通过将其设置为来使其持久化

Answer 2

表格锁定

指定获取表上的锁并将其保持到语句结束。如果正在读取数据，则采用共享锁。如果正在修改数据，则会采用排它锁。如果还指定了HOLDLOCK，则在事务结束之前一直持有共享表锁。

当与OPENROWSET大容量行集提供程序一起使用以将数据导入没有索引的表时，TABLOCK使多个客户端能够通过优化的日志记录和锁定将数据并发加载到目标表中。

摘自：http://msdn.microsoft.com/en-us/library/ms187373(SQL.90).aspx