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.
testproc
in a loop? In a high concurrency system if you have threads that are updating a counter, you must insert some kind of barrier when the counter reach the value you want. Maybe you can useget_lock()
mysql function. Could you tell more about what you are trying to do?