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.


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


Add the 'available' check? And didn't you mean to use the same table?

UPDATE stock
    set sold = sold + 4
    where id = 1
    AND sold + x <= available;

The FOR UPDATE say "This process may change this row(s); keep your hands off." and "If any other process is busy mucking with this row(s), I should wait."

If the WHERE clause on the UPDATE fails, then the update won't be performed. So put the business logic (eg, test against available) there.

The BEGIN and COMMIT delineate how long this process is being possessive about this row(s).

  • Yes, the whole operation happens in one table. Will AND sold + x <= available be enough if multiple queries try to change the same row? I know updates are atomic but I am not sure how it will work out with concurrent requests. – Shahin Nov 19 '19 at 7:21
  • @Shahin - The FOR UPDATE blocks changes to that row until the transaction finishes. – Rick James Nov 19 '19 at 7:22
  • I noticed my mistake in question body, fixed. Yes, I just need to increase/decrease one column - sold - with id = 1. But this field stores sensitive data, it has to be less and equal than available field. I am not sure the update query without SELECT ... FOR UPDATE will work in that case. Shouldn't I just lock that row and allow MySQL to perform only one operation at the same time? – Shahin Nov 19 '19 at 7:31
  • @Shahin - The FOR UPDATE provides the lock. See what I added to my Answer. – Rick James Nov 19 '19 at 14:24
  • I understand your point, your query will check sold + x <= available condition if true then process it. Is it possible that MySQL could update the field in the middle of the process? I mean, to update something internally MySQL has to pull the data then update it, right? In the middle of this process, another query cannot change that field, right? – Shahin Nov 19 '19 at 18:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.