Innodb锁(一)

共享和排他锁(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

Refereneces

• https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html