显式锁定SQL Server中的表? [英] Explicit lock to table(s) in SQL Server?

问题描述

是否可以在SQL Server中对表进行显式共享锁定?

Is it possible to do an explicit shared lock on a table in SQL Server?

我需要执行以下操作:

• 共享锁表
• 将表复制到临时表以进行操作
• 排他锁表
• 将内容从tempTable复制到表
• 释放所有锁

基本上，我想确保在对temp表进行处理时，什么都没有添加到表中，但我仍然希望它是可读的.

Basically, I want to be sure that nothing is added to the table, while I'm doing stuff to the temp table, but I still want it to be readable.

由于某种原因，将整个事情放在事务中会导致我对temp表所做的某些工作陷入僵局，所以这不是一个选择.

For some reason, putting the whole thing in a transaction causes a deadlock in some of the stuff I do to the temp table, so that is not an option.

推荐答案

持有表锁的唯一方法是保持事务打开.因此，我们可能需要有关此在我对临时表所做的某些工作中的僵局"的更多信息.

The only way to hold table locks is to keep a transaction open. So we may need more information about this "deadlock in some of the stuff I do to the temp table".

根据任务的不同，临时表可能根本不是必需的-听起来您是从过程的思想而非基于集合的思想中构建事物的.

Depending on the task, the temp table may not be necessary at all - it sounds like you're building something from a procedural mindset rather than a set-based one.

您概述的基本草图是:

/* Create temp table here */
BEGIN TRANSACTION
INSERT INTO #Table (/* Columns */)
SELECT /* Columns */ FROM SourceTable WITH (UPDLOCK,HOLDLOCK)

/* Work within temp table here */
UPDATE st
SET
   /* Set new column values based on temp table */
FROM
   SourceTable st
       inner join
   #Table t
       on
           /* Key column join here */
COMMIT

您需要指定一个UPDATE锁而不是共享锁，这实际上是现在是共享锁，但我打算稍后在同一事务中排他"，以防止可能的死锁.

You need to specify an UPDATE lock rather than a shared lock, which is effectively "A shared lock now, but I intend to go exclusive later on within the same transaction", to prevent a possible deadlock.

这篇关于显式锁定SQL Server中的表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！