表结构
CREATE TABLE `price_version` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`total` decimal(12,2) DEFAULT '0.00' COMMENT '总值',
`version` int(11) DEFAULT '0' COMMENT '并发版本控制',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1268 DEFAULT CHARSET=utf8;
SQL语句：
update price_version set version=version+1 ,total=total-1 where id=#{id} AND version =#{version} AND total >0
代码：
@Test
public void testVersion()throws Exception{
final CountDownLatch countDownLatch=new CountDownLatch(3);
for (int i = 0; i < 10; i++) {
Thread thread=new Thread(new Runnable() {
@Override
public void run() {
// countDownLatch.countDown();
PriceVersion priceVersion = priceVersionMapper.selectById(1);
int count= priceVersionService.updateByVersion(priceVersion);
if (count == 0){
logger.info(Thread.currentThread().getName()+"--更新失败");
}else {
logger.info(Thread.currentThread().getName()+"--更新成功");
}
}
});
thread.start();
}
// countDownLatch.await();
Thread.sleep(2000);
System.out.println("所有测试任务全部完成");
}
}
结果：
[2021-06-26 20:36:51] [Thread-9] INFO com.xulei.test.TestPriceVersion - Thread-9--更新失败
[2021-06-26 20:36:51] [Thread-8] INFO com.xulei.test.TestPriceVersion - Thread-8--更新失败
[2021-06-26 20:36:51] [Thread-12] DEBUG c.x.l.d.P.updateLock - <== Updates: 0
[2021-06-26 20:36:51] [Thread-12] INFO com.xulei.test.TestPriceVersion - Thread-12--更新失败
[2021-06-26 20:36:51] [Thread-7] DEBUG c.x.l.d.P.updateLock - <== Updates: 0
[2021-06-26 20:36:51] [Thread-7] INFO com.xulei.test.TestPriceVersion - Thread-7--更新失败
[2021-06-26 20:36:51] [Thread-10] INFO com.xulei.test.TestPriceVersion - Thread-10--更新成功
[2021-06-26 20:36:51] [Thread-5] DEBUG c.x.l.d.P.updateLock - <== Updates: 0
[2021-06-26 20:36:51] [Thread-4] DEBUG c.x.l.d.P.updateLock - <== Updates: 0
[2021-06-26 20:36:51] [Thread-4] INFO com.xulei.test.TestPriceVersion - Thread-4--更新失败
[2021-06-26 20:36:51] [Thread-5] INFO com.xulei.test.TestPriceVersion - Thread-5--更新失败
[2021-06-26 20:36:51] [Thread-11] DEBUG c.x.l.d.P.updateLock - <== Updates: 0
[2021-06-26 20:36:51] [Thread-11] INFO com.xulei.test.TestPriceVersion - Thread-11--更新失败
[2021-06-26 20:36:51] [Thread-3] DEBUG c.x.l.d.P.updateLock - <== Updates: 0
[2021-06-26 20:36:51] [Thread-6] DEBUG c.x.l.d.P.updateLock - <== Updates: 0
[2021-06-26 20:36:51] [Thread-6] INFO com.xulei.test.TestPriceVersion - Thread-6--更新失败
[2021-06-26 20:36:51] [Thread-3] INFO com.xulei.test.TestPriceVersion - Thread-3--更新失败
分析：
1：使用数据库本身，update作为锁机制存在问题。
C线程查出来是10个
A线程扣减1个，剩9个
B线程更新了库存，变回10个
C更新的时候发现还是10个，则更新成功，
虽然最后C是更新成功了。但是处理业务期间，可能不知道变动情况。所以避免这个问题，要求不管谁修改了库存，一定要加个version递增版本号。
数据读取后，更新前数据被别人篡改过，version只能做递增
场景：商品秒杀、优惠券方法，需要记录库存操作前后的业务
