mysql select for update + 事务处理数据一致性

时间：2023-04-23 16:35:56浏览次数：49

标签：UPDATE 事务处理 update test num mysql leyangjun 主键 SELECT

如果SELECT 后面若要UPDATE 同一个表数据的相关操作，最好使用SELECT ... FOR UPDATE。

一：举例说明

　假设商品表单test_leyangjun 内有一个存放商品库存的num字段，一个id主键，在生成订单前须先确定num>0 ，然后才把数量更新。代码如下(比如现在的库存：num=3对应的id=3，现在生成一个订单需要对库存做扣减少):

SELECT num FROM test_leyangjun WHERE id=3; UPDATE test_leyangjun SET num = num-1 WHERE id=3;

确实以上sql看上去没啥问题，逻辑也很明确就是做一个库存更新操作，但是在高并发请求的时候上面的sql就会暴露出对应问题，啥问题呢？就是在高并发请求的时候，如果我们需要在num>0 的情况下才能扣库存，假设程序在第一行SELECT 读到的num=3 ，但当MySQL 正准备要UPDATE 的时候，可能已经有人把库存扣成 “2|1|0 ”了，但是程序是不知道的咔嚓一下就执行UPDATE。因此必须透过的事务机制来确保读取及提交的数据都是正确的。

1: 每次操作开启事务

2：使用MySql -> SELECT ... FOR UPDATE机制

SELECT num FROM test_leyangjun WHERE id=3 FOR UPDATE; UPDATE test_leyangjun SET num = num-1 WHERE id=3;

二：注意

1：以上加上锁和for update其是在高并发的时候其实还是有低风险的数据不一致情况，在使用事务更新数据的时候加上历史数据值作为查询条件，改造后应该是这样，已避免风险存在

SELECT num FROM test_leyangjun WHERE id=3 FOR UPDATE; UPDATE test_leyangjun SET num = num-1 AND num = 3 WHERE id=3;//双保险

2：当然了，有些公司DBA是不允许是开发使用FOR UPDATE操作的怕操作不当把表搞挂掉，咋办? ----> 其实直接使用(事务+前提查询条件)也能有效避免,如果有人此时将库存改成其他了，当前这个操作update是执行不成功的回滚，进而保证了库存不超卖

SELECT num FROM test_leyangjun WHERE id=3; UPDATE test_leyangjun SET num = num-1 AND num = 3 WHERE id=3;

注意：select for update 主键索引和普通索引都能用，区别在于主键索引命中后是行锁，普通索引锁的范围更大甚至可能表锁，最终的效果是一样，最好在主键上使用。

二：FOR UPDATE锁机制介绍

mysql for update是分为Row Lock 与Table Lock - - -> MySQL SELECT ... FOR UPDATE 的Row Lock 与Table Lock，由于InnoDB 预设是Row-Level Lock，所以需指定主键MySQL 才会执行Row lock (锁定被读取的数据) ，否则MySQL 将会执行Table Lock (锁表)。

eg:假设有个表单test_leyangjun ，里面有主键id 跟num，name（普通索引）字段，id 是主键。
1: 明确指定主键，并且有此数据，产生行锁

-----> SELECT * FROM test_leyangjun WHERE id='3' FOR UPDATE;

2: 明确指定主键，若查无此数据，不产生锁

-----> SELECT * FROM test_leyangjun WHERE id='-1' FOR UPDATE;

3: 非主键含索引（name）进行查询，并且查询到数据，name字段产生行锁（没有查询到数据，不产生锁）