用户反馈服务器日志中出现大量:Deadlock found when trying to get lock; Try restarting transaction message from server: "Lock wait timeout exceeded; try restarting transaction";
进行了一段时间loadrunner并发测试并在网上找了很多先行者的文章,之后终于找到了解决的方法:
基本条件:
服务器存在两个进程(线程)均需要对同一个table进行插入动作:
表xxshistory(id2,id1,row1,row2),主键(id2,id1,row1),外键FK_1(id2,id1)
表zzequip主键(id2,id1),含字段netstat
线程1:
对xxshistory表插入当天数据,如:
insert into xxshistory(id2,id1,row1,row2)values(?,?,?,?);
线程2:
对xxshistory表插入当天数据,sql例:
INSERT INTO xxshistory (id1,row1,row2,id2) SELECT * FROM (SELECT h.id1,'2016-11-30','3 ' , h.id2 FROM xxshistory h LEFT JOIN zzequip s ON h.id1=s.AID AND h.id2=s.id2 AND s.netstat='1' GROUP BY h.id1,h.id2 HAVING MAX(h.row1)<'2016-11-30' ) AS b
-----------------然后就是deadlock
Deadlock found when trying to get lock; Try restarting transaction message from server: "Lock wait timeout exceeded; try restarting transaction"
原因:
根据MYSQL EXPLAIN语句的执行结果 线程2的sql在操作中用到了外键索引,而显然线程1的操作是根据主键操作的;
线程1:插入过程,获得主键锁(s锁),仍需要外键锁,等待对外键FK_1索引加锁(x锁),
线程2:插入过程,获得外键索引锁(x锁),仍需申请主键锁,等待对主键加锁(s锁),
(备注:INSERT可能产生的锁包括检查dup key时的s锁lock mode S locks rec but not gap waiting,
插入意向锁lock_mode X locks gap before rec insert intention waiting)
并发形成死锁; (主键索引锁与非聚簇索引锁冲突)
解决办法:
避免主键索引锁与非聚簇索引锁冲突,在插入操作时都先得到需要操作的记录主键,根据主键插入。
线程2把查询和插入操作拆分,先查询得到对应主键信息:
" SELECT h.id1,'' ,'' ,h.id2 FROM statushistory h LEFT JOIN zzequip s ON h.id1=s.AID AND h.id2=s.id2 AND s.netstat='1' "+ "GROUP BY h.id1,h.id2 HAVING MAX(h.row1)<'' ;
在逐条根据主键信息插入。
上述改进解决了deadlock问题,但是会产生主键冲突异常,解决办法是线程1、线程2在插入新记录时均增加ignore关键字以避免主键冲突;
deadlock日志:
=====================================
161130 0:02:54 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 38 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 2451 1_second, 2451 sleeps, 228 10_second, 750 background, 750 flush
srv_master_thread log flush and writes: 2663
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 205002, signal count 170785
Mutex spin waits 2454690, rounds 11657610, OS waits 127717
RW-shared spins 55583, rounds 971864, OS waits 20354
RW-excl spins 62517, rounds 1832253, OS waits 40305
Spin rounds per wait: 4.75 mutex, 17.48 RW-shared, 29.31 RW-excl
------------------------
LATEST DETECTED DEADLOCK
------------------------
161130 0:00:01
*** (1) TRANSACTION:
TRANSACTION 15B43CE, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1024, 3 row lock(s), undo log entries 1
MySQL thread id 21650, OS thread handle 0x347c, query id 6211894 localhost 127.0.0.1 usrer1 update
insert into xxshistory(id2,id1,row1,row2) values ('-1', 'X83','2016-11-30' ,'7 ' )
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 55130 n bits 672 index `FK_xxshistory` of table `db1`.`xxshistory` trx id 15B43CE lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 288 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 3; hex 583834; asc X84;;
1: len 2; hex 2d31; asc -1;;
2: len 3; hex 8fc16e; asc n;;
*** (2) TRANSACTION:
TRANSACTION 15B352B, ACTIVE 1 sec inserting, thread declared inside InnoDB 500
mysql tables in use 3, locked 3
288 lock struct(s), heap size 44352, 73483 row lock(s), undo log entries 947
MySQL thread id 21608, OS thread handle 0x2c20, query id 6204529 localhost 127.0.0.1 usrer1 Sending data
INSERT INTO xxshistory (id1,row1,row2,id2) SELECT * FROM (SELECT h.id1,'2016-11-30','3 ' , h.id2 FROM xxshistory h LEFT JOIN zzequiprow2 s ON h.id1=s.AID AND h.id2=s.id2 AND s.netstat='1' GROUP BY h.id1,h.id2 HAVING MAX(h.row1)<'2016-11-30' ) AS b
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 55130 n bits 672 index `FK_xxshistory` of table `db1`.`xxshistory` trx id 15B352B lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 3; hex 583634; asc X64;;
1: len 2; hex 2d31; asc -1;;
2: len 3; hex 8fc176; asc v;;
1: len 2; hex 2d31; asc -1;;
2: len 3; hex 8fc16e; asc n;;
Record lock, heap no 236 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 3; hex 583830; asc X80;;
1: len 2; hex 2d31; asc -1;;
2: len 3; hex 8fc16f; asc o;;
------------------------中间省略
Record lock, heap no 600 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 3; hex 583732; asc X72;;
1: len 2; hex 2d31; asc -1;;
2: len 3; hex 8fc17e; asc ~;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 56536 n bits 200 index `PRIMARY` of table `db1`.`xxshistory` trx id 15B352B lock mode S locks rec but not gap waiting
Record lock, heap no 122 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 3; hex 583833; asc X83;;
1: len 3; hex 8fc17e; asc ~;;
2: len 2; hex 2d31; asc -1;;
3: len 6; hex 0000015b43ce; asc [C ;;
4: len 7; hex d70000d9d20110; asc ;;
5: len 30; hex 374e4e4e4e4e4e4e4e4e4e4e4e4e4e4e4e4e4e4e4e4e4e4e4e4e4e4e4e4e; asc 7NNNNNNNNNNNNNNNNNNNNNNNNNNNNN; (total 96 bytes);
*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 15E0036
Purge done for trx's n:o < 15E0034 undo n:o < 0
History list length 1198
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 22260, OS thread handle 0x1ce0, query id 6544403 localhost 127.0.0.1 root
show engine innodb row2
--------
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, 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
8881 OS file reads, 280945 OS file writes, 157745 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 3, seg size 5, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 1365571, node heap has 79 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 3238511178
Log flushed up to 3238511178
Last checkpoint at 3238511178
0 pending log writes, 0 pending chkp writes
153570 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 349536256; in additional pool allocated 0
Dictionary memory allocated 5236825
Buffer pool size 21056
Free buffers 11046
Database pages 9931
Old database pages 3653
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 16, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 8870, created 1061, written 124154
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 9931, 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
1 read views open inside InnoDB
Main thread id 2792, state: waiting for server activity
Number of rows inserted 363271, updated 1551713, deleted 349439, read 872878012
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
相关推荐
主要给大家介绍了关于MySQL DeadLock故障排查的全过程,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧
网上传言C3P0是因为本身的BUG问题,然而今天我遇到这个问题并解决了,结果发现并不是。通过配置c3p0.maxStatements=0 这种方案只是治标不治本,或者干脆无效。我上传的解决方案肯定能解决这个问题的根本原因。出现...
避免或最小化mysql死锁deadlock一些方法.docx
SQL Server上的一个奇怪的Deadlock及其分析方法
DeadLock查找死锁的位置及解决 DeadLock查找死锁的位置及解决
c语言 deadlock
Deadlock detection method used
发现问题 最近在补以前数据的时候程序突然报如下错误: [2017-02-10 13:12:06.678] [INFO] mysqlLog - update tbl_playerdata_error: { [Error: ER_LOCK_...一看就是mysql出现了死锁问题,其实上面跑的程序在测试服跑
一次Oracle频繁DeadLock问题分析处理经历事件描述处理过程分析结果 事件描述 早上十点,客户电话打来,描述了一下业务缓慢,卡的已经不能正常进行下去,几句询问赶紧着手处理。 接过远程后,赶紧连上数据库服务器...
互斥条件: 一个资源只能被一个进程使用 请求和保持条件:进行获得一定资源,又对其他资源发起了请求,但是其他资源被其他线程占用,请求阻塞,但是也不会释放自己占用的资源。 不可剥夺条件: 指进程所获得的资源...
10 Technology to avoid Java Deadlock. very good.
数据库和操作系统一样,是一个多用户使用的共享资源。当多个用户并发地存取数据 时,在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据,破坏数据库的一致性...
文章介绍了MPI同步通信模型死锁检测理论和算法,该文已经被《HPC Asia 2007》录用,等待发表中。为方便后续文章引用,利用此空间暂时开放该文章。文章正式发表后,将删除该资源。
SQL SERVER 的阻塞和死锁,讲解的比较详细,可以参考看看
pt-deadlock-logger pt-index-usage pt-show-grants pt-table-sync pt-diskstats pt-ioprofile pt-sift pt-table-usage pt-duplicate-key-checker pt-kill pt-slave-delay pt-upgrade pt-fifo-split pt-mext pt-...
实现了不死锁的哲学家问题(进程同步互斥),有简单界面,有图。
一个基于C#实现的多线程Multi-Threading Deadlock Tracer Utility处理类库源码。
python库。 资源全名:deadlock_cli-1.1.0-py3-none-any.whl