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

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

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

Solution

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.