Why I shouldn't use "Repeatable Read" with locking reading (select..for update)"?

Question

In the Mysql Document: "https://dev.mysql.com/doc/refman/5.7/en/innodb-deadlocks-handling.html"

It mentioned: "If you use locking reads (SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE), try using a lower isolation level such as READ COMMITTED."

Could someone tell me why I can't use "Repeatable Read"? Example will be good.

Cheers

Answer 1

InnoDB avoids using certain kinds of locks if you use read-committed. This can help you avoid a deadlock.

I designed a whole presentation about this: InnoDB Locking Explained with Stick Figures.

But you'll practically never be able to avoid 100% cases of deadlocks. They're not a mistake, they're a natural part of concurrent systems. You can reduce how frequently deadlocks occur, but you might as well get used to getting some. Design your code to catch exceptions and retry database operations when they get a deadlock.

Answer 2

The reason is that if record doesn't exist in the table it looks like SELECT ... FOR UPDATE acquires next-key lock in shared mode (or something similar - it's not documented anywhere) for an index record following a non-existent record when REPEATABLE READ isolation mode is active.

Let's try an example for a simple table t that is empty in REPEATABLE READ isolation mode.

t1> SELECT * FROM t WHERE id = 1 FOR UPDATE;
    no rows found, next-key lock acquired in shared mode
t2> SELECT * FROM t WHERE id = 1 FOR UPDATE;
    no rows found, next-key lock acquired in shared mode
t1> INSERT INTO t (id) VALUES (1);
    transaction t1 is blocked by t2
t2> INSERT INTO t (id) VALUES (1);
    transaction t2 is blocked by t1 - deadlock

The deadlock will happen even if the second SELECT and INSERT will use id=2 because it also falls into the same gap, locked by SELECT ... FOR UPDATE, executed in t1. And if the table is empty this gap is infinity. If the table is not empty the probability of deadlock is smaller for inserting different records, but is still big (it depends on how many gaps in the table and how often you insert to the end of the table - the largest gap).

This happens because SELECT ... FOR UPDATE from t1 and t2 doesn't block each other when record doesn't exist. For existent record it acquires the X (exclusive) lock on the record in t1, so t2 will be blocked until t1 is committed or rolled back. But if the record doesn't exist - it acquires the S (shared) next-key lock on the gap (I'm not sure that it is really an S lock (it is not documented anywhere), but how else MySQL allows to acquire 2 locks concurrently on the same gap?). And this is the main reason of deadlock here - both t1 and t2 trying to acquire the IX (insert intention) lock on the gap and then X lock on the inserted record, but both waiting each other because of the lock, acquired by SELECT ... FOR UPDATE.

This problem doesn't exist when READ COMMITED transaction isolation level is used. SELECT ... FOR UPDATE doesn't hold any locks if record is not found and READ COMMITED isolation level is used. So the first INSERT will succeed. The second INSERT will be blocked by EXCLUSIVE lock acquired by first INSERT and after t1 will be commited, the second INSERT will just throw Duplicate entry '1' for key 'PRIMARY'.

You could think now that this situation is not better that deadlock. Just another error. But now imagine that second INSERT tries to insert a record with id=2. In this case it will not be blocked by t1 and both transactions will succeed. And this is a big difference for some applications.