InnoDB adopts row-level locking strategy. Although the row-level locking strategy greatly improves the concurrent performance of programs, InnoDB may occupy multiple rows at the same time when performing some operations due to the reduction of lock granularity, which increases the probability of lock conflicts. Different transaction isolation levels have different requirements on data consistency. For example, gap locks added in RR levels may cause a large number of lock conflicts. Bad business design can also cause deadlocks. Deadlocks can occupy system resources and block requests. At best, they can slow down SQL execution and slow down the system. At worst, they can drag down the server and make services unavailable. Therefore, we need to avoid the occurrence of deadlocks as far as possible in the process of use, and we can check deadlocks according to the relevant information to prevent deadlocks.

Deadlock mechanism

First, let’s review the four necessary conditions for deadlock:

  • Mutually exclusive, a resource can only be used by one process at a time.
  • Request and occupation, when a process is blocked by requesting resources, it holds on to acquired resources.
  • Resources that have been acquired by a process cannot be taken away by force until the process has exhausted its use.
  • Cyclic wait, which forms a head – to – end cyclic wait resource relationship between several processes.

Similarly, deadlock formation in InnoDB also needs to meet the above four conditions. Mysql > create a deadlock in InnoDB when multiple threads operate on the same data at the same time, i.e. two or more transactions exist.

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL DEFAULT ' ',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
Copy the code

The preset data is as follows:

id name
1 Zhang SAN
2 Li si
3 Cathy

Now follow the following operations to delete two pieces of data:

The serial number Transaction 1 state Transaction 2 state
1 begain transaction;
2 delete from test where id = 1; The X lock whose ID is 1 is held
3 begain transaction;
4 delete from test where id = 2; The X lock whose ID is 2 is held
5 delete from test where id = 2; Wait for transaction 2 to release the lock
6 delete from test where id = 1; Wait for transaction 1 to release the lock. The system detects a deadlock and rolls back transaction 2
7 commit;
8 commit;

In the above operation, we constructed a very typical deadlock condition. InnoDB deadlock mechanism detects deadlocks and selectively rolls back transactions with low weight. You can query the deadlock status by using the following statements:

show engine innodb status;
Copy the code

InnoDB deadlock policy

InnoDB has two types of deadlock handling:

  • Waiting for the, until the timeout (passinnodb_lock_wait_timeoutParameter control)
  • Deadlock detection, actively roll back a transaction to allow other transactions to continue (innodb_deadlock_detect=on), this mechanism only works when there are only row locks

Deadlock detection in InnoDB is implemented using the Waiting For Graph algorithm. In InnoDB, all transaction locks are recorded to form a directed graph with transaction vertices and lock edges. We only need to judge whether there is a loop in the digraph to know whether there is a deadlock. In the above deadlock scenario, transaction 1 is waiting for transaction 2 to release the lock with ID=2, and transaction 2 is also waiting for transaction 1 to release the lock with ID=1, forming a loopback structure, so InnoDB determines that there is a deadlock in the system.

So how does InnoDB handle when there are many transactions waiting for each other, forming a very long wait chain? In this case, InnoDB considers a deadlock when the length of InnoDB wait list exceeds 200. InnoDB also considers deadlocks to occur when waiting on chain transactions hold too many locks (more than 1,000,000 row locks are required for a transaction).

How does InnoDB release a transaction when it detects a deadlock? InnoDB uses transaction weight to determine the importance of a transaction, and lower weights will be released. Transaction weight is generally obtained by inserting, updating and deleting statements in a transaction that affect the number of data items. The smaller the number of data items affected by transaction statements, the lower the transaction weight.

InnoDB state analysis

If a deadlock exists, the status log will display the information about the last deadlock. You can use the log to roughly check the status of deadlock related transactions and locks:

show engine innodb status;
Copy the code

A standard status log would look like this:

=====================================
2019-11-19 23:19:23 0x7fa850f58700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 48 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 852151 srv_active, 0 srv_shutdown, 4608208 srv_idle
srv_master_thread log flush and writes: 5459518
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2440006
OS WAIT ARRAY INFO: signal count 2411688
RW-shared spins 0, rounds 1948637, OS waits 972484
RW-excl spins 0, rounds 1702845, OS waits 160434
RW-sx spins 4496, rounds 134826, OS waits 4138
Spin rounds per wait: RW - Shared 1948637.00, 1702845.00 RW - excl, 29.99 RW - sx -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- the LATEST DETECTED DEADLOCK -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 2019-11-19 23:19:08 0x7fa860156700 *** (1) TRANSACTION: TRANSACTION 25321837, ACTIVE 29 sec starting indexread
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 2348540, OS thread handle 140360898553600, query id 66353526 163.125.229.225 root updating
delete from test where id = 2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2266 page no 3 n bits 80 index PRIMARY of table `test`. `test` trx id 25321837 lock_mode X locks rec but not gap waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 000001826186; asc     a ;;
 2: len 7; hex 65000001250c88; asc e   %  ;;
 3: len 6; hex e69d8ee59b9b; asc       ;;

*** (2) TRANSACTION:
TRANSACTION 25321862, ACTIVE 15 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 2348917, OS thread handle 140361143248640, query id 66353634 163.125.229.225 root updating
delete from test where id = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 2266 page no 3 n bits 80 index PRIMARY of table `test`. `test` trx id 25321862 lock_mode X locks rec but not gap
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 000001826186; asc     a ;;
 2: len 7; hex 65000001250c88; asc e   %  ;;
 3: len 6; hex e69d8ee59b9b; asc       ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2266 page no 3 n bits 80 index PRIMARY of table `test`. `test` trx id 25321862 lock_mode X locks rec but not gap waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 00000182616d; asc     am;;
 2: len 7; hex 58000001412fef; asc X   A/ ;;
 3: len 6; hex e5bca0e4b889; asc       ;;

*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 25321913
Purge done for trx's n:o < 25321911 undo n:o < 0 state: running but idle
History list length 35
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421836854896032, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421836854885088, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421836854895120, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421836854891472, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421836854888736, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421836854886912, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421836854900592, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421836854898768, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421836854893296, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421836854892384, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421836854890560, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421836854886000, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421836854883264, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 25321912, ACTIVE 0 sec
5 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 2
MySQL thread id 2346328, OS thread handle 140360896157440, query id 66354003 47.98.152.73 root
Trx read view will not see trx with id >= 25321911, sees < 25321837
---TRANSACTION 25321837, ACTIVE 44 sec
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 2
MySQL thread id 2348540, OS thread handle 140360898553600, query id 66353526 163.125.229.225 root
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's: Pending flushes (fsync) log: 0; buffer pool: 0 846404 OS file reads, 16785073 OS file index, 5999964 OS fsyncs 0.00 reads/s, 0 AVg bytes/read, 8.37 enwebsite/index, 3.17 fsyncs/s -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- the INSERT BUFFER AND the ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 185, seg size 187, 28201 merges merged operations: insert 528, delete mark 7663041, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 138389, node heap has 31 buffer(s) Hash table size 138389, node heap has 2 buffer(s) Hash table size 138389, node heap has 3983 buffer(s) Hash table size 138389, node heap has 218 buffer(s) Hash table size 138389, node heap has 5 buffer(s) Hash table size 138389, node heap has 1 buffer(s) Hash table size 138389, node heap has 2 buffer(s) Hash table size 138389, Node heap has 5 buffer(s) 139.08 hash searches/s, 4.46 non-hash searches/s -- LOG -- LOG sequence number 24370884626 LOG uploading up to 24370882478 Pages uploading up to 24370875924 Last checkpoint at 24370875333 0 pending log flushes, 0 pending chkp writes 9315962 log i/o's done, 2.31 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 549715968 Dictionary memory allocated 10240009 Buffer pool size 32764 Free buffers 1024 Database pages 27493 Old database pages 10128 Modified db pages 34 Pending reads 0 Pending writes: LRU 0, Flush list 0, single page 0 Pages Made Young 964069, not young 143276717 0.00 youngs/s, 0.00 Non-youngs /s Pages Read 845408, created 77961, written 6649614 0.00 reads/s, 0.04 creates/s, 5.40 /s Buffer pool hit rate 1000/1000, young-making rate 0/1000 not 0/1000 Pages read ahead 0.00/s, Evicted without Access 0.00/s, Random read ahead 0.00/s 0 I/O sum[276]:cur[1], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Process ID=1773, Main thread ID=140361173219072, state: Sleeping Number of Rows Inserted 19006577, updated 853025, deleted 5584787, read 210247854332 0.85 inserts/s, 0.83 updates/s, 0.06 deletes/s, 21633.22 reads/s -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- END OF INNODB MONITOR OUTPUT = = = = = = = = = = = = = = = = = = = = = = = = = = = =Copy the code

InnoDB standard monitoring information includes output header, main thread load, semaphore, deadlock information, transaction information, IO information, insert buffer and adaptive hash index information, log information, buffer pool and memory usage, row operation information. Through these internal state information, we can understand the actual load inside InnoDB, which plays an important role in database operation and maintenance.

The output end

The standard monitoring information output header contains the following parts, which display the query time of the current monitoring, monitoring name, and the number of seconds between the current output and the last output.

Background thread information

This section shows the background InnoDB main thread workload. Generally speaking, a larger srv_active and a smaller SRv_IDLE indicates that the current mysql database is not under pressure.

Semaphore information

The semaphore section counts the number of idle times and the number of times a thread waits to acquire a mutex or read-write lock semaphore. The mutex and read/write locks refer to latches in mysql, which are mainly used to ensure the correct operation of critical resources by concurrent threads. If a large number of threads are waiting for a semaphore, it could be due to disk IO performance bottlenecks or intense competition for resources within InnoDB, which is typically caused by too many parallel queries or an operating system thread scheduling problem. A high NUMBER of OS_WAITS indicates frequent LATCH contention.

Latest deadlock information

This section displays deadlock information from the last time a deadlock occurred. Deadlock information contains some very useful information such as locking and waiting statements, the index used by the lock, but unfortunately the log does not show the held and waiting locks, which is not easy to analyze for multiple threads caused by deadlocks.

Transaction information

Transaction information shows the current innoDB transaction status in a previous period of time, which helps us to detect deadlocks.

File IO Information

This section shows the IO status of the background thread.

Insert buffer and adaptive hash index information

This section shows information about insert buffer execution and adaptive hash indexes.

Log information

Log information shows the current status of redo logs.

Buffer pool and memory information

This section shows page read and write statistics and their memory usage statistics in the buffer pool.

Line operation information

This section shows what the main thread is doing, such as the number of row operations of various types and performance statistics.

How do I avoid deadlocks

To minimize the impact of deadlocks on the system, we should avoid deadlocks whenever possible. There are usually several techniques to avoid deadlocks:

  • With transactions, try not to use table locks such as Lock tables. InnoDB cannot detect deadlocks when there are multiple lock types and can only wait for a deadlock timeout.
  • Minimize transaction execution time and avoid long transactions, which may hold locks for a long time and cause deadlocks more easily.
  • Lower the isolation level so that there is no gap lock at the RC level, which greatly reduces the probability of deadlock.
  • When MDL statements modify multiple tables or rows, the sequence of operations must be consistent to prevent improper locking from causing deadlocks.
  • MDL statements use primary keys or indexes as much as possible. InnoDB locks indexes. Using primary keys or indexes can reduce the probability of lock conflicts.
  • If deadlocks cannot be avoided due to business needs, consider table locks or distributed locks for control.

For InnoDB, the cause of deadlock is very complicated and needs to be analyzed in detail. The author of common MySQL Deadlock Case Analysis has collected and sorted out some common deadlock scenarios, and interested students can refer to it to deepen their understanding and analysis of deadlock.

Thank you

  • 15.7.5 Deadlocks in InnoDB
  • 14.17.2 Enabling InnoDB Monitors
  • 14.17.3 InnoDB Standard Monitor and Lock Monitor Output
  • MySQL deadlock case analysis
  • MySQL lock analysis
  • Appendix B Show Engine Innodb status details
  • Talk about MySQL locks
  • In-depth analysis and judgment on MySQL Latch contention
  • The Path to Deadlock Resolution (Epilogue) – Goodbye deadlock