MySQL 待解决死锁

官方文档：https://dev.mysql.com/doc/refman/5.6/en/innodb-locks-set.html

线上出现一个死锁现象，信息显示的是两条对同一个表的不同记录的update操作，表上只有一个主键索引，更新的条件上无索引，时间地段显示两个update只相差1ms

业务场景是同时一个事务中先是insert 再update新插入的行，存在并发；数据库环境是5.6，事务隔离级别RC，auto_increment_increment=1

通过定时任务实现两个会话同时对一个表先进行insert，然后update

#表结构

mysql> show create table test.t3;

+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table                                                                                                                                                                                                  |

+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| t3    | CREATE TABLE `t3` (

  `id` int() NOT NULL AUTO_INCREMENT,

  `name` varchar() DEFAULT NULL,

  `col` varchar() DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT= DEFAULT CHARSET=utf8 |

+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

 row in set (0.00 sec)

#定时任务

  * * * for i in `seq `;do /usr/local/mysql/bin/mysql -uroot -S /tmp/mysql_3309.sock -e "begin;insert into test.t3 values(null,'aa','$i');update test.t3 set name='aa$i' where col='$i';commit;";done

  * * * for i in `seq  `;do /usr/local/mysql/bin/mysql -uroot  -S /tmp/mysql_3309.sock -e "begin;insert into test.t3 values(null,'aa','$i');update test.t3 set name='aa$i' where col='$i';commit;";done

捕获到的死锁信息

------------------------

LATEST DETECTED DEADLOCK

------------------------

-- :: 0x7f5277ba6700

*** () TRANSACTION:

TRANSACTION , ACTIVE  sec fetching rows

mysql tables in use , locked

LOCK WAIT  lock struct(s), heap size ,  row lock(s), undo log entries

MySQL thread id , OS thread handle , query id  localhost root updating

update test.t3 set name='aa26' where col=''

*** () WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id  page no  n bits  index PRIMARY of table `test`.`t3` trx id  lock_mode X locks rec but not gap waiting

Record lock, heap no  PHYSICAL RECORD: n_fields ; compact format; info bits

 : len ; hex 8000004c; asc    L;;

 : len ; hex 0000000aa0be; asc       ;;

 : len ; hex b6000001910110; asc        ;;

 : len ; hex ; asc aa;;

 : len ; hex ; asc ;;

*** () TRANSACTION:

TRANSACTION , ACTIVE  sec fetching rows

mysql tables in use , locked

 lock struct(s), heap size ,  row lock(s), undo log entries

MySQL thread id , OS thread handle , query id  localhost root updating

update test.t3 set name='aa126' where col=''

*** () HOLDS THE LOCK(S):

RECORD LOCKS space id  page no  n bits  index PRIMARY of table `test`.`t3` trx id  lock_mode X locks rec but not gap

Record lock, heap no  PHYSICAL RECORD: n_fields ; compact format; info bits

 : len ; hex 8000004c; asc    L;;

 : len ; hex 0000000aa0be; asc       ;;

 : len ; hex b6000001910110; asc        ;;

 : len ; hex ; asc aa;;

 : len ; hex ; asc ;;

*** () WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id  page no  n bits  index PRIMARY of table `test`.`t3` trx id  lock_mode X locks rec but not gap waiting

Record lock, heap no  PHYSICAL RECORD: n_fields ; compact format; info bits

 : len ; hex 8000004b; asc    K;;

 : len ; hex 0000000aa0bd; asc       ;;

 : len ; hex 3500000142133d; asc    B =;;

 : len ; hex ; asc aa26;;

 : len ; hex ; asc ;;

*** WE ROLL BACK TRANSACTION ()

------------

解决

mysql> explain update test.t3 set name='aa126' where col=''

    -> ;

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

|  1 | UPDATE      | t3    | NULL       | index | NULL          | PRIMARY | 4       | NULL |  382 |   100.00 | Using where |

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

1 row in set (0.00 sec)

更新操作会对主键索引进行全索引扫描，我的理解为会一行行的处理先在innodb层在主键上加X锁，然后再server层通过where条件进行过滤，释放不符合条件的记录上的锁

在where条件字段上加索引，避免全索引扫描

测试发现通过在where条件上添加索引可以解决问题，但是还是无法解释这一现象，因为单独将事务拿出来重现是不会产生阻塞的，只有高并发下才会产生。。。。有知道的朋友请留言。

巴特西

MySQL 待解决死锁

最新文章

热门文章