I have a stock table with columns:
id, available, sold, status fields. Before increasing
sold field I have to check if
x is less and equal than
available. To make sure every transaction uses the freshest data and not allow other transactions to modify it I used
SELECT ... FOR UPDATE.
SELECT * FROM stock WHERE id=1 FOR UPDATE;
--some backend logic here check if sold + x <= available is true update else unlock --
UPDATE stock set sold = sold + x where id = 1;
But I am not really sure if I am doing it right. I looked into MySQL documentation and old questions, read about isolation levels. Should I set the transaction isolation level as
serializable or it is totally redundant?
I am using MySQL 8.0.17.