删除后插入死锁问题
环境准备
-
数据库版本
$ mysql -V mysql Ver 14.14 Distrib 5.6.35, for macos10.12 (x86_64) using EditLine wrapper 或者 mysql> select version(); +-----------+ | version() | +-----------+ | 5.6.35 | +-----------+
-
sql语句
DROP TABLE IF EXISTS `t_lock`; CREATE TABLE `t_lock` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `a` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `unique_a` (`a`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
开启监控
-
开启监控
//第一种方式:基于系统参数 set GLOBAL innodb_status_output=ON 开启 set GLOBAL innodb_status_output=OFF 关闭 //第二种方式:创建innodb_monitor的数据表 CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB; DROP TABLE innodb_monitor;
步骤
-
数据库信息相关操作
# mysql -u root -p # use lock //从innodb status获取更多的锁信息 # set GLOBAL innodb_status_output_locks=ON;
查看已有数据
mysql> select * from t_lock; +----+------+ | id | a | +----+------+ | 1 | a | | 2 | b | +----+------+ 2 rows in set (0.00 sec)
查看mysql事务隔离级别
//查看mysql系统的事务隔离级别 mysql> SELECT @@global.tx_isolation; +-----------------------+ | @@global.tx_isolation | +-----------------------+ | REPEATABLE-READ | +-----------------------+ 1 row in set (0.00 sec)
查看mysql会话的事务隔离级别
mysql> SELECT @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set (0.00 sec) mysql> SELECT @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | REPEATABLE-READ | +------------------------+ 1 row in set (0.00 sec)
查看是否自动提交事务(默认是自动提交)
mysql> select @@autocommit; +--------------+ | @@autocommit | +--------------+ | 1 | +--------------+ 1 row in set (0.00 sec)
设置不自动提交事务
mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec)
-
事务执行
session1 session2 mysql> begin; @1 mysql> begin; @11 mysql> delete from t_lock where a = ‘aa’; @2 mysql> delete from t_lock where a = ‘aa’; @12 mysql> insert into t_lock(a) values (‘aa’); @13 (等待锁) insert into t_lock(a) values ( ‘aa’); @3 死锁 @13:此处执行时,命令行处于等待状态。 当@3执行完毕时,出现`ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction`异常时 @13此时打印`Query OK, 1 row affected (4.19 sec)`
-
查看死锁日志
------------------------ LATEST DETECTED DEADLOCK ------------------------ 2019-02-20 14:38:27 700009ce6000 *** (1) TRANSACTION: //事务编号6719766,活跃11s TRANSACTION 6719766, ACTIVE 11 sec inserting mysql tables in use 1, locked 1 ///LOCK WAIT表示正在等待锁,获得3个锁结构 LOCK WAIT 3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1 MySQL thread id 3, OS thread handle 0x700009c62000, query id 95 localhost root update insert into t_lock(a) values ('aa') *** (1) WAITING FOR THIS LOCK TO BE GRANTED: //等待insert intention(插入意向锁) waiting RECORD LOCKS space id 4921 page no 4 n bits 72 index `unique_a` of table `lock`.`t_lock` trx id 6719766 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 1; hex 62; asc b;; 1: len 8; hex 0000000000000002; asc ;; *** (2) TRANSACTION: TRANSACTION 6719765, ACTIVE 18 sec inserting mysql tables in use 1, locked 1 3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1 MySQL thread id 9, OS thread handle 0x700009ce6000, query id 96 localhost root update insert into t_lock(a) values ( 'aa') *** (2) HOLDS THE LOCK(S): //locks gap before rec 表示为gap锁 RECORD LOCKS space id 4921 page no 4 n bits 72 index `unique_a` of table `lock`.`t_lock` trx id 6719765 lock_mode X locks gap before rec Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 1; hex 62; asc b;; 1: len 8; hex 0000000000000002; asc ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: // 等待insert intention(插入意向锁) waiting RECORD LOCKS space id 4921 page no 4 n bits 72 index `unique_a` of table `lock`.`t_lock` trx id 6719765 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 1; hex 62; asc b;; 1: len 8; hex 0000000000000002; asc ;; //回滚事务2 *** WE ROLL BACK TRANSACTION (2)
当session1执行删除语句时,由于条件
a = 'aa'
的记录不存在。session1 获得2个锁结构,分别是排他锁(行级锁)、gap锁(行级锁)。session2执行删除时,由于条件a = 'aa'
的记录不存在,session2也获得2个锁结构。session1, session2 都在等待插入意向锁,插入意向锁与gap锁冲突
,双方都没有释放gap锁,又都在等待插入意向锁,死锁发生。session1 session2 begin; begin; delete from t_lock
wherea
= ‘aa’;delete from t_lock
wherea
= ‘aa’; 等待insert into t_lock
(a
) values ( ‘aa’);死锁错误
解决方案
- 在不修改隔离级别的情况下(RR),修改业务代码逻辑,删除记录之前,先select,确认该记录存在,再执行delete删除该记录。
插入更新导致的死锁
-
表结构
DROP TABLE IF EXISTS `t_test`; CREATE TABLE `t_test` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `a` varchar(10) CHARACTER SET utf8 NOT NULL, `b` varchar(10) CHARACTER SET utf8 NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; BEGIN; INSERT INTO `t_test` VALUES (1, 'a1', 'b1'); INSERT INTO `t_test` VALUES (2, 'a2', 'b2'); INSERT INTO `t_test` VALUES (3, 'a3', 'b3'); INSERT INTO `t_test` VALUES (4, 'a4', 'b4'); COMMIT;
-
第一种情况,因为没有索引,所以会锁表
时间序 Session1 Session2 1 BEGIN; 2 BEGIN; 3 insert into t_test(a,b)
select a,b from t_test
where a = “a1”;4 update t_test set b = “b11” where a = “a1”;
阻塞5 update t_test set b = “b11”
where a = “a2”;6 Deadlock found when trying to get lock; try restarting transaction。出现死锁 -
第二种情况,如果给a列加一列索引,此时复现死锁
时间序 Session1 Session2 1 BEGIN; 2 BEGIN; 3 insert into t_test(a,b)
select a,b from t_test
where a = “a1”;4 update t_test set b = “b11” where a = “a1”;
阻塞5 update t_test set b = “b11”
where a = “a1”;6 Deadlock found when trying to get lock; try restarting transaction。出现死锁