来分析 mysql 死锁了

[weishao666]

------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-03-15 15:32:15 7f1948331700
*** (1) TRANSACTION:
TRANSACTION 45939504, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1
MySQL thread id 6117, OS thread handle 0x7f1948435700, query id 135246315 10.24.94.10 root update
INSERT INTO run_pod ( cluster, NAME, image_name, cpu_limit, memory_limit, cpu_request, memory_request, create_by, run_pod_type, priority, expire_time, create_time, update_time )
VALUES
	(
		'local',
		'vnc-261',
		'vivado2017-novnc-largeimage',
		1.0,
		1024,
		0.1,
		10,
		'10.24.94.10',
		3,
		0,
		'2023-03-15 16:02:15.427',
		'2023-03-15 15:32:15.427',
	'2023-03-15 15:32:15.427' 
	)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 796 page no 4 n bits 136 index `UNI_NAME` of table `localbridge`.`run_pod` trx id 45939504 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 45938917, ACTIVE 2 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1
MySQL thread id 5550, OS thread handle 0x7f1948331700, query id 135245596 10.24.94.10 root update
INSERT INTO run_pod ( cluster, NAME, image_name, cpu_limit, memory_limit, cpu_request, memory_request, create_by, run_pod_type, priority, expire_time, create_time, update_time )
VALUES
	(
		'local',
		'vnc-260',
		'vivado2017-novnc-largeimage',
		1.0,
		1024,
		0.1,
		10,
		'10.24.94.10',
		3,
		0,
		'2023-03-15 16:02:13.957',
		'2023-03-15 15:32:13.957',
	'2023-03-15 15:32:13.957' 
	)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 796 page no 4 n bits 136 index `UNI_NAME` of table `localbridge`.`run_pod` trx id 45938917 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 796 page no 4 n bits 136 index `UNI_NAME` of table `localbridge`.`run_pod` trx id 45938917 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 48671067
Purge done for trx's n:o < 48671067 undo n:o < 0 state: running but idle
History list length 3428
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 7366, OS thread handle 0x7f194b78f700, query id 142556188 172.17.0.1 root init
show engine innodb status
---TRANSACTION 48670419, not started
MySQL thread id 7363, OS thread handle 0x7f19481ab700, query id 142554235 10.24.94.10 root cleaning up
---TRANSACTION 48671065, not started
MySQL thread id 7293, OS thread handle 0x7f19483f4700, query id 142556186 10.24.94.10 root cleaning up
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (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
5341 OS file reads, 18997801 OS file writes, 18824565 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 6.99 writes/s, 6.99 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 57 buffer(s)
20.98 hash searches/s, 0.00 non-hash searches/s

表有唯一索引 name ，主键自增，业务场景是有多个线程同时接受多个用户的请求，创建容器。

每个线程的操作为：查询该表，如果有 name 对应的记录，则更新，否则插入记录 由于不同的线程对应不同的用户，name 与用户一一对应，所以不同的线程处理的 name 都不会相同，不会存在多个线程操作同一条记录

mysql5.6 ，RR 的隔离级别 mysql 锁掌握得不深，是因为有索引，mysql 在索引前后加了间隙锁，导致我两条相邻的记录插入会死锁么，那这样的话应该很容易死锁，可我们也运行了很久，才出现死锁

[weishao666]

补充一下，兴许真和隔离级别有关，运行了很久的场景是另外一套环境，隔离级别是 RC ，这个是新部署的环境，隔离级别是 RR

[echo1937]

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 796 page no 4 n bits 136 index `UNI_NAME` of table `localbridge`.`run_pod` trx id 45939504 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

昨天刚看过，插入意向锁。
个人觉得 2023 年了，确实应该上 8.0 ，然后用 RC 代替 RR 。

[Richared]

没用同步直接换成 rc 吧，有同步还是上 8.0 在开 rc