Shulou(Shulou.com)06/01 Report--

This article mainly introduces the reasons and solutions of deadlock in mysql, the content of the article is carefully selected and edited by the author, and it has a certain pertinence, and it is of great significance to everyone's reference, so let's work with the author to understand the causes and solutions of deadlock in mysql. What kind of locks does mysql have?

MySQL has three levels of locks: page level, table level, row level, and memory level (latch).

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: expensive and slow to add locks; deadlocks occur; locking granularity is the smallest, the probability of lock conflicts is the lowest, and the degree of 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.

Algorithm:

Next KeyLocks lock, simultaneously locking the record (data), and locking the Gap in front of the record

Gap lock, do not lock the record, only record the previous Gap

Recordlock lock (lock data, not Gap)

So in fact, Next-KeyLocks=Gap lock + Recordlock lock

Under what circumstances will it cause a deadlock

The so-called deadlock DeadLock refers to the execution of two or more processes

A phenomenon of mutual waiting caused by competition for resources, without external force, they will not be able to move forward.

At this point, it is said that the system is in a deadlock state or that the system has a deadlock. These processes that are always waiting for each other are called deadlock processes.

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

The key to deadlocks is that two (or more) Session locks are not in the same order.

Then the corresponding key to solve the deadlock problem is to make different session locks in order.

4, here is a simple way to recreate the deadlock:

Deadlock reappears:

Transaction A:

Root@test 16:01 > select connection_id ()

+-+

| | connection_id () |

+-+

| | 47274 |

+-+

1 row in set (0.01 sec)

Root@test 16:02 > set autocommit = 0

Query OK, 0 rows affected (0.00 sec)

Root@test 16:02 > select * from t where id = 1 for update

+-- +

| | id |

+-- +

| | 1 |

+-- +

1 row in set (0.00 sec)

Root@test 16:02 > select * from t where id = 2 for update

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Root@test 16:03 >

Transaction B:

Root@test 16:02 > select connection_id ()

+-+

| | connection_id () |

+-+

| | 47272 |

+-+

1 row in set (0.00 sec)

Root@test 16:02 > set autocommit = 0

Query OK, 0 rows affected (0.00 sec)

Root@test 16:02 > select * from t where id = 2 for update

+-- +

| | id |

+-- +

| | 2 |

+-- +

1 row in set (0.00 sec)

Root@test 16:03 > select * from t where id = 1 for update

+-- +

| | id |

+-- +

| | 1 |

+-- +

1 row in set (5.53 sec)

=

Deadlock information:

LATEST DETECTED DEADLOCK

2018-10-19 16:03:14 7f9612b6d700

(1) TRANSACTION:

TRANSACTION 870600, ACTIVE 11 sec starting index read

Mysql tables in use 1, locked 1

LOCK WAIT 3 lock struct (s), heap size 360,2 row lock (s)

MySQL thread id 47272, OS thread handle 0x7f9612e38700, query id 1112421 127.0.0.1 root statistics

Select from t where id = 1 for update

* * (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 330 page no 3 n bits 72 index PRIMARY of table test.t trx id 870600 lock_mode X locks rec but not gap waiting

(2) TRANSACTION:

TRANSACTION 870599, ACTIVE 22 sec starting index read

Mysql tables in use 1, locked 1

3 lock struct (s), heap size 360,2 row lock (s)

MySQL thread id 47274, OS thread handle 0x7f9612b6d700, query id 1112422 127.0.0.1 root statistics

Select * from t where id = 2 for update

(2) HOLDS THE LOCK (S):

RECORD LOCKS space id 330 page no 3 n bits 72 index PRIMARY of table test.t trx id 870599 lock_mode X locks rec but not gap

(2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 330 page no 3 n bits 72 index PRIMARY of table test.t trx id 870599 lock_mode X locks rec but not gap waiting

* WE ROLL BACK TRANSACTION (2)

5 Analysis:

1, which shows the sql that the transaction generated a deadlock and prints out the corresponding held and waiting locks

2. The above information does not output the sql before the transaction deadlock, so you can directly heap the sql executed by the two transactions so that they hold the lock that the other is waiting for.

3. Deadlocks are inevitable. Slow sql and unreasonable business logic are the main reasons for too many deadlocks.

Say the important things three times: optimize sql, optimize business, optimize logic

After reading the above about the causes and solutions of deadlock in mysql, many readers must have some understanding. If you need to get more industry knowledge and information, you can continue to follow our industry information column.

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.