I. Business background

Our current business is a data product, and a lot of real-time calculation and crawling data are summarized on the big data warehouse and data mining platform ODPS. When the application reads these data, the data is first imported to MySQL, which is more capable of concurrent reads and suitable for structured queries. In the process of development, it was found that a large number of deadlocks occurred when a table containing a unique key was concurrently inserted, making the insertion almost impossible. Therefore, in order to troubleshoot the problem, I consulted the students in DBA and database business department, and finally found the problem, which is hereby recorded

Two. Deadlock scene

1. The table structure

CREATE TABLE tkn_tb_cinema_show_data (

…

cinema_idBigint (20) DEFAULT NULL COMMENT ‘ID’,

show_idBigint (20) DEFAULT NULL COMMENT ‘ID’,

now_dateVarchar (32) DEFAULT NULL COMMENT ‘date’,

…

PRIMARY KEY (id),

UNIQUE KEY uid_cinema_show_date (cinema_id.show_id.now_date),

…

ENGINE=InnoDB AUTO_INCREMENT= utf8 DEFAULT CHARSET=utf8 COMMENT= ’10’

2. Problem status



You can see that deadlocks occur because of the transaction at the time of batch inserts

Hold locks

index uid_cinema_show_date of table tkn.tkn_tb_cinema_show_data trx id 73278630826 lock_mode X

Waiting for the lock

index uid_cinema_show_date of table tkn.tkn_tb_cinema_show_data trx id 73278630826 lock_mode X locks gap before rec insert intention waiting

How can I insert a deadlock on a different line

(SHOW ENGINE INNODB STATUS;)

transactions deadlock detected, dumping detailed information.
2017-06-11 08:41:03 2ac742684700
*** (1) TRANSACTION:
TRANSACTION 73278630816, ACTIVE 1 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 622 lock struct(s), heap size 79400.743 row lock(s), undo log entries 388
MySQL thread id 13824253, OS thread handle 0x2ac195786700, query id 53621728233 11.22764.76. dwexp update
INSERT INTOtkn_tb_cinema_show_data ...... * * * (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 29500 page no 398903 n bits 336 index `uid_cinema_show_date` of table `tkn`.`tkn_tb_cinema_show_data` trx id 73278630816 lock_mode X
Record lock, heap no 253 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 8; hex 80000000000035cb; asc 5 ;;
1: len 8; hex 80000000000356f1; asc V ;;
2: len 10; hex 323031372d30362d3138; asc 2017-06-18;;
3: len 8; hex 80000000807f52b2; asc R ;;

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 29500 page no 423377 n bits 304 index `uid_cinema_show_date` of table `tkn`.`tkn_tb_cinema_show_data` trx id 73278630816 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 228 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 8; hex 8000000000006262; asc bb;;
1: len 8; hex 8000000000035911; asc Y ;;
2: len 10; hex 323031372d30362d3138; asc 2017-06-18;;
3: len 8; hex 80000000807fdae4; asc ;;

*** (2) TRANSACTION:
TRANSACTION 73278630826, ACTIVE 1 sec inserting, thread declared inside InnoDB 4836
mysql tables in use 1, locked 1
2425 lock struct(s), heap size 292392.3363 row lock(s), undo log entries 1804
MySQL thread id 13824252, OS thread handle 0x2ac742684700, query id 53621728249 11.22764.76. dwexp update
INSERT INTOtkn_tb_cinema_show_data ...... * * * (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 29500 page no 423377 n bits 304 index `uid_cinema_show_date` of table `tkn`.`tkn_tb_cinema_show_data` trx id 73278630826 lock_mode X
Record lock, heap no 228 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 8; hex 8000000000006262; asc bb;;
1: len 8; hex 8000000000035911; asc Y ;;
2: len 10; hex 323031372d30362d3138; asc 2017-06-18;;
3: len 8; hex 80000000807fdae4; asc ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 29500 page no 398903 n bits 336 index `uid_cinema_show_date` of table `tkn`.`tkn_tb_cinema_show_data` trx id 73278630826 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 253 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 8; hex 80000000000035cb; asc 5 ;;
1: len 8; hex 80000000000356f1; asc V ;;
2: len 10; hex 323031372d30362d3138; asc 2017-06-18;;
3: len 8; hex 80000000807f52b2; asc R ;;

*** WE ROLL BACK TRANSACTION (1)Copy the code

3. Analyze the problem

Read the deadlock log. 1. From the log, you can see the locking status and waiting status of the two transactions: a. Transaction 1 HOLDS THE LOCK(S)…… Lock_mode X holding X LOCK WAITING FOR THIS LOCK TO BE GRANTED…… X locks gap before rec insert intention waiting HOLDS THE LOCK(S)…… Lock_mode X holding X LOCK WAITING FOR THIS LOCK TO BE GRANTED…… X locks gap before rec insert intention waiting

2. Add some knowledge about locks

  • InnoDB uses a more precise logic to determine whether a row lock conflicts, in addition to the basic IS IX S X lock conflicts. In addition to the above mentioned lock types, InnoDB also subdivides locks into the following sub-types:

    • Record Lock (RK) locks the key directly on the index record
    • Gap Lock (GK) A gap lock that locks a range but does not include the record itself. The purpose of the GAP lock is to prevent the illusion of two current reads of the same transaction
    • Next key lock (NK) Row lock and gap lock together is called next-key lock
    • If the gap is already locked by gap before insertion, insert will apply for an intention lock. Because of phantom avoidance, inserting an intent lock is blocked when another transaction holds an interval lock for that gap (gap locks are not used directly because gap locks are not mutually exclusive). The “exact mode” lock compatibility matrix is drawn below
Columns add \ rows already RK GK IK NK
RK 0 1 1 0
GK 1 1 1 1
IK 1 0 1 0
NK 0 1 1 0
  • Lock logic for unique indexes in insert

    • If there is a target row, add S NK (next key lock in S lock, same below) to the target row. If the lock is successfully inserted (the record is deleted by other transactions during the waiting period, the lock will be deleted at the same time).
    • If 1 succeeds, add X IK to the corresponding row
    • If 2 succeeds, insert the record and add X RK to the record (possibly an implicit lock)

SQL > select * from GK; select * from XIK; select * from GK; select * from XIK; select * from GK; Insert does not require GK, so two transactions X IK are requested to wait because S NK is blocked. 2. After the insert, only X RK locks remain, which is why both transactions have X RK, indicating that they have just inserted some records. 3. It can be inferred from 1,2 that deadlock means that S NK of transaction 1 is blocked by X RK of transaction 2, indicating that the records inserted by transaction 2 are within the range of 1 S NK. However, S NK of transaction 2 is blocked by application S NK of transaction 1, indicating that the scope of transaction 1 S NK is larger than that of transaction 2 S NK. 4. Inferred from point 3, it can be proved that all the record range REC2 of transaction 2 is after all the record range REC1 of transaction 1, since REC2 < REC1, the inserted data of the business scene is:

The transaction1
('10076'.'150686'.'the 2017-06-11 08:39:15. 866'), ('10111'.'150686'.'the 2017-06-11 08:39:15. 866'), ('10133'.'214563'.'the 2017-06-11 08:39:15. 866'), ('10171'.'150686'.'the 2017-06-11 08:39:15. 866') transactions2
('15186'.'150686'.'the 2017-06-11 08:39:15. 866'), ('15186'.'151509'.'the 2017-06-11 08:39:15. 866'), ('15186'.'207522'.'the 2017-06-11 08:39:15. 866'), ('15187'.'151509'.'the 2017-06-11 08:39:15. 866')Copy the code

The actual insert data matched our expectations

5. From the above conclusion, we can get a deadlock cycle graph

4. Prevent deadlocks

Conditions under which a deadlock occurs: 1, the resources cannot be Shared, need can only be used by a process or thread 2, request and maintain, has not been released 3 locking resources self-sufficiency stays, not deprive, self to resources cannot be deprived of 4, circular wait Ways to prevent deadlocks is to avoid deadlock conditions, suitable for the problem solution are: 1. Deadlocks usually occur when keeping transactions short and executing multiple long-running transactions concurrently in the same database in a batch. The longer a transaction runs, the longer it will hold an exclusive lock or update lock, blocking other activities and potentially causing deadlocks. Keeping transactions in a batch minimizes the amount of network traffic for transactions, reduces the possible delay in completing transactions, and releases locks. 2. Use low isolation levels to determine whether transactions can run at lower isolation levels. Performing commit reads allows a transaction to read data that has been read (unmodified) by another transaction without waiting for the first transaction to complete. Using a lower isolation level (such as commit Read) instead of a higher isolation level (such as serializable Read) can shorten the time of holding the shared lock and thus reduce lock grabs (such as S NK and X IK are only available for Repeatable Read of InnoDB engine this time).