What causes mysql deadlock?

The cause of mysql deadlock: two or more processes compete for resources during execution. There are three types of locks in MySQL, namely table-level locks, row-level locks and page locks. Among them, table-level locks have low overhead, fast locking, and no deadlocks.

Mysql is a relational database management system that uses the SQL language, which is the most commonly used standardized language for accessing databases. During the use of mysql, deadlocks may occur. What is the reason for this?

MySQL’s three types of locks:

Table-level locks: low overhead, fast locking; no deadlocks; large locking granularity, high probability of lock conflicts The highest and the lowest concurrency.

Row-level locks: high overhead, slow locking; deadlocks may occur; the locking granularity is the smallest, the probability of lock conflicts is the lowest, and the concurrency is the highest.

Page lock: The cost and locking time are between table locks and row locks; deadlocks will occur; the locking granularity is between table locks and row locks, and the concurrency is average

Algorithm:

next KeyLocks lock, lock the record (data) at the same time, and lock the Gap in front of the record

Gap lock, do not lock the record, only record the Gap in front

Recordlock lock (lock data, not lock Gap)

So actually Next-KeyLocks=Gap lockRecordlock lock

Under what circumstances will a deadlock occur

The so-called deadlock: refers to a phenomenon of two or more processes waiting for each other due to competition for resources during execution. Without external force, they will not be able to advance. At this time, the system is said to be in a deadlock state or the system has a deadlock. These processes that are always waiting for each other are called deadlock processes.

Table-level locks will not produce deadlocks. Therefore, the solution to deadlocks is mainly for the most commonly used InnoDB.

The key to deadlocks is: two (or more) The order of Session locking is inconsistent.

Then the key to solving the deadlock problem is to lock different sessions in order.

The above is the detailed content of What causes mysql deadlock?. For more information, please follow other related articles on the PHP Chinese website!