如何将MySQL的乐观锁和悲观锁思想融合，构建一个高效的分布式锁长尾词？

本文共计1304个文字，预计阅读时间需要6分钟。

在低并发场景下，使用MySQL实现比较简洁且高效。以下是一个小例子，针对不加密、乐观锁和悲观锁这三种方式实现。

主要是一个用户表，包含年龄字段。

sqlCREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, age INT NOT NULL);

1. 不加密：

sqlINSERT INTO users (age) VALUES (25);

2. 乐观锁：

sqlCREATE TABLE users_optimistic ( id INT PRIMARY KEY AUTO_INCREMENT, age INT NOT NULL, version INT DEFAULT 0);

UPDATE users_optimistic SET age=26, version=version + 1 WHERE id=1 AND version=0;

3. 悲观锁：

sqlSELECT age, version FROM users_pessimistic WHERE id=1 FOR UPDATE;

UPDATE users_pessimistic SET age=27, version=version + 1 WHERE id=1 AND version=0;

对于一些并发量不是很高的场景，使用MySQL来实现会比较精简且巧妙。 下面就一个小例子，针对不加锁、乐观锁以及悲观锁这三种方式来实现。 主要是一个用户表，它有一个年龄的字段，然后并发地对其加一，看看结果是否正确。

目录

• 背景
• 一些基础实现类
• 不加锁
• 乐观锁
• 悲观锁
• 总结

背景

对于一些并发量不是很高的场景，使用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)

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

总结

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