Isolation Level is: Repeat Read(RR) MySQL 5.6 DeadLock log:
------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-09-21 10:01:58 2b0d1fb0b700
*** (1) TRANSACTION:
TRANSACTION 19414864283, ACTIVE 0.448 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 9 lock struct(s), heap size 2936, 4 row lock(s), undo log entries 4
LOCK BLOCKING MySQL thread id: 8219895 block 8219858
MySQL thread id 8219858, OS thread handle 0x2b0d3978d700, query id 194299614451 10.111.76.151 test_trade update
insert into merchandise (merchandise_no, serial_no, `status`,
expand_status, quantity, title, describes)
values ('TR20220111100058055986', '20000666665555620435', 20,
10, 1, '',
'')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 9057 page no 5298 n bits 600 index `uk_serial_no` of table `test_trade`.`merchandise` trx id 19414864283 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 144 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 20; hex 3230323230363236313635343139373736383435; asc 20000666665555776845;;
1: len 4; hex 00f06178; asc ax;;
*** (2) TRANSACTION:
TRANSACTION 19414864275, ACTIVE 0.412 sec inserting
mysql tables in use 1, locked 1
8 lock struct(s), heap size 1184, 4 row lock(s), undo log entries 4
MySQL thread id 8219895, OS thread handle 0x2b0d1fb0b700, query id 194299614132 10.111.76.156 test_trade update
insert into merchandise (merchandise_no, serial_no, `status`,
expand_status, quantity, title, describes)
values ('TR20220111100058135388', '20000666665555630510', 20,
10, 1, '',
'')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 9057 page no 5298 n bits 600 index `uk_serial_no` of table `test_trade`.`merchandise` trx id 19414864275 lock mode S
Record lock, heap no 144 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 20; hex 3230323230363236313635343139373736383435; asc 20000666665555776845;;
1: len 4; hex 00f06178; asc ax;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 9057 page no 5298 n bits 600 index `uk_serial_no` of table `test_trade`.`merchandise` trx id 19414864275 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 144 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 20; hex 3230323230363236313635343139373736383435; asc 20000666665555776845;;
1: len 4; hex 00f06178; asc ax;;
*** WE ROLL BACK TRANSACTION (2)
Table merchandise has a primary key and auto-incre column "id", and a unique key column "serial_no". From the deadlock log, I know then reason of DeadLock: the insert intention X-lock of transaction(2) waiting the insert intention X-lock of transaction(1),and then insert intention X-lock of transaction(1) waiting then S-lock of transaction(2).
I do not know 'Why is there a S-lock'? Then column serial_no is unique key, and there is not select sql in my server.