MySQL InnoDB: Difference Between `FOR UPDATE` and `LOCK IN SHARE MODE`

Question

What is the exact difference between the two locking read clauses:

SELECT ... FOR UPDATE

and

SELECT ... LOCK IN SHARE MODE 

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

Solution 1

I have been trying to understand the difference between the two. I'll document what I have found in hopes it'll be useful to the next person.

Both LOCK IN SHARE MODE and FOR UPDATE ensure no other transaction can update the rows that are selected. The difference between the two is in how they treat locks while reading data.

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

FOR UPDATE prevents other locking reads of the same row (non-locking reads can still read that row; LOCK IN SHARE MODE and FOR UPDATE are locking reads).

This matters in cases like updating counters, where you read value in 1 statement and update the value in another. Here using LOCK IN SHARE MODE will allow 2 transactions to read the same initial value. So if the counter was incremented by 1 by both transactions, the ending count might increase only by 1 - since both transactions initially read the same value.

Using FOR UPDATE would have locked the 2nd transaction from reading the value till the first one is done. This will ensure the counter is incremented by 2.

Solution 2

For Update --- You're informing Mysql that the selected rows can be updated in the next steps(before the end of this transaction) ,,so that mysql does'nt grant any read locks on the same set of rows to any other transaction at that moment. The other transaction(whether for read/write )should wait until the first transaction is finished.

For Share- Indicates to Mysql that you're selecting the rows from the table only for reading purpose and not to modify before the end of transaction. Any number of transactions can access read lock on the rows.

Note: There are chances of getting a deadlock if this statement( For update, For share) is not properly used.

Solution 3

Either way the integrity of your data will be guaranteed, it's just a question of how the database guarantees it. Does it do so by raising runtime errors when transactions conflict with each other (i.e. FOR SHARE), or does it do so by serializing any transactions that would conflict with each other (i.e. FOR UPDATE)?

FOR SHARE (a.k.a. LOCK IN SHARE MODE): Transactions face a higher probability of failure due to deadlock, because they delay blocking until the moment an update statement is received (at which point they either block until all readlocks are released, or fail due to deadlock if another write is in progress). However, only one client blocks and eventually succeeds: the other clients will fail with deadlock if they try to update, so only one of them will succeed and the rest will have to retry their transactions.

FOR UPDATE: Transactions won't fail due to deadlock, because they won't be allowed to run concurrently. This may be desirable for example because it makes it easier to reason about multi-threading if all updates are serialized across all clients. However, it limits the concurrency you can achieve because all other transactions block until the first transaction is finished.

Pro-Tip: As an exercise I recommend taking some time to play with a local test database and a couple mysql clients on the command line to prove this behavior for yourself. That is how I eventually understood the difference myself, because it can be very abstract until you see it in action.

Comments

[Anatolii Stepaniuk]

According to dev.mysql.com/doc/refman/5.7/en/innodb-locking-reads.html "at least one of them ends up in deadlock" - not incrementing counter by 1 instead of 2 as you stated. Any Ideas why deadlock can occur?

[michalburger1]

If two transactions lock the row in SHARE mode then neither one will be able to update the row at all - updates are not allowed while other transactions are holding locks. What will really happen in this situation is that one of the two will time out, release its lock, and then the other one will successfully update the row. So on one hand, the database will stay consistent, but on the other hand, you will end up with an unnecessary failure.

[NeverEndingQueue]

In which case what would be the practical use case of the SHARE mode lock if you can't really update the row due to deadlocking if there is other transaction holding the SHARE mode lock as well? Is it only if you don't intend to UPDATE/DELETE, but want to rely on read consistency?

[karagog]

The answer is mostly correct, but if there is such a deadlock between two transactions using FOR SHARE, then one transactions will succeed and the other won't. If you try the given example with a test database and interactive mysql clients, you will observe the following behavior: Both transactions will read counter value 1, but only one of them will succeed in updating it to value 2. Whichever gets to the update first will block due to the read lock in the other transaction. When the other transaction tries to update it will fail due to deadlock, then the first will unblock and succeed.

[Doin]

You can still get deadlocks with FOR UPDATE if (say) you have 2 concurrent transactions that try to lock the same two rows (or index ranges, etc) and they do them in different order (i.e. trans1 locks row1, trans2 locks row2, and then each tries to lock the other row => deadlock, one of the transactions will time out).

[ZhengguanLi]

Can you elaborate more on "LOCK IN SHARE MODE does not prevent another transaction from reading the same row that was locked."? Because normal select can read the row in both cases as I can see, as they are non-locking, does not acquire the lock.