MySQL锁的介绍

天天想着裸辞回家种田
82 阅读8分钟

MySQL锁

锁.png

锁粒度

  • 表锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；
  • 行锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；
  • 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

加锁机制

乐观锁

指的是在操作数据的时候非常乐观，乐观地认为别人不会同时修改数据，因此乐观锁默认是不会上锁的，只有在执行更新的时候才会去判断在此期间别人是否修改了数据，如果别人修改了数据则放弃操作，否则执行操作。

MySQL实现乐观锁：版本号机制

update user_balance set balance = 20 ,version=version+1 where user_id=1 and version =1;

在数据中增加一个version字段用来表示该数据的版本号，每当数据被修改版本号就会加1。当某个线程查询数据的时候，会将该数据的版本号一起读取出来，之后在该线程需要更新该数据的时候，就将之前读取的版本号与当前版本号进行比较，如果一致，则执行操作，如果不一致，则放弃操作。

悲观锁

BEGIN;
select * from user_balance WHERE user_id=1 for UPDATE;
update user_balance set balance = 27 where user_id=1;
COMMIT;

指的是在操作数据的时候比较悲观，悲观地认为别人一定会同时修改数据，因此悲观锁在操作数据时是直接把数据上锁，直到操作完成之后才会释放锁，在上锁期间其他人不能操作数据。

兼容性

共享(S)锁：读锁

select * from user_balance WHERE user_id=1 lock in share mode;

对当前行加共享锁，不会阻塞其他事务对同一行的读请求，但会阻塞对同一行的写请求。只有当读锁释放后，才会执行其它事物的写操作。

排他(X)锁：写锁

select * from user_balance WHERE user_id=1 FOR UPDATE;

会阻塞其他事务对同一行的读和写操作，只有当写锁释放后，才会执行其它事务的读写操作。

对于InnoDB 在RR(MySQL默认隔离级别) 而言，对于 update、delete 和 insert 语句， 会自动给涉及数据集加排它锁（X）；

总结：读锁会阻塞写(X)，但是不会堵塞读(S)。而写锁则会把读(S)和写(X)都堵塞。

加锁模式

记录锁（Record Locks）

对表中的记录加锁，叫做记录锁，简称行锁。比如

select * from `user` where id=1 for UPDATE;
-- update也会加上锁
update `user` set age=21 where id=2;

它会在 id=1 的记录上加上记录锁，以阻止其他事务插入，更新，删除 id=1 这一行。

记录锁条件：

  • id 列必须为唯一索引列或主键列，否则上述语句加的锁就会变成临键锁(有索引)
  • 同时查询语句必须为精准匹配（=），不能为 >、<、like等，否则也会退化成临键锁

记录锁是锁住记录，锁住索引记录，而不是真正的数据记录.

如果要锁的列没有索引，进行全表记录加锁

间隙锁（Gap Lock）

锁加在不存在的空闲空间，可以是两个索引记录之间，也可能是第一个索引记录之前或最后一个索引之后的空间。

间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。

以下sql会对age大于7的间隙加锁，此时无法在该间隙中插入数据。

BEGIN;
select * from `user` where age>7 for UPDATE;
COMMIT;

间隙锁.png

临键锁（Next-Key Locks）

临键锁是记录锁和间隙锁的组合，它指的是加在某条记录以及这条记录前面间隙上的锁。（临键锁的索引是基于非唯一索引(和主键列)）

临键锁也可以理解为一种特殊的间隙锁。每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。

比如上图age列潜在的临键锁有： (-∞, 3], (3, 7], (7, 11], (11, 15], (15, +∞]；

在事务A对age=7加上临键锁：

BEGIN;
select * from `user` where age=7 for UPDATE;
COMMIT;

此时执行以下sql：

-- 被锁，需要等待事务A提交
INSERT INTO `user`(`username`, `age`) VALUES ('田8', 5);

-- 被锁，需要等待事务A提交
INSERT INTO `user`(`username`, `age`) VALUES ('田8', 3);

-- 不被锁，执行成功
INSERT INTO `user`(`username`, `age`) VALUES ('田8', 12);

-- 不被锁，执行成功
INSERT INTO `user`(`username`, `age`) VALUES ('田8', 2);

由上面结果可以看出，锁的区间是(3, 7]和(7, 11]。

-- 不被锁，执行成功
INSERT INTO `user`(id,`username`, `age`) VALUES (1,'田8', 3);

为什么相同age=3,上面不设置id的确不能执行成功，设置了id=1时，确能执行成功？

是因为相同age是，还会根据主键id来排序，如下图

临键锁.png

同理，以下sql也不会被阻塞

-- 不被锁，执行成功
INSERT INTO `user`(`username`, `age`) VALUES ('田8', 11);

间隙锁解决幻读

间隙锁是 Innodb 在 RR(可重复读) 隔离级别 下为了解决幻读问题时引入的锁机制。

在可重复读隔离级别下，MySQL通过MVCC(多版本控制)和间隙锁解决幻读。

  • 快照读：在快照读（snapshot read）的情况下，MySQL通过MVCC（多版本并发控制）来避免幻读。

    原理：将历史数据存一份快照，所以其他事务增加与删除数据，对于当前事务来说是不可见的。

    -- 事务B
BEGIN;
INSERT INTO `user`(`username`, `age`) VALUES ('田8', 5);
COMMIT;

-- 事务A
BEGIN;
select * from `user` where age>3;
select * from `user` where age>3;
COMMIT;

    在事务A执行第一次查询后，事务B执行并提交，之后事务再执行查询，查询结果与一次查询相同，并不会查到最新的结果。

  • 当前读：当前读，读取的是记录的最新版本，并且会对当前记录加锁，防止其他事务发修改这条记录。MySQL通过间隙锁解决幻读。

    当前读sql语句有：

    select * from user WHERE ? lock in share mode;
select * from user WHERE ? for UPDATE;
update;
insert;
delete;

    -- 事务A
BEGIN;
select * from `user` where age>7 for UPDATE;
select * from `user` where age>7 for UPDATE;
COMMIT;

-- 事务B
INSERT INTO `user`(`username`, `age`) VALUES ('田8', 8);

    在事务A执行第一次查询后，事务B执行后需要阻塞等待事务A提交后才能执行，也就是age>7的范围被锁，所以事务A第二次查询的结果肯定与第一次查询结果相同。

    注意：间隙锁的机制必须在使用索引的情况下才会生效，比如以下情况：

    -- 事务A
BEGIN;
select * from `user` where age>=7 for UPDATE;
select * from `user` where age>=7 for UPDATE;
COMMIT;

-- 事务B
INSERT INTO `user`(`username`, `age`) VALUES ('田8', 2);

    按前面间隙锁的分析，事务A锁的范围应该是(3,7],(7,+∞]，此时我们插入age=2应该是不会被阻塞的，单执行事务B时发现还是被阻塞了，为什么跟预想的结果不一致？通过explain查看后，我们可以看到sql已经被优化不走索引查询了，所以此时是锁了全表的数据情况，所以插入就会被阻塞。改成以下情况，则跟预想结果一致：

间隙锁必须有索引.png

-- 事务A
BEGIN;
select * from `user` where age>=11 for UPDATE;
select * from `user` where age>=11 for UPDATE;
COMMIT;

-- 事务B
INSERT INTO `user`(`username`, `age`) VALUES ('田8', 6);

意向锁

  • 意向共享(IS)锁：事务有意向对表中的某些行加共享锁（S锁）

    -- 事务要获取某些行的 S 锁，必须先获得表的 IS 锁。 
SELECT * FROM `user` ... LOCK IN SHARE MODE;

  • 意向排他(IX)锁：事务有意向对表中的某些行加排他锁（X锁）

     -- 事务要获取某些行的 X 锁，必须先获得表的 IX 锁。
 SELECT * FROM `user` ... FOR UPDATE;

意向锁的作用是为了提高表锁效率的，在给user表加排他锁的前提下，需要：

  • 当前没有其他事务持有 user 表的排他锁
  • 当前没有其他事务持有 user 表中任意一行的排他锁

第一个条件比较好检测；但如果没有意向锁的情况下，需要一行行检测第二个条件，那效率也太差了；有了意向锁后，只需检测表上是否有意向锁，这样效率就大大提高了。

插入意向锁

插入意向锁是一种间隙锁，专门针对的是数据行的插入操作，多个事务插入相同的索引间隙时，只要不是插入到相同的位置，则不需要进行锁等待。

假设有索引记录的值分别是3和7，单独的事务分别尝试插入5和6，在获得插入行的排它锁之前，每个事务都是用插入意图锁来锁定3和7之间的空间，但是不会相互阻塞。因为行级别是没有冲突的。

-- 事务A
BEGIN;
INSERT INTO `user`(`username`, `age`) VALUES ('田8', 6);
COMMIT;

-- 事务B
BEGIN;
INSERT INTO `user`(`username`, `age`) VALUES ('田8', 5);
COMMIT;

事务A和事务B的age虽然都是在间隙3到7之间，但执行时它们不会相互阻塞，因为id自增所以插入的位置不同，不需要进行锁等待。但如果id相同时，则会进行锁等待，如下：

-- 事务A
BEGIN;
INSERT INTO `user`(`id`,`username`, `age`) VALUES (1,'田8', 6);
COMMIT;

-- 事务B
BEGIN;
INSERT INTO `user`(`id`,`username`, `age`) VALUES (1,'田8', 5);
COMMIT;

总结：

  • InnoDB在RR的事务隔离级别下，使用插入意向锁来控制和解决并发插入。
  • 插入意向锁是一种特殊的间隙锁。
  • 插入意向锁在锁定区间相同但记录行本身不冲突的情况下互不排斥。
avatar
文章
阅读
粉丝