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
Last activity: Nov 19, 2019, 02:22 PM