java程序员

Mysql在可重复读隔离级别下死锁排查过程

2019-01-04  本文已影响12人  Java分布式架构实战

我可以学到什么?(这应该是各位读者最关注的)

  1. MySQL事务(ACID)
  2. 事务隔离级别(RU, RC, RR, Serializeable)
  3. 锁(S锁(读锁),X锁(写锁),Table Lock, Records Lock, Gap Lock)
  4. 索引(主键索引,唯一索引,联合索引,覆盖索引)
  5. 索引下推
  6. 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

总结

  1. 在写查询时一定要将选择性高的字段放在最左边,尽量严格遵守这个最佳实践。
  2. 执行计划中评估的数据行数越少,证明索引效率越高。
  3. 使用using index可以强制MySQL引擎使用某个索引
    SELECT * FROM common.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;
  4. 使用以下命令连接mysql服务器:mysql -h 127.0.0.1 -P13306 -udev -p
  5. show engine innodb status\G可以输出MySQL InnoDB 引擎的状态
  6. 显示创建表的脚本: show create table common.event_log
  7. set session transaction isolation level read committed;
  8. 设置 session auto_increment_increment可以改变自增的步长
set session auto_increment_increment=2;
xxx
set session auto_increment_increment=1;
  1. 查看当前的隔离级别: SELECT @@global.tx_isolation;
show variables like 'transaction_isolation'
show variables like 'innodb_autoinc_lock_mode';

参考资料

交流学习&打赏

为自己的成长付费是一种好习惯。


微信名片-JamesFu.png
微信支付-JameFu.png
上一篇下一篇

猜你喜欢

热点阅读