Preventing Inventory Overselling in High‑Concurrency Scenarios: Java, Redis Distributed Lock, MySQL Row Lock, Optimistic Lock, and SQL Solutions

The article starts with a simple Java method public String buy(Long goodsId, Integer goodsNum) { /* query inventory, check zero, check stock, update inventory */ } that works correctly under sequential requests but fails in flash‑sale (秒杀) scenarios where many requests read the same stock value simultaneously, leading to overselling.

It then describes the high‑concurrency characteristics of flash‑sale systems: massive simultaneous requests, need for fast response, and often a distributed architecture.

Solution 1 – Redis Distributed Lock (Redisson) introduces Redisson as a Java client for Redis providing a distributed lock. The configuration class @Configuration public class RedissonConfig { @Bean(destroyMethod = "shutdown") public RedissonClient redissonClient() { Config config = new Config(); config.useSingleServer().setAddress("redis://127.0.0.1:6379").setPassword("123456"); return Redisson.create(config); } } is shown, followed by the locked purchase method public String buyRedisLock(Long goodsId, Integer goodsNum) { RLock lock = redissonClient.getLock("goods_buy"); try { lock.lock(); // same logic as original method } catch (Exception e) { log.error("秒杀失败"); } finally { lock.unlock(); } return "购买失败"; } . This serializes the purchase operations, eliminating oversell at the cost of increased latency.

Solution 2 – MySQL Row Lock uses InnoDB row‑level locking with SELECT ... FOR UPDATE . The original query Goods goods = goodsMapper.selectById(goodsId); is replaced by a SQL statement SELECT * FROM t_goods WHERE id = #{goodsId} FOR UPDATE , ensuring that once a row is read, other transactions must wait until the lock is released.

Solution 3 – Optimistic Lock adds a version column to the t_goods table. The update SQL becomes UPDATE t_goods SET goods_inventory = goods_inventory - #{goodsNum}, version = version + 1 WHERE id = #{goodsId} AND version = #{version} . The Java method public String buyVersion(Long goodsId, Integer goodsNum) { Goods goods = goodsMapper.selectById(goodsId); if (goods.getGoodsInventory() <= 0) return "商品已经卖光了！"; if (goodsMapper.updateInventoryAndVersion(goodsId, goodsNum, goods.getVersion()) > 0) return "购买成功！"; return "库存不足！"; } retries when the version check fails.

Solution 4 – Conditional SQL Update & Unsigned Field moves the inventory decrement entirely into SQL: UPDATE t_goods SET goods_inventory = goods_inventory - #{goodsNum} WHERE id = #{goodsId} AND (goods_inventory - #{goodsNum}) >= 0 . This guarantees that the stock never becomes negative, even under concurrent updates. An alternative is to define the goods_inventory column as UNSIGNED to prevent negative values.

The article concludes that there is no single best solution; the choice depends on business requirements, and combinations of the above methods can be applied to achieve both correctness and performance.