MySQL InnoDB锁

好像在哪儿见过
88 阅读6分钟

持续创作，加速成长！这是我参与「掘金日新计划 · 10 月更文挑战」的第14天，点击查看活动详情

官网click me!

行锁

共享锁

Shared Locks（共享锁），我们获取了一行数据的读锁以后，可以用来读取数据，所以它也叫做读锁。用 select …… lock in share mode; 的方式手工加上一把读锁。

释放锁有两种方式，只要事务结束，锁就会自动事务，包括提交事务和结束事务。

Transaction 1Transaction 2
begin;
SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;
begin;
SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE; // OK

排它锁

Exclusive Locks（排它锁），它是用来操作数据的，所以又叫做写锁。只要一个事务获取了一行数据的排它锁，其他的事务就不能再获取这一行数据的共享锁和排它锁。

增删改，都会默认加上一个排它锁。

手工加锁，用 FOR UPDATE 给一行数据加上一个排它锁，这个无论是在我们的代码里面还是操作数据的工具里面，都比较常用。

释放锁的方式跟前面是一样的。

排他锁的验证：

Transaction 1Transaction 2
begin;
UPDATE student SET sname = '猫老公 555' WHERE id=1;
begin;
SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE; // BLOCKED
SELECT * FROM student where id=1 FOR UPDATE; // BLOCKED
DELETE FROM student where id=1 ; // BLOCKED

表锁

意向锁

当我们给一行数据加上共享锁之前，数据库会自动在这张表上面加一个意向共享锁。

当我们给一行数据加上排他锁之前，数据库会自动在这张表上面加一个意向排他锁。

反过来说：

如果一张表上面至少有一个意向共享锁，说明有其他的事务给其中的某些数据行加上了共享锁。

如果一张表上面至少有一个意向排他锁，说明有其他的事务给其中的某些数据行加上了排他锁。

select * from t2 where id = 4 for update;

TABLE LOCK table `xiao`.`t2` trx id 24467 lock mode IX RECORD LOCKS space id 64 page no 3 n bits 72 index PRIMARY of table `xiao`.`t2` trx id 24467 lock_mode X locks rec but not gap

第一个，我们有了表级别的锁，在 InnoDB 里面就可以支持更多粒度的锁。

第二个作用，当我们准备给一张表加上表锁的时候，必须先要去判断有没其他的事务锁定了其中了某些行。如果有的话，肯定不能加上表锁。那么这个时候我们就要去扫描整张表才能确定能不能成功加上一个表锁，如果数据量特别大，比如有上千万的数据的时候，加表锁的效率是不是很低？

但是我们引入了意向锁之后就不一样了。我只要判断这张表上面有没有意向锁，如果有，就直接返回失败。如果没有，就可以加锁成功。所以 InnoDB 里面的表锁，我们可以把它理解成一个标志。就像火车上厕所有没有人使用的灯，是用来提高加锁的效率的。

Transaction 1Transaction 2
begin;
SELECT * FROM student where id=1 FOR UPDATE;
begin;
LOCK TABLES student WRITE; // BLOCKED
UNLOCK TABLES; // 释放表锁的方式

行锁的原理

首先我们有三张表，一张没有索引的 t1，一张有主键索引的 t2，一张有唯一索引的t3。

没有索引的表（假设锁住记录）

我们先假设 InnoDB 的锁锁住了是一行数据或者一条记录\color{red}{锁住了是一行数据或者一条记录}。

Transaction 1Transaction 2
begin;
SELECT * FROM t1 WHERE id =1 FOR UPDATE;
select * from t1 where id=3 for update; //blocked\color{red}{//blocked}
INSERT INTO t1 (id, name) VALUES (5, '5'); //blocked\color{red}{//blocked}

现在我们在两个会话里面手工开启两个事务。

在第一个事务里面，我们通过 where id =1 锁住第一行数据。

在第二个事务里面，我们尝试给 id=3 的这一行数据加锁，这个加锁的操作被阻塞了。

我们再来操作一条不存在的数据，插入 id=5。它也被阻塞了。

实际上这里整张表都被锁住了。所以，我们的第一个猜想被推翻了，InnoDB的锁锁住的应该不是Record\color{red}{InnoDB 的锁锁住的应该不是Record}。

有主键索引的表

Transaction 1Transaction 2
begin;
select * from t2 where id=1 for update;
select * from t2 where id=1 for update; //blocked\color{red}{// blocked}
select * from t2 where id=4 for update; //OK\color{blue}{// OK}

第一种情况，使用相同的 id 值去加锁，冲突；使用不同的 id 加锁，可以加锁成功。那么，既然不是锁定一行数据，有没有可能是锁住了id的这个字段呢\color{red}{锁住了 id的这个字段呢}？

唯一索引（假设锁住了字段）

Transaction 1Transaction 2
begin;
select * from t3 where name= '4' for update;
select * from t3 where name = '4' for update; //blocked\color{red}{// blocked}
select * from t3 where id = 4 for update; //blocked\color{red}{// blocked}

在第一个事务里面，我们通过 name 字段去锁定值是 4 的这行数据。

在第二个事务里面，尝试获取一样的排它锁，肯定是失败的，这个不用怀疑。

在这里我们怀疑 InnoDB锁住的是字段，所以这次我换一个字段，用 id=4 去给这行数据加锁，又被阻塞了，说明锁住的是字段的这个推测也是错的，否则就不会出现第一个事务锁住了name，第二个字段锁住id 失败的情况。

既然锁住的不是 record，也不是 column，InnoDB 里面锁住的到底是什么呢？其实答案就是索引。InnoDB的行锁，就是通过锁住索引记录来实现的。

索引又是什么东西？为什么它可以被锁住？

Version:0.9 StartHTML:0000000105 EndHTML:0000000329 StartFragment:0000000141 EndFragment:0000000289

还有两个问题没有解决：

1、为什么表里面没有索引的时候，锁住一行数据会导致锁表？

或者说，如果锁住的是索引，一张表没有索引怎么办？

所以，一张表有没有可能没有索引？答案是否定的 InnoDB的表一定会有一个索引。

1）如果我们定义了主键(PRIMARY KEY)，那么 InnoDB 会选择主键作为聚集索引。

2）如果没有显式定义主键，则 InnoDB 会选择第一个不包含有 NULL 值的唯一索引作为主键索引。

3）如果也没有这样的唯一索引，则 InnoDB 会选择内置 6 字节长的 ROWID 作为隐藏的聚集索引，它会随着行记录的写入而逐渐递增。

所以，为什么锁表，是因为查询没有使用索引，会进行全表扫描，然后把每一个隐藏的聚集索引都锁住了。

2、为什么通过唯一索引给数据行加锁，主键索引也会被锁住？

在 InnoDB 里面，当我们使用辅助索引的时候，它是怎么检索数据的？辅助索引的叶子节点存储的是什么内容？

在辅助索引里面，索引存储的是二级索引和主键的值。比如name=4，存储的是name的索引和主键 id 的值4。

而主键索引里面除了索引之外，还存储了完整的数据。所以我们通过辅助索引锁定一行数据的时候，它跟检索数据的步骤是一样的，会通过主键值找到主键索引，然后也锁定。

image.png

avatar
文章
阅读
粉丝