记一次线上死锁问题排查

最新推荐文章于 2023-12-02 19:35:40 发布

undefined--

最新推荐文章于 2023-12-02 19:35:40 发布

阅读量94

点赞数

文章标签： java mysql 安全

本文链接：https://blog.csdn.net/qq_29767649/article/details/134586957

版权

一、分析死锁日志

去数据库中将死锁日志下载

执行sql: SHOW ENGINE INNODB STATUS

=====================================
2023-11-23 21:01:25 0x7f75724c3700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 4 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 57152137 srv_active, 0 srv_shutdown, 4929829 srv_idle
srv_master_thread log flush and writes: 62081753
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 104206559
OS WAIT ARRAY INFO: signal count 8669932669
RW-shared spins 0, rounds 2364586667, OS waits 27018476
RW-excl spins 0, rounds 6610507666, OS waits 18381943
RW-sx spins 27801716, rounds 434452192, OS waits 5336373
Spin rounds per wait: 2364586667.00 RW-shared, 6610507666.00 RW-excl, 15.63 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-11-07 18:57:11 0x7f6767121700
*** (1) TRANSACTION:
TRANSACTION 2831641219, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 2
MySQL thread id 19046815, OS thread handle 140079181850368, query id 11788450197 10.2.0.21 jdadmin update
INSERT INTO md_file_info  ( object_type,
object_type2,
object_id,
file_url,
create_id,
create_by,
create_time,
update_id,
update_by,
update_time )  VALUES  ( 40,
10,
525293,
'/minio/cert/30/18220118906/4e8679a0df9740a41f7a931f9b3638b0.jpg',
119954,
'张健',
'2023-11-07 18:57:11.802',
119954,
'张健',
'2023-11-07 18:57:11.802' )
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 322 page no 82099 n bits 512 index index_md_file_info_object of table `smolp`.`md_file_info` trx id 2831641219 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 443 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 8; hex 80000000000803ed; asc         ;;
 1: len 4; hex 80000028; asc    (;;
 2: len 4; hex 80000014; asc     ;;
 3: len 8; hex 80000000008cab40; asc        @;;

*** (2) TRANSACTION:
TRANSACTION 2831641218, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
80 lock struct(s), heap size 24784, 123 row lock(s), undo log entries 74
MySQL thread id 19049437, OS thread handle 140082087597824, query id 11788450284 10.2.0.22 jdadmin update
INSERT INTO md_file_info  ( object_type,
object_type2,
object_id,
file_url,
create_id,
create_by,
create_time,
update_id,
update_by,
update_time )  VALUES  ( 40,
50,
525292,
'/minio/cert/30/18391186518/2a2d63100c81201660371f4e7a57ef48.jpg',
89894,
'姬存浪',
'2023-11-07 18:57:11.863',
89894,
'姬存浪',
'2023-11-07 18:57:11.863' )
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 322 page no 82099 n bits 512 index index_md_file_info_object of table `smolp`.`md_file_info` trx id 2831641218 lock_mode X locks gap before rec
Record lock, heap no 443 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 8; hex 80000000000803ed; asc         ;;
 1: len 4; hex 80000028; asc    (;;
 2: len 4; hex 80000014; asc     ;;
 3: len 8; hex 80000000008cab40; asc        @;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 322 page no 82099 n bits 512 index index_md_file_info_object of table `smolp`.`md_file_info` trx id 2831641218 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 443 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 8; hex 80000000000803ed; asc         ;;
 1: len 4; hex 80000028; asc    (;;
 2: len 4; hex 80000014; asc     ;;
 3: len 8; hex 80000000008cab40; asc        @;;

*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 2921447495
Purge done for trx's n:o < 2921447493 undo n:o < 0 state: running but idle
History list length 6

END OF INNODB MONITOR OUTPUT
============================