MySQL READ+CHECK+UPDATE并发问题
背景
在工作常常遇到先查询数据，在数据满足某种条件情况下再更新数据。比如，在需要大批量采购某商品时，通常采购方与供应商签订固定金额的协议，在需要采购时，先检查合同剩余金额是否足够，然后再下单并更新合同剩余金额。但查询到更新之间存在时间差，如果在查询到更新之间有其他用户并发更新协议信息，则会导致判断不准确。
解决方案
这个问题最简单解决方案是，在更新合同剩余金额时再次判断。
创建表结构如下：
CREATE TABLE `protocol` (
`id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`total_amount` DECIMAL(20,6) DEFAULT NULL COMMENT '总金额',
`executed_amount` DECIMAL(20,6) DEFAULT NULL COMMENT '已执行金额',
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8 COMMENT='协议'
对应实体类
public class Protocol {
private Long id;
private BigDecimal totalAmount; // 总金额
private BigDecimal executedAmount; // 已执行金额
private BigDecimal thisPayAmount; // 本次扣除金额
public BigDecimal getRemainingAmount() { // 计算剩余金额
if (Objects.nonNull(totalAmount)) {
if (Objects.isNull(executedAmount)) {
return totalAmount;
} else {
return totalAmount.subtract(executedAmount);
}
}
return null;
}
Mybatis Xml
<update id="updateOneById">
update protocol
set
executed_amount = executed_amount + #{thisPayAmount,jdbcType=DECIMAL}
where id = #{id,jdbcType=BIGINT}
and executed_amount = #{executedAmount,jdbcType=DECIMAL}
</update>
<sql id="columns">
${protocol}.id, ${protocol}.total_amount, ${protocol}.executed_amount
</sql>
<select id="selectOneById" resultMap="BaseResultMap">
select
<include refid="columns"><property name="protocol" value="p"/></include>
from protocol p
where p.id = #{id,jdbcType=BIGINT}
</select>
表中存协议总金额和已执行金额，它们之差为协议剩余金额。表中数据如下：
下单程序如下：
public boolean checkAndUpdate(Protocol protocal) {
Protocol selectedProtocal = protocolRepository.selectOneById(protocal);
// 将查出的协议已执行金额作为参数传到更新SQL中，在更新时判断是否一致，一致才更新
protocal.setExecutedAmount(selectedProtocal.getExecutedAmount());
BigDecimal remainingAmount = selectedProtocal.getRemainingAmount();
if(remainingAmount.compareTo(protocal.getThisPayAmount()) >= 0) { // 剩余金额足够
int updated = protocolRepository.updateOneById(protocal); // 更新协议剩余金额
if(updated == 1) {
return Boolean.TRUE;
}
}
return Boolean.FALSE;
}
需要注意的地方是，mybatis 的 update 操作的返回值是 matched 的记录数，并不是受影响的记录数。如果需要返回受影响的记录数，需要更改数据库链接配置。
此方案基于CAS（Compare and Swap）思想，CAS前提是整个CAS语句需要具有原子性，即updateOneById 这个SQL语句不存在线程问题。
关于这个问题，我在网上找了一下资料，把几篇有价值的资料贴出来。
- The Processing Method of MySQL Concurrent Update Data
这篇博文提出的问题如下，和我们遇到的问题类似。
When the SQL statement is as follows, will it be locked?
UPDATE table1 SET num = num + 1 WHERE id=1;
作者解答
The answer is No.
In fact, MySQL supports InnoDB and does automatically add exclusive locks to operations such as UPDATE/DELETE. justNot just the UPDATE keyword will lock the whole processFor the above MySQL statement, it is not just an UPDATE statement, but should be similar to two SQL statements (pseudocode):
a = SELECT * FROM table1 WHERE id=1;
UPDATE table1 SET num = a.num + 1 WHERE id=1;
意思就是说，这种写法其实可以拆分成查询原值和基于原值update两个步骤，所以不具备原子性。
- stackoverflow 上一个类似问题，Is incrementing a field in MySQL atomic?。
UPDATE votes SET num = num + 1;
Will this cause problems if multiple connections are doing the same query, or will MySQL take care of it and lock the table or something to make sure that there are no conflicts?
被采纳的回答是基于MyISAM 引擎。
MyISAM tables use table level locking. This means that the whole table will be locked during the execution of your update query. So the answer for your simplified use case is: yes, this is thread safe. But this may not be the case if you use another storage engine or your update includes multiple tables.
大概意思是MyISAM 引擎会锁表，所以整个更新语句是线程安全的，但是基于其他引擎或者更新时包含多表，则不确定。
另外一个赞成比较多的回答和第一篇质料差不多，并给出了使用事务的解决方案。
The write is atomic but an increment also requires a read. So the question is: Are you sure the read is safe, in other words, are you sure another thread doing the increment will not end up with the same value to be incremented? I have doubts. The 100% correct way of doing this would be.
-- begin transaction here
select counter from myCounters where counter_id = 1 FOR UPDATE;
-- now the row is locked and nobody can read or modify its values
update myCounters set counter = ? where id = 1;
-- set ? to counter + 1 programmatically
commit; -- and unlock...
- 另外，MySQL 手册也举了一个类似的例子，Locking Read Examples。
For another example, consider an integer counter field in a table CHILD_CODES, used to assign a unique identifier to each child added to table CHILD. Do not use either consistent read or a shared mode read to read the present value of the counter, because two users of the database could see the same value for the counter, and a duplicate-key error occurs if two transactions attempt to add rows with the same identifier to the CHILD table.
Here, FOR SHARE is not a good solution because if two users read the counter at the same time, at least one of them ends up in deadlock when it attempts to update the counter.
To implement reading and incrementing the counter, first perform a locking read of the counter using FOR UPDATE, and then increment the counter. For example:
SELECT counter_field FROM child_codes FOR UPDATE;
UPDATE child_codes SET counter_field = counter_field + 1;
大概意思是，在CHILD_CODES表中用child_codes 字段标识一个唯一标识符。作者推荐的做法是使用SELECT … FOR UPDATE读时加排他锁。而且着重提了不要使用一致性读（事务利用MVCC进行的读取操作）和共享锁（Shared Locks）。MVCC这种多版本并发控制，每次看到的不是最新的数据，而是以前的一个快照。
而共享锁可能产生死锁。如果两个线程同时获取到S锁（共享锁），然后进行更新，此时S锁需要上升到X锁（共享锁），但获取X锁需要等待另外一个线程放掉S锁或者X锁，这样大家都等待对方放掉S锁或者X锁，便会造成死锁。
其实对于唯一标识符，这篇文章给出了最佳实践，如下：
UPDATE child_codes SET counter_field = LAST_INSERT_ID(counter_field + 1);
SELECT LAST_INSERT_ID();
- 在stackoverflow 还有类似问题，这里再列举一个，MySQL concurrent updates。
I have small POS system with server side uses PHP and MySql (InnoDB). When “Sale” is being completed the following query is executed for update stock quantity:
"UPDATE products SET qty=qty-:qty, ustatus=1 WHERE pid=:pid";
Does this statement is “Concurrency Update Secure”, meaning do i need any transactions or locking tables.
被采纳的回答：
If you use innodb, then all sql statements are executed in a transaction, you do not need to explicitly specify that.
Concurrency control is done via locks, not transactions. Transactions may only determine the lifespan of locks.
The update statement places an exclusive lock on the records that it wants to modify, meaning no other statements can modify (sometimes cannot even read) the locked record until the exclusive lock is released. So, your statement is safe from concurrency point of view.
大概意思是，所有SQL语句默认会放到一个事务中执行，所以是并发安全的。
总结
为了避免此种情况产生并发问题，可以
- 在读时加X锁。
- 将READ+CHECK+UPDATE放到同一事务中，事务隔离级别为了安全开到最高串行，但MySQL也会使用锁。
这两种方式都比较影响效率。
扫一扫
409
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
