愿意付费请教mysql死锁日志

Macrotea 发布于 2018/06/25 00:58
阅读 479
收藏 1

mysql 5.7 ,在进行update操作的时候出现了死锁

请教下如何定位和解决这个问题,本地jmeter压测却不能复现,请教下,愿意付费请教,也可以私聊我,着急,谢谢!

=====================================
2018-06-24 21:35:20 0x2b0c INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 25 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 15919 srv_active, 0 srv_shutdown, 3638 srv_idle
srv_master_thread log flush and writes: 19557
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 838468
OS WAIT ARRAY INFO: signal count 719240
RW-shared spins 0, rounds 923318, OS waits 386405
RW-excl spins 0, rounds 15243088, OS waits 427711
RW-sx spins 5718, rounds 136468, OS waits 1663
Spin rounds per wait: 923318.00 RW-shared, 15243088.00 RW-excl, 23.87 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-06-24 18:30:10 0x2b7c
*** (1) TRANSACTION:
TRANSACTION 227056446, ACTIVE 0 sec fetching rows
mysql tables in use 3, locked 3
LOCK WAIT 8 lock struct(s), heap size 1136, 55 row lock(s), undo log entries 1
MySQL thread id 9, OS thread handle 10480, query id 10554780 localhost 127.0.0.1 trading_test updating
/* ConnName=fOhMgxTR, ConnID=1, PoolName=db_trading_m1 */ UPDATE money_info
SET FreezeMoney=FreezeMoney-1217.88404481
WHERE (PassportID = 21 AND CoinTypeID = 1)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 95 page no 8 n bits 232 index PRIMARY of table `trading_test`.`money_info` trx id 227056446 lock_mode X locks rec but not gap waiting
Record lock, heap no 28 PHYSICAL RECORD: n_fields 13; compact format; info bits 0
 0: len 8; hex 800000000000006a; asc        j;;
 1: len 6; hex 0000030441da; asc     A ;;
 2: len 7; hex 60000001f32efd; asc `    . ;;
 3: len 5; hex 999f82f2e2; asc      ;;
 4: len 8; hex 8000000000000001; asc         ;;
 5: len 8; hex 0000000000000000; asc         ;;
 6: len 8; hex ce50fdffff3f8f40; asc  P   ? @;;
 7: len 8; hex 42c4a9f438229741; asc B   8" A;;
 8: len 8; hex 0000000000000000; asc         ;;
 9: SQL NULL;
 10: len 8; hex 8000000000000017; asc         ;;
 11: len 8; hex 4c31393238323531; asc L1928251;;
 12: len 5; hex 999f82f2e2; asc      ;;

*** (2) TRANSACTION:
TRANSACTION 227056456, ACTIVE 0 sec fetching rows
mysql tables in use 3, locked 3
7 lock struct(s), heap size 1136, 43 row lock(s)
MySQL thread id 53, OS thread handle 11132, query id 10554848 localhost 127.0.0.1 trading_test updating
/* ConnName=XgDFHsMG, ConnID=5, PoolName=db_trading_m1 */ UPDATE money_info
SET Money=Money+5.91
WHERE (PassportID = 23 AND CoinTypeID = 4)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 95 page no 8 n bits 232 index PRIMARY of table `trading_test`.`money_info` trx id 227056456 lock_mode X locks rec but not gap
Record lock, heap no 6 PHYSICAL RECORD: n_fields 13; compact format; info bits 0
 0: len 8; hex 8000000000000054; asc        T;;
 1: len 6; hex 0000004f4ec0; asc    ON ;;
 2: len 7; hex f7000001760110; asc     v  ;;
 3: len 5; hex 999f82f2e2; asc      ;;
 4: len 8; hex 8000000000000004; asc         ;;
 5: len 8; hex 0000000000000000; asc         ;;
 6: len 8; hex 0000000000000000; asc         ;;
 7: len 8; hex 0000000000000000; asc         ;;
 8: len 8; hex 0000000000000000; asc         ;;
 9: SQL NULL;
 10: len 8; hex 8000000000000012; asc         ;;
 11: len 8; hex 4c38383932353736; asc L8892576;;
 12: len 5; hex 999f82f2e2; asc      ;;

Record lock, heap no 11 PHYSICAL RECORD: n_fields 13; compact format; info bits 0
 0: len 8; hex 8000000000000059; asc        Y;;
 1: len 6; hex 0000004f4ed0; asc    ON ;;
 2: len 7; hex a6000001660110; asc     f  ;;
 3: len 5; hex 999f82f2e2; asc      ;;
 4: len 8; hex 8000000000000004; asc         ;;
 5: len 8; hex 0000000000000000; asc         ;;
 6: len 8; hex 0000000000000000; asc         ;;
 7: len 8; hex 0000000000000000; asc         ;;
 8: len 8; hex 0000000000000000; asc         ;;
 9: SQL NULL;
 10: len 8; hex 8000000000000013; asc         ;;
 11: len 8; hex 4c33363733313330; asc L3673130;;
 12: len 5; hex 999f82f2e2; asc      ;;

Record lock, heap no 16 PHYSICAL RECORD: n_fields 13; compact format; info bits 0
 0: len 8; hex 800000000000005e; asc        ^;;
 1: len 6; hex 0000004f4ee0; asc    ON ;;
 2: len 7; hex b5000001290110; asc     )  ;;
 3: len 5; hex 999f82f2e2; asc      ;;
 4: len 8; hex 8000000000000004; asc         ;;
 5: len 8; hex 0000000000000000; asc         ;;
 6: len 8; hex 0000000000000000; asc         ;;
 7: len 8; hex 0000000000000000; asc         ;;
 8: len 8; hex 0000000000000000; asc         ;;
 9: SQL NULL;
 10: len 8; hex 8000000000000014; asc         ;;
 11: len 8; hex 4c34383133393335; asc L4813935;;
 12: len 5; hex 999f82f2e2; asc      ;;

Record lock, heap no 28 PHYSICAL RECORD: n_fields 13; compact format; info bits 0
 0: len 8; hex 800000000000006a; asc        j;;
 1: len 6; hex 0000030441da; asc     A ;;
 2: len 7; hex 60000001f32efd; asc `    . ;;
 3: len 5; hex 999f82f2e2; asc      ;;
 4: len 8; hex 8000000000000001; asc         ;;
 5: len 8; hex 0000000000000000; asc         ;;
 6: len 8; hex ce50fdffff3f8f40; asc  P   ? @;;
 7: len 8; hex 42c4a9f438229741; asc B   8" A;;
 8: len 8; hex 0000000000000000; asc         ;;
 9: SQL NULL;
 10: len 8; hex 8000000000000017; asc         ;;
 11: len 8; hex 4c31393238323531; asc L1928251;;
 12: len 5; hex 999f82f2e2; asc      ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 95 page no 8 n bits 232 index PRIMARY of table `trading_test`.`money_info` trx id 227056456 lock_mode X locks rec but not gap waiting
Record lock, heap no 21 PHYSICAL RECORD: n_fields 13; compact format; info bits 0
 0: len 8; hex 8000000000000063; asc        c;;
 1: len 6; hex 00000d889b15; asc       ;;
 2: len 7; hex 2e000002fb3035; asc .    05;;
 3: len 5; hex 999f82f2e2; asc      ;;
 4: len 8; hex 8000000000000004; asc         ;;
 5: len 8; hex 0000000000000000; asc         ;;
 6: len 8; hex d3bce3141de554c0; asc       T ;;
 7: len 8; hex 10b633e79c902d41; asc   3   -A;;
 8: len 8; hex 0000000000000000; asc         ;;
 9: SQL NULL;
 10: len 8; hex 8000000000000015; asc         ;;
 11: len 8; hex 4c38323832363332; asc L8282632;;
 12: len 5; hex 999f82f2e2; asc      ;;

*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 227198027
Purge done for trx's n:o < 227198027 undo n:o < 0 state: running but idle
History list length 20
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 284583839998312, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 284583839997440, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 284583839996568, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 284583840006160, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 284583840068944, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 284583840068072, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 284583840067200, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 284583840066328, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 284583840065456, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 284583840064584, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 284583840063712, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 284583840008776, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 284583839995696, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
I/O thread 6 state: wait Windows aio (write thread)
I/O thread 7 state: wait Windows aio (write thread)
I/O thread 8 state: wait Windows aio (write thread)
I/O thread 9 state: wait Windows aio (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
2220375 OS file reads, 1054777 OS file writes, 667210 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 32.84 writes/s, 14.88 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 89, seg size 91, 41419 merges
merged operations:
 insert 55879, delete mark 39, delete 2
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 2212699, node heap has 1 buffer(s)
Hash table size 2212699, node heap has 1 buffer(s)
Hash table size 2212699, node heap has 1 buffer(s)
Hash table size 2212699, node heap has 1 buffer(s)
Hash table size 2212699, node heap has 20851 buffer(s)
Hash table size 2212699, node heap has 4108 buffer(s)
Hash table size 2212699, node heap has 49 buffer(s)
Hash table size 2212699, node heap has 683 buffer(s)
230.39 hash searches/s, 635.97 non-hash searches/s
---
LOG
---
Log sequence number 121439104637
Log flushed up to   121439104637
Pages flushed up to 121439096039
Last checkpoint at  121439090279
0 pending log flushes, 0 pending chkp writes
513022 log i/o's done, 6.72 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 8787066880
Dictionary memory allocated 9044330
Buffer pool size   524288
Free buffers       8192
Database pages     490401
Old database pages 180863
Modified db pages  54
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 16335403, not young 55924005
0.00 youngs/s, 0.00 non-youngs/s
Pages read 2220193, created 18609, written 481923
0.00 reads/s, 0.80 creates/s, 23.04 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: 490401, unzip_LRU len: 0
I/O sum[8384]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   65536
Free buffers       1024
Database pages     61304
Old database pages 22609
Modified db pages  7
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1646139, not young 6598729
0.00 youngs/s, 0.00 non-youngs/s
Pages read 247570, created 2278, written 51747
0.00 reads/s, 0.08 creates/s, 3.24 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: 61304, unzip_LRU len: 0
I/O sum[1048]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   65536
Free buffers       1024
Database pages     61295
Old database pages 22606
Modified db pages  2
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2232205, not young 7619610
0.00 youngs/s, 0.00 non-youngs/s
Pages read 330674, created 2141, written 37630
0.00 reads/s, 0.12 creates/s, 1.84 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: 61295, unzip_LRU len: 0
I/O sum[1048]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size   65536
Free buffers       1024
Database pages     61297
Old database pages 22607
Modified db pages  5
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2223087, not young 6725299
0.00 youngs/s, 0.00 non-youngs/s
Pages read 274014, created 1886, written 58574
0.00 reads/s, 0.00 creates/s, 2.08 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: 61297, unzip_LRU len: 0
I/O sum[1048]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size   65536
Free buffers       1024
Database pages     61299
Old database pages 22607
Modified db pages  5
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1859008, not young 6742876
0.00 youngs/s, 0.00 non-youngs/s
Pages read 253217, created 2178, written 76929
0.00 reads/s, 0.00 creates/s, 2.48 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: 61299, unzip_LRU len: 0
I/O sum[1048]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size   65536
Free buffers       1024
Database pages     61311
Old database pages 22612
Modified db pages  20
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2158285, not young 7076098
0.00 youngs/s, 0.00 non-youngs/s
Pages read 268094, created 2866, written 88672
0.00 reads/s, 0.00 creates/s, 5.64 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: 61311, unzip_LRU len: 0
I/O sum[1048]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size   65536
Free buffers       1024
Database pages     61305
Old database pages 22610
Modified db pages  8
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2052252, not young 7326046
0.00 youngs/s, 0.00 non-youngs/s
Pages read 279618, created 2008, written 51015
0.00 reads/s, 0.04 creates/s, 3.12 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: 61305, unzip_LRU len: 0
I/O sum[1048]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size   65536
Free buffers       1024
Database pages     61294
Old database pages 22606
Modified db pages  2
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1862941, not young 6439739
0.00 youngs/s, 0.00 non-youngs/s
Pages read 262059, created 2834, written 53458
0.00 reads/s, 0.56 creates/s, 1.92 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: 61294, unzip_LRU len: 0
I/O sum[1048]:cur[0], unzip sum[0]:cur[0]



---BUFFER POOL 7
Buffer pool size   65536
Free buffers       1024
Database pages     61296
Old database pages 22606
Modified db pages  5
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2301486, not young 7395608
0.00 youngs/s, 0.00 non-youngs/s
Pages read 304947, created 2418, written 63898
0.00 reads/s, 0.00 creates/s, 2.72 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: 61296, unzip_LRU len: 0
I/O sum[1048]: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=11164, Main thread ID=3136, state: sleeping
Number of rows inserted 421073, updated 89971, deleted 91, read 28404191056
9.52 inserts/s, 0.32 updates/s, 0.00 deletes/s, 212.19 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

加载中
0
zigzagroad
zigzagroad
数据量较多的情况下,在多线程环境下 对同一个表分别执行SQL更新语句(更新字段和条件字段相同),有可能会造成死锁现象。可以考虑在程序中加同步锁,然后在同步块程序中 先查询数据、后更新所查询到的数据 的方式来避免数据库锁死。
0
老菜鸟0217
老菜鸟0217
事务T1里,存在先update表a,再update表b ; 事务T2里,存在先update表b,再update表a ; 最容易死锁,注意尽量顺序一致。
0
foy
foy

money_info 表索引情况?

0
using
using

1:PassportID = 21 AND CoinTypeID = 1 是否有索引?用explain查看一下

2:检查你程序所有操作money_info这个表是不是有整表读取,或者大结果集读取的慢查询操作。导致你money_info这个表lock。如果有的话你用jmeter并发操作下。应该就能复现。

0
Tuesday
Tuesday

发一堆垃圾代码...

还不如发进程, 分析..  慢日志等数据. 

返回顶部
顶部