Shulou(Shulou.com)05/31 Report--

This article introduces the reasons for the deadlock in MySQL, the content is very detailed, interested friends can refer to, hope to be helpful to you.

What are the causes of MySQL deadlock problem

1. Lock mechanism of common storage engines in MySQL.

MyISAM and MEMORY use table-level locks (table-levellocking)

BDB uses page lock (page-levellocking) or table level lock. The default is page lock.

InnoDB supports row-level locks (row-levellocking) and table-level locks. The default is row-level locks.

2. Characteristics of various locks

Table-level lock: low overhead, fast locking; no deadlock; large lock granularity, the highest probability of lock conflict and the lowest concurrency

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

Page lock: the overhead and locking time are between table lock and row lock; deadlocks occur; lock granularity is between table lock and row lock, and the concurrency is average.

3. Applicable scenarios for various locks

Table-level locks are more suitable for applications that are query-based, and only a few update data according to index conditions, such as Web applications.

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

4. Deadlock

Refers to two or more processes in the implementation process, due to competition for resources caused by a mutual waiting phenomenon, if there is no external force, they will not be able to move forward.

Table-level locks do not produce deadlocks. Therefore, the solution to deadlock is mainly aimed at the most commonly used InnoDB.

5. Example analysis of deadlock

In MySQL, row-level locks are not direct lock records, but lock indexes. Indexes are divided into primary key index and non-primary key index. If a sql statement operates on a primary key index, MySQL will lock the primary key index; if a statement operates on a non-primary key index, MySQL will lock the non-primary key index first, and then lock the relevant primary key index.

During UPDATE and DELETE operations, MySQL locks not only all index records scanned by WHERE conditions, but also adjacent key values, called next-keylocking.

For example, a table db.tab_test has the following structure:

Id: primary key

State: statu

Time: time

Index: idx_1 (state,time)

The deadlock log is as follows:

* * (1) TRANSACTION:TRANSACTION0677833455,ACTIVE0sec,processno11393,OSthreadid278546startingindexreadmysqltablesinuse1,locked1LOCKWAIT3lockstruct (s), heapsize320MySQLthreadid83,queryid162348740dcnet03dcnetSearchingrowsforupdateupdatetab_testsetstate=1064,time=now () wherestate=1061andtime

Cause analysis:

When "updatetab_testsetstate=1064,time=now () wherestate=1061andtime"

Suppose that when "updatetab_testsetstate=1067,time=now () whereidin (9921180)" executes almost at the same time, this statement first locks the primary key index, and some index records of idx_1 need to be locked because the value of state needs to be updated.

So the first statement locks the idx_1 record and waits for the primary key index, while the second statement locks the primary key index record and waits for the idx_1 record, resulting in a deadlock.

How to solve the problem of MySQL deadlock

Split the first sql, first find out the eligible primary key value, and then update the record according to the primary key:

Selectidfromtab_testwherestate=1061andtime

What are the reasons for deadlocks in MySQL to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.