A log

------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-07-27 16:28:53 0x7fc914aee700
*** (1) TRANSACTION:
TRANSACTION 484991260, ACTIVE 0 sec setting auto-inc lock
mysql tables inuse 2, locked 2 LOCK WAIT 3 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 319333, OS thread handle 140501656090368, Query ID 185921901 192.168.1.2 beta EXECUTING INSERT INTO P_session (transaction_id,status) SELECT'18'.'ON' FROM dual WHERE NOT EXISTS (SELECT transaction_id FROM p_session WHERE transaction_id = '18')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `mmm`.`p_session` trx id 484991260 lock mode AUTO-INC waiting


*** (2) TRANSACTION:
TRANSACTION 484991259, ACTIVE 0 sec inserting
mysql tables in use 2, locked 2
5 lock struct(s), heap size 1136, 2 row lock(s), undo logentries 1 MySQL thread id 319335, OS thread handle 140501612160768, Query ID 185921900 192.168.1.2 beta EXECUTING INSERT INTO P_session (transaction_id,status) SELECT'18'.'ON' FROM dual WHERE NOT EXISTS (SELECT transaction_id FROM p_session WHERE transaction_id = '18')
*** (2) HOLDS THE LOCK(S):
TABLE LOCK table `msc`.`p_session` trx id 484991259 lock mode AUTO-INC
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1019 page no 8 n bits 600 index uniq_transaction_id of table `mmm`.`p_session` trx id 484991259 lock_mode X locks gap before rec insert intention waiting
*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-07-27 16:28:53 0x7fc914aee700
*** (1) TRANSACTION:
TRANSACTION 484991260, ACTIVE 0 sec setting auto-inc lock
mysql tables inuse 2, locked 2 LOCK WAIT 3 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 319333, OS thread handle 140501656090368, Query ID 185921901 192.168.1.2 beta EXECUTING INSERT INTO P_session (transaction_id,status) SELECT'18'.'ON' FROM dual WHERE NOT EXISTS (SELECT transaction_id FROM pay_session WHERE transaction_id = '18')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `mmm`.`p_session` trx id 484991260 lock mode AUTO-INC waiting


*** (2) TRANSACTION:
TRANSACTION 484991259, ACTIVE 0 sec inserting
mysql tables in use 2, locked 2
5 lock struct(s), heap size 1136, 2 row lock(s), undo logentries 1 MySQL thread id 319335, OS thread handle 140501612160768, Query ID 185921900 192.168.1.2 beta EXECUTING INSERT INTO P_session (transaction_id,status) SELECT'18'.'ON' FROM dual WHERE NOT EXISTS (SELECT transaction_id FROM p_session WHERE transaction_id = '18')
*** (2) HOLDS THE LOCK(S):
TABLE LOCK table `mmm`.`p_session` trx id 484991259 lock mode AUTO-INC
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1019 page no 8 n bits 600 index uniq_transaction_id of table `mmm`.`p_session` trx id 484991259 lock_mode X locks gap before rec insert intention waiting
*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Copy the code

This problem: searched the Internet for a long time, but did not find the same deadlock log.

Environment: MySQL 5.7. Isolation level RR. The default value is 1.

Table P_session has a unique index, uniq_transaction_id, and id is an incremented column.

Although the question has been settled, try to write down my understanding.

 

Tran1, trans 2 obtained the next key lock in insert SELECT.

The official example is three concurrent inserts

INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row. Prior to inserting the row, a type of gap lock called an insert intention gap lock is set. This lock signals the intent to insert in such a way that  multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6 each lock the gap between 4 and 7 with insert intention locks prior to obtaining the  exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting. If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock. This can occur if another session deletes the row. Suppose that an InnoDB table t1 has the following structure:Copy the code

If a duplicate-key error occurs, a shared lock on the duplicate index record is set. Is to get the shared S lock.

Self-increment lock

Innodb_autoinc_lock_mode = 1 (" row "lock mode) This is the default lock mode. In This mode, "Bulk inserts" use the special auto-inc table-level lock and hold it until the end of the statement. This applies to all  INSERT ... SELECT, REPLACE ... SELECT, and LOAD DATA statements. Only one statement holding the AUTO-INC lock can execute at a time. If the source table of the  bulk insert operation is different from the target table, the AUTO-INC lock on the target table is taken after a shared lock is taken on the first row selected from the source table. If the source and target of the bulk insert operation are the same table, the AUTO-INC lock is taken after shared locks are taken on all selected rows.Copy the code

Default mode =1, If the source and target of the bulk insert operation are the same table, the AUTO-INC lock is taken after shared locks are taken on all selected rows. Operate on the same table, so the increment lock is acquired after the shared S lock.

* * * * * * * * * * * * * * * * * * *

Go back to the log.

Trans1 holds the shared lock and waits for trans 2 to add the lock.

Trans2 has acquired the auto-add lock and will request to insert the intent lock. (Insert intent lock and wait for Trans1’s shared lock) deadlock occurred.

 

* * * * * * * * * * * * * *

Pure speculation, please tell me.