MySQL数据库锁应该这样用

发布于 2022-4-21 23:26
浏览
0收藏

 

本文针对我们平时使用场景最多的MySQL数据库在RR隔离级别下容易产生幻读的问题,来进行分析并分享解决方案。 

 

PartⅠ 问题回顾

 


幻读的定义:幻读是指某个事务读取某个范围内的记录时,另外一个事务又在该范围内插入了新的记录,当之前事务再次读取该范围内的记录时就会产生幻行。


举一个例子,user表中id是主键索引,T1是主事务:
 MySQL数据库锁应该这样用-开源基础软件社区


T2是干扰事务:
 MySQL数据库锁应该这样用-开源基础软件社区


Step1:T1开始,并检查user表中是否有id=1的记录。
Step2:T2开始,插入id为1的记录且成功执行。
Step3:T1查到没有id=1的记录就开始插入id=1的记录,但是失败了(主键冲突)。
Step4:T1不能接受现实又查了一遍是否存在id=1的记录,发现的确没有,彻底崩溃...


从第四步我们可以看出,在主事务执行commit之前,不管再查多少次,都无法获取到id=1的这条记录,因为此时它已经产生幻读了。

 

Part Ⅱ 解决方案

 


要解决幻读的问题有两种方案,一种是采用SERIALIZABLE 数据隔离级别,但是这种方案会强制把所有事务排序,来达到事务之间不互相冲突产生幻读的问题,当事务并发高的时候,很容易产生大量超时和锁竞争的情况,所以一般不太建议采用这种方案。另一种方案是采用在RR数据隔离级别下,手动给select操作加上x锁(排它锁)或者s锁(共享锁),下面就具体介绍一下x锁和s锁。


1. 什么是共享锁和排它锁

 

共享锁(SELECT ... LOCK IN SHARE MODE)即一个事务获取一条记录共享锁的同时,其他事务也可以获得这条记录的共享锁,但是如果同时有多个事务获得这条记录的共享锁,谁也无法修改这条记录,直到都释放掉共享锁,只剩下一个事务拥有这条记录的锁为止。


排它锁(SELECT ... FOR UPDATE)即一个事务获得了一条记录的排它锁的同时,其他事务就不能获得这条记录的共享锁和排它锁,也无法修改这条记录,直到这个事务释放掉锁为止。


2. 相同点和不同点

 

相同点:一个事务在获得一条记录的共享锁或者排它锁的同时,其他事务都不能修改这条记录,直到这个事务释放掉锁为止。


不同点:排它锁比共享锁多阻塞了其他事务对相同记录的共享锁,但是不影响快照读。


3. 举例说明

 

共享锁:

 

事务1 事务2
start  transaction;
   
   select * from tb where id = 1 lock in share mode;
   +-----+------------+
   | id  | name       |
   +-----+------------+
   | 1 | aaa            |
   +-----+------------+
 
  start transaction;
   select * from tb where id = 1 lock in share mode;
   +-----+------------+
   | id  | name       |
   +-----+------------+
   | 1 | aaa            |
   +-----+------------+
update  tb set name = 'ccc' where id = 1;
   
   等待事务2释放锁
 
执行成功
   
   Query OK, 1 row affected (0.02 sec)
update tb set name = 'ddd' where  id = 1;
   
   产生死锁,回滚释放锁
  update tb set name = 'ddd' where  id = 1;
   
   等待事务1释放锁
commit;  
  update tb set name = 'ddd' where  id = 1;
   
   Query OK, 1 row affected (0.02 sec)

  

排它锁:

 

事务1 事务2
start transaction;
select * from tb where id = 1 for update;
+-----+------------+
| id  | name       |
+-----+------------+
| 1 | aaa            |
+-----+------------+
 
  start transaction;
select * from tb where id = 1 for update;
等待事务1释放锁
update tb set name = 'ccc' where id = 1;
Query OK, 1 row affected (0.02 sec)
 
commit;  
  select * from tb where id = 1 for update;
+-----+------------+
| id  | name       |
+-----+------------+
| 1 | ccc            |
+-----+------------+

 

Part Ⅲ 应用场景

 


结合我们平常的营销活动,这里分别举一个例子加以说明。

 

共享锁举例:
譬如一个工会活动,我们会设计一张存放工会信息的总表teamInfo,还会设计一张存放操作工会日志的表teamLog,当用户操作工会的时候,我们如果直接insert一条日志到日志表,其实是有一定的风险的,如果这时会长删除了工会,那么就可能出现数据不一致的情况,所以我们在插入数据时先select * from teamInfo where teamId = xx lock in share mode;再insert into teamLog xxxxxxx;这样就可以了。


排它锁举例:
譬如我们常见的秒杀活动,一般秒杀活动参与秒杀的物品都是有数量限制的,我们在判断用户是否能购买时会判断,是否物品还有剩余,有剩余的情况下再把剩余数量减1,具体sql为1.select * from goods where id = xxx;2.update num = num-1 from goods where id = xxx;当多个事务并发的时候就会出现秒杀物品超卖的情况。如果我们改成select * from goods where id = xxx for update;这样就可以了(这种用法的性能不是很高,这里只是举例排它锁的使用场景,使用时要考虑具体场景是否适合用)。

 

Part Ⅳ 总结

 


那共享锁和排它锁是否能互相代替呢,这要看具体的场景,像上面两个例子就不行,第一个例子如果用了排它锁就会造成一个用户在操作工会的时候,其他用户就不能获取这条记录共享锁的情况。第二个例子如果使用共享锁的话,其他事务都能获得goods表这条记录的共享锁,会导致谁也更新不了剩余数量这个值的情况。所以共享锁和排它锁都有各自的作用,不能互相替代。

 

本文作者:裘维清,腾讯高级运营开发工程师,主要负责腾讯游戏的运营开发工作。

 

文章转自公众号:腾讯云数据库

标签
收藏
回复
举报
回复
添加资源
添加资源将有机会获得更多曝光,你也可以直接关联已上传资源 去关联
    相关推荐