利用MySQL实现分布式锁，涉及到乐观锁和悲观锁的思想 - eaglelihh

对于一些并发量不是很高的场景，使用MySQL来实现乐观锁会比较精简且巧妙。

下面就一个小例子，针对不加锁、乐观锁以及悲观锁这三种方式来实现。

主要是一个用户表，它有一个年龄的字段，然后并发地对其加一，看看结果是否正确。

一些基础实现类

@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class User {
    private Integer age;
    private String name;
    private Long id;
    private Long version;
}

public interface UserMapper {
    @Results(value = {
            @Result(property = "id", column = "id", javaType = Long.class, jdbcType = JdbcType.BIGINT),
            @Result(property = "age", column = "age", javaType = Integer.class, jdbcType = JdbcType.INTEGER),
            @Result(property = "name", column = "name", javaType = String.class, jdbcType = JdbcType.VARCHAR),
            @Result(property = "version", column = "version", javaType = Long.class, jdbcType = JdbcType.BIGINT),
    })
    @Select("SELECT id, age, name, version FROM user WHERE id = #{id}")
    User getUser(Long id);

    @Update("UPDATE user SET age = #{age}, version=version+1 WHERE id = #{id} AND version = #{version}")
    Boolean compareAndSetAgeById(Long id, Long version, Integer age);

    @Update("UPDATE user SET age = #{age} WHERE id = #{id}")
    Boolean setAgeById(Long id, Integer age);

    @Select("SELECT id, age, name, version FROM user WHERE id = #{id} for update")
    User getUserForUpdate(Long id);
}

private static void exe(CountDownLatch countDownLatch, int threads, Runnable runnable) {
    ExecutorService executorService = Executors.newCachedThreadPool();
    for (int i = 0; i < threads; i++) {
        executorService.execute(() -> {
            runnable.run();
            countDownLatch.countDown();
        });
    }
    executorService.shutdown();
}

private static User getUser(long id) {
    try (SqlSession session = openSession(getDataSource1())) {
        UserMapper userMapper = session.getMapper(UserMapper.class);
        return userMapper.getUser(id);
    }
}

public static SqlSession openSession(DataSource dataSource) {
    TransactionFactory transactionFactory = new JdbcTransactionFactory();
    Environment environment = new Environment("development", transactionFactory, dataSource);
    Configuration configuration = new Configuration(environment);
    configuration.addMapper(UserMapper.class);
    configuration.setCacheEnabled(false);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(configuration);
    return sqlSessionFactory.openSession();
}

private static DataSource getDataSource1() {
    MysqlConnectionPoolDataSource dataSource = new MysqlConnectionPoolDataSource();
    dataSource.setUser("root");
    dataSource.setPassword("root");
    dataSource.setUrl("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8");
    return dataSource;
}

private static Boolean addAge(long id, int value) {
    Boolean result;
    try (SqlSession session = openSession(getDataSource1())) {
        UserMapper userMapper = session.getMapper(UserMapper.class);
        User user = userMapper.getUser(id);
        result = userMapper.setAgeById(user.getId(), user.getAge() + value);
        session.commit();
    }
    return result;
}

public static void main(String[] args) throws Exception {
    long id = 1L;
    int threads = 50;
    CountDownLatch countDownLatch = new CountDownLatch(threads);
    log.info("user:{}", getUser(id));
    long start = System.currentTimeMillis();
    exe(countDownLatch, threads, () -> addAge(1, 1));
    countDownLatch.await();
    long end = System.currentTimeMillis();
    log.info("end - start : {}", end - start);
    log.info("user:{}", getUser(id));
}

865  [main] INFO  cn.eagle.li.mybatis.UpdateMain - user:User(age=0, name=3, id=1, version=0) 
1033 [main] INFO  cn.eagle.li.mybatis.UpdateMain - end - start : 164 
1046 [main] INFO  cn.eagle.li.mybatis.UpdateMain - user:User(age=9, name=3, id=1, version=0) 

从输出可以看出，50个并发，但是执行成功的只有9个，这种实现很明显是有问题的。

private static Boolean compareAndAddAge(long id, int value, int times) {
    int time = 0;
    Boolean result = false;
    while (time++ < times && BooleanUtils.isFalse(result)) {
        result = compareAndAddAge(id, value);
    }
    return result;
}

private static Boolean compareAndAddAge(long id, int value) {
    try (SqlSession session = openSession(getDataSource1())) {
        UserMapper userMapper = session.getMapper(UserMapper.class);
        User user = userMapper.getUser(id);
        Boolean result = userMapper.compareAndSetAgeById(id, user.getVersion(), user.getAge() + value);
        session.commit();
        return result;
    }
}

public static void main(String[] args) throws Exception {
    long id = 1L;
    int threads = 50;
    CountDownLatch countDownLatch = new CountDownLatch(threads);
    log.info("user:{}", getUser(id));
    long start = System.currentTimeMillis();
    exe(countDownLatch, threads, () -> addAge(1, 1, 20));
    countDownLatch.await();
    long end = System.currentTimeMillis();
    log.info("end - start : {}", end - start);
    log.info("user:{}", getUser(id));
}

758  [main] INFO  cn.eagle.li.mybatis.UpdateMain - user:User(age=0, name=3, id=1, version=0) 
1270 [main] INFO  cn.eagle.li.mybatis.UpdateMain - end - start : 509 
1277 [main] INFO  cn.eagle.li.mybatis.UpdateMain - user:User(age=50, name=3, id=1, version=50) 

从输出可以看出，并发的情况下，结果是没问题的。

private static Boolean addAgeForUpdate(long id, int value) {
    Boolean result;
    try (SqlSession session = openSession(getDataSource1())) {
        UserMapper userMapper = session.getMapper(UserMapper.class);
        User user = userMapper.getUserForUpdate(id);
        result = userMapper.setAgeById(id, user.getAge() + value);
        session.commit();
    }
    return result;
}

public static void main(String[] args) throws Exception {
    long id = 1L;
    int threads = 50;
    CountDownLatch countDownLatch = new CountDownLatch(threads);
    log.info("user:{}", getUser(id));
    long start = System.currentTimeMillis();
    exe(countDownLatch, threads, () -> addAgeForUpdate(1, 1));
    countDownLatch.await();
    long end = System.currentTimeMillis();
    log.info("end - start : {}", end - start);
    log.info("user:{}", getUser(id));
}

631  [main] INFO  cn.eagle.li.mybatis.UpdateMain - user:User(age=0, name=3, id=1, version=50) 
829  [main] INFO  cn.eagle.li.mybatis.UpdateMain - end - start : 196 
837  [main] INFO  cn.eagle.li.mybatis.UpdateMain - user:User(age=50, name=3, id=1, version=50) 

从输出可以看出，并发的情况下，结果是没问题的。

从以上来看，乐观锁和悲观锁实现都是没有问题的，至于选哪一种，还是要看业务的场景，比如说并发量的多少，加锁时长等等。