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!
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
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.
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.
