I have a simple MySQL table with 3 columns
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
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;
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
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;