SQL Server表锁定和解锁问题

发布于 2024-12-01 10:56:13 字数 675 浏览 4 评论 0原文

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

我有一个包含数据的表，如下所示： DVDSerialNumbers(ID, Create_Date, SerialNumber, CategoryID)

我的应用程序生成 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 返回我的选择查询的结果，但删除命令也被执行。

基本上我得到的序列号不超过 n 个具有给定 CategoryID 的序列号，并且我删除了它们的行。

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

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

预先感谢您的帮助。

原文

I have a very interesting problem.

I have a table where I have data, like this:
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):

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.

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

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.

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?

Thanks in advance for your help.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

黑凤梨 2024-12-08 10:56:13

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

然后您需要一个用于选择/删除的事务。相反，您可以使用 OUTPUT 子句在一条语句中执行此操作

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

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

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

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

回复收藏 0 原文

~没有更多了~