MySQL死锁案例分析
- 2025-06-02 16:09:00
- 丁国栋
- 原创 26
=====================================
2025-06-02 14:37:23 0x7fdeafcff700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 1 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 4595146 srv_active, 0 srv_shutdown, 2512 srv_idle
srv_master_thread log flush and writes: 4597658
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 3211579
OS WAIT ARRAY INFO: signal count 7202133
RW-shared spins 0, rounds 24029227, OS waits 1309440
RW-excl spins 0, rounds 99133921, OS waits 14071
RW-sx spins 201140, rounds 1726826, OS waits 13304
Spin rounds per wait: 24029227.00 RW-shared, 99133921.00 RW-excl, 8.59 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2025-06-02 14:33:33 0x7fdedf1ff700
*** (1) TRANSACTION:
TRANSACTION 772524087, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 85685566, OS thread handle 140657389729536, query id 2702858504 10.12.1.95 zentao_net_rw Searching rows for update
UPDATE eps_contact SET `counts` = '0' wHeRe counts < '0' AND eps_contact.lang in('zh-cn', 'all')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1335 page no 3 n bits 160 index PRIMARY of table `zentao_net`.`eps_contact` trx id 772524087 lock_mode X locks rec but not gap waiting
Record lock, heap no 14 PHYSICAL RECORD: n_fields 14; compact format; info bits 0
0: len 2; hex 8480; asc ;;
1: len 6; hex 00002e0bc836; asc . 6;;
2: len 7; hex 5a000002b20a2b; asc Z +;;
3: len 30; hex 616c6c202020202020202020202020202020202020202020202020202020; asc all ;;
4: len 9; hex e9ad8fe4b8ade698be; asc ;;
5: len 11; hex 3138353631393339373236; asc 18561939726;;
6: len 24; hex 7765697a686f6e677869616e406368616e64616f2e636f6d; asc weizhongxian@chandao.com;;
7: len 10; hex 31373436373439333938; asc 1746749398;;
8: len 11; hex 3138353631393339373236; asc 18561939726;;
9: len 0; hex ; asc ;;
10: len 2; hex 800f; asc ;;
11: len 2; hex 7fff; asc ;;
12: len 30; hex 7a682d636e20202020202020202020202020202020202020202020202020; asc zh-cn ;;
13: len 4; hex 80a18e70; asc p;;
*** (2) TRANSACTION:
TRANSACTION 772524086, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 85685567, OS thread handle 140595202881280, query id 2702858503 10.12.1.95 zentao_net_rw updating
UPDATE eps_contact SET counts=counts-1, total=total+1 wHeRe id = '1152' AND eps_contact.lang in('zh-cn', 'all')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1335 page no 3 n bits 160 index PRIMARY of table `zentao_net`.`eps_contact` trx id 772524086 lock_mode X locks rec but not gap
Record lock, heap no 14 PHYSICAL RECORD: n_fields 14; compact format; info bits 0
0: len 2; hex 8480; asc ;;
1: len 6; hex 00002e0bc836; asc . 6;;
2: len 7; hex 5a000002b20a2b; asc Z +;;
3: len 30; hex 616c6c202020202020202020202020202020202020202020202020202020; asc all ;;
4: len 9; hex e9ad8fe4b8ade698be; asc ;;
5: len 11; hex 3138353631393339373236; asc 18561939726;;
6: len 24; hex 7765697a686f6e677869616e406368616e64616f2e636f6d; asc weizhongxian@chandao.com;;
7: len 10; hex 31373436373439333938; asc 1746749398;;
8: len 11; hex 3138353631393339373236; asc 18561939726;;
9: len 0; hex ; asc ;;
10: len 2; hex 800f; asc ;;
11: len 2; hex 7fff; asc ;;
12: len 30; hex 7a682d636e20202020202020202020202020202020202020202020202020; asc zh-cn ;;
13: len 4; hex 80a18e70; asc p;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1335 page no 5 n bits 520 index counts of table `zentao_net`.`eps_contact` trx id 772524086 lock_mode X locks rec but not gap waiting
Record lock, heap no 333 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 2; hex 8000; asc ;;
1: len 2; hex 8480; asc ;;
*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 772554475
Purge done for trx's n:o < 772554411 undo n:o < 0 state: running but idle
History list length 303
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422163356068784, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422163356067776, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422163356066768, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422163356065760, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422163356064752, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422163356063744, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422163356062736, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422163356060720, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422163356056688, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422163356058704, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422163356061728, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422163356057696, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422163356059712, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422163356054672, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
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] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
42695132 OS file reads, 128444527 OS file writes, 116929921 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 28.97 writes/s, 28.97 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 686, seg size 688, 2940056 merges
merged operations:
insert 3067308, delete mark 179542, delete 22545
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 796967, node heap has 0 buffer(s)
Hash table size 796967, node heap has 0 buffer(s)
Hash table size 796967, node heap has 0 buffer(s)
Hash table size 796967, node heap has 0 buffer(s)
Hash table size 796967, node heap has 0 buffer(s)
Hash table size 796967, node heap has 0 buffer(s)
Hash table size 796967, node heap has 0 buffer(s)
Hash table size 796967, node heap has 0 buffer(s)
0.00 hash searches/s, 75004.00 non-hash searches/s
---
LOG
---
Log sequence number 353062856535
Log flushed up to 353062856423
Pages flushed up to 352689247997
Last checkpoint at 352689247997
0 pending log flushes, 0 pending chkp writes
113381458 log i/o's done, 29.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 3303014400
Dictionary memory allocated 4185214
Buffer pool size 196608
Free buffers 2047
Database pages 194561
Old database pages 71780
Modified db pages 20676
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 244100107, not young 663538594
0.00 youngs/s, 0.00 non-youngs/s
Pages read 42692839, created 1620361, written 13641102
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: 194561, unzip_LRU len: 0
I/O sum[16]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 98304
Free buffers 1023
Database pages 97281
Old database pages 35890
Modified db pages 10122
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 120172637, not young 337502333
0.00 youngs/s, 0.00 non-youngs/s
Pages read 21380414, created 812624, written 6759612
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: 97281, unzip_LRU len: 0
I/O sum[8]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 98304
Free buffers 1024
Database pages 97280
Old database pages 35890
Modified db pages 10554
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 123927470, not young 326036261
0.00 youngs/s, 0.00 non-youngs/s
Pages read 21312425, created 807737, written 6881490
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: 97280, unzip_LRU len: 0
I/O sum[8]: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=483148, Main thread ID=140684315055872, state: sleeping
Number of rows inserted 133306552, updated 56774410, deleted 1035749, read 728022553457
93.91 inserts/s, 15.98 updates/s, 0.00 deletes/s, 174794.21 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
--
发表评论
