0

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?

Sarthak Agarwal
  • 404
  • 1
  • 3
  • 13
  • Seems to have closed on the wrong duplicate: should be https://stackoverflow.com/questions/17431338/optimistic-locking-in-mysql – Strawberry Jul 11 '20 at 08:12
  • I've seen these links. They don't answer my question. They explain how MVCC and optimistic locking works. But I have also seen articles which explain how 2PL is used in MySQL. Internally, how does mysql decide on the start of a transaction whether to lock the row or rollback after a conflict? – Sarthak Agarwal Jul 11 '20 at 08:27
  • I suggest you edit your question to incorporate these points – Strawberry Jul 11 '20 at 08:54
  • Done..hoping for an answer now – Sarthak Agarwal Jul 11 '20 at 09:32
  • Look at "SELECT...FOR UPDATE", "gap locking", diff types of locks, wait_timeout versus deadlock. There are lots of blogs on the topics. Read some more, then come back with a more specific question. – Rick James Jul 11 '20 at 16:02

1 Answers1

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828