mysql 乐观锁版本号 mysql乐观锁解决并发

转载

编程小匠人之魂 2023-10-26 19:46:39

文章标签 mysql 乐观锁版本号 mysql乐观锁秒杀上传 System 数据库 文章分类 MySQL 数据库

数据库:mysql

数据库的乐观锁:一般通过数据表加version来实现,相对于悲观锁的话，更能省数据库性能,废话不多说，直接看代码

第一步：

建立数据库表：

CREATE TABLE `skill_activity` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '活动id',
`name` varchar(20) NOT NULL COMMENT '活动名称',
`num` bigint(10) NOT NULL COMMENT '活动数量限制',
`surplus_num` bigint(10) NOT NULL COMMENT '活动剩余数量',
`person_limit` bigint(10) NOT NULL COMMENT '单人上传限制',
`version` bigint(10) DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
CREATE TABLE `skill_activity_order` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`activity_id` bigint(20) NOT NULL COMMENT '活动id',
`thread_id` bigint(20) NOT NULL COMMENT '线程id',
`create_at` datetime DEFAULT NULL COMMENT '创建时间',
`name` varchar(20) NOT NULL COMMENT '活动名称',
`url` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`),
KEY `index_thread_id` (`thread_id`) ) ENGINE=InnoDB AUTO_INCREMENT=106338 DEFAULT CHARSET=utf8

往数据库活动表(skill_activity)插入一条数据

mysql 乐观锁版本号 mysql乐观锁解决并发_mysql 乐观锁版本号

num:商品数量;surplus_num:商品剩余数量;person_limit:单人上传数量限制;version:版本号,解决高并发问题

具体的活动秒杀订单表(skill_activity_order)：

mysql 乐观锁版本号 mysql乐观锁解决并发_System_02

activity_id:就是上面活动表的id;thread_id:就是线程id,实际秒杀就是用户id,name和url就是秒杀填写的一些内容,不必关注

第二步:

java代码:

1)2个表对应的mapper.java类

public interfaceSkillActivityMapper {intdeleteByPrimaryKey(Long id);intinsert(SkillActivity record);intinsertSelective(SkillActivity record);

SkillActivity selectByPrimaryKey(Long id);intupdateByPrimaryKeySelective(SkillActivity record);intupdateByPrimaryKey(SkillActivity record);intupdateSkillActivityNum(SkillActivity record);//秒杀修改剩余数量的方法

}
public interface SkillActivityOrderMapper {
int deleteByPrimaryKey(Long id);
int insert(SkillActivityOrder record);
int insertSelective(SkillActivityOrder record);
SkillActivityOrder selectByPrimaryKey(Long id);
List selectBythreadId(Long threadId);
int updateByPrimaryKeySelective(SkillActivityOrder record);
int updateByPrimaryKey(SkillActivityOrder record);
}

2)2个类对应的mapper.xml方法就不一一写出来了,看SkillActivityMapper的updateSkillActivityNum这个方法的sql语句:

update skill_activity

set

surplus_num= #{surplusNum,jdbcType=BIGINT},

version= #{version,jdbcType=BIGINT}+1where id= #{id,jdbcType=BIGINT} and version=#{version,jdbcType=BIGINT} and surplus_num>0

还有SkillActivityOrderMapper.selectBythreadId方法

selectfrom skill_activity_order

where thread_id= #{threadId,jdbcType=BIGINT}

3)version版本号是关键,update成功会导致version加1,而其他线程如果是原先的version就无法update。

4)看一下service代码：

@OverridepublicSkillActivityResponse SkillActivity(SkillActivirtReq req) {
SkillActivityResponse skillActivityResponse=newSkillActivityResponse();int failNum=0;
SkillActivity skillActivity=skillActivityMapper.selectByPrimaryKey(req.getActivityId());
List urls=req.getUrls();if(skillActivity.getSurplusNum()<=0){
skillActivityResponse.setErrorMsg("活动已经结束");
skillActivityResponse.setFailNum(urls.size());
skillActivityResponse.setSucceed(false);returnskillActivityResponse;
}else{//先查询用户上传了多少张
int count=skillActivityOrderMapper.selectBythreadId(req.getThreadId()).size();//查询每个用户上传了多少张if(count>skillActivity.getPersonLimit()){
skillActivityResponse.setErrorMsg("已经超出上传上限,上传失败");
skillActivityResponse.setFailNum(urls.size());
skillActivityResponse.setSucceed(false);returnskillActivityResponse;
}int index=(int) (skillActivity.getPersonLimit()-count);//表示还能上传的数量
if(urls.size()<=index){//都可以上传
}else{//表示只能上传index张图片
urls=urls.subList(0, index);
}//上传订单
for(int i=0;i
skillActivity=skillActivityMapper.selectByPrimaryKey(req.getActivityId());
skillActivity.setSurplusNum(skillActivity.getSurplusNum()-1);if(skillActivity.getSurplusNum()<0){
failNum++;continue;
}int result=skillActivityMapper.updateSkillActivityNum(skillActivity);//这个是关键if(result>0){//上传成功
SkillActivityOrder activityOrder=newSkillActivityOrder();
activityOrder.setActivityId(skillActivity.getId());
activityOrder.setCreateAt(newDate());
activityOrder.setName(skillActivity.getName());
activityOrder.setThreadId(req.getThreadId());
activityOrder.setUrl(urls.get(i));
skillActivityOrderMapper.insertSelective(activityOrder);
}else{//上传失败
failNum++;
}
}
skillActivityResponse.setFailNum(failNum);
skillActivityResponse.setSucceed(true);returnskillActivityResponse;
}
}

5)使用spring的junit来单元测试

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations={"classpath:spring/applicationContext.xml"})public classSkillActivityServieTest {
@AutowiredprivateSkillActivityService skillActivityService;class MyRun implementsRunnable{privateCyclicBarrier barrier;privateCountDownLatch countDownLatch;privateLong threadId;publicMyRun(CyclicBarrier barrier, CountDownLatch countDownLatch,
Long threadId) {super();this.barrier =barrier;this.countDownLatch =countDownLatch;this.threadId =threadId;
}
@Overridepublic voidrun() {
System.err.println("线程"+threadId+"准备完毕");try{
barrier.await();
SkillActivirtReq req=newSkillActivirtReq();
req.setActivityId(1L);
req.setThreadId(threadId);
req.setUrls(Lists.newArrayList("url1","url2","url3","url4","url5","url6","url7","url7","url8","url9","url10"));
SkillActivityResponse skillActivityResponse=skillActivityService.SkillActivity(req);if(skillActivityResponse.isSucceed()){
System.err.println("线程:"+threadId+",failNum:"+skillActivityResponse.getFailNum());
}else{
System.err.println("线程:"+threadId+",errmsg:"+skillActivityResponse.getErrorMsg());
}
}catch (InterruptedException |BrokenBarrierException e) {//TODO Auto-generated catch block
e.printStackTrace();
}finally{
countDownLatch.countDown();
}
}
}
@Testpublic void test01() throwsInterruptedException{
CyclicBarrier barrier= new CyclicBarrier(20000);//让20000个线程同时进行操作 调用20000次方法await() 才会让20000个线程同时执行
CountDownLatch countDownLatch=new CountDownLatch(20000);//统计耗时
ExecutorService executorService=Executors.newCachedThreadPool();long start=System.currentTimeMillis();for(int i=1;i<=20000;i++){
executorService.submit(new MyRun(barrier, countDownLatch, new Long(i+"")));
}
executorService.shutdown();
countDownLatch.await();
System.err.println("耗时:"+(System.currentTimeMillis()-start)+"ms");try{
Thread.sleep(Integer.MAX_VALUE); //防止线程结束
}catch(InterruptedException e) {
e.printStackTrace();
}
}
}

6)运行junit

mysql 乐观锁版本号 mysql乐观锁解决并发_上传_03