Mysql在可重复读隔离级别下死锁排查过程
我可以学到什么?(这应该是各位读者最关注的)
- MySQL事务(ACID)
- 事务隔离级别(RU, RC, RR, Serializeable)
- 锁(S锁(读锁),X锁(写锁),Table Lock, Records Lock, Gap Lock)
- 索引(主键索引,唯一索引,联合索引,覆盖索引)
- 索引下推
- MRR
当然这里不会给你讲所有的知识点,我会不断总结,然后再分享给各位读者。各位读者如果比较感兴趣,可以自己多研究,然后分享出来。
线上异常
com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
; SQL []; Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
核心SQL
insert into common.event_log(XXX)
SELECT
1 AS 'XXX'
FROM dual
WHERE NOT EXISTS(SELECT 1 FROM common
.event_log
WHERE InUserSysNo=310783
AND Source=0
AND OperationCode='LOGIN'
AND (InDate BETWEEN DATE_ADD('2018-11-23 14:01:14.750', INTERVAL -30 * 60 SECOND ) AND '2018-11-23 14:01:14.750' )
LIMIT 1
-- for update
)
LIMIT 1;
之前event_log表里面有IX_InUserSysNo, IX_InDate。MySQL最终评估下来使用索引IX_InDate。其实在上线前我还有疑问为什么MySQL不用IX_InUserSysNo,因为在我看来显然用IX_InUserSysNo选择性更高。上线后,由于分布式部署了多个节点存在并发,最终还是出现了上面提到的异常报警。原因是使用了IX_InDate,导致所锁的数据比较多,在并发的情况下,会存在互相等待对方被锁数据的情况,从而导致死锁。最后考虑使用联合索引来让InnoDB引擎使用选择性高的字段。
-- 创建联合索引
alter table event_log
ALGORITHM=inplace,
add key IDX_InUserSysNo_InDate(InUserSysNo,InDate);
-- 由于MySQL具有最左前缀匹配的特性,原来的最左边的单字段索引不再需要
drop index IX_InUserSysNo on event_log;
死锁日志分析(已脱敏)
------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-11-29 17:56:42 0x7fb5ed509700
*** (1) TRANSACTION:
TRANSACTION 231171102, ACTIVE 0 sec setting auto-inc lock
mysql tables in use 2, locked 2
LOCK WAIT 3 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 3242083, OS thread handle 140419863303936, query id 202676815 10.160.300.140 dev_admin executing
------------------------
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `xxx_xxx_common`.`event_log` trx id 231171102 lock mode AUTO-INC waiting
*** (2) TRANSACTION:
TRANSACTION 231171097, ACTIVE 0 sec inserting, thread declared inside InnoDB 1
mysql tables in use 2, locked 2
6 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 3242082, OS thread handle 140419347289856, query id 202676817 10.160.300.140 dev_admin executing
------------------------
*** (2) HOLDS THE LOCK(S):
TABLE LOCK table `xxx_xxx_common`.`event_log` trx id 231171097 lock mode AUTO-INC
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1262 page no 5086 n bits 736 index IDX_InUserSysNo_InDate of table `xxx_xxx_common`.`event_log` trx id 231171097 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 202 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80035ada; asc Z ;;
1: len 7; hex 99a15d1db41374; asc ] t;;
2: len 8; hex 000000000002c2c8; asc ;;
*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 231177068
Purge done for trx's n:o < 231159195 undo n:o < 0 state: running but idle
History list length 8848
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421896190642032, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
从事务日志可以看出,一个事务(trx id 231171097)在持有TABLE LOCK lock mode AUTO-INC, 一个在等待。最关键的在这里“lock_mode X locks gap before rec insert intention waiting”。
总结:本质上的原因是这种写法都是分成两步在执行,涉及两种锁:(trx id 231171097 lock mode AUTO-INC(innodb_autoinc_lock_mode),lock_mode X locks gap before rec insert intention waiting
Record lock),在RR隔离级别下就不允许持有间隙锁时,插入自增值。
SQL Server, Oracle默认的隔离级别都是RC
总结
- 在写查询时一定要将选择性高的字段放在最左边,尽量严格遵守这个最佳实践。
- 执行计划中评估的数据行数越少,证明索引效率越高。
- 使用using index可以强制MySQL引擎使用某个索引
SELECT * FROMcommon
.event_log
using index(IX_InUserSysNo)
WHERE InUserSysNo=310783
AND Source=0
AND OperationCode='LOGIN'
AND (InDate BETWEEN DATE_ADD('2018-11-23 14:01:14.750', INTERVAL -30 * 60 SECOND ) AND '2018-11-23 14:01:14.750' )
LIMIT 1; - 使用以下命令连接mysql服务器:mysql -h 127.0.0.1 -P13306 -udev -p
- show engine innodb status\G可以输出MySQL InnoDB 引擎的状态
- 显示创建表的脚本: show create table common.event_log
- set session transaction isolation level read committed;
- 设置 session auto_increment_increment可以改变自增的步长
set session auto_increment_increment=2;
xxx
set session auto_increment_increment=1;
- 查看当前的隔离级别: SELECT @@global.tx_isolation;
show variables like 'transaction_isolation'
show variables like 'innodb_autoinc_lock_mode';
参考资料
- 关于Mysql 的 ICP、MRR、BKA等特性
- mysql中dual表
- MySQL的四种事务隔离级别
- MySQL innodb_autoinc_lock_mode 详解
- MySQL批量更新死锁案例分析
- lock_mode X locks gap before rec insert intention waiting
交流学习&打赏
为自己的成长付费是一种好习惯。
微信名片-JamesFu.png
微信支付-JameFu.png