当前位置： 首页 > article >正文

mysql-死锁排查

article 2025/3/1 16:17:19

模拟sql

事务1

START TRANSACTION;

-- 执行一些事务操作
UPDATE admin SET `password` ="t2" WHERE id =2;

-- 休眠10秒
SELECT SLEEP(10);

-- 继续执行事务操作
UPDATE admin SET `password` ="t2" WHERE id =1;

-- 提交事务
COMMIT;

事务2

START TRANSACTION;

-- 执行一些事务操作
UPDATE admin SET `password` ="t1" WHERE id =1;

-- 休眠10秒
SELECT SLEEP(10);

-- 继续执行事务操作
UPDATE admin SET `password` ="t1" WHERE id =2;

-- 提交事务
COMMIT;

查询锁信息

SHOW ENGINE INNODB STATUS;

-- LATEST DETECTED DEADLOCK 部分以查看最近检测到的死锁信息。
-- TRANSACTIONS 部分查看当前活动的事务和它们持有的锁


=====================================
2025-01-11 15:18:54 140485557905152 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 35 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 1093522 srv_active, 0 srv_shutdown, 296 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 19395
OS WAIT ARRAY INFO: reservation count 0
OS WAIT ARRAY INFO: reservation count 20996
OS WAIT ARRAY INFO: reservation count 0
OS WAIT ARRAY INFO: signal count 1258173
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx




------------------------
## 最近一次的死锁信息
LATEST DETECTED DEADLOCK 
------------------------
2025-01-11 15:18:33 140486060799744
*** (1) TRANSACTION:
TRANSACTION 50151418, ACTIVE 18 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
MySQL thread id 11660, OS thread handle 140485558957824, query id 8412959 112.10.132.142 root updating
-- 继续执行事务操作
UPDATE admin SET `password` ="t1" WHERE id =2

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 7 page no 5 n bits 320 index PRIMARY of table `test`.`admin` trx id 50151418 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 64
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000002fd3ffa; asc     ? ;;
 2: len 7; hex 020000014c1331; asc     L 1;;
 3: len 5; hex 526f736531; asc Rose1;;
 4: len 2; hex 7431; asc t1;;
 5: len 20; hex 00000007000076720000000100000000000084f2; asc       vr            ;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 7 page no 5 n bits 320 index PRIMARY of table `test`.`admin` trx id 50151418 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 64
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 000002fd3dec; asc     = ;;
 2: len 7; hex 01000001a82bc5; asc      + ;;
 3: len 5; hex 526f736532; asc Rose2;;
 4: len 2; hex 7432; asc t2;;
 5: len 20; hex 00000007000076750000000100000000000084f2; asc       vu            ;;


*** (2) TRANSACTION:
TRANSACTION 50151415, ACTIVE 20 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 11661, OS thread handle 140485174580992, query id 8413015 112.10.132.142 root updating
-- 继续执行事务操作
UPDATE admin SET `password` ="t2" WHERE id =1

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 7 page no 5 n bits 320 index PRIMARY of table `test`.`admin` trx id 50151415 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 64
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 000002fd3dec; asc     = ;;
 2: len 7; hex 01000001a82bc5; asc      + ;;
 3: len 5; hex 526f736532; asc Rose2;;
 4: len 2; hex 7432; asc t2;;
 5: len 20; hex 00000007000076750000000100000000000084f2; asc       vu            ;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 7 page no 5 n bits 320 index PRIMARY of table `test`.`admin` trx id 50151415 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 64
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000002fd3ffa; asc     ? ;;
 2: len 7; hex 020000014c1331; asc     L 1;;
 3: len 5; hex 526f736531; asc Rose1;;
 4: len 2; hex 7431; asc t1;;
 5: len 20; hex 00000007000076720000000100000000000084f2; asc       vr            ;;

*** WE ROLL BACK TRANSACTION (2)




------------
## 事务信息
TRANSACTIONS
------------
Trx id counter 50151516
Purge done for trx's n:o < 50151500 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421965119620936, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421965119627464, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421965119626648, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421965119624200, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421965119617672, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421965119619304, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421965119621752, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421965119622568, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421965119620120, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421965119616856, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests ((null))
I/O thread 1 state: waiting for completed aio requests (insert buffer 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)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:
Pending flushes (fsync) log: 0; buffer pool: 0
9632813 OS file reads, 19016805 OS file writes, 5306225 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 2.68 writes/s, 2.59 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 103, node heap has 0 buffer(s)
Hash table size 103, node heap has 0 buffer(s)
Hash table size 103, node heap has 0 buffer(s)
Hash table size 103, node heap has 0 buffer(s)
Hash table size 103, node heap has 0 buffer(s)
Hash table size 103, node heap has 0 buffer(s)
Hash table size 103, node heap has 0 buffer(s)
Hash table size 103, node heap has 0 buffer(s)
0.00 hash searches/s, 1.60 non-hash searches/s
---
LOG
---
Log sequence number          84728494304
Log buffer assigned up to    84728494304
Log buffer completed up to   84728494304
Log written up to            84728494304
Log flushed up to            84728494304
Added dirty pages up to      84728494304
Pages flushed up to          84726311217
Last checkpoint at           84726311217
Log minimum file id is       834
Log maximum file id is       861
14013510 log i/o's done, 2.69 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 0
Dictionary memory allocated 650712
Buffer pool size   196538
Free buffers       4096
Database pages     192442
Old database pages 70873
Modified db pages  256
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0, flush chunk 0
Pages made young 9893609, not young 66190397
7.97 youngs/s, 0.00 non-youngs/s
Pages read 9628407, created 2312940, written 4419975
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 61 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 192442, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   24565
Free buffers       512
Database pages     24053
Old database pages 8858
Modified db pages  73
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0, flush chunk 0
Pages made young 183191, not young 10042965
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1194013, created 288162, written 510147
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 24053, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   24566
Free buffers       512
Database pages     24054
Old database pages 8859
Modified db pages  26
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0, flush chunk 0
Pages made young 122648, not young 8337947
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1205535, created 288306, written 473133
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 24054, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size   24569
Free buffers       512
Database pages     24057
Old database pages 8860
Modified db pages  27
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0, flush chunk 0
Pages made young 123579, not young 8424889
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1231676, created 290304, written 467199
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 24057, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size   24565
Free buffers       512
Database pages     24053
Old database pages 8858
Modified db pages  1
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0, flush chunk 0
Pages made young 131695, not young 7231499
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1197689, created 289268, written 488615
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 24053, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size   24570
Free buffers       512
Database pages     24058
Old database pages 8860
Modified db pages  30
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0, flush chunk 0
Pages made young 123054, not young 6926477
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1194465, created 289434, written 465651
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 24058, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size   24566
Free buffers       512
Database pages     24054
Old database pages 8859
Modified db pages  33
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0, flush chunk 0
Pages made young 8876204, not young 7734313
7.97 youngs/s, 0.00 non-youngs/s
Pages read 1187514, created 287826, written 771450
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 71 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 24054, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size   24565
Free buffers       512
Database pages     24053
Old database pages 8858
Modified db pages  31
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0, flush chunk 0
Pages made young 133615, not young 7639514
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1211198, created 289369, written 469927
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 24053, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size   24572
Free buffers       512
Database pages     24060
Old database pages 8861
Modified db pages  35
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0, flush chunk 0
Pages made young 199623, not young 9852793
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1206317, created 290271, written 773853
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 24060, 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
Process ID=973, Main thread ID=140486016759552 , state=sleeping
Number of rows inserted 98629321, updated 696110, deleted 10000001, read 1282756636
91.28 inserts/s, 0.03 updates/s, 0.00 deletes/s, 91.31 reads/s
Number of system rows inserted 81044, updated 119863, deleted 80920, read 569472
0.09 inserts/s, 0.11 updates/s, 0.00 deletes/s, 0.60 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

LATEST DETECTED DEADLOCK   死锁

TRANSACTIONS  部分查看当前活动的事务和它们持有的锁


http://www.kler.cn/a/500159.html

相关文章：

  • 计算机网络 （34）可靠传输的工作原理
  • [石榴翻译] 维吾尔语音识别 + TTS语音合成
  • Lua语言的多线程编程
  • Haskell语言的数据结构
  • BurpSuite之FUZZ模糊测试
  • Swagger学习⑱——@Callback 注解
  • Cygwin, MinGW
  • (java) String 字符串
  • simulink建模与仿真代做matlab程序代编设计帮做电力电子电机控制
  • Django Admin中添加自定义按钮与默认按钮并列
  • Objective-C语言的网络编程
  • 蓝桥与力扣刷题（389 找不同）
  • 设计一篇利用python爬虫获取1688详情API接口的长篇软文
  • 芯片：为何英伟达的GPU能在AI基础设施领域扮演重要角色？
  • 【网络协议】交换机概念与配置（第一部分）
  • _STM32关于CPU超频的参考_HAL
  • RabbitMQ介绍及安装（基于docker）
  • 服务器多节点 Grafana、Prometheus 和 Node-Exporter Docker版本部署指南
  • Bash语言的正则表达式
  • 9.java中String,StringBuilder,StringBuffer 什么区别