共享和排他锁(shard and Exclusive Locks)
Innodb 支持标准的行级共享和排他锁。
共享锁：允许多个事务同时持有同一行的共享锁，阻止其他事物持有其排他锁
排他锁：阻止其他事务持有共享锁和排他锁
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c` (`c`)
) ENGINE=InnoDB;
insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);
执行上面脚本，该表将作为后面示例表
示例
排他锁阻止其他事务获得锁
| session 1 | session 2 | Time |
|---|---|---|
| select * from t where t.id = 0 for update | T1 | |
| select * from t where t.id = 0 lock in share mode(block) | T2 |
---TRANSACTION 2049725, ACTIVE 3 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 1354, OS thread handle 0x7000049a5000, query id 4451 localhost root statistics
select * from t where id = 0 lock in share mode
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1083 page no 3 n bits 80 index `PRIMARY` of table `test`.`t` trx id 2049725 lock mode S locks rec but not gap waiting
输入innodb status后发现，2049725正等待获得共享锁
共享锁
| Session1 | session2 | Session3 | Time |
|---|---|---|---|
| select * from t where t.id = 0 lock in share mode | T1 | ||
| select * from t where t.id = 0 lock in share mode(no block) | T2 | ||
| select * from t where t.id = 0 for update(block) | T3 |
共享锁会阻止其他事务获取排他锁，但是不会阻止其他事务获得共享锁
意向锁(Intention Locks)
意向锁是innodb为了支持多种粒度的锁而引入的。意向锁也分为两种
共享意向锁一个事务获取行共享锁之前需要先申请表的共享意向锁
排他意向锁一个事务获取行排他锁之前需要先申请表的排他意向锁
意向锁最主要的目的是表明有事务正锁定了一行或将要锁定一行
表级别锁兼容
79050747.png
如上图可以看出表级别意向锁不论排他还是共享都是互相兼容的。意向锁不会阻塞任何事务，除了锁表请求(Lock tables writes)
示例
排他意向锁
select * from t where t.id = 0 for update
---TRANSACTION 2049731, ACTIVE 4 sec
2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 1357, OS thread handle 0x700004b3d000, query id 4479 localhost root
TABLE LOCK table `test`.`t` trx id 2049731 lock mode IX
RECORD LOCKS space id 1083 page no 3 n bits 80 index `PRIMARY` of table `test`.`t` trx id 2049731 lock_mode X locks rec but not gap
从内核状态可以看出对id=0这行除了申请了行级别排他锁外，还申请了表级别的排他意向锁(IX)
排他意向锁阻塞
| session 1 | session 2 | Time |
|---|---|---|
| select * from t where t.id = 0 lock in share mode | T1 | |
| lock table t writes(block) | T2 |
lock信息输出设置
set global innodb_status_output_locks=ON;
set global innodb_status_output=ON
