Geek Answers Handbook

MySQL InnoDB: difference between `FOR UPDATE` and` LOCK IN SHARE MODE`

What is the exact difference between the two reading comments:

SELECT ... FOR UPDATE

and

 SELECT ... LOCK IN SHARE MODE

And why do you need to use one over the other?

+6
source share
1 answer

I tried to understand the difference between the two. I will document what I found in the hope that it will be useful for the next person.

Both LOCK IN SHARE MODE and FOR UPDATE do not guarantee that no other transaction can update the selected rows. The difference between the two is how they handle locks when reading data.

LOCK IN SHARE MODE does not prevent another transaction from reading the same row that was locked.

FOR UPDATE prevents other blocking reads of the same line (uncommitted reads can read this line, LOCK IN SHARE MODE and FOR UPDATE are read records).

This matters in cases such as updating counters, where you read the value in the 1st row and update the value in the other. Here, using LOCK IN SHARE MODE , allow 2 transactions to read the same initial value. Therefore, if the counter was increased by 1 for both transactions, the final account can only increase by 1 - since both transactions initially read the same value.

Using FOR UPDATE would block the second transaction from reading the value to the first. This will increase the counter by 2.

+14
source

More articles:


All Articles