注册
登录
| 关键词： nbsp 死锁 事务 LOCK RECORD len hex asc bits TRANSACTION |
1. 测试描述环境说明：RHEL 6.4 x86_64 + MySQL 5.5.37，事务隔离级别为RC测试表：[sql] view plaincopyprint?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) 测试表中的数据：[sql] view plaincopyprint?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查看死锁信息，下面只摘取了死锁信息部分，其他的省略。[html] view plaincopyprint?------------------------ 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 ----来源：http://blog.csdn.net/hw_libo/article/details/38789115 |
|
声明：文章版权归原作者所有 部分文章转自互联网 如有侵权请联系
[邮箱地址] 删除
|
