SQL 分析
- MySQL 加行锁的前提是:item_id 这一列上必须有索引;
- 如果 item_id 这一列上没有索引,那么只能加表锁;
-
<update
id=
"decreaseStock">
-
update item_stock
-
set stock = stock -
#{amount}
-
where item_id =
#{itemId} and stock >= #{amount}
-
</update>
- 给 item_id 加唯一索引
alter table item_stock add unique index item_id_index(item_id);
- 表结构
-
CREATE
TABLE `item_stock` (
-
`id`
int
NOT
NULL AUTO_INCREMENT,
-
`stock`
int
NOT
NULL
DEFAULT
'0',
-
`item_id`
int
NOT
NULL
DEFAULT
'0',
-
PRIMARY KEY (`id`),
-
UNIQUE KEY `item_id_index` (`item_id`)
-
) ENGINE
=InnoDB AUTO_INCREMENT
=
3
DEFAULT CHARSET
=utf8
COLLATE
=utf8_unicode_ci;
扣减库存 Redis 化
- 活动发布时,同步库存到 Redis 中:
-
@Override
-
public
void
publishPromo
(Integer promoId) {
-
PromoDO
promoDO
= promoDOMapper.selectByPrimaryKey(promoId);
-
if (promoDO.getItemId() ==
null || promoDO.getItemId().intValue() ==
0) {
-
return;
-
}
-
ItemModel
itemModel
= itemService.getItemById(promoDO.getItemId());
-
redisTemplate.opsForValue().set(
"promo_item_stock_" + itemModel.getId(), itemModel.getStock());
-
}
- 下单交易减 Redis 中的库存;
-
@Override
-
@Transactional
-
public
boolean
decreaseStock(
Integer itemId, Integer amount) {
-
// int affectedRows = itemStockDOMapper.decreaseStock(itemId, amount);
-
long result = redisTemplate.
opsForValue().
increment(
"promo_item_stock_" + itemId, amount * -
1);
-
if (result >=
0) {
-
return
true;
-
}
else {
-
return
false;
-
}
-
}