Simultaneous READ COMMITTED transactions updates a select for update columns

Question

I have a READ COMMITTED transaction in which I am trying to update a row and increment a column value till it reaches a bucket value contained in another column. Please see the code below

CREATE PROCEDURE `testproc`()
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
    ROLLBACK;
    SIGNAL SQLSTATE 'ERR0R' SET MESSAGE_TEXT = 'An error has occurred, operation rollbacked and the stored procedure was terminated' ;
    END;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

--- SOME LOGIC HERE ---
START TRANSACTION;
IF(SELECT IF(CurrentSize<BucketSize,TRUE,FALSE) FROM TestTable WHERE id = someId)
THEN
--- SOME INSERTIONS HERE ---
IF(SELECT IF(CurrentSize=BucketSize-1,TRUE,FALSE) FROM TestTable WHERE id = someId FOR UPDATE)
THEN
UPDATE TestTable SET CurrentSize = CurrentSize+1 WHERE id = someId;
--- SOME LOGIC HERE ---
ELSE
UPDATE TestTable SET CurrentSize = CurrentSize+1 WHERE id = someId;
END IF;

--- BELOW IS ADDED AS A EXTRA PRECAUTION ---
IF((SELECT COUNT(1) FROM TestTable WHERE CurrentSize > BucketSize AND id = someId) > 0)
THEN
ROLLBACK;
END IF;
END IF;
END

This works fine under normal load. But when we reach a high traffic I don't know for what reason the above logic fails and The CurrentSize column reaches a greater value than BucketSize.

It would be great if someone could help me with the same.

Answer 1

You can try to modify the testproc in the following way. I have tryed to be more simple as possible:

DELIMITER $$
CREATE PROCEDURE `testproc`()
BEGIN    
  START TRANSACTION;
  select 'try to acquire lock';
  SELECT CurrentSize, BucketSize into @cursize, @bucketsize 
  FROM TestTable WHERE id = 1 FOR UPDATE;
  select 'acquired lock';  -- only one thread is permitted here
  select sleep(1);  -- some work here
  IF @cursize < @bucketsize THEN
    select concat('CurrentSize < BucketSize (', @cursize, '<', @bucketsize, ')');
    UPDATE TestTable SET CurrentSize = CurrentSize+1 WHERE id = 1;
  ELSE
    select concat('CurrentSize >= BucketSize (', @cursize, '>', @bucketsize, ')');
  END IF;
  COMMIT;  
  select 'lock released';
END $$
DELIMITER ;

The main point is: only one thread can get a lock on the row. Only after the lock is acquired, it can update the counter. (You can use double check design pattern for performance reason but it is a little more complex). You don't need to read uncommited transactions.

To get an insight, I have called testproc from two or more mysql clients in a loop. So I have defined a loop:

create table TestTable( id int primary key, CurrentSize int, BucketSize int ) engine=innodb;
insert into TestTable (id, CurrentSize, BucketSize) values (1, 1, 5);

delimiter $$
create procedure test_loop()
begin
   declare i int default 30;
   while i >0 do
     call testproc();
     set i = i -1;
   end while;
end $$
delimiter ;

I have launched two mysql clients:

mysql1> call test_loop();

mysql2> call test_loop();

mysql1> select * from TestTable;
+----+-------------+------------+
| id | CurrentSize | BucketSize |
+----+-------------+------------+
|  1 |           5 |          5 |
+----+-------------+------------+
1 row in set (0.00 sec)

You can try to comment out FOR UPDATE into the testproc:

SELECT CurrentSize, BucketSize into @cursize, @bucketsize 
FROM TestTable WHERE id = 1;  -- FOR UPDATE;

If you try several times the previous test, you could get 6 into CurrentSize column.

mysql1> call test_loop();

mysql2> call test_loop();

mysql1> select * from TestTable;
+----+-------------+------------+
| id | CurrentSize | BucketSize |
+----+-------------+------------+
|  1 |           6 |          5 |
+----+-------------+------------+
1 row in set (0.00 sec)

Hope it is help.