Remember one time online MySQL database deadlock problem

The recent online project reported a MySQL deadlock (deallock) error, although the business is not any impact, because of their own database lock this piece of knowledge is not much, before also did not how to encounter on the line. This time just met, then record it here.

• There is a deadlock problem background

Project level: The error of the project is to do a batch order of the action, will be written to multiple orders at the same time, the code is written in a transaction in a loop a single insert to the database (as for why no use of BULK insert will not be pursued, historical reasons). Database level: A test table (not a real table on the line), it is more important to have a unique index of type and name. Transaction ISOLATION level: Read commited

CREATE TABLE' Test ' (' ID ')bigint( One) not NULL, ' name 'varchar( +)CHARACTER SETUTF8 COLLATE utf8_general_ciNULL DEFAULT NULL, ' type 'tinyint(4)NULL DEFAULT NULL, ' uid 'int( One)NULL DEFAULT NULL ,PRIMARY KEY(' id '),UNIQUE INDEX' uniq_type_name ' (' type ', ' name ') USING BTREE) ENGINE=InnoDBDEFAULT CHARACTER SET=UTF8 COLLATE=Utf8_general_cirow_format=COMPACT;

The case of a deadlock is the bulk order of the interface, the external repeated requests two times, two times separated by 10 milliseconds. Two requests executed SQL (one request executes three inserts) except for the primary key ID, the others are the same:

Insert  intoTest (ID, name, type, UID)Values(1, "DT590",3,1001);Insert  intoTest (ID, name, type, UID)Values(2, "DT589",3,1001);Insert  intoTest (ID, name, type, UID)Values(3, "DT588",3,1001);

Deadlock Log for error:

------------------------LATEST detected DEADLOCK------------------------2018- .- + Ten:Wuyi:Geneva2b16deb03700***(1)TRANSACTION:TRANSACTION 1905650677, ACTIVE0.001sec insertingmysql Tablesinch  Use 1, Locked1LOCK WAIT2Lock struct (s), Heap size the,1Row lock (s), undoLogEntries1LOCK BLOCKING MySQL thread ID:16983306Block34208692MySQL thread ID34208692, OS thread handle0x2b2203b0b700, Query ID9093982364 172.24.18.106App_redcliffcUpdateINSERT  into' Test ' (ID, name, type, UID)VALUES(4,'DT590',3,1001)***(1) Waiting forThis LOCK toBe Granted:record LOCKSSpaceId138Page no16492N Bits408 Index' Uniq_type_name ' of Table' DB '. ' Test ' TRX ID1905650677lock mode S Waitingrecord lock, Heap no341Physical Record:n_fields3; Compact format; Info bits00:Len 4; Hex80000048;ASCH;;1:Len Ten; Hex44543631393230363835;ASCDT61920685;;2:Len 8; Hex 0461116807c09a00;ASCa h;;***(2)TRANSACTION:TRANSACTION 1905650675, ACTIVE0.004sec insertingmysql Tablesinch  Use 1, Locked13Lock struct (s), Heap size1184,2Row lock (s), undoLogEntries2MySQL thread ID16983306, OS thread handle0x2b16deb03700, Query ID9093982366 172.24.18.105App_redcliffcUpdateINSERT  into' Test ' (ID, name, type, UID)VALUES(2,'DT589',3,1001)***(2) holds the LOCK (S): RECORD LOCKSSpaceId138Page no16492N Bits408 Index' Uniq_type_name ' of Table' DB '. ' Test ' TRX ID1905650675Lock_mode X Locks Rec but notGaprecord Lock, Heap no341Physical Record:n_fields3; Compact format; Info bits00:Len 4; Hex80000048;ASCH;;1:Len Ten; Hex44543631393230363835;ASCDT61920685;;2:Len 8; Hex 0461116807c09a00;ASCa h;;***(2) Waiting forThis LOCK toBe Granted:record LOCKSSpaceId138Page no16492N Bits408 Index' Uniq_type_name ' of Table' DB '. ' Test ' TRX ID1905650675Lock_mode X Locks Gap before RecInsertintention Waitingrecord Lock, heap no341Physical Record:n_fields3; Compact format; Info bits00:Len 4; Hex80000048;ASCH;;1:Len Ten; Hex44543631393230363835;ASCDT61920685;;2:Len 8; Hex 0461116807c09a00;ASCa h;;***WE Roll BackTRANSACTION(1)

• Problem analysis

Deadlock Log Analysis

Session1		Session2
INSERT into Test (ID, name, type, UID) VALUES (1, "DT590", 3, 1001);	The first record of the transaction first, insert DT590, successful
INSERT into Test (ID, name, type, UID) VALUES (2, "DT589", 3, 1001);	Transaction one continues to insert the next day DT589 record, this time the transaction two requested, began inserting the first record DT590, then reported the deadlock, transaction two rollback, transaction one successful execution	INSERT into Test (ID, name, type, UID) VALUES (1, "DT590", 3, 1001);

Session1	Holding lock	Session2	Holding lock
INSERT into Test (ID, name, type, UID) VALUES (1, "DT590", 3, 1001);	Insert a record that is not in a database and add an X lock to the DT590 record
INSERT into Test (ID, name, type, UID) VALUES (2, "DT589", 3, 1001);	When the transaction is inserted into the DT589, it is found that this record already has a gap lock (DT589 This record is just the transaction two DT590 when the application of Gap lock included), will first apply for an insert intention waiting insert intent lock, This lock and transaction two holds the gap lock mutex and a deadlock occurs. Transaction one in the same transaction two release this record gap lock, transaction two at the same transaction one release DT590 x lock	INSERT into Test (ID, name, type, UID) VALUES (1, "DT590", 3, 1001);	Transaction two inserts a unique index DT590 this record, found that the record has an X lock, so will apply for a record of the S lock and Gap lock

• Related to some lock knowledge

The InnoDB lock is subdivided into the following seed types: The record lock (RK) lock is directly added to the index record, and the key Gap Lock (GK) Gap Lock is locked, locking a range but excluding the record itself 。 The purpose of the gap lock is to prevent two times of the same transaction from being read, the presence of Phantom reads next key lock (NK) row lock and Gap lock together called Next-key lock Insert Intent Ion Lock (IK) If the gap is locked by gap before insertion, insert will request an intent lock. Because in order to avoid phantom reading, when other transactions hold the interval lock of the gap, insert intent lock will be blocked (do not use gap lock directly, because gap lock is not mutually exclusive) inserta unique index in the lock logic: first to do a unique index collision detection, if there is a target row, the target line is first to add S NK,

• Summarize

1. Ensure that the transaction is short and in one batch, avoid the loop insert deadlock problem here the scene record deadlock is the concurrent insertion of multiple records, the order of the same deadlock, in the concurrent insert if the order of a different deadlock probability will be greater.

Remember one time online MySQL database deadlock problem