I have a simple MySQL table with 3 columns id, balance and locked. I have a requirement where the locked column's value must be increased by x, sometimes by multiple processes running at the same time, such that balance - locked must always be at least x. The query for the same is as below:

UPDATE tablename  
SET locked = (locked + x)  
WHERE id = 1  
    AND balance - locked >= x;

After the locked count is increased, the process does some job, and then comes back and reduces the balance and the locked counts using:

UPDATE tablename  
SET balance = balance - x  
    , locked = locked - x  
WHERE id = 1;

The idea is to get lock for x items, then do some processing, and then reduce the balance by x and release the locks.

The issue I have is that after running the multiple processes for some time, at one point the balance column goes negative. What could be causing this issue?

I am aware of the race condition, and that is why I have the check in the where clause. The value still goes negative, even when balance is 2, and there are only 2 threads trying to lock 1 balance each.

I do check that the first update actually updates a row. Only if the first query affected rows is 1, then I go an do the processing work, and then reduce the locked counts. I ensure locked is never negative by having update query as

update tablename set locked = IF(locked - x > 0, locked - x, 0) where id = 1;

The error cause is a race condition that is happening, because your query was not immune to it.
There are ways to code your queries to avoid it (atomic update, transactions, etc.).
Because you did not use table locking or transactions, you ended up with an error when your query read an updated value from another thread. This produced a negative value.

Proposed solution:

Issue both SELECT and UPDATE in a single statement to avoid race condition, as you have tried, however, select from underlying table into a session variable.
That way, you can have control of the time when you select a value, and not get value from another thread.

I have rewritten your code to use user-defined, session variables.

Update 1 rewritten:

-- @blnc and @lckd are user-defined variables  
UPDATE tablename  
SET locked = (@lckd + x)  
WHERE id = 1  
    AND ((@blnc := balance) - (@lckd := locked)) >= x;

Update 2 rewritten:

-- @blnc and @lckd are user-defined variables  
UPDATE tablename  
SET balance = (@blnc := balance) - x  
    , locked = (@lckd := locked) - x  
WHERE id = 1;
  • With above sqls, how do you make sure the value of the locked column is consistent when multiple threads/processes are hitting the same sql? – Rajesh Jul 31 '18 at 9:44
  • @Rajesh - by doing a combination of Atomic Update and reading into session variables, as described in my proposed solution text. – Milan Jul 31 '18 at 10:11
  • @Rajesh - Have you had the chance to check with your QA team? Is the issue fixed? – Milan Aug 4 '18 at 12:37
  • Yeah. The issue appears fixed with the changes I did. – Rajesh Aug 4 '18 at 14:08
  • @Rajesh - Good. If it is OK with you that it is a little slower, because of holding a lock. You can replace the select...for update with the part with session variables from my answer, and it should be even faster then with ...for update – Milan Aug 4 '18 at 14:41

I've coded the logic to first lock the row using select...for update and then update the column values using an update query, all within a transaction. I hope that should take care of the multi threading issue. Waiting for few more tests to be done by my QA team.

  • yes, it should, but it will be a little slower, because of holding a lock. The way in my answer should be even faster then ...for update – Milan Jul 31 '18 at 9:09

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.