回复
MySQL数据库锁应该这样用
netcat20000
发布于 2022-4-21 23:26
浏览
0收藏
本文针对我们平时使用场景最多的MySQL数据库在RR隔离级别下容易产生幻读的问题，来进行分析并分享解决方案。
PartⅠ 问题回顾
幻读的定义：幻读是指某个事务读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前事务再次读取该范围内的记录时就会产生幻行。
举一个例子，user表中id是主键索引，T1是主事务：
T2是干扰事务：
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表这条记录的共享锁，会导致谁也更新不了剩余数量这个值的情况。所以共享锁和排它锁都有各自的作用，不能互相替代。
本文作者：裘维清，腾讯高级运营开发工程师，主要负责腾讯游戏的运营开发工作。
文章转自公众号：腾讯云数据库
分类
标签
赞
收藏
回复
相关推荐
