高并发下的sql优化

假设有数据库表中有列（列名是energy），作为一个计数器，这个计数器有一个上限。用户发来一个请求，参数是一个随机值，计数器会加上这个随机值，直到到达上限为止（不能超过上限）。我们需要记录处理用户请求所增加的值，这个值包括正数和零。一开始的sql如下：


update table
set energy = least(energy + #{incr, jdbcType=DECIMAL}, #{limit, jdbcType=DECIMAL})
, UPDATE_TIME = sysdate
where energy <#{limit, jdbcType=DECIMAL} AND id = #{id, jdbcType=VARCHAR}



这条sql中，id表示某条记录的id，limit表示上限，incr表示增加的值（随机值，每次请求的数值不同）。由于sql使用了least方法，可以确保energy不会超过上限（超出的数值会截断）。

这条sql看起来没问题，测试环境中也工作良好。后来做压力测试，10个线程还工作良好，但线程数量增加到20时，发现了一个问题：用户的请求参数值不一定是增加的值。如果energy当前是9.5，上限是10。现在有A和B两个线程，A的incr是0.5，B的incr是0.2，B线程的请求先操作，energy变成9.7，然后再处理A线程的请求，也会成功，但A线程误以为0.5全部增加，其实只增加了0.3。

因为考虑到Mybatis的update不能返回实际增加值，所以我当时认为改动比较大，还想到用redis控制。但我试图找到一个简单方法，发现错误只是出现在即将封顶的情况，在高并发情况下其数量只有1-3条的。那么可以使用乐观锁，更新失败不要重试，相当于incr是0，按这种思路sql可以改成以下：


update table
set energy = energy + #{incr, jdbcType=DECIMAL} , UPDATE_TIME = sysdate
where energy <#{limit, jdbcType=DECIMAL} AND id = #{id, jdbcType=VARCHAR}
AND energy + #{incr, jdbcType=DECIMAL} <= #{limit, jdbcType=DECIMAL}



只是增加了一个where条件。在上面的问题中，A线程where条件是false，所以不会更新，会认为这次请求的incr是0