0

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.

  • Do you call the 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 use get_lock() mysql function. Could you tell more about what you are trying to do? – Giovanni May 29 '15 at 13:02
  • Hi @Giovanni Thanks for the comment. I am not running the above proc in a loop. I believe SELECT .. FOR UPDATE does a similar thing to get_lock() (correct me if I am wrong here). Basically I am trying to maintain a bucket which when fills creates a new bucket. I have just shown the simple logic of incrementing counter which should halt on reaching its max value. But it fails under high concurrency. – amit1310 Jun 1 '15 at 6:15
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.

| improve this answer | |
  • Hi @Giovanni, Thanks for the effort you put into it but I guess I have found out the issue which was cause of the above behavior. What I didn't show or mention in my question was that I had multiple ROLLBACK in my logic which executed in my SP and I also expected the SP to quit after my ROLLBACK statement. Well I found out that it is not possible without using a label and a LEAVE statement. So now I have added the same to my SP and load tested it but I am still observing the behavior under actual load. Keeping fingers crossed. Thanks alot for the help again. – amit1310 Jun 5 '15 at 5:37
  • @amit1310 - please update the code in your question to indicate the ROLLBACK flaw. Otherwise readers will get confused until the read the fine print here. – Rick James Jun 7 '15 at 17:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.