Default Concurrency Control Implementation in MySQL

Question

What is the default implementation of concurrency control in MySQL? Is it optimistic locking (multi version concurrency control), or pessimistic locking (2 phase locking)? More specifically, how does InnoDb do it? Internally, how does mysql (with innodb) decide on the start of a transaction whether to lock the row, or rollback after a conflict?

Answer 1

InnoDB uses optimistic locking.

There is no locking at the start of a transaction. How would it know which rows to lock until you execute a specific query? It doesn't even know which table(s) that you will eventually need to lock rows in.

There is no need for a rollback after a lock conflict. If you do a query in one transaction that has to wait because another session holds the lock, then your query waits up to a certain number of seconds (per the config option innodb_lock_wait_timeout, default 50 seconds).

• If the other session commits before the timeout, then your session stops waiting, acquires the locks it needs, and proceeds with the query.
• If your wait times out before the other session commits, your query returns an error. This still does NOT rollback your transaction; previous changes you made during your transaction are still able to be committed. You can even try the query that timed out again.

Exception: in cases of deadlock, InnoDB chooses one of the transactions involved in the deadlock, and forcibly does a rollback on one of them. It tries to choose the transaction that has modified fewer rows. If the transactions are tied, then the choice is arbitrary.