使用mysql数据库实现抢红包

使用mysql数据库实现抢红包的功能
1.建表
红包表
create table red_packet
(
   id                   int(12)     auto_increment COMMENT '红包编号',
   user_id              int(12)                        COMMENT '发红包的用户id',
   amount               decimal(16,2)                  COMMENT '红包金额',
   send_date            timestamp                      DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '发红包日期',
   total                int(12)                        COMMENT '红包总数',
   unit_amount          decimal(12)                    COMMENT '单个红包的金额',
   stock                int(12)                        COMMENT '红包剩余个数',
   version              int(12) default 0              COMMENT '版本号',
   primary key(id)
);
用户抢红包表
create table red_packet_user
(
   id                   int(12)                        auto_increment COMMENT '用户抢到的红包id',
   red_packet_id        int(12)                        COMMENT '红包id',
   user_id              int(12)                        COMMENT '抢红包用户的id',
   amount               decimal(16,2)                  COMMENT '抢到的红包金额',
   grab_time            timestamp                      DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '抢红包时间',
   note                 varchar(256)                   null COMMENT '备注',
   primary key(id)
);

insert into red_packet(user_id, amount, send_date, total, unit_amount, stock)
values(1, 10000, now(), 2000, 5, 2000);

2.测试：性能1万元2000人抢

3.代码
@Service
public class RedPacketServiceImpl implements IRedPacketService {

@Resource
private RedPacketMapper redPacketMapper;

public RedPacketPO getRedPacket(Integer id) {
return redPacketMapper.getRedPacket(id);
}

public int decreaseRedPacket(Integer id) {
return redPacketMapper.updateRedPacketStock(id);
}

@Transactional(isolation = Isolation.READ_COMMITTED, propagation = Propagation.REQUIRED)
public int grapRedPacket(Integer redPacketId, Integer userId) {
// 获取红包信息
    RedPacketPO redPacket = redPacketMapper.getRedPacket(redPacketId);
    int leftRedPacket = redPacket.getStock();
    // 当前小红包库存大于0
    if (leftRedPacket > 0) {
      redPacketMapper.updateRedPacketStock(redPacketId);
      // 生成抢红包信息
      RedPacketUserPO userRedPacket = new RedPacketUserPO();
      userRedPacket.setRedPacketId(redPacketId);
      userRedPacket.setUserId(userId);
      userRedPacket.setAmount(5);
      // 插入抢红包信息
      int result = redPacketMapper.insertRedPacketUser(userRedPacket);
      return result;
    }
    //没有红包啦.....剩余Stock数量
    return -1;
}

public int grapRedPacketProc(Integer redPacketId, Integer userId) {
int num = -1;
Map<string> map = new HashMap<string integer>();
map.put("v_red_packet_id", redPacketId);
map.put("v_user_id", userId);
redPacketMapper.grabRedPacketProcedure(map);
if(map.containsKey("num")) {
num = map.get("num");
}
return num;
}
}

<mapper namespace="cn.web.mapper.RedPacketMapper"><resultmap type="cn.web.po.RedPacketPO" id="redPacketMap"><result property="id" column="id"></result><result property="userId" column="user_id"></result><result property="amount" column="amount"></result><result property="sendDate" column="send_date"></result><result property="total" column="total"></result><result property="unitAmount" column="unit_amount"></result><result property="stock" column="stock"></result><result property="version" column="version"></result></resultmap><select id="getRedPacket" resultmap="redPacketMap" parametertype="int">
select * from red_packet where id = #{id}
</select><insert id="insertRedPacketUser" usegeneratedkeys="true" parametertype="cn.web.po.RedPacketUserPO">
        insert into red_packet_user (red_packet_id,user_id,amount) values (#{redPacketId},#{userId},#{amount})
    </insert><update id="updateRedPacketStock" parametertype="int">
    update red_packet set stock=stock-1 where id = #{id}
    </update><select id="grabRedPacketProcedure" parametermap="paramMap" resulttype="map" statementtype="CALLABLE">
    {call grabRedPacketProcedure(?,?,?)}
    </select><parametermap type="java.util.Map" id="paramMap"><parameter property="v_red_packet_id" mode="IN" javatype="INTEGER" jdbctype="INTEGER"></parameter><parameter property="v_user_id" mode="IN" javatype="INTEGER" jdbctype="INTEGER"></parameter><parameter property="num" mode="OUT" javatype="INTEGER" jdbctype="INTEGER"></parameter></parametermap></mapper>
4.说明：
使用mysql来抢红包，2000人抢完红包大概花20多秒;使用存储过程也差不多20多秒
优化：
（1）使用存储过程
delimiter //
DROP PROCEDURE IF EXISTS grabRedPacketProcedure
//
create PROCEDURE grabRedPacketProcedure(IN v_red_packet_id INTEGER,IN v_user_id INTEGER,OUT num INTEGER)
begin
set num:= -1;
select @total:=stock from red_packet where id = v_red_packet_id;
if @total > 0 then
begin
start transaction;
update red_packet set stock=stock-1 where id = v_red_packet_id;
insert into red_packet_user(red_packet_id,user_id,amount) values (v_red_packet_id,v_user_id,5);
commit;
set num:= 1;
end;
end if;
end
//
DELIMITER ;

存储过程测试:
DELIMITER //
set @num=0;
call grabRedPacketProcedure(1,1,@num);
select @num;
//
DELIMITER ;

</string></string>