MySQL读锁的区别和应用场景分析

脚本之家 2022-05-10

The following article is from 网管叨bi叨 Author KevinYan11

关注

“脚本之家

”，与百万开发者在一起

作者 | KevinYan11

出品 | 网管叨bi叨（ID：kevin_tech）

读锁的概念和区别

如果在MySQL的事务里查询数据，然后在同一事务中插入或更新相关数据，常规的SELECT语句不能提供足够的保护。其他并行的事务可以更新或删除第一个事务里刚查询的相同行。InnoDB支持两种类型的读锁，提供了额外的安全性：

SELECT ... LOCK IN SHARE MODE
在读取到的行上设置共享锁。其他会话可以读取行，也可以继续给行加共享锁，但是在当前事务提交之前其他会话不能修改加了共享锁的行。如果这些行中的任何一个被尚未提交的另一个事务更改，则当前查询将等待直到该事务结束，然后使用最新值。
SELECT ... FOR UPDATE
用排他锁锁定行和任何关联的索引条目，就像在这些行上执行UPDATE语句一样。禁止其他事务在这些加了锁的行上进行UPDATE、执行LOCK IN SHARE MODE或者读取某些事务隔离级别的数据。

通过对比，发现FOR UPDATE的加锁方式类似并发编程里的写锁，而LOCK IN SHARE MODE则是读锁，同一时间点相同的行上只允许出现一个写锁，或者是多个读锁。一旦有一种锁在数据行上成功加上了锁，另外一种加锁尝试就会进入等待。

这两种锁都不会阻塞普通SELECT语句读取这些行，一致的读（快照读）将忽略行记录上设置的任何锁。（行记录的旧版本无法被锁定；可以通过在行记录的内存副本上应用undo log重构它们。）

应用场景

SELECT ... LOCK IN SHARE MODE的应用场景适合于两张表存在关系时的写操作，拿MySQL官方文档的例子来说，假如存在两张有关系的表：PARENT和CHILD，使用普通的SELECT语句（快照读）来查询表PARENT并验证父行是否存在后再将子行插入CHILD表，这种方式安全吗？答案是否定的，因为其他会话可能会在你这个会话的SELECT和INSERT之间的某个时间点删除了父行，这个删除操作你是无法察觉到的。

为避免这种潜在的问题，我们使用“加共享锁”的方式执行SELECT：

SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;

在LOCK IN SHARE MODE查询返回PARENT表里名为" Jones"的数据行之后，你就可以安全地将子记录添加到CHILD表中并提交事务。在事务提交前任何试图在PARENT表的对应行上获取排他锁的事务都将等到你完成操作提交事务后再进行。

但是如果是同一张表的应用场景，举个例子，电商系统中在产生订单之前需要确认商品数量大于1,产生订单之后应该将商品数量减1。

1.	select id, amount from products where product_name='Apple11'
2.	update products set amount=amount-1 where id = {id}

显然上面的做法是是有问题，因为如果1查询出amount的值为1，但是这时正好其他会话也买了该商品并产生了订单，那么amount就变成了0，这时这个会话里的第二步再执行就有问题。

那么可以使用LOCK IN SHARE MODE给这行加共享锁可行吗？也是不合理的，因为两个会话同时用共享读锁锁定该行记录时，这时两个会话再进行第二步的UPDATE时都会等待其他事务的读锁释放，这必然会产生死锁导致其中一个事务回滚。

时间	事务1	事务2
1	SELECT id, amount FROM products WHERE product_name='Apple11' LOCK IN SHARE MODE;
2	判断amount > 1	SELECT id, amount FROM products WHERE product_name='Apple11' LOCK IN SHARE MODE;
3	UPDATE products SET amount=amount-1 WHERE id = {id};	判断amount > 1
4	等待事务2释放读锁	UPDATE products SET amount=amount-1 WHERE id = {id};
5	继续等待	检测到死锁（ Deadlock found when trying to get lock; try restarting transaction）
6	继续等待	回滚事务
7	事务提交

通过上面这个案例可知LOCK IN SHARE MODE的方式在这个场景中不适用，如果两个事务以共享模式锁定该行，则任何人都将无法更新该行（当其他事务持有该行的锁时，不允许进行更新）。在这种情况下真正发生的是，两者之一将超时，释放锁，然后另一个将成功更新该行。

我们需要使用FOR UPDATE的方式直接加写锁，从而短暂地阻塞事务2。下面是两个操作根据发生时间的推演过程

时间	事务1	事务2
1	SELECT id, amount FROM products WHERE product_name='Apple11' FOR UPDATE
2	判断amount > 1	SELECT id, amount FROM products WHERE product_name='Apple11' FOR UPDATE
3	UPDATE products SET amount=amount-1 WHERE id = {id};	等待事务1释放写锁
4	事务提交	等待事务1释放写锁
5		判断amount > 1 不成立
6		事务提交退出