Mysql locks and deadlocks _ MySQL

Mysql locks and deadlocks are summarized below. some experiences on the network are referenced.

MyISAM and MEMORY storage engines use table-level locking

The BDB storage engine uses page-level locking, but also supports table-level locks.

The InnoDB storage engine supports row-level locking and table-level locking. However, row-level locks are used by default.

Table-level lock costs are small and locks are fast; no deadlock occurs; large lock granularity, the highest probability of lock conflict, and the lowest concurrency

Large row-level lock overhead and slow locking; deadlock may occur; minimum lock granularity, minimum probability of lock conflict, and highest concurrency

The overhead of the page lock and the lock time are between the table lock and the row lock. there will be deadlocks. the lock granularity is between the table lock and the row lock, and the concurrency is normal.

From the lock perspective only:

Table-level locks are more suitable for queries, and only a few applications update data based on index conditions, such as Web applications.

Row-level locks are more suitable for applications with a large number of concurrent updates of a small amount of different data based on index conditions and concurrent queries, such as some online transaction processing systems.

Deadlock

Deadlock : Two or more processes are in the execution process,

A phenomenon of mutual wait caused by competition for resources, if there is no external force, they will not be able to continue.

It is said that the system is in a deadlock state or the system has a deadlock. these processes that are always waiting for mutual deadlock are called deadlock processes.

Table-level locks do not produce deadlocks. Therefore, the most common InnoDB is to solve deadlocks.

In case of problems

First execute show processlist to find the deadlock thread number. then Kill processNo

Of course, the main solution is to take a look at the specific operation. a deadlock may occur.

Show innodb status checks the engine status to see which statements generate deadlocks

Then we solved the problem. we still need to see what the problem is.