Row locks

In MyISAM, there are no row locks and only table-level locks can be used when concurrency control is required. InnoDB engine has row locks.

Two-phase commit protocol

In InnoDB engine, row locks are added when a statement is executed and released when a transaction commits, i.e. row locks are not released immediately after the execution of the corresponding statement. We call this protocol for the use of row locks a two-phase commit.

As shown above, each line corresponds to a timeline. When transaction A starts to execute the UPDATE statement, it will hold the row lock with ID 1 and 2. When transaction B executes the UPDATE statement, transaction B wants to apply for the row lock with ID 1, but the lock is obtained by transaction A, so transaction B is in the waiting phase. The row locks with IDS 1 and 2 will not be released until transaction A commits, at which point transaction B can attempt to acquire the row lock with ID 1.

As we can see from the above example, when we write code, if a transaction will hold more than one row lock, we need to place the row most likely to cause conflicts at the end of the row, so that the time to hold the row lock can be minimized.

Deadlocks and deadlock detection

If transaction A holds 1 row lock and needs to apply for 2 rows lock, and transaction B holds 2 rows lock and needs to apply for 1 row lock. In this case, both transaction A and transaction B are waiting indefinitely, which is called A deadlock. Details are shown below.

At this point, mysql has two processing strategies:

  • The transaction waits until innodb_LOCAL_WAIT_TIMEOUT (default is 50s) and the row lock is released.
  • Launch deadlock detection, and when a deadlock is detected, roll back one of the transactions so that the other transactions can proceed normally.

What is deadlock detection? When transaction A is locked, the locks held by transaction A are checked to see if other transactions B are locked. If the locks requested by transaction B happen to be held by transaction A, transaction A and transaction B are deadlocked.

reference

Geek time — MySQL Practice 45 Lecture 7 — Dinky