When MySql deadlocks occur, InnoDB automatically detects transaction deadlocks, rolls back immediately, and returns errors. Rollback usually chooses the transaction with the smallest undo amount.
Prior to MySql 5.6, the last deadlock that occurred could be returned by using the SHOW ENGINE INNODB STATUS command. After MySql 5.6, all InnoDB deadlock information can be recorded in the error log through the variable innodb_print_all_deadlocks.
MySql deadlock involves more than two transactions. The LATEST DETECTED DEADLOCK part of the information returned by SHOW ENGINE INNODB STATUS detects only the two most recent transactions. In addition, only the last statement executed in two transactions is shown. Some ways to view more information will be given later.
How to Judge MySql Deadlock
Next, let's look at two examples to see what information is generated when deadlocks occur.
1 141013 6:06:22 2 *** (1) TRANSACTION: 3 TRANSACTION 876726B90, ACTIVE 7 sec setting auto-inc lock 4 mysql tables in use 1, locked 1 5 LOCK WAIT 9 lock struct(s), heap size 1248, 4 row lock(s), undo log entries 4 6 MySQL thread id 155118366, OS thread handle 0x7f59e638a700, query id 87987781416 localhost msandbox update 7 INSERT INTO t1 (col1, col2, col3, col4) values (10, 20, 30, 'hello') 8 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: 9 TABLE LOCK table `mydb`.`t1` trx id 876726B90 lock mode AUTO-INC waiting 10 *** (2) TRANSACTION: 11 TRANSACTION 876725B2D, ACTIVE 9 sec inserting 12 mysql tables in use 1, locked 1 13 876 lock struct(s), heap size 80312, 1022 row lock(s), undo log entries 1002 14 MySQL thread id 155097580, OS thread handle 0x7f585be79700, query id 87987761732 localhost msandbox update 15 INSERT INTO t1 (col1, col2, col3, col4) values (7, 86, 62, "a lot of things"), (7, 76, 62, "many more") 16 *** (2) HOLDS THE LOCK(S): 17 TABLE LOCK table `mydb`.`t1` trx id 876725B2D lock mode AUTO-INC 18 *** (2) WAITING FOR THIS LOCK TO BE GRANTED: 19 RECORD LOCKS space id 44917 page no 529635 n bits 112 index `PRIMARY` of table `mydb`.`t2` trx id 876725B2D lock mode S locks rec but not gap waiting 20 *** WE ROLL BACK TRANSACTION (1)
Line 1 gives the time when deadlocks occur, and if the application code captures and records deadlock errors, you can compare the two times to find the code that causes the problem.
Lines 3 and 11 record transaction numbers and active times.
Lines 4 and 12, the number of tables in use and the number of tables locked by related statements. Therefore, the use of a table does not mean that a transaction contains only one table.
Lines 5 and 13 describe what changes have been made to the transaction, in this case undo log entries. It also includes how many row lock s a transaction holds, namely row locks (s).
Lines 6 and 14 record thread id, host and user connected.
Line 9, for the first transaction, shows the lock it is waiting for, in this case, the AUTO-INC lock on table t1. Other possible locks include S locks or X locks.
Lines 16 and 17 show the lock held by the second transaction, in this case the AUTO-INC lock, which is the lock waiting for the first transaction.
Lines 18 and 19 show the lock waiting for the second transaction, which is a shared non-gap lock on the primary key of another table.
Shared locks are generated in InnoDB in the following situations.
1) Use SELECT... LOCK IN SHARE MODE
2) Records referenced by foreign keys
3) INSERT. INTO... Table of SELECT in SELECT.
The current statement of trx(2) is an insertion statement that inserts data into table t1. Looking at the table definition through SHOW CREATE TABLE, it can be found that the shared lock waiting for foreign keys.
1 2014-10-11 10:41:12 7f6f912d7700 2 *** (1) TRANSACTION: 3 TRANSACTION 2164000, ACTIVE 27 sec starting index read 4 mysql tables in use 1, locked 1 5 LOCK WAIT 3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1 6 MySQL thread id 9, OS thread handle 0x7f6f91296700, query id 87 localhost ro ot updating 7 update t1 set name = 'b' where id = 3 8 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: 9 RECORD LOCKS space id 1704 page no 3 n bits 72 index `PRIMARY` of table `tes t`.`t1` trx id 2164000 lock_mode X locks rec but not gap waiting 10 Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bit s 0 11 0: len 4; hex 80000003; asc ;; 12 1: len 6; hex 000000210521; asc ! !;; 13 2: len 7; hex 180000122117cb; asc ! ;; 14 3: len 4; hex 80000008; asc ;; 15 4: len 1; hex 63; asc c;; 16 17 *** (2) TRANSACTION: 18 TRANSACTION 2164001, ACTIVE 18 sec starting index read 19 mysql tables in use 1, locked 1 20 3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1 21 MySQL thread id 10, OS thread handle 0x7f6f912d7700, query id 88 localhost r oot updating 22 update t1 set name = 'c' where id = 2 23 *** (2) HOLDS THE LOCK(S): 24 RECORD LOCKS space id 1704 page no 3 n bits 72 index `PRIMARY` of table `tes t`.`t1` trx id 2164001 lock_mode X locks rec but not gap 25 Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bit s 0 26 0: len 4; hex 80000003; asc ;; 27 1: len 6; hex 000000210521; asc ! !;; 28 2: len 7; hex 180000122117cb; asc ! ;; 29 3: len 4; hex 80000008; asc ;; 30 4: len 1; hex 63; asc c;; 31 32 *** (2) WAITING FOR THIS LOCK TO BE GRANTED: 33 RECORD LOCKS space id 1704 page no 3 n bits 72 index `PRIMARY` of table `tes t`.`t1` trx id 2164001 lock_mode X locks rec but not gap waiting 34 Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bit s 0 35 0: len 4; hex 80000002; asc ;; 36 1: len 6; hex 000000210520; asc ! ;; 37 2: len 7; hex 17000001c510f5; asc ;; 38 3: len 4; hex 80000009; asc ;; 39 4: len 1; hex 62; asc b;;
Lines 9 and 10: The space ID is the table space id, and page no gives the page number where the record lock is located. 'n bits'is the number in the lock bitmap. The page offset is represented by'heap no'on line 10.
Lines 11 to 15: Recorded data is presented in hexadecimal form. 0 represents a clustered index (primary key). Ignore the highest position and its value is 3. 1 is the transaction ID of the last modification of the record, the decimal representation is 2164001, and the formal transaction 2.2 is the rollback pointer. Beginning with 3, the rest is row data. The column shown in 3 is an integer with a value of 8.4 and a string followed by a'c'. By reading this information, we can know which rows are locked and what the current value is.
Other information available from the above analysis
In Example 1, trx(2) is waiting for a shared lock, so TRX (1) either holds a shared lock on the primary key of table t2 or an exclusive lock on the primary key of table t2. Col2 column is a foreign key. By examining the current statement of TRX (1), we find that TRX (1) does not need the same record lock, so it must be a statement before TRX (1) that requires S or X locks on the primary key of table t2. Trx (1) modified four rows of records in seven seconds. By analyzing TRX (1), we can see that it performs a lot of processing, but only modifies a little data. The modifications include table t1 and inserting a record into table t2. Combining this information can help developers locate transactions.
Where can I find the statement executed before the transaction?
In addition to applying logs and using the SHOW ENGINE INNODB STATUS command before, we can use binlog,slow log and general query log. Through binlog, if binlog_format=statement, every binlog event will have thread_id. Only committed transactions are recorded in the binlog, so we can query Trx(2) in the binlog. In Example 1, we know when a deadlock occurs and that Trx(2) starts nine seconds ago. So you can find the position of the statement by using the mysqlbinlog command.
mysqlbinlog -vvv --start-datetime="2014-10-13 6:06:12" --stop-datatime="2014-10-13 6:06:22" mysql-bin.000010 > binlog_1013_0606.out
How to Avoid Deadlock in MySql
Modify the application: In some cases, by dividing large transactions into smaller ones, deadlock frequencies can be reduced and locks can be released faster after dividing. In addition, deadlocks occur when two transactions operate in different order on the same data set, either on the same table or on multiple tables. Therefore, the access order is modified, i.e. serialized access. When transactions are executed concurrently, lock waiting occurs instead of deadlock.
Modify table schemas: For example, delete foreign keys, or add indexes to reduce the number of rows scanned and locked.
Gap locks: The isolation level of transactions can be read committed to avoid gap locks. But binlog format should be modified to ROW or MIXED.
Innodb Locks in: https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html