InnoDB deadlock Case Analysis

In the Innodb deadlock case, paste the case information first:

* ** (1) TRANSACTION:
TRANSACTION 52ed1_61, ACTIVE 0 sec inserting
Mysql tables in use 1, locked 1
Lock wait 3 lock struct (s), heap size 1248, 2 row lock (s)
MySQL thread id 34815573, OS thread handle 0x7f1e42a6a700, query id 9442822687 192.168.1.216 pns update
Insert ignore into user_device_app (uid, aid) VALUES (22504356,219843041)
* ** (1) waiting for this lock to be granted:
Record locks space id 527 page no 7603 n bits 584 index 'primary' of table 'pns'. 'user _ device_app 'trx id 52ed1_61 lock_mode X lock
S rec but not gap waiting
* ** (2) TRANSACTION:
TRANSACTION 52ed000062, ACTIVE 0 sec inserting
Mysql tables in use 1, locked 1
3 lock struct (s), heap size 1248, 2 row lock (s)
MySQL thread id 34823701, OS thread handle 0x7f1e58309700, query id 9442822688 192.168.1.86 pns update
Insert ignore into user_device_app (uid, aid) VALUES (22504356,219843041)
* ** (2) holds the lock (S ):
Record locks space id 527 page no 7603 n bits 584 index 'primary' of table 'pns'. 'user _ device_app 'trx id 52ed000062 lock mode S lock
S rec but not gap
* ** (2) waiting for this lock to be granted:
Record locks space id 527 page no 7603 n bits 584 index 'primary' of table 'pns'. 'user _ device_app 'trx id 52ed1_62 lock_mode X lock
S rec but not gap waiting
* ** We roll back transaction (2)

Explanation: uid and aid are used as the primary key, and transactions are not enabled in the program. For user_device_app tables, only delete...; insert ignore..., select operations are available;
The deadlock is caused by two identical statements: insert ignore into user_device_app;
Why can't I reproduce the deadlock in my testing environment?
After consultation, insert ignore first obtains the S lock (shared lock) and then upgrades it to the X lock. (So after the S lock after transaction 2 in the deadlock information, again)
Because both transactions obtain the S lock at the same time and both are upgrading to the X lock, they are waiting for each other, so a deadlock occurs;
Summary:
Database: To avoid deadlock when using keywords such as ignore:
To disable parallel INSERT… execution of the same row... On duplicate key, especially INSERT... On duplicate key update and insert ignore statements. If this is not the case, change it to (select + insert for processing)
Program end
Code for capturing deadlock rollback information;
Restrictions can be imposed in front-end code to prevent repeated records from being clicked multiple times.

Recommended reading:

Startup, shutdown, and restoration of the InnoDB Storage Engine

MySQL InnoDB independent tablespace Configuration

Architecture of MySQL Server layer and InnoDB Engine Layer