mysql 乐观锁 多线程 乐观锁sql语句实现

基于数据库实现分布式锁

• 乐观锁

• 乐观锁实战

• 数据库设计

• 悲观锁实战

乐观锁

乐观锁是一种佛系（乐观的）锁，在操作数据的时候永远数据不会被其他线程共享，但是在数据持久化的时候，检查数据是否正常
通常采用一个版本号version的机制实现
1.取出数据的时候，顺便取出版本号version
2.数据持久化的时候，将version作为存储条件，更新成功后version+1
3.而其他线程如果获取同样的数据进行操作，在持久化的时候由于version已经不是之前获取的那个值了，所以更新失败。
sql语法如下

update table set key=value, version=version+1 where id=#{id} and version=#{versio
n};

乐观锁实战

我们以“余额提现”为业务场景，当用户有余额的时候点击“提现申请”，即可进入余额的申请页面，输入提现额和银行卡号，点击提现，即可将申请的余额提现到账户上。

如果用户疯狂的点击提现按钮，造成了大量的并发请求，如果满足条件就会被扣除,这种结果会造成最终的账号余额出现了一个负数，这是致命的

数据库设计

CREATE TABLE user_account_record (
 id int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
 account_id int(11) NOT NULL COMMENT '账户表主键id',
 money decimal(10,4) DEFAULT NULL COMMENT '提现成功时记录的金额',
 create_time datetime DEFAULT NULL,
 PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=360 DEFAULT CHARSET=utf8 COMMENT='用户每次成功提现 时的金额记录表';

CREATE TABLE user_account (
 id int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
 user_id int(11) NOT NULL COMMENT '用户账户id',
 amount decimal(10,4) NOT NULL COMMENT '账户余额',
 version int(11) DEFAULT '1' COMMENT '版本号字段',
 is_active tinyint(11) DEFAULT '1' COMMENT '是否有效(1=是;0=否)',
 PRIMARY KEY (id),
 UNIQUE KEY 'idx_user_id' (user_id) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='用户账户余额记录表
';

package com.learn.boot.controller;

import com.learn.boot.dto.UserAccountDto;
import com.learn.boot.mapper.redis.order.UserAccountMapper;
import com.learn.boot.mapper.redis.order.UserAccountRecordMapper;
import com.learn.boot.model.UserAccount;
import com.learn.boot.model.UserAccountRecord;
import com.learn.boot.resultVo.ResultVo;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import java.util.Date;

/**
 * 基于数据库级别的实现锁机制，
 * 这里省略服务层，直接调用Mapper层
 */
@RequestMapping("/order")
@RestController
public class DataBaseLockController {
    //定义日志
    private static final Logger log= LoggerFactory.getLogger(DataBaseLockController.class);

    @Autowired
    private UserAccountMapper userAccountMapper;

    @Autowired
    private UserAccountRecordMapper userAccountRecordMapper;

    @RequestMapping("/noLock")
    public ResultVo noLock(@RequestBody UserAccountDto model) {
        UserAccount userAccount = userAccountMapper.selectByUserId(model.getUserId());
        if (userAccount == null || userAccount.getAmount().compareTo(model.getAmount()) < 0) {
            return ResultVo.error("您没有提现的金额");
        }
        // 如果发现有足够的金额就更新
        int status = userAccountMapper.updateAmount(userAccount.getAmount().subtract(model.getAmount()),userAccount.getId());
        // 有
        if (status == 1) {
            // 插入提现记录
            UserAccountRecord userAccountRecord = new UserAccountRecord(){
                {
                    setAccountId(userAccount.getId());
                    setCreateTime(new Date());
                    setMoney(model.getAmount());
                }
            };
            userAccountRecordMapper.insertSelective(userAccountRecord);
            //输出日志
            log.info("当前待提现的金额为：{} 用户账户余额为：{}",model.getAmount(),userAccount.getAmount().subtract(model.getAmount()));
            return null;
        }
        return ResultVo.error("提现异常");
    }

}

主要sql如下

<select id="selectByUserId" resultType="com.learn.boot.model.UserAccount">
    select <include refid="Base_Column_List"></include>
    from user_account where user_id = #{userId,jdbcType=INTEGER}
  </select>

  <update id="updateAmount">
    UPDATE user_account SET amount = #{money}
     WHERE is_active=1 AND id=#{id}
 </update>

我们用1000个线程去调度一下，发现出现了问题

如果我把整个请求方法加上synchronized，看一下吞吐量

等会我们使用乐观锁，我们再看看吞吐量

以下使用乐观锁

@RequestMapping("/lock")
    public ResultVo lock(@RequestBody UserAccountDto model) {
        UserAccount userAccount = userAccountMapper.selectByUserId(model.getUserId());
        if (userAccount == null || userAccount.getAmount().compareTo(model.getAmount()) < 0) {
            return ResultVo.error("您没有提现的金额");
        }
        BigDecimal subMoney = userAccount.getAmount().subtract(model.getAmount());
        // 如果发现有足够的金额就更新
        int status = userAccountMapper.updateAmountByLock(subMoney,userAccount.getId(),userAccount.getVersion());
        // 有
        if (status == 1) {
            // 插入提现记录
            UserAccountRecord userAccountRecord = new UserAccountRecord(){
                {
                    setAccountId(userAccount.getId());
                    setCreateTime(new Date());
                    setMoney(model.getAmount());
                }
            };
            userAccountRecordMapper.insertSelective(userAccountRecord);
            //输出日志
            log.info("当前待提现的金额为：{} 用户账户余额为：{}",model.getAmount(),subMoney);
            return null;
        }
        return ResultVo.error("提现异常");
    }

修改使用的sql

<update id="updateAmountByLock">
      UPDATE user_account SET amount = #{money}, version =  version  + 1
     WHERE is_active=1 AND id=#{id} and version = #{version}
  </update>

吞吐量上去了！！

悲观锁实战

这里实现乐观锁，是用到了mysql对索引排它锁的实现，mysql模式是可重复读，有自动提交的属性，我们需要修改成读已提交，这样就保证了一个事务必须等待其他的事务处理完了再提交

@RequestMapping("/lock2")
    @Transactional(isolation = Isolation.READ_COMMITTED)
    public ResultVo lock2(@RequestBody UserAccountDto model) {
        UserAccount userAccount = userAccountMapper.selectByUserIdByLock(model.getUserId());
        if (userAccount == null || userAccount.getAmount().compareTo(model.getAmount()) < 0) {
            return ResultVo.error("您没有提现的金额");
        }
        BigDecimal subMoney = userAccount.getAmount().subtract(model.getAmount());
        // 如果发现有足够的金额就更新
        int status = userAccountMapper.updateAmount(subMoney,userAccount.getId());
        // 有
        if (status == 1) {
            // 插入提现记录
            UserAccountRecord userAccountRecord = new UserAccountRecord(){
                {
                    setAccountId(userAccount.getId());
                    setCreateTime(new Date());
                    setMoney(model.getAmount());
                }
            };
            userAccountRecordMapper.insertSelective(userAccountRecord);
            //输出日志
            log.info("用悲观锁，当前待提现的金额为：{} 用户账户余额为：{}",model.getAmount(),subMoney);
            return null;
        }
        return ResultVo.error("提现异常");
    }

<select id="selectByUserIdByLock" resultType="com.learn.boot.model.UserAccount">
    select <include refid="Base_Column_List"></include>
    from user_account where user_id = #{userId,jdbcType=INTEGER} for update
  </select>

看看吞吐量，比乐观锁稍微差了一点