表结构和数据
CREATE TABLE `tmp` (
`id` int(11) NOT NULL,
`code` int(11) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into tmp values(1,1,'1');
insert into tmp values(2,2,'2');
- 实验1
| session1 | session2 |
|---|---|
| begin; | begin; |
| update tmp set id =4 where code = 1; | - |
| - | select * from tmp where code = 1 for update;阻塞 |
- show engine innodb status\G
Trx id counter 83138900
Purge done for trx's n:o < 83138898 undo n:o < 0 state: running but idle
History list length 43
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 281479622667840, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 83138899, ACTIVE 11 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 226, OS thread handle 123145521414144, query id 249688691 localhost root statistics
select * from tmp where code = 1 for update
------- TRX HAS BEEN WAITING 11 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 41 page no 4 n bits 80 index code of table `test`.`tmp` trx id 83138899 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 80000001; asc ;;
1: len 4; hex 80000001; asc ;;
------------------
TABLE LOCK table `test`.`tmp` trx id 83138899 lock mode IX
RECORD LOCKS space id 41 page no 4 n bits 80 index code of table `test`.`tmp` trx id 83138899 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 80000001; asc ;;
1: len 4; hex 80000001; asc ;;
---TRANSACTION 83138898, ACTIVE 16 sec
5 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 2
MySQL thread id 227, OS thread handle 123145521692672, query id 249688689 localhost root
TABLE LOCK table `test`.`tmp` trx id 83138898 lock mode IX
RECORD LOCKS space id 41 page no 4 n bits 80 index code of table `test`.`tmp` trx id 83138898 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 80000001; asc ;;
1: len 4; hex 80000001; asc ;;
RECORD LOCKS space id 41 page no 3 n bits 80 index PRIMARY of table `test`.`tmp` trx id 83138898 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 4; hex 80000001; asc ;;
1: len 6; hex 000004f49952; asc R;;
2: len 7; hex 40000004a50b87; asc @ ;;
3: len 4; hex 80000001; asc ;;
4: len 1; hex 31; asc 1;;
RECORD LOCKS space id 41 page no 4 n bits 80 index code of table `test`.`tmp` trx id 83138898 lock mode S
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 80000001; asc ;;
1: len 4; hex 80000001; asc ;;
Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000002; asc ;;
1: len 4; hex 80000002; asc ;;
RECORD LOCKS space id 41 page no 4 n bits 80 index code of table `test`.`tmp` trx id 83138898 lock mode S locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 4; hex 80000004; asc ;;
问题提出
看一个update语句，对应的show engine innodb status\G;日志输出有如下疑问
- lock mode S是什么锁，Next Key、间隙锁、记录锁？
- lock mode S locks gap before rec是什么锁，共享间隙锁？
- 间隙锁我印象中是没有S、X区分的，那问题2的锁是什么锁呢
- 1,4这条记录的间隙为什么上了lock mode S locks gap before rec
- 2，2这条记录为什么上了lock mode S
