mysql解决不可重复读解决 mysql可重复读死锁

转载

西门吹雪 2023-09-27 22:58:54

文章标签 mysql解决不可重复读解决 mysql可重复读加锁 MySQL mysql 联合索引 文章分类 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)SELECT1 AS 'XXX'FROM dualWHERE NOT EXISTS(SELECT 1 FROM common.event_logWHERE InUserSysNo=310783AND Source=0AND 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 2LOCK 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 1mysql tables in use 2, locked 26 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1MySQL 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 231177068Purge 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 * FROM common.event_log using index(IX_InUserSysNo)WHERE InUserSysNo=310783AND Source=0AND 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;

xxxset 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表