|
表user：
- CREATE TABLE `USER` (
- `ID` INT(11) NOT NULL AUTO_INCREMENT,
- `NAME` VARCHAR(300) DEFAULT NULL,
- `AGE` INT(11) DEFAULT NULL,
- PRIMARY KEY (`ID`)
- ) ENGINE=INNODB AUTO_INCREMENT=5 DEFAULT CHARSET=UTF8
下面演示事务1、事务2工作的情况：
这是一个简单的死锁场景，事务1、事务2彼此等待对方释放锁，InnoDB存储引擎检测到死锁发生，让事务2回滚，这使得事务1不再等待事务B的锁，从而能够继续执行。那么InnoDB存储引擎是如何检测到死锁的呢？为了弄明白这个问题，我们先检查此时InnoDB的状态：
- show engine innodb statusG
------------------------
LATEST DETECTED DEADLOCK
------------------------
- 2018-01-14 12:17:13 0x70000f1cc000
- *** (1) TRANSACTION:
- TRANSACTION 5120, ACTIVE 17 sec starting index read
- mysql tables in use 1, locked 1
- LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
- MySQL thread id 10, OS thread handle 123145556967424, query id 2764 localhost root updating
- update user set name='haha' where id=4
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
- RECORD LOCKS space id 94 page no 3 n bits 80 index PRIMARY of table `test`.`user` trx id 5120 lock_mode X locks rec but not gap waiting
- Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
- 0: len 4; hex 80000004; asc ;;
- 1: len 6; hex 0000000013fa; asc ;;
- 2: len 7; hex 520000060129a6; asc R ) ;;
- 3: len 4; hex 68616861; asc haha;;
- 4: len 4; hex 80000015; asc ;;
*** (2) TRANSACTION:
- TRANSACTION 5121, ACTIVE 12 sec starting index read
- mysql tables in use 1, locked 1
- 3 lock struct(s), heap size 1136, 2 row lock(s)
- MySQL thread id 11, OS thread handle 123145555853312, query id 2765 localhost root updating
- update user set name='hehe' where id=3
*** (2) HOLDS THE LOCK(S):
- RECORD LOCKS space id 94 page no 3 n bits 80 index PRIMARY of table `test`.`user` trx id 5121 lock_mode X locks rec but not gap
- Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
- 0: len 4; hex 80000004; asc ;;
- 1: len 6; hex 0000000013fa; asc ;;
- 2: len 7; hex 520000060129a6; asc R ) ;;
- 3: len 4; hex 68616861; asc haha;;
- 4: len 4; hex 80000015; asc ;;
（编辑：滁州站长网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
