SQL Server 2008:选择更新 [英] SQL Server 2008: SELECT FOR UPDATE

问题描述

我在这里看到了一个与此相关的问题，但是它已经很老了，所以如果现在存在解决方案，我将再次询问.

I have seen a question on here about this however it was old so I will ask again in case a solution now exists.

我的问题是这个.我有一个数据库表，希望从中选择，但是我想锁定选择的行.这样做的原因是，我可能正在运行另一个进程，该进程也希望选择相同的行，但我想避免这种情况.

My issue is this. I have a database table which I wish to select from but I want to lock the rows that I have selected. The reason for this is that I may have another process running that will also want to select the same rows and I want to prevent this.

想象一下，我有两个进程在做同一件事.一个执行选择并开始执行其数据处理.然后几秒钟后，执行下一个过程并进行选择，但是由于未锁定行，因此它也会获取相同的记录并开始处理它们.当然，这是一个糟糕的情况.在Oracle中，您可以使用SELECT FOR UPDATE，它将对行进行锁定，以防止第二个进程使用它们.在SQL Server 2008中如何实现?

Imagine I have two processes doing the same thing. One performs a select and begins to perform its processing of the data. Then a few seconds later the next process comes along and does a select but because the rows aren't locked it also takes the same records and begins to process them. This is of course a bad situation to be in. In Oracle you can use SELECT FOR UPDATE which will take out a lock on the rows to prevent them from being used by the 2nd process. How can this be achieved in SQL Server 2008?

我应该补充一点，我只能使用标准的sql语句.我无权访问过程，函数等.必须通过简单的语句来完成.这是一个漫长的故事，而设计方面的考虑已经从我手中夺走了.解决方案必须能够存储在表中，以后再检索，然后通过C#中的ADO对象(特别是分配给命令对象的对象)运行.

I should add that I can only use standard sql statements. I do not have access to procedures, functions etc. It has to be done via a simple statement. Its a long story and a design consideration that has been taken out of my hands. The solution must be able to be stored in a table, retrieved later and then run via the ADO objects in C# in particular assigned to a command object.

如何将锁应用于此语句?

How can a lock be applied to this statement?

SELECT * 
FROM 
  (SELECT TOP (20) * 
   FROM [TMA_NOT_TO_ENTITY_QUEUE]  
   WHERE [TMA_NOT_TO_ENTITY_QUEUE].[STATE_ID] = 2 
   ORDER BY TMA_NOT_TO_ENTITY_QUEUE.ID) a

推荐答案

您需要使用所谓的 更新锁可防止其他进程尝试更新或删除相关行-但不会阻止读取访问:

The update lock prevents other processes from attempting to update or delete the rows in question - but it does not prevent read access:

    SELECT TOP (20) * 
    FROM [TMA_NOT_TO_ENTITY_QUEUE] WITH (UPDLOCK)
    WHERE [TMA_NOT_TO_ENTITY_QUEUE].[STATE_ID] = 2 
    ORDER BY TMA_NOT_TO_ENTITY_QUEUE.ID

还有一个排他锁，但基本上，更新锁就足够了.选择具有更新锁定的行后，将保护"这些行以防止更新和写入，直到事务结束.

There's also an exclusive lock, but basically, the update lock should be enough. Once you've selected your rows with an update lock, those rows are "protected" against updates and writes until your transaction ends.

这篇关于SQL Server 2008:选择更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！