Sample Header Ad - 728x90

Serializable Transactions vs SELECT FOR UPDATE in MySQL

-1 votes
1 answer
1957 views
I have a stock table with columns: id, available, sold, status fields. Before increasing sold field I have to check if sold + 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. `START TRANSACTION; 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; COMMIT;` 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.
Asked by Shahin (65 rep)
Nov 19, 2019, 06:44 AM
Last activity: Nov 19, 2019, 02:22 PM