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.