十分钟了解一下MySql锁机制

·  阅读 717

MySql锁的概念

在MySql中有很多不同种类的锁

  • 从操作粒度来说有 表锁,行锁,页锁

    • 表级锁:每次操作锁住整张表。锁定粒度大,发生锁冲突的概率最高,并发度最低。应用在MyISAM、InnoDB、BDB 等存储引擎中。

    • 行级锁:每次操作锁住一行数据。锁定粒度最小,发生锁冲突的概率最低,并发度最高。应用在InnoDB 存储引擎中。

    • 页级锁:每次锁定相邻的一组记录,锁定粒度界于表锁和行锁之间,开销和加锁时间界于表锁和行锁之间,并发度一般。应用在BDB 存储引擎中。

  • 从操作类型来说有:写锁,读锁

    • 读锁(S锁):共享锁,针对同一份数据,多个读操作可以同时进行而不会互相影响。

    • 写锁(X锁):排他锁,当前写操作没有完成前,它会阻断其他写锁和读锁

IS锁、IX锁:意向读锁、意向写锁,属于表级锁,S和X主要针对行级锁。在对表记录添加S或X锁之前,会先对表添加IS或IX锁。

  • 从操作性能来说有:悲观锁,乐观锁

    • 乐观锁:一般的实现方式是对记录数据版本进行比对,在数据更新提交的时候才会进行冲突检测,如果发现冲突了,则提示错误信息。

    • 悲观锁:在对一条数据修改的时候,为了避免同时被其他人修改,在修改数据之前先锁定,再修改的控制方式。共享锁和排他锁是悲观锁的不同实现,但都属于悲观锁范畴。

MySql加锁原理

在InnoDB引擎中,我们可以使用行锁和表锁,其中行锁又分为共享锁和排他锁。InnoDB行锁是通过对索引数据页上的记录加锁实现的,主要实现算法有 3 种:Record Lock、Gap Lock 和 Next-key Lock。

  • RecordLock锁:锁定单个行记录的锁。(记录锁,RC、RR隔离级别都支持)

  • GapLock锁:间隙锁,锁定索引记录间隙,确保索引记录的间隙不变。(范围锁,RR隔离级别支持)

  • Next-key Lock 锁:记录锁和间隙锁组合,同时锁住数据,并且锁住数据前后范围。(记录锁+范围锁,RR隔离级别支持)

在RR隔离级别,InnoDB对于记录加锁行为都是先采用Next-Key Lock,但是当SQL操作含有唯一索引时,Innodb会对Next-Key Lock进行优化,降级为RecordLock,仅锁住索引本身而非范围。

接下来以案例形式测试MySql针对各种情况的加锁情况

创建测试表

CREATE TABLE `lock_test` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
复制代码

这个表中 id 不是主键,跟 name字段一样,就是个普通的字段,可以通过 show index from lock_test 看到该表中没有任何索引

image.png

插入几条测试数据

insert into lock_test values (1,'a');
insert into lock_test values (5,'b');
insert into lock_test values (9,'c');
insert into lock_test values (15,'d');
复制代码

对于普通的 select * from table;innodb不加锁

在第一个窗口开启一个事务,在事务中查询lock_test表,此时事务还没有提交,这时候另外一个事务也去查询该表数据,发现可以查到,证明对于普通的 select innodb不加锁 普通查询.png

对于 select * from table where id = 1 for update; 会加锁

1、如果 查询条件 上没有索引

由于id字段上没有索引,而锁是基于索引树来实现的,那么就会锁住全表。如下:

第一个窗口开启一个事务执行 select * from table for update; 在第二个事务中想要插入数据就会被阻塞住,不管是插入 id = 2 还是id = 10都会被阻塞掉,因为已经把全表锁住了,只要这个事务没提交,那么别的事务就没办法进行操作,但是可以进行操作

  • 写操作被阻塞 截屏2022-05-27 上午9.42.15.png

截屏2022-05-27 上午9.42.55.png

  • 读操作正常 image.png

  • 总结:当执行 select * from table where ... for update; 如果where条件后面的字段没有索引,就会锁住全表,此时其它事务是不能对该表的任何记录做写操作,但是可以读

2、如果 查询条件 普通索引

// 给id字段创建普通索引
create index id_index on lock_test(id);
复制代码

在一个窗口开启一个事务,执行 select * from lock_test where id = 5 for update; 在另一个窗口尝试插入 id = 3的数据,insert into lock_test values (3,'ee');会发现被阻塞住了。但是你插入 id = 10的数据,会发现插入成功了

image.png image.png

因为此时 id 上是有索引了,所以不会锁住全表,但是会使用Next-key Lock 锁锁住该数据前后的范围包括该数据。表中的数据如下:

image.png

此时你是查询 id = 5的这条记录,此时发现 id 字段是一个普通索引,这时候就会锁住 id = 5 旁边二条记录的范围,id = 5 旁边记录是 id = 1 和 id = 9,所以此时会把 id = (1,9)范围内都锁住,也就是你可以插入数据,但是插入数据的id的值不能在(1,9)范围之间,这就是Next-key Lock 锁,所以你会发现当插入 id = 10 的记录可以插入成功,因为 id = 10 并不在(1,9)这个范围之间

  • 总结:当执行 select * from table where ... for update; 如果where条件后面的字段是普通索引,就会使用Next-key Lock 锁,会锁住该记录旁边二条记录的范围内的所有数据,其它事务不能对该范围内的数据进行写操作,但是对于范围外的数据可以进行写操作,但是整个表可以进行读操作

3、如果 查询条件 是唯一索引

// 给 id 字段加上唯一索引
create UNIQUE index id_index on lock_test(id);
复制代码

在一个窗口开启一个事务,执行 select * from lock_test where id = 5 for update; 在另一个窗口尝试插入 id = 3的数据,insert into lock_test values (3,'ee');会发现插入成功了,并没有锁住(1,9)这个范围内的数据 image.png

  • 总结:当执行 select * from table where ... for update; 如果where条件后面的字段是唯一索引,就会对锁进行优化,锁会从Next-key Lock 锁 下降到 RecodeLock行级锁,也就是只会锁住id=5这一行记录

对于 update ..... where 会加锁

//将之前创建的索引删除掉
drop index id_index on lock_test;
复制代码

1、如果 查询条件 上没有索引

修改表上的任何数据或者是插入数据都会被阻塞住

image.png

  • 总结:当执行update ..... where时候,where条件没有任何索引,就会升级为表锁,在这个事务没有提交之前,其它事务不能对该表做任何写操作,但是可以读

2、如果 查询条件 上是普通索引

image.png 是会加上间隙锁,当你修改 id = 5的这条记录的时候,你想插入一条记录ID在 (1~9)范围之间的数据就会被阻塞,但是你修改其它数据是不会被阻塞的 image.png

总结:如果 查询条件 上是普通索引,那么你是没办法在该记录锁住的范围内添加的,比如上图中的ID是(1~9)范围内的任何数据,但是修改不会被阻塞

2、如果 查询条件 上是唯一索引

当查询条件是唯一索引的时候,这时候锁会降级为记录锁,也就是只会锁住这一行记录,你可以添加或者修改其它任何记录 image.png

对于 delete ..... where 会加锁

1、如果 where条件 上没有索引

与 update ... where 是一样的,没有索引就会锁住全表

2、如果 where条件 普通索引

与 update ... where 是一样的,会锁住该记录范围内,不可以添加记录,但是可以修改

2、如果 where条件 唯一索引

与 update ... where 是一样的,只会锁住这一行记录,可以添加任何记录或者修改

整体锁的情况与update ... where是一样的

结论

1、select ... from 语句:InnoDB引擎采用MVCC机制实现非阻塞读,所以对于普通的select语句,InnoDB不加锁

2、select ... from lock in share mode语句:追加了共享锁,InnoDB会使用Next-Key Lock锁进行处理,如果扫描发现唯一索引,可以降级为RecordLock锁。

3、select ... from for update语句:追加了排他锁,InnoDB会使用Next-Key Lock锁进行处理,如果扫描发现唯一索引,可以降级为RecordLock锁。

4、update ... where 语句:InnoDB会使用Next-Key Lock锁进行处理,如果扫描发现唯一索引,可以降级为RecordLock锁。

5、delete ... where 语句:InnoDB会使用Next-Key Lock锁进行处理,如果扫描发现唯一索引,可以降级为RecordLock锁。

6、insert语句:InnoDB会在将要插入的那一行设置一个排他的RecordLock锁。

分类:
后端
标签:
收藏成功!
已添加到「」, 点击更改