MySQL-InnoDB锁

最新推荐文章于 2024-12-11 11:11:32 发布

daliucheng

最新推荐文章于 2024-12-11 11:11:32 发布

阅读量461

点赞数

分类专栏：数据库文章标签： mysql 数据库 java

本文链接：https://blog.youkuaiyun.com/daliucheng/article/details/124918955

版权

数据库专栏收录该内容

6 篇文章

订阅专栏

MySQL-InnoDB锁

innoDB中支持下面的几种锁：

Shared and Exclusive Locks（共享锁和独占锁）
Intention Locks（意向锁）
Record Locks（记录锁）
Gap Locks（间隙锁）
Next-Key Locks
Insert Intention Locks（插入意向锁）
AUTO-INC Locks（自动提交锁）
Predicate Locks for Spatial Indexes（空间索引的谓词判断锁）

Shared and Exclusive Locks（共享锁和独占锁）

这两个锁的作为范围是行级别，共享（share锁，也叫做s锁）允许持有锁的事务读取一行，允许多个事务同时获取一把share（s）锁。独占锁（Exclusive锁，也叫做x锁）允许持有锁的事务更新或者删除一行，独占锁和共享锁是互斥的，并且独占锁不支持多个事务同时获取一把锁。例子如下：

如果T1事务在一行上已经持有了r锁，这时有一个事务T2想要获取这一行数据的锁：

想要获取s锁，允许，T1和T2都是同持有对一行数据的s锁。
想要获取x锁，不允许，s锁和x锁互斥。

Intention Locks（意向锁）

他是一种表级锁。它表示，当前有事务正在获取表中行级别的锁。获取锁的顺序是，先获取意向锁（表级锁）在或者行级别锁。意向锁也有两种类型

intention shared lock（IS锁）

表示事务打算在表中的单个行上设置共享锁。

intention exclusive lock (IX锁)

表示事务打算对表中的单个行设置独占锁。

比如select ... from ... lock in share mode 获取IS lock，select ... from ... for update获取IX锁。

IS和IX对应的就是独占锁和共享锁在表级别的锁，在获取X锁之前要写获取IX锁，同样的，在获取S锁之前要先获取IS锁。IS，IX，X，S锁的关系如下：

T1/T2	`X`	`IX`	`S`	`IS`
`X`	Conflict	Conflict	Conflict	Conflict
`IX`	Conflict	Compatible	Conflict	Compatible
`S`	Conflict	Conflict	Compatible	Compatible
`IS`	Conflict	Compatible	Compatible	Compatible

这个表格的意思如下：行代码事务T1，列代表事务T2。

T1先获取X锁，T2获取X锁，结果为Conflict（互斥）。T1先获取IS锁，T2获取IS锁，是可以获取到的。

一般来说，意向锁是不会堵塞任何，除非有显示的锁表的语句比如lock tables ... write,意向锁主要的目的是为了表示某个事务正在锁定表中的一行。通过show engine innoDB status来看，类似如下：

TABLE LOCK table `test`.`t` trx id 10080 lock mode IX

比如说，现在要修改一个表结构，有两个事务，一个要改表结构（T1），一个要做查询操作（T2），那T1怎么知道这个表中有没有行锁？全部遍历一遍所有的行？那怎么保证在遍历的时候会不会有别的事务也在获取锁呢？有了意向锁，看一下就知道了。

Record Locks（记录锁）

记录锁就是索引上的锁，记录锁的作用范围就是索引，innoDB中的锁基本都是记录锁，所以主键字段一定是有记录锁的，要是没有主键字段，Mysql就会默认增加Row_id来作为隐藏的主键字段。

比如select * from t_student where age = 12 for update，t_student 表中age是索引，这个语句就是锁住了这一行，阻止任何事务对这条数据的修改，更新，插入。用show engine innoDB status来看，如下：
在这里插入图片描述

------------

TRANSACTIONS

------------

Trx id counter 30386

Purge done for trx’s n:o < 30383 undo n:o < 0 state: running but idle

History list length 0

LIST OF TRANSACTIONS FOR EACH SESSION:

—TRANSACTION 421797202743296, not started

0 lock struct(s), heap size 1128, 0 row lock(s)

—TRANSACTION 421797202741680, not started

0 lock struct(s), heap size 1128, 0 row lock(s)

—TRANSACTION 421797202740872, not started

0 lock struct(s), heap size 1128, 0 row lock(s)

—TRANSACTION 30385, ACTIVE 5 sec inserting

mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 1

MySQL thread id 1029, OS thread handle 140321211012864, query id 38645 xxxx root update

/* ApplicationName=DataGrip 2021.2.2 */ insert into t_student(name, age) VALUES

(‘小粉’,12)

------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 1056 page no 5 n bits 80 index idx_student_age of table data.t_student trx id 30385 lock_mode X locks gap before rec insert intention waiting

Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

0: len 4; hex 8000000d; asc ;;

1: len 4; hex 80000002; asc ;;

Gap Locks（间隙锁）

gap锁是索引之间的锁，他是一个范围锁。比如select * from t_student where age > 12 and age < 14 for update,范围锁，锁的是12到14之间的index，它会阻止在这个区间里面的插入不管数据是否存在，比如插入age为13的数据，13在这个区间，不管13是否真的存在。因为现在存在的这些记录之间的范围已经被锁住了。

在这里插入图片描述

Gap可能会跨越单个索引值，多个索引值，或者可能为空，比如:

select *
from t_student where (age > 12 and score > 0) and (age<15 and  score < 5);
-- age和score都加了索引

gap锁也有两种类型

shared gap lock（共享gap锁，也叫做gap S-lock）。
exclusive gap lock（排他gap锁，也叫做gap X-lock）。

gap锁的目的只是为了阻止区间插入，gap锁是可以共存的，两个事务可以同时获取同一个区间的gap锁。共享和排他的gap锁没有区别，它们之间没有冲突，并且执行同样的功能。

现在来看删除和更新的情况

表结构如下:

-- auto-generated definition
create table t_student
(
    id    int auto_increment
        primary key,
    name  varchar(200)     null,
    age   int              null,
    score double default 0 not null comment '分数'
);

create index idx_score
    on t_student (score);

create index idx_student_age
    on t_student (age);

删除
- 删除存在的数据

在这里插入图片描述

删除存在的数据，会堵塞。gap锁锁住现存的记录之间的范围。因为13存在。

删除不存在的数据

在这里插入图片描述

不会堵塞，gap锁会锁住现存的记录之间的范围，13已经被删除了。

更新操作和上面的结果一样。

要知道，gap锁只是在RR隔离级别下才会用得到的，高的隔离级别会导致并发性降低。对于索引字段，如果不指定区间范围，gap锁锁住的当前记录到下一个记录之间的间隙。在这个间隙里面不能插入数据。对于非索引字段，gap锁会锁住整个表看下面的分析

gap锁分析

有索引

一开始的时候数据如下：（age字段有普通索引）
在这里插入图片描述

有两个事务，事务（T1）查询age=12的。事务（T2）插入数据，age=10。结果如下：

在这里插入图片描述

插入会堵塞，gap锁会锁住age=12和第一个小于12数据之间的范围，因为这里还没有小于12的，所以，这里gap锁的范围是(-oo,12)。

age=10的数据已经插入了，还是上面的两个事务，事务（T1）查询age=12的。事务（T2）插入数据，age=8。结果如下：

在这里插入图片描述

插入不会堵塞，小于12的第一个数据是10，所以，这里gap锁锁住的范围是（10，12），插入8没有问题。

事务（T1）查询age=12的。事务（T2）插入数据，age=11。结果如下：

在这里插入图片描述

堵塞，因为这个时候gap锁的范围是（10，12），插入11肯定会堵塞。

没有索引

事务（T1）查询,事务（T2）插入数据age=10

在这里插入图片描述

注意，这里age字段没有索引，插入age=10的时候会堵塞。按照上面例子的结论，应该堵塞。没有问题。

事务（T1）查询,事务（T2）插入数据age=8

在这里插入图片描述

上面10的数据在事务t1提交之后，会插入成功。按照之前上一章节的例子的结论来说，这次的插入不应该堵塞，结果确实堵塞了。

结论分析

在有索引的字段中，索引的实现是B+树，都是有顺序的，比如age=12，只要锁住小于12的第一条数据和12之间的间隙就好了，别的区间和事务t1的查询没有关系，事务t1只关心age=12的数据，因为B+树的原因，他已经很明确的知道了可能会出现问题的地方，所以，只要锁住这个区间就好了。第一次插入数据，因为没有小于12的，gap锁锁的是（-oo，12）,之后插入age=10，他只要锁住age=10和age=12的区间就好了，别的区间已经很明确的知道不会影响t1这次的查询。

在没有索引的字段中，锁住的是整个表，因为到处都有可能，没有B+树的加持，鬼知道哪里有可能。所以，直接锁住整个表。

gap锁退化

如果说，age字段上面是一个唯一索引，事务t1查询age=12，gap锁就不会去加之前的范围了，因为已经很明确了。唯一索引。别的插入数据和t1这次age=12查询没有关系，已经唯一索引已经保证不会在有age=12的数据出现。但是如果查询条件并不是一个唯一索引，gap锁该怎么弄，就怎么弄，因为不能确定唯一值。比如，student表中age和name加了一个唯一索引，但是查询条件确实age=12,这个时候gap锁不会退化。

Next-Key Locks

Next-key锁是Gap锁和Record锁的结合体。gap锁锁的是范围，record锁的是index，两个组合在一起就是next-key锁。

innoDB中的行级锁都是锁在索引上的。比如上面的例子age字段上面增加了索引，那么他的上锁的范围如下：

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

这里的例子可以结合上面gap锁的分析，索引记录中的下一个索引值也影响着gap锁的范围，但gap锁没有锁index的能力，他只是能阻止在这个区间里面不能插入数据。默认的情况下，next-key锁在RR隔离级别下使用。

Insert Intention Locks（插入意向锁）

插入意向锁是Insert操作在插入一行数据之前设置的一种间隙锁，它表示插入到同一个索引的不同事务如果没有插入到间隙中的同一个位置，则不需要等待。比如现在表中有两个字段，age=4和age=7，现在有两个插入数据的事务，事务t1想要插入age=5，事务t2想要插入age=6，他们并不会因为是同一个区间而冲突，每个事务在获取插入行的行锁之前先获取4和7之间的插入意向锁。

下面的例子展示了在获取插入的记录的独占锁之前先获取插入意图锁

事务t1给id大于100的防止排他锁，事务t2插入id等于101的数据。

表结构如下

CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
 INSERT INTO child (id) values (90),(102);

在这里插入图片描述

用show engine innoDB status看结果如下

在这里插入图片描述

AUTO-INC Locks（自动提交锁）

特别的表级别的锁，主要是为了AUTO_INCREMEN的列的值连续。如果一个事务在插入值，别的事务就要等待这个事务将数据插入到表中，这样AUTO_INCREMENT的列才能连续，

Predicate Locks for Spatial Indexes（空间索引的谓词判断锁）

它的出现主要是为了解决next-key锁在空间索引中不知道下一个键在哪里，计算不了，在B+树种，方向是递增的，很好计算，但是在空间索引中，next-key就不行了。

关于博客这件事，我是把它当做我的笔记，里面有很多的内容反映了我思考的过程，因为思维有限，不免有些内容有出入，如果有问题，欢迎指出。一同探讨。谢谢。