update 死锁问题

记录一次 mysql 死锁问题

场景：两个定时任务同时进行

任务1：事务-----用户券表（table_c_user）插入数据，更新优惠券配置表（table_c）已发放数量

INSERT INTO table_c_user (a, x, v) (SELECT a, x, v FROM table_c WHERE `one` = 2 AND `type` = 33 );
UPDATE `table_c` SET `send`=send + 6 WHERE `one` = 2 AND `type` = 3 ;

任务2：非事务-----更新用户券表（table_c_user）过期状态，更新优惠券配置表（table_c）已过期数量（根据主键条件）

UPDATE `table_c_user` SET `status`=999 WHERE .... ;
UPDATE `table_c` SET `timeout`= `timeout` + 6 WHERE `id` = 4 ;

问题1：数据库报死锁：

{ 
  code: 'ER_LOCK_DEADLOCK',
  errno: 1213,
  sqlState: '40001',
  index: 0
}

在本地调试没有百分百复现，根据日志分析是 update table_c 同一行数据时 死锁，原因分析:

更新时会加行锁，但行锁是基于索引的，如果更新条件是用到了非主键索引，数据库会先锁住非主键索引，再锁主键索引；如果更新条件用的是主键索引，那么会先锁主键索引再锁非主键索引；【1】

任务1 用的更新条件是非主键索引，任务2 用的是主键索引，所以有几率出现任务1执行更新时锁了对应行的非聚簇索引，但是任务2 锁住了该行的主键索引，任务1就在等待任务2释放主键索引，任务2等待任务1的非主键索引释放；这就是产生了死锁。

问题1解决方案：

方案1.：把两个任务时间错开；

方案2（最后使用该方案）：把任务一的根据非主键索引更新改为先查出满足条件的 table_c 记录，然后根据主键id更新数据；

问题2：

先介绍一下自增锁模式【1】：

0、1、2，分别对应”传统模式”, “连续模式”, “交错模式”；

传统模式：涉及auto-increment列的插入语句加的表级AUTO-INC锁，只有插入执行结束后才会释放锁。这是一种兼容MySQL 5.1之前版本的策略。
连续模式：可以事先确定插入行数的语句(包括单行和多行插入)，分配连续的确定的auto-increment值；对于插入行数不确定的插入语句，仍加表锁。这种模式下，事务回滚，auto-increment值不会回滚，换句话说，自增列内容会不连续。
交错模式：同一时刻多条SQL语句产生交错的auto-increment值。

mysql> show variables like 'innodb_autoinc_lock_mode';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 1 |
+--------------------------+-------+

我的数据库使用的是连续模式，任务1 insert 行数不确定，会把 table_c_user 整表锁，有几率影响业务；

问题2解决方案：

接上方问题1解决方案，根据遍历的 table_c 记录，自己生成要插入 table_c_user 的数据，然后 insert 这些数据，这样 table_c_user 就不会锁表了

问题3：

INSERT INTO table_c_user (a, x, v) (SELECT a, x, v FROM table_c WHERE `one` = 2 AND `type` = 33 );

如果 table_c 查询时没有用到索引，会把 table_c 表锁（表锁又分 全锁和逐步锁 此处不细说）；

问题3解决方案：

 同问题2解决方案；

 

参考资料：

【1】https://blog.csdn.net/varyall/article/details/80219459