ITPub博客

首页 > 数据库 > MySQL > mysql锁之死锁

mysql锁之死锁

原创 MySQL 作者:水逸冰 时间:2019-07-19 19:43:31 0 删除 编辑

    死锁是事务绕不开的话题,mysql当然也不例外,本文主要模拟一下mysql的死锁,以及应对措施。
    首先看一个参数,默认innodb_print_all_deadlocks参数是关闭。开启后可以将死锁记录到error.log中。否则只能通过show engine innodb status查看。
mysql> SHOW VARIABLES LIKE 'INNODB_PRINT_ALL_DEADLOCKS';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_print_all_deadlocks | OFF   |
+----------------------------+-------+
1 row in set (0.00 sec)

开启innodb_print_all_deadlocks,改参数是全局参数,可以动态调整。
mysql> SET GLOBAL innodb_print_all_deadlocks=1;
Query OK, 0 rows affected (0.00 sec)

表test01上c1是主键,c2是唯一约束。
mysql> show create table test01\G
*************************** 1. row ***************************
       Table: test01
Create Table: CREATE TABLE `test01` (
  `c1` bigint(20) NOT NULL AUTO_INCREMENT,
  `c2` int(11) DEFAULT NULL,
  PRIMARY KEY (`c1`),
  UNIQUE KEY `uidx_test01_c2` (`c2`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> select * from test01;
+----+------+
| c1 | c2   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
+----+------+
3 rows in set (0.00 sec)
会话A
mysql> begin ;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from test01 where c2=2;
Query OK, 1 row affected (0.00 sec)

会话B
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from test01 where c2=2;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

会话A
mysql> insert into test01 select 2,2;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
会话A在(2,2)上加了X的行锁,会话B要删除相同的数据行,那么也要在该行上加X的行锁(lock_mode X locks rec but not gap),所以出现了等待(lock_mode X waiting)。后面会话A要插入一行(2,2),因为字段c2上有唯一索引,插入的时候要检查duplicate key的检查,这个过程需要申请S的锁,而在得到这个锁之前,它需要等会话B先得到会话A最开始执行的X锁。也就是说,会话B要等待会话A第一条语句释放X锁,会话A第二条语句又要等待会话B释放X锁,两个会话之间形成了等待的闭合回路,形成了死锁。出现死锁后,mysql会选择一个小事务进行回滚,以解决死锁。



show engine innodb status查看死锁信息,error.log中记录的死锁也类似下面:
------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-07-18 11:11:32 0x7fdc50298700
*** (1) TRANSACTION:
TRANSACTION 713521, ACTIVE 122 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 4, OS thread handle 140584214165248, query id 144 localhost root updating
delete from test01 where c2=2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 25 page no 4 n bits 72 index uidx_test01_c2 of table `ming`.`test01` trx id 713521 lock_mode X waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000002; asc     ;;
 1: len 8; hex 8000000000000002; asc         ;;

*** (2) TRANSACTION:
TRANSACTION 713523, ACTIVE 16 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 2
MySQL thread id 3, OS thread handle 140584214431488, query id 146 localhost root executing
insert into test01 select 2,2
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 25 page no 4 n bits 72 index uidx_test01_c2 of table `ming`.`test01` trx id 713523 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000002; asc     ;;
 1: len 8; hex 8000000000000002; asc         ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 25 page no 4 n bits 72 index uidx_test01_c2 of table `ming`.`test01` trx id 713523 lock mode S waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000002; asc     ;;
 1: len 8; hex 8000000000000002; asc         ;;

针对死锁的举措:
1.如果死锁较多的话,那么建议开启innodb_print_all_deadlocks,因为show engine innodb status只显示最近一次的死锁信息。
2.保持事务小而短,并尽快提价
3.避免在一个事务里面修改几张表,或者是同一张表修改不同的结果集
4.可以尝试使用较低的隔离级别,比如RC。也可以使用锁定读
5.建立合适的索引
6.如果应用允许的话,串行化事务

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31480688/viewspace-2651193/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
精通oracle和linux,热衷于研究oracle,擅长shell和Python编程,喜欢自动化运维。

注册时间:2017-08-05

  • 博文量
    91
  • 访问量
    78114