MySQL 待解决死锁

时间:2022-04-10 04:41:56

官方文档: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(11) NOT NULL AUTO_INCREMENT,
`name` varchar(
10) DEFAULT NULL,
`col` varchar(
10) DEFAULT NULL,
PRIMARY KEY (`
id`)
) ENGINE
=InnoDB AUTO_INCREMENT=225 DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
#定时任务
32 13 * * * for i in `seq 100`;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
32 13 * * * for i in `seq 100 200`;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
------------------------
2017-06-08 13:32:04 0x7f5277ba6700
*** (1) TRANSACTION:
TRANSACTION
696509, ACTIVE 0 sec fetching rows
mysql tables
in use 1, locked 1
LOCK WAIT
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 2
MySQL thread
id 112, OS thread handle 139992172484352, query id 965 localhost root updating
update test.t3 set name
='aa26' where col='26'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space
id 83 page no 3 n bits 152 index PRIMARY of table `test`.`t3` trx id 696509 lock_mode X locks rec but not gap waiting
Record lock, heap no
82 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 8000004c; asc L;;
1: len 6; hex 0000000aa0be; asc ;;
2: len 7; hex b6000001910110; asc ;;
3: len 2; hex 6161; asc aa;;
4: len 3; hex 313236; asc 126;;

*** (2) TRANSACTION:
TRANSACTION
696510, ACTIVE 0 sec fetching rows
mysql tables
in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread
id 113, OS thread handle 139992172750592, query id 966 localhost root updating
update test.t3 set name
='aa126' where col='126'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space
id 83 page no 3 n bits 152 index PRIMARY of table `test`.`t3` trx id 696510 lock_mode X locks rec but not gap
Record lock, heap no
82 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 8000004c; asc L;;
1: len 6; hex 0000000aa0be; asc ;;
2: len 7; hex b6000001910110; asc ;;
3: len 2; hex 6161; asc aa;;
4: len 3; hex 313236; asc 126;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space
id 83 page no 3 n bits 152 index PRIMARY of table `test`.`t3` trx id 696510 lock_mode X locks rec but not gap waiting
Record lock, heap no
83 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 8000004b; asc K;;
1: len 6; hex 0000000aa0bd; asc ;;
2: len 7; hex 3500000142133d; asc 5 B =;;
3: len 4; hex 61613236; asc aa26;;
4: len 2; hex 3236; asc 26;;

*** WE ROLL BACK TRANSACTION (2)
------------

 

解决

mysql> explain update test.t3 set name='aa126' where col='126'
-> ;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 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条件上添加索引可以解决问题,但是还是无法解释这一现象,因为单独将事务拿出来重现是不会产生阻塞的,只有高并发下才会产生。。。。有知道的朋友请留言。