0

Each item (stock) in the inventory is modelled as a row. 

stock
=====
code: primary key
itemType: foreign key to items table
available: boolean

So to reduce stock what is done is something like: 

UPDATE stock SET available = false where code = 'something' and available = true

This is done in a transaction. So it looks like: 

BEGIN TRANSACTION;
UPDATE stock SET available = false where code = 'something1' and available = true;
UPDATE stock SET available = false where code = 'something2' and available = true;
UPDATE stock SET available = false where code = 'something3' and available = true;
COMMIT;

I suspect a race condition is possible. If I have 2 queries like the above running at approximately the same time, would they both succeed?

Jiew Meng
  • 84,767
  • 185
  • 495
  • 805

1 Answers1

0

They will not be done exactly at the same time (for one database).

Only one by one.

1 - succeed. (affected rows MAYBE > 0)

2 - not succeed. (affected rows = 0)

So everithing is ok.

BTW there is nothing in your query that can break things if even both succeedd at the same time (if it will be possible)...

M0rtiis
  • 3,676
  • 1
  • 15
  • 22
  • RE: "*there is nothing in your query that can break things if even both succeed*". If both succeeds, problem is I would have given the same item twice. Ok but good to know this is not the problem – Jiew Meng Sep 01 '15 at 06:24
  • sorry? UPDATE can not give u any item at all i guess? only change its properties? INSERT - yea, may give u some new item – M0rtiis Sep 01 '15 at 06:43
  • in this case, available is like a flag indicating availability of an item. If both updates are successful, its like saying its possible to give an item twice. Hope its clearer? – Jiew Meng Sep 01 '15 at 07:00
  • if "getting an item logic" depends on affected rows of update-query - yes. but this will not happen – M0rtiis Sep 01 '15 at 08:18