最近公司接了个商城系统的项目,在秒杀商品这块的设计上,因为我们需要考虑到秒杀时,在线的人数可能会非常多,所以需要考虑高并发的情况;
一、在线下单代码
1.1、数据库设计
这里我们简单建立两个表 goods和order表
/*货物表*/
CREATE TABLE `goods` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '商品id',
`goods_name` varchar(255) DEFAULT NULL COMMENT '商品名称',
`stock` int DEFAULT '0' COMMENT '商品库存',
`add_time` timestamp NULL DEFAULT NULL COMMENT '添加时间',
`update_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
`version` int DEFAULT '0' COMMENT '版本,用于乐观锁实现',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*加入一条商品信息 */
insert into `goods`(`id`,`goods_name`,`stock`,`add_time`,`update_time`,`version`) values (1,'1',0,'2020-09-01 17:53:06','2020-09-02 15:50:28',50);
/*订单表 */
CREATE TABLE `order` (
`id` int NOT NULL AUTO_INCREMENT,
`goods_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`add_time` timestamp NULL DEFAULT NULL,
`update_time` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1466 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
1.2、商品代码
实体类
package com.example.demo.entity;
import java.util.Date;
import java.time.LocalDateTime;
public class GoodsEntity {
private Integer id;
private String goodsName;
private Integer stock;
private Integer version;
private Date addTime;
private Date updateTime;
public Integer getStock() {
return stock;
}
public void setStock(Integer stock) {
this.stock = stock;
}
public Integer getVersion() {
return version;
}
public void setVersion(Integer version) {
this.version = version;
}
public Date getAddTime() {
return addTime;
}
public void setAddTime(Date addTime) {
this.addTime = addTime;
}
public Date getUpdateTime() {
return updateTime;
}
public void setUpdateTime(Date updateTime) {
this.updateTime = updateTime;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getGoodsName() {
return goodsName;
}
public void setGoodsName(String goodsName) {
this.goodsName = goodsName;
}
@Override
public String toString() {
return "GoodsEntity{" +
"id=" + id +
", goodsName='" + goodsName + '\'' +
", stock=" + stock +
", addTime=" + addTime +
", updateTime=" + updateTime +
'}';
}
}
dao层
package com.example.demo.dao;
import com.example.demo.entity.GoodsEntity;
import org.springframework.stereotype.Repository;
import java.util.List;
@Repository
public interface GoodsDao {
GoodsEntity findById(Integer id);
int updateStick(GoodsEntity goodsEntity);
}
GoodsMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.dao.GoodsDao">
<resultMap id="BaseResultMap" type="com.example.demo.entity.GoodsEntity">
<result column="id" jdbcType="INTEGER" property="id" />
<result column="goods_name" jdbcType="VARCHAR" property="goodsName" />
<result column="stock" jdbcType="INTEGER" property="stock"/>
<result column="add_time" jdbcType="TIMESTAMP" property="addTime"/>
<result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
<result column="version" jdbcType="INTEGER" property="version"/>
</resultMap>
<select id="findById" resultMap="BaseResultMap">
select * from goods where id= #{id,jdbcType=INTEGER}
</select>
<update id="updateStick" parameterType="com.example.demo.entity.GoodsEntity">
update goods set goods_name=#{goodsName,jdbcType=VARCHAR},stock=#{stock,jdbcType=INTEGER} where id=#{id,jdbcType=INTEGER}
</update>
</mapper>
service
package com.example.demo.service;
import com.example.demo.entity.GoodsEntity;
import org.springframework.stereotype.Service;
import java.util.List;
public interface GoodsService {
/**
* 下单
*/
public int submit(Integer id);
}
serviceImpl实现类
package com.example.demo.service.impl;
import com.example.demo.dao.GoodsDao;
import com.example.demo.entity.GoodsEntity;
import com.example.demo.entity.OrderEntity;
import com.example.demo.service.GoodsService;
import com.example.demo.service.OrderService;
import com.example.demo.utils.RedisUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.util.Date;
import java.util.List;
@Service
public class GoodsServiceImpl implements GoodsService {
@Autowired
GoodsDao goodsDao;
@Autowired
OrderService orderService;
/**
* 乐观锁实现下单
* @param id
* @return
*/
@Override
@Transactional
public int submit(Integer id) {
GoodsEntity goods = goodsDao.findById(id);
int result=0;
//查询库存
if (goods==null){
return result;
}
if (goods.getStock()<1){
return result;
}
goods.setStock(goods.getStock()-1);
// goods.setUpdateTime(new Date());
result=goodsDao.updateStick(goods);
//
if (result==0){
throw new RuntimeException("库存减少失败了");
}
//加入订单表
OrderEntity orderEntity=new OrderEntity();
orderEntity.setGoodsName(goods.getGoodsName());
orderEntity.setAddTime(new Date());
orderEntity.setUpdateTime(new Date());
if (orderService.insert(orderEntity)==0){
throw new RuntimeException("加入订单失败了");
}
return result;
}
}
GoodsController
package com.example.demo.controller;
import com.example.demo.entity.GoodsEntity;
import com.example.demo.service.GoodsService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
@RestController
@RequestMapping("/goods")
public class GoodsController {
@Autowired
GoodsService goodsService;
/**
* 下单操作
*/
@RequestMapping("submitOrder")
public Object submitOrder(Integer id){
return goodsService.submit(id);
}
}
1.3、订单代码
实体类
package com.example.demo.entity;
import java.util.Date;
public class OrderEntity {
private Integer id;
private String goodsName;
private Date addTime;
private Date updateTime;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getGoodsName() {
return goodsName;
}
public void setGoodsName(String goodsName) {
this.goodsName = goodsName;
}
public Date getAddTime() {
return addTime;
}
public void setAddTime(Date addTime) {
this.addTime = addTime;
}
public Date getUpdateTime() {
return updateTime;
}
public void setUpdateTime(Date updateTime) {
this.updateTime = updateTime;
}
@Override
public String toString() {
return "OrderEntity{" +
"id=" + id +
", goodsName='" + goodsName + '\'' +
", addTime=" + addTime +
", updateTime=" + updateTime +
'}';
}
}
dao层
package com.example.demo.dao;
import com.example.demo.entity.OrderEntity;
import org.springframework.stereotype.Repository;
import java.util.List;
@Repository
public interface OrderDao {
int insert(OrderEntity orderEntity);
}
OrderMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.dao.OrderDao">
<resultMap id="BaseResultMap" type="com.example.demo.entity.OrderEntity">
<result column="id" jdbcType="INTEGER" property="id" />
<result column="goods_name" jdbcType="VARCHAR" property="goodsName" />
<result column="add_time" jdbcType="TIMESTAMP" property="addTime"/>
<result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
</resultMap>
<insert id="insert" parameterType="com.example.demo.entity.OrderEntity">
insert into `order`(goods_name,add_time,update_time) value
(#{goodsName,jdbcType=VARCHAR},#{addTime,jdbcType=TIMESTAMP},#{updateTime,jdbcType=TIMESTAMP})
</insert>
</mapper>
OrderService
package com.example.demo.service;
import com.example.demo.entity.GoodsEntity;
import com.example.demo.entity.OrderEntity;
import java.util.List;
public interface OrderService {
/**
* 插入订单
*/
public int insert(OrderEntity orderEntity);
}
OrderServiceImpl实现类
package com.example.demo.service.impl;
import com.example.demo.dao.GoodsDao;
import com.example.demo.dao.OrderDao;
import com.example.demo.entity.GoodsEntity;
import com.example.demo.entity.OrderEntity;
import com.example.demo.service.GoodsService;
import com.example.demo.service.OrderService;
import com.example.demo.utils.RedisUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class OrderServiceImpl implements OrderService {
@Autowired
OrderDao orderDao;
@Override
public int insert(OrderEntity orderEntity) {
return orderDao.insert(orderEntity);
}
}
1.4、这里我们的商品表中的货物有50个库存,我们使用JMeter模拟5000个用户来下单,理论上访问后,我们订单表应该有50行数据,也就是50个条订单
查看订单表:
这里产生了400条订单数据,证明是不对;
二、原因分析
产生多条订单数据出现的原因就是并发造成的;
假设有10个并发线程查询了库存,此时库存都是10,然后这10个线程都对库存进行-1操作,最后库存就变成了9,所以理论应该减10个库存的,最后只减了1个;
三、解决方案
1、采用乐观锁,就是在该商品数据增加一个version字段,每次进行修改时都对version增加1;
假设有10个并发线程,此时访问的库存都是10,线程1减了库存之后,version就增加了1,假设原来version是0,现在就变成了1;线程2也来减库存,此时线程2查询的version依旧是0,但数据库中的version由于线程1的减库操作,变成了2了;所以线程2减库存时,就会失败;
2、我们修改一下xml中的sql语句
<update id="updateStick" parameterType="com.example.demo.entity.GoodsEntity">
update goods set goods_name=#{goodsName,jdbcType=VARCHAR},stock=#{stock,jdbcType=INTEGER},version=version+1,update_time=#{updateTime,jdbcType=TIMESTAMP} where version=#{version,jdbcType=INTEGER} AND id=#{id,jdbcType=INTEGER}
</update>
四、测试