Detailed explanation of how MySQL (InnoDB) handles deadlocks

1. What is deadlock?

The official definition is as follows: two transactions hold locks required by the other party and are waiting for the other party to release them, and neither party will release their own locks.

This is like you have a hostage and the other party has a hostage, and the two of you go to negotiate to exchange people. You ask the other side to release the player, and the other side asks you to release the player.

2. Why does deadlock occur?

Seeing this, you may have such a question. Transactions are different from negotiations. Why can't transactions release the lock immediately after using it? Do you have to hold the lock after the operation is completed? This involves MySQL's concurrency control.

There are two ways of concurrency control in MySQL, one is MVCC and the other is two-phase locking protocol. So why do we need concurrency control? This is because when multiple users operate MySQL at the same time, in order to improve concurrency performance, it is required that the requests from multiple users be executed serially (serializable scheduling). The specific concurrency control will not be expanded here. Let's continue to discuss the two-phase locking protocol in depth.

Two-Phase Locking Protocol (2PL)

Official definition:

The two-phase locking protocol means that all transactions must lock and unlock data in two phases. Before reading or writing any data, the transaction must first obtain a lock on the data; after releasing a lock, the transaction no longer applies for or obtains any other locks.

Corresponding to MySQL, it is divided into two stages:

1. Extension phase (after the transaction starts and before commit): acquire locks
2. Contraction phase (after commit): release locks

That is to say, only by following the two-stage lock protocol can serializable scheduling be achieved.

However, the two-phase locking protocol does not require that a transaction must lock all the data it needs to use at one time, and there is no order requirement in the locking phase, so this concurrency control method will cause deadlock.

3. How does MySQL handle deadlock?

MySQL has two deadlock handling methods:

1. Wait until timeout (innodb_lock_wait_timeout=50s).
2. Initiate deadlock detection, actively roll back a transaction, and allow other transactions to continue executing (innodb_deadlock_detect=on).

Due to performance reasons, deadlock detection is generally used to handle deadlocks.

Deadlock Detection

The principle of deadlock detection is to construct a directed graph with transactions as vertices and locks as edges, and determine whether there is a cycle in the directed graph. If there is a cycle, there is a deadlock.

rollback

After a deadlock is detected, the transaction with the least number of inserted, updated, or deleted rows is selected for rollback, based on the trx_weight field in the INFORMATION_SCHEMA.INNODB_TRX table.

4. How to avoid deadlock

Collect deadlock information:

1. Use the SHOW ENGINE INNODB STATUS command to view the cause of the deadlock.
2. During the debugging phase, innodb_print_all_deadlocks is enabled to collect all deadlock logs.

Reduce deadlock:

1. Use transactions, do not lock tables.
2. Ensure that there are no long transactions.
3. Commit transactions immediately after operation, especially in interactive command line.
4. If you are using (SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE), try lowering the isolation level.
5. When modifying multiple tables or multiple rows, keep the modification order consistent.
6. Creating an index can create fewer locks.
7. It is best not to use (SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE).
8. If the above does not solve the problem, try to lock multiple tables using lock tables t1, t2, t3

The above is a detailed explanation of how MySQL (InnoDB) handles deadlocks. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!