MySQL 技巧:数据库实现 乐观锁 (版本控制/条件过滤)| 悲观锁(for update)
使用 MySQL 5.7 做测试,数据库引擎为 InnoDB,数据库隔离级别为可重复读(REPEATABLE-READ),读读共享,读写互斥。在这个隔离级别下,在多事务并发的情况下,还是会出现数据更新的冲突问题。先分析一下更新冲突的问题是如何产生的。假设我们有一张销量表 goods_sale ,表结构如下:字段数据类型说明goods_sale_idvarc...
目录
实现方式1:程序实现-----加锁用synchronlized
总结如下:加for update可以无锁,可以行锁,也可以表锁
乐观锁(版本控制/条件过滤)
使用 MySQL 5.7 做测试,数据库引擎为 InnoDB,
数据库隔离级别为可重复读(REPEATABLE-READ),
读读共享,读写互斥。[深刻理解这句:两个不同事物可以同时读取一条记录,但是不能同时写一条事物(也就是写是互斥的)]
在这个隔离级别下,在多事务并发的情况下,还是会出现数据更新的冲突问题。
场景再现:
销量表 goods_sale
,表结构如下:
字段 | 数据类型 | 说明 |
---|---|---|
goods_sale_id | varchar(32) | 销量 id |
goods_id | varchar(32) | 商品 id |
count | int(11) | 销量 |
比如在某一时刻事务 A 和事务 B,在同时操作表 goods_sale
的 goods_id = 20191017344713049535651840506935 的数据,当前销量为 100。
goods_sale_id | goods_id | count |
---|---|---|
20191017344778600995856384326638 | 20191017344713049535651840506935 | 100 |
两个事务的内容一样,都是先读取的数据,count +100 后更新。
我们这里只讨论乐观锁的实现,为了便于描述,假设项目已经集成 Spring 框架,使用 MyBatis 做 ORM,Service 类的所有方法都使用了事务,事务传播级别使用 PROPAGATION_REQUIRED
,在事务失败会自动回滚。
Service 为 GoodsSaleService
,更新数量的方法为 addCount()
。
@Service
@Transaction
pubic class GoodsSaleService {
@Autowire
private GoodsSaleDao dao;
public void addCount(String goodsId, Integer count) {
GoodsSale goodsSale = dao.selectByGoodsId(goodsId);
if (goodsSale == null) {
throw new Execption("数据不存在");
}
int count = goodsSale.getCount() + count;
goodsSale.setCount(count);
int count = dao.updateCount(goodsSale);
if (count == 0) {
throw new Exception("添加数量失败");
}
}
}
使用的 Dao 为 GoodsSaleDao
,有两个方法
public interface GoodsSaleDao {
GoodsSale selectByGoodsId(@Param("goodsId") String goodsId);
int updateCount(@Param("record") GoodsSale goodsSale);
}
mapper 文件对应的 sql 操作为:
<!-- 查询 -->
<select id="selectByGoodsId" resultMap="BaseResultMap">
select
<include refid="Base_Column_List"/>
from goods_sale
where goods_id = #{goodsId}
</select>
<!-- 更新 -->
<update id="updateCount">
update
goods_sale
set count = #{record.count},
where goods_sale_id = #{record.goodsSaleId}
</update>
好了,假设现在有两个线程同时调用了 GoodsSaleService#addCount
,操作同一行数据,会有什么问题?
假设这两个线程对应的事务分为事务 A 和事务 B。用一张流程图来说明问题:
MySQL-多事务更新冲突
更新冲突了!两次 addCount(100)
,结果应该是 300,结果还是 200。
该如何处理这个问题,有一个简单粗暴的方法,既然这里多线程访问会有线程安全问题,那就上锁,方法加入 synchronized
进行互斥。
实现方式1:程序实现-----加锁用synchronlized
public synchronized void addCount(String goodsId, Integer count) {
...
}
这个方案确实也可以解决问题,但是这种简单互斥的做法,锁的粒度太高,事务排队执行,并发度低,性能低。但如果是分布式应用,还得考虑应用分布式锁,性能就更低了。
实现方式2:数据库实现
乐观锁方式1:版本控制+自旋
考虑到这些更新冲突发生的概率其实并不高。这里讨论另一种解决方案,使用数据库乐观锁来实现。
原理就是基于 CAS
,比较并交换数据,如果发现被更新过了,直接更新失败。
然后加入自旋(自循环)接着更新,直到成功。乐观就在于我们相信冲突发生概率低,
如果发生了,就用一种廉价的机制迅速发现,快速失败。
我们来讨论如何实现它。数据库表 GoodsSale
新增一行 data_version
来记录数据更新的版本号。新的表结构如下:
字段 | 数据类型 | 说明 |
---|---|---|
goods_sale_id | varchar(32) | 销量 id |
goods_id | varchar(32) | 商品 id |
count | int(11) | 销量 |
data_version | int(11) | 版本号 |
GoodsSaleDao#updateCount
对应的 mapper 的 SQL 语句进行调整,数据更新的时候同时进行 data_version = data_version + 1
,执行这个 sql 时候已经对数据上行锁了,所以这个 data_version 加 1 的操作为原子操作。
<!-- 乐观锁更新 -->
<update id="updateCount">
update
goods_sale
set count = #{record.count}, data_version = data_version + 1
where goods_sale_id = #{record.goodsSaleId}
and data_version = #{record.dataVersion}
</update>
Dao 调整之后,事务 A 和事务 B 的变化如下:
MySQL-多事务更新冲突-加锁
有了发现冲突快速失败的方案,要想让更新成功,可以在 GoodsSaleService
中加入自旋,重新开始事务业务逻辑的执行,直到没有发生冲突,更新成功。
自旋的实现有两种,
一种是使用循环,while(true) 记得return;
一种是使用递归。
循环实现:
public void addCount(String goodsId, Integer count) {
while(true) {
GoodsSale goodsSale = dao.selectByGoodsId(goodsId);
if (goodsSale == null) {
throw new Execption("数据不存在");
}
int count = goodsSale.getCount() + count;
goodsSale.setCount(count);
int count = dao.updateCount(goodsSale);
if (count > 0) {
return;
}
}
}
递归实现:
public void addCount(String goodsId, Integer count) {
GoodsSale goodsSale = dao.selectByGoodsId(goodsId);
if (goodsSale == null) {
throw new Execption("数据不存在");
}
int count = goodsSale.getCount() + count;
goodsSale.setCount(count);
int count = dao.updateCount(goodsSale);
if (count == 0) {
addCount(goodsId, count)
}
}
通过乐观锁+自旋的方式,解决数据更新的线程安全问题,而且锁粒度比互斥锁低,并发性能好。
乐观锁局限:版本号的方法并不是适用于所有的乐观锁场景
乐观锁方式2:条件过滤
举个例子,当电商抢购活动时,大量并发进入,如果仅仅使用版本号或者时间戳,就会出现大量的用户查询出库存存在,但是却在扣减库存时失败了,而这个时候库存是确实存在的。想象一下,版本号每次只会有一个用户扣减成功,不可避免的人为造成失败。这种时候就需要我们的第二种场景的乐观锁方法
表结构修改如下:
mysql> select * from t_goods;
+----+--------+------+---------+
| id | status | name | num |
+----+--------+------+---------+
| 1 | 1 | 道具 | 10 |
| 2 | 2 | 装备 | 10 |
+----+--------+------+---------+
rows in set
status表示产品状态:1、在售。2、暂停出售。 num表示产品库存
更新库存操作如下:
UPDATE t_goods
SET num = num - #{buyNum}
WHERE
id = #{id}
AND num - #{buyNum} >= 0
AND STATUS = 1
说明:num-#{buyNum}>=0 ,这个情景适合不用版本号,只更新是做数据安全校验,适合库存模型,扣份额和回滚份额,性能更高。这种模式也是目前我用来锁产品库存的方法,十分方便实用
注意:乐观锁的更新操作,最好用主键或者唯一索引来更新,这样是行锁,否则更新时会锁表
作者:DestinLee
链接:https://www.jianshu.com/p/5a081ff5de58
来源:简书
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。
悲观锁(xx for update)
使用场景举例:以MySQL InnoDB为例
商品goods表中有一个字段status,status为1代表商品未被下单,status为2代表商品已经被下单,那么我们对某个商品下单时必须确保该商品status为1。假设商品的id为1。
如果不采用锁,那么操作方法如下:
//1.查询出商品信息
select status from t_goods where id=1;
//2.根据商品信息生成订单
insert into t_orders (id,goods_id) values (null,1);
//3.修改商品status为2
update t_goods set status=2 where id = 1;
上面这种场景在高并发访问的情况下很可能会出现问题。
前面已经提到,只有当goods status为1时才能对该商品下单,上面第一步操作中,查询出来的商品status为1。但是当我们执行第三步Update操作的时候,有可能出现其他人先一步对商品下单把goods status修改为2了,但是我们并不知道数据已经被修改了,这样就可能造成同一个商品被下单2次,使得数据不一致。所以说这种方式是不安全的。
注:要使用悲观锁,我们必须关闭mysql数据库的自动提交属性,
因为MySQL默认使用autocommit模式,也就是说,当你执行一个更新操作后,MySQL会立刻将结果进行提交。
步骤1:设置MySQL为非autocommit模式:
set autocommit=0;
设置完autocommit后,我们就可以执行我们的正常业务了。具体如下:
步骤2:执行脚本
//0.开始事务
begin;/begin work;/start transaction; (三者选一就可以)
//1.查询出商品信息
select status from t_goods where id=1 for update;
//2.根据商品信息生成订单
insert into t_orders (id,goods_id) values (null,1);
//3.修改商品status为2
update t_goods set status=2 where id = 1;
//4.提交事务
commit;/commit work;
注:上面的begin/commit为事务的开始和结束,因为在前一步我们关闭了mysql的autocommit,所以需要手动控制事务的提交,在这里就不细表了。
for update的方式,这样就通过数据库实现了悲观锁。
此时在t_goods表中,id为1的 那条数据就被我们锁定了,其它的事务必须等本次事务提交之后才能执行。
这样我们可以保证当前的数据不会被其它事务修改。
总结如下:加for update可以无锁,可以行锁,也可以表锁
下图的条件明确指的是:如id=1 而不是id>0
注:需要注意的是,在事务中,
只有SELECT ... (FOR UPDATE 或LOCK IN SHARE MODE )同一笔数据时会等待其它事务结束后才执行,
一般SELECT ... 则不受此影响。
举例:
看清楚下面的不同会话就是不同的窗口(程序里不同的线程)
会话1执行:select status from t_goods where id=1 for update;(若查无此数据无lock,这个明确指定主键如果有数据则为行锁)
会话2执行:select status from t_goods where id=1 for update; 会话2会等待会话1提交,此时会话2处于阻塞的状态,如果会话1长时间未提交,则会报错:ERROR 1205 : Lock wait timeout exceeded; try restarting transaction
会话3执行:select * from t_goods where name='道具' for update; (无主键,则是表锁)
会话4执行:select * from t_goods where id>0 for update; (主键不明确,table lock)
会话5执行:select status from t_goods where id=1;(没有加for update 条件)则能正常查询出数据,不会受第一个事务的影响。
补充:MySQL select…for update的Row Lock(行锁)与Table Lock(表锁)
上面我们提到,使用select…for update会把数据给锁住,不过我们需要注意一些锁的级别,
MySQL InnoDB默认(行锁)Row-Level Lock,
所以只有「明确」地指定主键,MySQL 才会执行Row lock (只锁住被选取的数据) ,
【这里的明确指的是条件明确比如id=1 而不是id >0这种,而且必须要求是主键】
否则MySQL 将会执行Table Lock (将整个数据表单给锁住)
除了主键外,使用索引也会影响数据库的锁定级别
给status字段创建一个索引
select * from t_goods;
+----+--------+------+
| id | status | name |
+----+--------+------+
| 1 | 1 | 道具 |
| 2 | 2 | 装备 |
+----+--------+------+
2 rows in set
会话1:select * from t_goods where status=1 for update;
+----+--------+------+
| id | status | name |
+----+--------+------+
| 1 | 1 | 道具 |
+----+--------+------+
1 row in set
会话1:查询status=3的数据,返回空数据(明确指定索引,若查无此数据,无lock)
select * from t_goods where status=3 for update;
Empty set
会话2:查询status=1的数据时阻塞,超时后返回为空,说明数据被console1锁定了
select * from t_goods where status=1 for update;
Query OK, -1 rows affected
会话2:查询status=2的数据,能正常查询,说明console1只锁住了行,未锁表
select * from t_goods where status=2 for update;
+----+--------+------+
| id | status | name |
+----+--------+------+
| 2 | 2 | 装备 |
+----+--------+------+
1 row in set
会话2:select * from t_goods where status=3 for update;
Empty set
更多推荐
所有评论(0)