3

In my application I want to take a value from an InnoDB table, and then increment and return it within a single transaction. I want also lock the row that i am going to update in order to prevent another session from changing the value during the transaction. I wrote this query;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
SELECT @no:=`value` FROM `counter` where name='booking' FOR UPDATE;
UPDATE `counter` SET `value` = `value` + 1 where `name`='booking';
SELECT @no;
COMMIT;

I want to know if the isolation level is right and is there any need for 'FOR UPDATE' statement. Am i doing it right?

QuickOrBeDead
  • 41
  • 4

1 Answers1

4

Yes whatever you are doing perfectly fine.

Below lines I am directly quoting from MySQL documentation.

"If you query data and then insert or update related data within the same transaction, the regular SELECT statement does not give enough protection. .. To implement reading and incrementing the counter, first perform a locking read of the counter using FOR UPDATE, and then increment the counter. For example:

SELECT counter_field FROM child_codes FOR UPDATE;
UPDATE child_codes SET counter_field = counter_field + 1;

A SELECT ... FOR UPDATE reads the latest available data, setting exclusive locks on each row it reads. Thus, it sets the same locks a searched SQL UPDATE would set on the rows.

Reference:

https://dev.mysql.com/doc/refman/5.6/en/innodb-locking-reads.html

Thiru
  • 41
  • 2