Mysql – Why is column value going negative even with checks

concurrencyMySQLupdate

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;

Best Answer

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;
Related Question