SQL Server 并发事务问题 [英] SQL Server concurrent transaction issue

问题描述

我有一个方法，它通过以下查询启动事务:

I have a method where it starts a transaction with the following queries:

INSERT INTO order_item (item_no, order_id)
SELECT TOP " + Quantity + " item_no, @order_id 
FROM items where status = 'Unused'

一旦执行了第一个插入命令，我想更新插入到 order_item 中的 items 表中的所有 item_no上一个命令:

once the first insert command has been executed, I want to update all the item_no in the items table that was inserted into order_item in the previous command:

UPDATE items (select item_no from order_item where order_id = @order_id) 
SET status = 'Used'

我担心如果其他交易在现有交易正在运行时开始，他们可能会选择应该标记为已使用"的套件编号，因为可能会提交第一个交易以将项目标记为已使用".

I am worried that if other transaction starts while an existing one is running, they could potentially choose kit numbers that supposed to be marked as 'Used' since the first transaction might be committed for the items to be marked as "Used".

如果有人能就这个问题提供一些建议，我们将不胜感激.

Would appreciate if anyone can give some advice on this issue.

谢谢！

推荐答案

使用一些可组合的 DML 将这一切合而为一?

Do it all in one with some composable DML?

INSERT INTO order_item (item_no, order_id)
SELECT 
    X.item_no, @order_id
FROM
    (
    MERGE INTO items AS tgt
    USING
      (SELECT TOP (@whatever) item_no
       FROM items
       WHERE status = 'Unused'
      ) AS src ON tgt.item_no = src.item_no
    WHEN MATCHED
      UPDATE SET status = 'Used'
      OUTPUT $action as action, item_no -- $action needed for more complex stuff
    ) AS X
-- WHERE action = 'UPDATE' -- needed for more complex stuff

这篇关于SQL Server 并发事务问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！