SQL Server concurrent transaction issue

Question

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'

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.

Thanks!

Answer 1

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

Answer 2

Assuming you're using a version of SQL Server that supports the OUTPUT clause (2005 or later), I'd reverse the operations:

DECLARE @Items table (item_no int /* Or varchar?, Whatever suits */ not null)
DECLARE @Quantity int

SET @Quantity = 5 /* or however this gets set */

UPDATE TOP (@Quantity) items SET status = 'Used'
OUTPUT inserted.item_no INTO @Items (item_no)
WHERE status = 'Unused'

INSERT INTO order_item (item_no,order_id)
SELECT item_no,@order_id from @Items

That way, your first statement (the UPDATE) is both selecting items and marking them as unavailable atomically.

Answer 3

If i understand your problem right, you have an inventory of items and you have a table of ordered items.

If that is true, I think you should re-think this ordering mechanism. I would prefer a design like bank account and transactions. You debit from accounts and add a entry in transactions table in same db transction. I don't really like this "unused" flag system.