SQL server表锁定和解锁问题

I have a very interesting problem.

我有一个非常有趣的问题。

I have a table where I have data, like this: DVDSerialNumbers(ID, Create_Date, SerialNumber, CategoryID)

我有一个表格，我有数据，如下所示：DVDSerialNumbers（ID，Create_Date，SerialNumber，CategoryID）

My application generates serial numbers for DVD's and I have a SQL command like this (issued by my application):

我的应用程序生成DVD的序列号，我有一个像这样的SQL命令（由我的应用程序发出）：

ExecuteQuery("select top {0} SerialNumber from DVDSerialNumbers where CategoryID = {1};" & 
"delete from DVDSerialNumbers where ID in (select top{0} ID from DVDSerialNumber where " & 
 "CategoryID = {1});", n, CategoryID)

ExecuteQuery returns the result of my select query but the delete command is executed too.

ExecuteQuery返回我的select查询的结果，但也执行delete命令。

Basically I get no more than n SerialNumbers which have the given CategoryID and I delete their rows.

基本上我得到的不只是具有给定CategoryID的n个SerialNumbers，我删除了它们的行。

However, here I have a concurency problem. If the code above runs twice in the same time, it's possible that the results will be the same, however, the idea is to get a given SerialNumber from the table only once.

但是，我在这里遇到了一个问题。如果上面的代码同时运行两次，那么结果可能是相同的，但是，想法是只从表中获取给定的SerialNumber一次。

How can I make instance B to wait for instance A to finish this command? Should I lock the table? Or should I lock some rows? Or is there a better solution?

如何让实例B等待实例A完成此命令？我应该锁桌子吗？或者我应该锁定一些行？或者有更好的解决方案吗？

Thanks in advance for your help.

在此先感谢您的帮助。

1 个解决方案

#1

You need READPAST, UPDLOCK, ROWLOCK hints. See this for more: SQL Server Process Queue Race Condition

您需要READPAST，UPDLOCK，ROWLOCK提示。有关更多信息，请参阅此内容：SQL Server进程队列竞争条件

Then you'd need a transaction for the SELECT/DELETE. Instead, you can do this in one statement with an OUTPUT clause

然后你需要一个SELECT / DELETE事务。相反，您可以在一个带有OUTPUT子句的语句中执行此操作

ExecuteQuery("delete top ({0})
                  DVDSerialNumbers WITH (READPAST, UPDLOCK, ROWLOCK)
              OUTPUT DELETED.SerialNumber
              where CategoryID = {1}" & 
                , n, CategoryID)

#1