前言

select ... for update 语句显式锁定表记录,一般对于账务等重要的数据我们都会采取这种方式更新,但是有一种情况,会导致结果与预期不符: for update时数据库(或缓存页)中还没有对应的记录

TLDR

• select ... for update 在查询不到记录时是没有锁的
• 在一个事务中插入一条记录,即使事务尚未提交,在另一个事务中 select ... for udpdate 依然能到知晓这条数据的存在.这一特点和事务的创建先后顺序无关
• 数据库锁(无论有多少)获取后,在事务提交/回归后才会释放

正文

假设现在有一张用户余额表,表结构如下

drop table if exists user_balance;
create table user_balance
(
    id      bigint auto_increment,
    userId  bigint,
    balance int,
    primary key (id)
) engine = InnoDB
  default charset = utf8mb4 comment ='用户余额表';

在业务上我们实行懒加载原则: 只有在用户第一次余额变化(增加)是才会创建这条记录,Java代码如下

public void addBalance(long userId,int balance){
    userBalance = DB.getUserBalanceForUpdate(uesrId);
    if(userBalance==null){
        userBalance=new UserBalance(userId,0);
    }
    userBalance.setBalance(userBalance.getUserBalance()+balance);
    DB.save(userBalance)
}

看起来没什么问题,但是如果在数据库还没有这个用户的余额记录时并发调用addBalance,就可能导致这个用户有多条余额记录. 这显然是异常的(如果我们对userId加唯一索引,会导致另一个问题: 余额丢失,这同样是不可接受的),为什么会导致这种情况呢? 一个可能的异常流程sql执行如下

    # 事务1
    # 1
    begin;
    select * from user_balance where userId={userId} for update;
    # 3
    insert into user_balance(userId,balance) value({userId},{balance});
    commit;


​    
    # 事务2
    # 2
    begin;
    select * from user_balance where userId={userId} for update;
    # 4
    insert into user_balance(userId,balance) value({userId},{balance});
    commit;

1. 事务1 for update,发现没有记录
2. 事务2 for update,发现没有记录
3. 事务1插入数据,提交
4. 事务2插入数据,提交

这样就导致一个用户有两条余额记录

如何解决这个问题呢: 另加一个业务锁,Java代码如下

public void addBalance(long userId,int balance){
    userBalance = DB.getUserBalanceForUpdate(uesrId);
    if(userBalance==null){
        //如果是分布式环境 使用分布式锁,如Redisson
        Lock lock=Lock.getLock(userId);
        try{
            lock.lock();
            userBalance = DB.getUserBalanceForUpdate(uesrId);
            if(userBalance==null){
                userBalance=new UserBalance(userId,0);
            }
        }catch(Exception e){
            ...
        }finally{
            lock.unlock();
        }
    }
    userBalance.setBalance(userBalance.getUserBalance()+balance);
    DB.save(userBalance)
}

类似于单例模式中的双重锁定检查,这样在数据库锁未生效时,业务锁可以保证只有一个事务去进行insert操作.其他事务都会等待.sql执行流程如下,事务1获取到了业务锁(在牢记业务锁存在的前提下,有兴趣可以自己尝试下其他顺序,都可以保证安全)

    # 事务1
    # 1
    begin ;
    select * from user_balance where userId={userId} for update;
    # 3
    insert into user_balance(userId,balance) value({userId},{balance});
    # 5
    commit ;


​    
    # 事务2
    # 2
    begin ;
    select * from user_balance where userId={userId} for update;
    # 4
    select * from user_balance where userId={userId} for update;
    # 6
    update user_balance set balance=balance+{balance} where id={id};
    commit ;

1. 事务1先执行了for update,发现没有记录
2. 事务2也执行了for update,也没有发现记录
3. 事务1 获取到业务锁,事务2未获取到业务锁,休眠等待业务锁,事务1释放业务锁
4. 事务2获取到锁,再次for update,发现记录,但是事务1还没有提交事务,未释放数据库锁,继续等待
5. 事务1提交事务
6. 事务2获取的记录,执行业务逻辑~

这样就达到了在没有数据库锁的时候,依靠业务锁保证逻辑的正常.

后记

本文依靠 翻阅到的博文 + 对<<高性能MySQL>>的残存记忆 + 实测 得出,如有错误多谢指点.

MySQL 乐观锁与悲观锁