首页 > 代码库 > MySQL死锁分析

MySQL死锁分析

2024-07-19 04:19:29   225人阅读

1. 测试描述

环境说明：RHEL 6.4 x86_64 + MySQL 5.5.37，事务隔离级别为RC

测试表：

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a` int(11) NOT NULL DEFAULT '0',
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

测试表中的数据：

mysql> select * from t1;
+----+------+
| a  | b    |
+----+------+
|  1 |    1 |
|  3 |    3 |
|  5 |    6 |
+----+------+
3 rows in set (0.01 sec)

2. 测试过程



3. 死锁日志分析

此时查询show engine innodb status\G查看死锁信息，下面只摘取了死锁信息部分，其他的省略。

------------------------
LATEST DETECTED DEADLOCK
------------------------
140824  1:01:24
*** (1) TRANSACTION:
TRANSACTION 110E, ACTIVE 73 sec starting index read   ## 事务ID=110E，活跃了73s
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 376, 2 row lock(s)  ## 有2个行锁
MySQL thread id 1, OS thread handle 0x7f55ea639700, query id 81 localhost root updating  ## 该事务的线程ID=1
delete from t1 where a=1   ## 这是当前事务执行的SQL
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:  ## 上面SQL等待的锁信息
RECORD LOCKS space id 12 page no 3 n bits 80 index `PRIMARY` of table `test`.`t1` trx id 110E lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 32  ## 等待在主键上的page num=3上有加一个X锁(not gap waiting)，锁80 bits
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 00000000110c; asc       ;;
 2: len 7; hex 0d000002350084; asc     5  ;;
 3: len 4; hex 80000001; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 110C, ACTIVE 1716 sec starting index read, thread declared inside InnoDB 500  ## 事务ID=110C，活跃了1716s
mysql tables in use 1, locked 1
3 lock struct(s), heap size 376, 2 row lock(s), undo log entries 1  ## 3个锁，2个行锁，1个undo log
MySQL thread id 2, OS thread handle 0x7f563c05e700, query id 82 localhost root updating  ## 该事务的线程ID=2
delete from t1 where a=3   ## 这是当前事务执行的SQL
*** (2) HOLDS THE LOCK(S):  ## 这个事务持有的锁信息
RECORD LOCKS space id 12 page no 3 n bits 80 index `PRIMARY` of table `test`.`t1` trx id 110C lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 32  ## 在主键上的page num=3上已持有一个X锁(not gap)，锁80 bits
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 00000000110c; asc       ;;
 2: len 7; hex 0d000002350084; asc     5  ;;
 3: len 4; hex 80000001; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:  ## 同时这个事务还等待的锁信息
RECORD LOCKS space id 12 page no 3 n bits 80 index `PRIMARY` of table `test`.`t1` trx id 110C lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0  ## 同样等待在主键上的page num=3上有加一个X锁(not gap waiting)，锁80 bits
 0: len 4; hex 80000003; asc     ;;
 1: len 6; hex 000000000f71; asc      q;;
 2: len 7; hex ed0000022f0090; asc     /  ;;
 3: len 4; hex 80000003; asc     ;;

*** WE ROLL BACK TRANSACTION (1)   ## 这里选择回滚了事务110E。

也就是说，这个死锁使用事务110E中的SQL没有执行，回滚了：
delete from t1 where a=1
而事务110C中的SQL是正常被执行的：
delete from t1 where a=3

-- Bosco
---- END ----

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

版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!


MySQL死锁分析


联系
我们