新的死锁分类： insert insert场景。求分析原因。

[maicon0610]

insert insert，
事务1等待锁：lock mode S waiting Record lock
事务2等待锁：lock_mode X waiting Record lock
事务2持有锁：lock_mode X locks rec but not gap Record lock

复现场景：
CREATE TABLE test123 (
id int(11) NOT NULL AUTO_INCREMENT,
a int(11) DEFAULT NULL,
b int(11) DEFAULT NULL,
c int(11) DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY uk (a,b)
) ENGINE=InnoDB AUTO_INCREMENT=0

事务0： insert into test123(a, b, c) values(0,1,171);
事务1： insert into test123(a, b, c) values(0,1,172);
事务2: insert into test123(a, b, c) values(0,1,173);
事务0: update test123 set a = 171 where c = 171;
事务0: commit;
事务1、2 死锁。

死锁日志：
'InnoDB', '', '

2021-03-30 14:56:09 700001b4a000 INNODB MONITOR OUTPUT

Per second averages calculated from the last 16 seconds

BACKGROUND THREAD

srv_master_thread loops: 46 srv_active, 0 srv_shutdown, 1094569 srv_idle
srv_master_thread log flush and writes: 1094545

SEMAPHORES

OS WAIT ARRAY INFO: reservation count 102
OS WAIT ARRAY INFO: signal count 101
Mutex spin waits 4356, rounds 4124, OS waits 4
RW-shared spins 90, rounds 2730, OS waits 91
RW-excl spins 3, rounds 159, OS waits 2
Spin rounds per wait: 0.95 mutex, 30.33 RW-shared, 53.00 RW-excl

LATEST DETECTED DEADLOCK

2021-03-30 14:55:39 700001b8e000
*** (1) TRANSACTION:
TRANSACTION 245654, ACTIVE 26 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
MySQL thread id 7, OS thread handle 0x700001bd2000, query id 446 localhost admin update
insert into test123(a, b, c) values(0,1,173)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 38 page no 4 n bits 80 index uk of table test.test123 trx id 245654 lock mode S waiting
Record lock, heap no 8 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
0: len 4; hex 80000000; asc ;;
1: len 4; hex 80000001; asc ;;
2: len 4; hex 8000001e; asc ;;

*** (2) TRANSACTION:
TRANSACTION 245648, ACTIVE 54 sec fetching rows
mysql tables in use 1, locked 1
7 lock struct(s), heap size 1184, 12 row lock(s), undo log entries 2
MySQL thread id 3, OS thread handle 0x700001b8e000, query id 447 localhost admin updating
update test123 set a = 171 where c = 171
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 38 page no 4 n bits 80 index uk of table test.test123 trx id 245648 lock_mode X locks rec but not gap
Record lock, heap no 8 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
0: len 4; hex 80000000; asc ;;
1: len 4; hex 80000001; asc ;;
2: len 4; hex 8000001e; asc ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 38 page no 3 n bits 80 index PRIMARY of table test.test123 trx id 245648 lock_mode X waiting
Record lock, heap no 11 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000020; asc ;;
1: len 6; hex 00000003bf96; asc ;;
2: len 7; hex e0000001b80110; asc ;;
3: len 4; hex 80000000; asc ;;
4: len 4; hex 80000001; asc ;;
5: len 4; hex 800000ad; asc ;;

*** WE ROLL BACK TRANSACTION (1)

TRANSACTIONS

Trx id counter 245656
Purge done for trx's n:o < 245656 undo n:o < 0 state: running but idle
History list length 228
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 245653, not started
MySQL thread id 6, OS thread handle 0x700001c16000, query id 443 localhost admin
---TRANSACTION 245654, not started
MySQL thread id 7, OS thread handle 0x700001bd2000, query id 446 localhost admin
---TRANSACTION 245648, not started
MySQL thread id 3, OS thread handle 0x700001b8e000, query id 448 localhost admin
---TRANSACTION 0, not started
MySQL thread id 1, OS thread handle 0x700001b06000, query id 456 localhost 127.0.0.1 root
---TRANSACTION 245647, not started
MySQL thread id 2, OS thread handle 0x700001b4a000, query id 458 localhost 127.0.0.1 root init
show engine innodb status

FILE I/O

I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
408 OS file reads, 534 OS file writes, 450 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s

INSERT BUFFER AND ADAPTIVE HASH INDEX

Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 276671, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s

LOG

Log sequence number 42684143
Log flushed up to 42684143
Pages flushed up to 42684143
Last checkpoint at 42684143
0 pending log writes, 0 pending chkp writes
208 log i/o's done, 0.00 log i/o's/second

BUFFER POOL AND MEMORY

Total memory allocated 137363456; in additional pool allocated 0
Dictionary memory allocated 92081
Buffer pool size 8191
Free buffers 7784
Database pages 406
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 391, created 15, written 340
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 406, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

ROW OPERATIONS

0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Main thread id 123145325797376, state: sleeping
Number of rows inserted 14, updated 11, deleted 4, read 75
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s

END OF INNODB MONITOR OUTPUT

'