最近在做一个wms仓储管理系统，需求是入库时候，如果仓库有这个商品就更改库存，没有这个商品的话就需要加上这个商品的的库存记录。
乐观锁
看了之前项目写的对库存的操作是先查询库存表是否存在这个商品，如果不存在就新增，如果存在的话就修改，此时修改这里加上乐观锁，这里只对修改库存时候加了锁，所以如果这个商品不存在，可能会出现重复插入的问题。 附上代码片段：
final LambdaQueryWrapper<MaterialInventory> materialQuery = Wrappers.lambdaQuery(MaterialInventory.class);
materialQuery.eq(MaterialInventory::getTenantId, currentUser.getTenantId());
materialQuery.eq(MaterialInventory::getWarehouseId, materialInventoryUpdateDTO.getWarehouseId());
materialQuery.eq(MaterialInventory::getSkuId, materialInventoryUpdateDTO.getSkuId());
materialQuery.eq(MaterialInventory::getProperties, materialInventoryUpdateDTO.getProperties());
final MaterialInventory materialInventory = this.baseMapper.selectOne(materialQuery);
if (materialInventory == null) {
// 新增库存
final MaterialInventoryAddDTO materialInventoryAddDTO = returnMaterialInventory(materialInventoryUpdateDTO);
materialInventoryServiceTx.doAddMaterialInventory(materialInventoryAddDTO);
} else {
// 修改库存
materialInventoryServiceTx.doUpdateMaterialInventory(materialInventory.getId(), currentUser.getTenantId(), materialInventoryUpdateDTO);
}@Transactional(rollbackFor = Exception.class)
public void doUpdateMaterialInventory(long inventoryId, long tenantId, MaterialInventoryUpdateDTO updateDTO) {
int i = 0;
boolean isSuccess = true;
// 进入乐观锁控制循环，执行5次，
do {
i++;
if (!isSuccess) {
log.info("乐观锁拦截本次更新，强迫重新执行");
if (i > CommonConstant.VERSION_LOCKER_EXEC_TIMES) {
throw BusinessException.createBusinessException(CommonErrorCode.E_220001, "更新库存时出现并发异常，请稍后重试");
}
}
final LambdaQueryWrapper<MaterialInventory> inventoryQuery = Wrappers.lambdaQuery(MaterialInventory.class);
inventoryQuery.eq(MaterialInventory::getTenantId, tenantId);
inventoryQuery.eq(MaterialInventory::getId, inventoryId);
inventoryQuery.eq(MaterialInventory::getWarehouseId, updateDTO.getWarehouseId());
inventoryQuery.eq(MaterialInventory::getProperties, updateDTO.getProperties());
final MaterialInventory materialInventory = materialInventoryService.getOne(inventoryQuery);
materialInventory.setQuantity(materialInventory.getQuantity().add(updateDTO.getQuantity()));
materialInventory.setQuantityAssistant(materialInventory.getQuantityAssistant().add(updateDTO.getQuantityAssistant()));
materialInventory.setWaitingInQuantity(materialInventory.getWaitingInQuantity().add(updateDTO.getWaitingInQuantity()));
materialInventory.setWaitingInQuantityAssistant(materialInventory.getWaitingInQuantityAssistant().add(updateDTO.getWaitingInQuantityAssistant()));
materialInventory.setWaitingOutQuantity(materialInventory.getWaitingOutQuantity().add(updateDTO.getWaitingOutQuantity()));
materialInventory.setWaitingOutQuantityAssistant(materialInventory.getWaitingOutQuantityAssistant().add(updateDTO.getWaitingOutQuantityAssistant()));
materialInventory.setUpdateBy(userService.getCurrentUser().getId());
materialInventory.setTenantId(tenantId);
if (updateDTO.getQuantity().compareTo(BigDecimal.ZERO) > 0 && updateDTO.getPrice().compareTo(materialInventory.getPrice()) != 0) {
BigDecimal price;
BigDecimal amount;
// 计算平均价格
if (materialInventory.getQuantity().add(updateDTO.getQuantity()).compareTo(BigDecimal.ZERO) != 0) {
amount = materialInventory.getQuantity().multiply(materialInventory.getPrice()).add(
updateDTO.getQuantity().multiply(updateDTO.getPrice()));
price = amount.divide(materialInventory.getQuantity().add(updateDTO.getQuantity()), 4, RoundingMode.HALF_UP);
materialInventory.setPrice(price);
}
}
isSuccess = materialInventoryService.updateById(materialInventory);
} while (!isSuccess);
}悲观锁
现在项目我使用的是mysql的悲观锁，select……for update，因为mysql默认隔离级别是可重复读，针对索引查询时候可能会出现间隙锁，造成死锁等问题，所以此处先把mysql隔离级别改成了读已提交，然后把仓库id+商品id加了个联合唯一索引
- 1.先查询表里是否存在仓库id+商品id的记录（使用 select……for update）
- 2.存在的话更新即可
- 3.不存在的话插入，放到try catch里然后在catch捕获一个唯一键冲突异常，因为加了联合唯一索引，所以并发可能会重复插入报错。并在catch里再次执行步骤1，然后更改库存。 附上代码片段：（因为我的库存表还有一个子库存表，所以finally里执行了一个向子库存表插入的逻辑，只要保证了主表的唯一性，子表也肯定会唯一，所以子表无需加锁）
// 基于RC隔离级别
public void updateInventory(InventoryDTO inventoryDTO) {
Inventory inventory = this.baseMapper.selectInventoryLock(inventoryDTO.getWarehouseId(), inventoryDTO.getSkuId());
Inventory newInventory = null;
Inventory newInventoryEntry = null;
if (inventory == null) {
// 不存在插入
Inventory inventoryEntry = new Inventory();
BeanUtils.copyProperties(inventoryDTO, inventoryEntry);
try {
this.baseMapper.insert(inventoryEntry);
newInventoryEntry = inventoryEntry;
} catch (DuplicateKeyException e) {
// todo日志
// 唯一键冲突
Inventory inventorySecond = this.baseMapper.selectInventoryLock(inventoryDTO.getWarehouseId(), inventoryDTO.getSkuId());
inventorySecond.setQuantity(inventorySecond.getQuantity().add(inventoryDTO.getQuantity()));
inventorySecond.setPackageQty(inventorySecond.getQuantity().divide(new BigDecimal(inventorySecond.getItemQty()), 2, RoundingMode.HALF_UP));
this.updateById(inventorySecond);
newInventory = inventorySecond;
} finally {
if (newInventory == null) {
// 代表插入成功
if (newInventoryEntry == null) {
throw new JeecgBootException("插入inventory表失败");
}
if (inventoryDTO.getUseUniCode().equals(CommonYesNo.YES)) {
List<InventoryUniCodeDTO> uniCodes = inventoryDTO.getUniCodes();
for (InventoryUniCodeDTO inventoryUniCodeDTO : uniCodes) {
InventoryUniCode inventoryUniCode = new InventoryUniCode();
inventoryUniCode.setInventoryId(newInventoryEntry.getId());
inventoryUniCode.setWarehouseId(newInventoryEntry.getWarehouseId());
inventoryUniCode.setMaterialCode(newInventoryEntry.getMaterialCode());
inventoryUniCode.setBatch(newInventoryEntry.getBatch());
inventoryUniCode.setUniCode(inventoryUniCodeDTO.getUnicode());
inventoryUniCode.setItemQty(newInventoryEntry.getItemQty());
inventoryUniCode.setQuantity(inventoryUniCodeDTO.getQuantity());
inventoryUniCodeMapper.insert(inventoryUniCode);
}
}
} else {
// 代表catch已经执行
if (inventoryDTO.getUseUniCode().equals(CommonYesNo.YES)) {
updateDetailQuantity(inventoryDTO, newInventory);
}
}
}
} else {
// 存在更新
inventory.setQuantity(inventory.getQuantity().add(inventoryDTO.getQuantity()));
inventory.setPackageQty(inventory.getQuantity().divide(new BigDecimal(inventory.getItemQty()), 2, RoundingMode.HALF_UP));
this.updateById(inventory);
if (inventoryDTO.getUseUniCode().equals(CommonYesNo.YES)) {
updateDetailQuantity(inventoryDTO, inventory);
}
}
}public void updateDetailQuantity(InventoryDTO inventoryDTO, Inventory newInventory) {
for (InventoryUniCodeDTO inventoryUniCodeDTO : inventoryDTO.getUniCodes()) {
LambdaQueryWrapper<InventoryUniCode> lambdaQueryWrapper = Wrappers.lambdaQuery();
lambdaQueryWrapper.eq(InventoryUniCode::getInventoryId, newInventory.getId());
lambdaQueryWrapper.eq(InventoryUniCode::getUniCode, inventoryUniCodeDTO.getUnicode());
InventoryUniCode inventoryUniCode = inventoryUniCodeMapper.selectOne(lambdaQueryWrapper);
if (inventoryUniCode == null) {
// 明细是空
InventoryUniCode inventoryUniCode1 = new InventoryUniCode();
inventoryUniCode1.setInventoryId(newInventory.getId());
inventoryUniCode1.setWarehouseId(newInventory.getWarehouseId());
inventoryUniCode1.setMaterialCode(newInventory.getMaterialCode());
inventoryUniCode1.setBatch(newInventory.getBatch());
inventoryUniCode1.setUniCode(inventoryUniCodeDTO.getUnicode());
inventoryUniCode1.setItemQty(newInventory.getItemQty());
inventoryUniCode1.setQuantity(inventoryUniCodeDTO.getQuantity());
inventoryUniCodeMapper.insert(inventoryUniCode1);
} else {
// 明细不是空
inventoryUniCode.setQuantity(inventoryUniCode.getQuantity().add(inventoryUniCodeDTO.getQuantity()));
inventoryUniCodeMapper.updateById(inventoryUniCode);
}
}
}