记一次MySQL innodb引擎死锁问题

      最近，在排查项目中服务端的报错时，发现服务端日志有死锁报错（偶发性）。

      查看了具体的报错模块后，梳理出模拟表如下：

CREATE TABLE `test_a` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;



CREATE TABLE `test_b` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `aid` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_aid` (`aid`),
  CONSTRAINT `fk_aid` FOREIGN KEY (`aid`) REFERENCES `test_a` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;



CREATE TABLE `test_c` (
  `name` varchar(255) NOT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `aid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_test_c_aid` (`aid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
DROP TRIGGER IF EXISTS `test`;
DELIMITER ;;
CREATE TRIGGER `test` AFTER INSERT ON `test_c` FOR EACH ROW INSERT INTO `demo`.`test` (`price`, `realPrice`, `isDeleted`, `createdDate`) VALUES ('0.00', '000000.00', '0', '2018-06-18 13:48:46')
;;
DELIMITER ;

         表关系为，test_a与test_b表有外键关联关系，test_c表独立，但是它有一个触发器（也不会对另外两张表加锁）。

         做如下测试：

-- 会话框1：

SET AUTOCOMMIT = 0;

-- 插入语句1
INSERT INTO test_b (`name`, aid) VALUES ('1111', 1); -- S
-- 更细语句1
UPDATE test_a SET `name` = '112' WHERE id = 1;	-- X

COMMIT;


-- 会话框2：
SET AUTOCOMMIT = 0;

-- 插入语句2
INSERT INTO test_c (`name`, aid) VALUES ('1111', 1); -- trigger
-- 插入语句2
UPDATE test_a SET `name` = '112' WHERE id = 1;	-- X

COMMIT;

    执行顺序：

    插入语句1->插入语句2-> 更新语句2->更新语句1

    执行结果：

    会话1报deadlock，会话2可正常提交；

    执行顺序：

    插入语句1-> 更新语句2->更新语句1

    执行结果：

    会话2报deadlock，会话1可正常提交；

 

    如果取消test_c表的触发器，建立test_c与test_a的外键关系：

    执行顺序：

    插入语句1->插入语句2-> 更新语句2->更新语句1

    执行结果：

    会话1报deadlock，会话2可正常提交；

 

     以上执行结果，表明除了共享锁（S）、排它锁（X）之外，有时候触发器会影响两个事物的commit和rollback结果。

     因此，在给数据库表加外键时应该慎重考虑，因为表在做一些操作的时候会对外键关联表的索引数据加S锁，可能会引起意想不到的死锁报错。且尽量避免在同一事务中对两张有关联的表同时操作（或者保证X锁的操作在S锁操作之前执行），否则高并发情况下容易导致死锁问题。

     加一些拓展：

参考mysql5.6官方文档(我测试使用的mysql版本5.6)：

文中提到：当启用死锁检测时（默认），InnoDB会自动检测事务死锁并回滚一个或多个事务来打破死锁。InnoDB会尝试选择小型事务进行回滚，其中事务的大小由插入，更新或删除的行数决定。