今天线上环境查看状态时,发现一个死锁:
分析:
事务1:
2018-07-15 07:15:39 7efc9e815700
*** (1) TRANSACTION:
TRANSACTION 39968365183, ACTIVE 0 sec inserting
mysql tables in use 3, locked 3
LOCK WAIT 6 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 2
MySQL thread id 5082619, OS thread handle 0x7efc9d0b9700, query id 103091639267 10.117.213.228 sdk update
INSERT INTO `tbl_chargecode_statistics` (appid,codeid,price,province,allocation,success,curday,curmonth,createtime) VALUES(@appid,new.codeid,new.price,new.province,1,0,new.curday,LEFT(new.curday,6),NOW())
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 52917 page no 45514 n bits 400 index `unique_reco` of table `chlog`.`code_stat` trx id 39968365183 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 288 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 30383037; asc 0807;;
1: len 6; hex e5ae89e5bebd; asc ;;
2: len 4; hex 8133eeeb; asc 3 ;;
3: len 4; hex 80019175; asc u;;
4: len 4; hex 805e535d; asc ^S];;
显示在向code_stat表插入一条数据,并且在等待索引unique_reco上的一个X锁(lock_mode X locks gap before rec insert intention waiting)。
事务2:
*** (2) TRANSACTION:
TRANSACTION 39968365182, ACTIVE 0 sec inserting
mysql tables in use 3, locked 3
6 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 2
MySQL thread id 5082618, OS thread handle 0x7efc9e815700, query id 103091639268 10.117.213.228 dok update
INSERT INTO `code_stat` (appid,codeid,price,province,allocation,success,curday,curmonth,createtime) VALUES(@appid,new.codeid,new.price,new.province,1,0,new.curday,LEFT(new.curday,6),NOW())
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 52917 page no 45514 n bits 400 index `unique_reco` of table `chlog`.`code_stat` trx id 39968365182 lock_mode X locks gap before rec
Record lock, heap no 288 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 30383037; asc 0807;;
1: len 6; hex e5ae89e5bebd; asc ;;
2: len 4; hex 8133eeeb; asc 3 ;;
3: len 4; hex 80019175; asc u;;
4: len 4; hex 805e535d; asc ^S];;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 52917 page no 45514 n bits 400 index `unique_reco` of table `chlog`.`code_stat` trx id 39968365182 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 288 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 30383037; asc 0807;;
1: len 6; hex e5ae89e5bebd; asc ;;
2: len 4; hex 8133eeeb; asc 3 ;;
3: len 4; hex 80019175; asc u;;
4: len 4; hex 805e535d; asc ^S];;
*** WE ROLL BACK TRANSACTION (2)
分为两部分:
(1)已经持有的锁HOLDS THE LOCK(S)::
在向code_stat表插入数据的时候,在其索引unique_reco上得到了一个X锁。
(2)正在等待的锁 WAITING FOR THIS LOCK TO BE GRANTED::
在向code_stat表插入一条数据,并且在等待索引unique_reco上的一个X锁( lock_mode X locks gap before rec insert intention waiting)。
在未开启二进制日志的情况下:
在准备阶段,日志记录直接写入硬盘。
在开启二进制日志的情况下:
在准备阶段,日志记录同样直接写入硬盘,等待提交。
在事物提交时,同时写入二进制日志和redo日志,保证事物一致,两者写入受sync_binlog和innodb_flush_log_at_trx_commit影响。
innodb_flush_log_at_trx_commit:
0:每秒输入一次日志,并且调用fsync()强制刷入硬盘。
1:事物每次提交输入硬盘。
2:每秒刷新日志,但不是强制刷新到硬盘,先刷新到系统的缓存里,由OS决定何时刷新到硬盘。
检查点的增加固定是每秒一次,由master主线程决定。
master线程每秒执行一次:
1)刷新脏页到硬盘
2)生成检查点
3)清除旧的MVCC日志版本
4)刷新redo日志