【分布式锁】通过MySQL数据库的表来实现-V1

【分布式锁】通过MySQL数据库的表来实现-V1

一、来源

之所以要写这篇文章是因为想对自己当前的分布式知识做一个归纳。今天就先推出一篇MySQL实现的分布式锁，后续会继续推出其他版本的分布式锁，比如通过Zookeeper、Redis实现等。

二、正题

要想通过MySQL来实现分布式锁，那么必定是需要一个唯一的特性才可以实现，比如主键、唯一索引这类。因为锁是为了限制资源的同步访问，也就是一个瞬间只能有一个线程去访问该资源。分布式锁就是为了解决这个资源的访问竞争问题。

那么，主键这个方式是不建议使用的，因为我们的锁有可能是各种字符串，虽然字符串也可以当作主键来使用，但是这样会让插入变得完全随机，进而触发页分裂。所以站在性能角度，MySQL分布式锁这一块不使用主键来实现，而采用唯一索引的方式来实现。

直接上数据库脚本：

DROP TABLE IF EXISTS `distribute_lock_info`;CREATE TABLE `distribute_lock_info` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增ID', `lock_key` varchar(100) NOT NULL COMMENT '加锁Key', `lock_value` varchar(100) NOT NULL COMMENT '加锁Value', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `expire_time` datetime DEFAULT NULL COMMENT '过期时间', PRIMARY KEY (`id`), UNIQUE KEY `uidx_lock_key` (`lock_key`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=21884 DEFAULT CHARSET=utf8mb4;

这里主要的是3个字段：加锁key（唯一索引），加锁value，过期时间。id采用自增，保证顺序性。两个时间主要是作为一个补充信息，非必需字段。

ok，那么到这里，一张基本的分布式锁的表设计就已经完成了。这里的唯一索引是实现互斥的一个重点。

接着就开始代码的编写了，先来一份依赖文件。

org.projectlombok lombok false org.springframework.boot spring-boot-starter-web com.baomidou mybatis-plus-boot-starter com.baomidou mybatis-plus-generator 3.2.0 mysql mysql-connector-java

分布式锁有个过期时间，那么就需要定时清除这些过期的锁信息，这也是预防死锁的一个手段。所以，我们可以编写一个清除的定时任务，来帮助我们清除过期的锁信息。代码如下：

package cn.lxw.task;import cn.lxw.configdb.DistributeLockInfoMapper;import cn.lxw.entity.DistributeLockInfo;import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;import org.springframework.context.annotation.Configuration;import org.springframework.scheduling.annotation.EnableScheduling;import org.springframework.scheduling.annotation.Scheduled;import javax.annotation.Resource;import java.time.LocalDateTime;@Configuration@EnableSchedulingpublic class LockCleanTask { @Resource private DistributeLockInfoMapper lockInfoMapper; /** * 功能描述:
* 〈Clean the lock which is expired.〉 * @Param: [] * @Return: {@link Void} * @Author: luoxw * @Date: 2021/7/26 20:13 */ @Scheduled(cron = "0/6 * * * * *") public void cleanExpireLock() { int deleteResult = lockInfoMapper.delete(new UpdateWrapper() { { le("expire_time", LocalDateTime.now()); } }); System.out.println("[LockCleanTask]The count of expired lock is " + deleteResult + "!"); }}

清除任务搞定，那么我们可以开始建立数据库的分布式锁那张表对应的实体类，方便后面操作表数据。

package cn.lxw.entity;import com.baomidou.mybatisplus.annotation.IdType;import com.baomidou.mybatisplus.annotation.TableField;import com.baomidou.mybatisplus.annotation.TableId;import com.baomidou.mybatisplus.annotation.TableName;import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;import java.io.Serializable;import java.time.LocalDateTime;/** * 功能描述:
* 〈The entity of ditribute_lock_info table in database.〉 * @Param: * @Return: {@link } * @Author: luoxw * @Date: 2021/7/26 20:19 */@Data@AllArgsConstructor@NoArgsConstructor@TableName("distribute_lock_info")public class DistributeLockInfo implements Serializable { @TableId(value = "id", type = IdType.AUTO) private Long id; @TableField("lock_key") private String lockKey; @TableField("lock_value") private String lockValue; @TableField("create_time") private LocalDateTime createTime; @TableField("update_time") private LocalDateTime updateTime; @TableField("expire_time") private LocalDateTime expireTime; @Override public String toString() { return "DistributeLockInfo{" + "id=" + id + ", lockKey='" + lockKey + '\'' + ", lockValue='" + lockValue + '\'' + ", createTime=" + createTime + ", updateTime=" + updateTime + ", expireTime=" + expireTime + '}'; }}

接着，就是编写这张表对应的增删改查操作了。这里我采用的是Mybatis-Plus，这样比较快捷一些。

package cn.lxw.configdb;import cn.lxw.entity.DistributeLockInfo;import com.baomidou.mybatisplus.core.mapper.BaseMapper;/** * 功能描述:
* 〈Judge by whether the record insert success or not to prove that lock-operation is whether success or not.So I need to define a method which can ignore insert when failed.〉 * @Param: * @Return: {@link } * @Author: luoxw * @Date: 2021/7/26 20:19 */public interface DistributeLockInfoMapper extends BaseMapper { int insertIgnore(DistributeLockInfo entity);}

package cn.lxw.service;import cn.lxw.entity.DistributeLockInfo;import java.util.concurrent.TimeUnit;public interface ILockService { /** * 功能描述:
* 〈Lock until success!〉 * @Param: [lockKey, lockValue] * @Return: {@link Void} * @Author: luoxw * @Date: 2021/7/26 20:14 */ void lock(String lockKey,String lockValue); /** * 功能描述:
* 〈Lock method, return the result immediately if failed .〉 * @Param: [lockKey, lockValue] * @Return: {@link boolean} * @Author: luoxw * @Date: 2021/7/26 20:14 */ boolean tryLock(String lockKey,String lockValue); /** * 功能描述:
* 〈Lock with a timeout param, return the result immediately if failed.If lock success and it is expired,will be freed by LockCleanTask {@link cn.lxw.task.LockCleanTask}〉 * @Param: [lockKey, lockValue, expireTime, unit] * @Return: {@link boolean} * @Author: luoxw * @Date: 2021/7/26 20:14 */ boolean tryLock(String lockKey,String lockValue,long expireTime, TimeUnit unit); /** * 功能描述:
* 〈Unlock with lockKey & lockValue.If doesn't matched,will be lock failed.〉 * @Param: [lockKey, lockValue] * @Return: {@link boolean} * @Author: luoxw * @Date: 2021/7/26 20:14 */ boolean unlock(String lockKey,String lockValue); /** * 功能描述:
* 〈Get lock info by lockKey!〉 * @Param: [lockKey] * @Return: {@link DistributeLockInfo} * @Author: luoxw * @Date: 2021/7/26 20:14 */ DistributeLockInfo getLock(String lockKey);}

接着就是通过数据库的增删改查操作去实现这些API。

package cn.lxw.service.impl;import cn.lxw.configdb.DistributeLockInfoMapper;import cn.lxw.entity.DistributeLockInfo;import cn.lxw.service.ILockService;import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;import org.springframework.context.annotation.Primary;import org.springframework.stereotype.Service;import javax.annotation.Resource;import java.time.LocalDateTime;import java.util.concurrent.TimeUnit;@Service@Primarypublic class MysqlDistributeLockServiceImpl implements ILockService { @Resource private DistributeLockInfoMapper lockInfoMapper; /** * 功能描述:
* 〈Lock until success!〉 * @Param: [lockKey, lockValue] * @Return: {@link Void} * @Author: luoxw * @Date: 2021/7/26 20:14 */ @Override public void lock(String lockKey, String lockValue) { int insertResult = 0; // trying until success while(insertResult < 1) { insertResult = lockInfoMapper.insertIgnore(new DistributeLockInfo() { { setLockKey(lockKey); setLockValue(lockValue); } }); } } /** * 功能描述:
* 〈Lock method, return the result immediately if failed .〉 * @Param: [lockKey, lockValue] * @Return: {@link boolean} * @Author: luoxw * @Date: 2021/7/26 20:14 */ @Override public boolean tryLock(String lockKey, String lockValue) { int insertResult = lockInfoMapper.insertIgnore(new DistributeLockInfo() { { setLockKey(lockKey); setLockValue(lockValue); } }); return insertResult == 1; } /** * 功能描述:
* 〈Lock with a timeout param, return the result immediately if failed.If lock success and it is expired,will be freed by LockCleanTask {@link cn.lxw.task.LockCleanTask}〉 * @Param: [lockKey, lockValue, expireTime, unit] * @Return: {@link boolean} * @Author: luoxw * @Date: 2021/7/26 20:14 */ @Override public boolean tryLock(String lockKey, String lockValue, long expireTime, TimeUnit unit) { long expireNanos = unit.toNanos(expireTime); LocalDateTime expireDateTime = LocalDateTime.now().plusNanos(expireNanos); int insertResult = lockInfoMapper.insertIgnore(new DistributeLockInfo() { { setLockKey(lockKey); setLockValue(lockValue); setExpireTime(expireDateTime); } }); return insertResult == 1; } /** * 功能描述:
* 〈Unlock with lockKey & lockValue.If doesn't matched,will be lock failed.〉 * @Param: [lockKey, lockValue] * @Return: {@link boolean} * @Author: luoxw * @Date: 2021/7/26 20:14 */ @Override public boolean unlock(String lockKey, String lockValue) { int deleteResult = lockInfoMapper.delete(new UpdateWrapper() { { eq("lock_key", lockKey); eq("lock_value", lockValue); } }); return deleteResult == 1; } /** * 功能描述:
* 〈Get lock info by lockKey!〉 * @Param: [lockKey] * @Return: {@link DistributeLockInfo} * @Author: luoxw * @Date: 2021/7/26 20:14 */ @Override public DistributeLockInfo getLock(String lockKey) { return lockInfoMapper.selectOne(new QueryWrapper(){ { eq("lock_key",lockKey); } }); }}

理解起来没有那么困难，【加锁】实际就是添加一条记录，【解锁】就是删除这条记录，【获取锁信息】就是查询出这条记录，【加过期时间的锁】就是添加记录的时候多设置一个过期时间。

这样的话，就可以进行测试工作了。测试之前，需要先准备一个锁信息的生成工具类，帮助我们生成统一格式的锁信息。主要的锁信息有：IP+节点ID+线程ID+线程名称+时间戳。这个锁信息一方面是为了解锁的时候是唯一值，不会误解掉别人的锁，还有一方面是可以提供有效的信息帮助你排查问题。

package cn.lxw.util;/** * 功能描述:
* 〈A string util of lock.〉 * @Param: * @Return: {@link } * @Author: luoxw * @Date: 2021/7/26 20:09 */public class LockInfoUtil { private static final String LOCAL_IP = "192.168.8.8"; private static final String NODE_ID = "node1"; private static final String STR_SPILT = "-"; private static final String STR_LEFT = "["; private static final String STR_RIGHT = "]"; /** * 功能描述:
* 〈Return the unique String value of lock info.〉 * "[192.168.8.8]-[node1]-[37]-[pool-1-thread-1]-[1627301265325]" * @Param: [] * @Return: {@link String} * @Author: luoxw * @Date: 2021/7/26 20:08 */ public static String createLockValue(){ StringBuilder stringBuilder = new StringBuilder(); stringBuilder .append(STR_LEFT) .append(LOCAL_IP) .append(STR_RIGHT) .append(STR_SPILT) .append(STR_LEFT) .append(NODE_ID) .append(STR_RIGHT) .append(STR_SPILT) .append(STR_LEFT) .append(Thread.currentThread().getId()) .append(STR_RIGHT) .append(STR_SPILT) .append(STR_LEFT) .append(Thread.currentThread().getName()) .append(STR_RIGHT) .append(STR_SPILT) .append(STR_LEFT) .append(System.currentTimeMillis()) .append(STR_RIGHT); return stringBuilder.toString(); }}

测试开始，我这边直接通过main函数进行测试工作。大家有觉得不妥的，可以写个test类，效果是一样的。

package cn.lxw;import cn.lxw.entity.DistributeLockInfo;import cn.lxw.service.ILockService;import cn.lxw.util.LockInfoUtil;import org.mybatis.spring.annotation.MapperScan;import org.springframework.boot.SpringApplication;import org.springframework.boot.autoconfigure.SpringBootApplication;import org.springframework.context.ConfigurableApplicationContext;import org.springframework.scheduling.annotation.EnableScheduling;import org.springframework.transaction.annotation.EnableTransactionManagement;import java.util.concurrent.LinkedBlockingQueue;import java.util.concurrent.ThreadPoolExecutor;import java.util.concurrent.TimeUnit;@SpringBootApplication@EnableTransactionManagement@MapperScan("cn.lxw")@EnableSchedulingpublic class MainApp { /** * 功能描述:
* 〈DistributeLock testing start here!〉 * * @Param: [args] * @Return: {@link Void} * @Author: luoxw * @Date: 2021/7/26 18:20 */ public static void main(String[] args) { // run the springboot app ConfigurableApplicationContext context = SpringApplication.run(MainApp.class, args); // define some lock infomation final String lockKey = "lock_test"; ILockService lockService = context.getBean(ILockService.class); // create a ThreadPoolExecutor ThreadPoolExecutor tpe = new ThreadPoolExecutor(5, 5, 60, TimeUnit.SECONDS, new LinkedBlockingQueue<>(10)); // execute the simulator for (int i = 0; i < 3; i++) { tpe.execute(() -> { while (true) { // get the unique lock value of current thread String lockValue = LockInfoUtil.createLockValue(); // start lock the lockKey boolean tryLockResult = lockService.tryLock(lockKey, lockValue, 10L, TimeUnit.SECONDS); // get the most new lock info with lockKey DistributeLockInfo currentLockInfo = lockService.getLock(lockKey); System.out.println("[LockThread]Thread[" + Thread.currentThread().getId() + "] lock result:" + tryLockResult + ",current lock info:" + (currentLockInfo==null?"null":currentLockInfo.toString())); // here do some business opearation try { TimeUnit.SECONDS.sleep((int) (Math.random() * 10)); } catch (InterruptedException e) { e.printStackTrace(); } // continue to fight for lock if failed if(!tryLockResult){ continue; } // start unlock the lockKey with lockKey & lockValue lockService.unlock(lockKey, lockValue); } }); } }}

查看日志，是否满足分布式锁的要求：同一个瞬间，必然只有一个线程可以争抢到对应资源的锁。

2021-07-27 20:33:40.764 INFO 14128 --- [pool-1-thread-1] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...2021-07-27 20:33:40.972 INFO 14128 --- [pool-1-thread-1] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.[LockThread]Thread[39] lock result:false,current lock info:DistributeLockInfo{id=22354, lockKey='lock_test', lockValue='[192.168.8.8]-[node1]-[38]-[pool-1-thread-2]-[1627389195666]', createTime=2021-07-27T20:33:15, updateTime=2021-07-27T20:33:15, expireTime=2021-07-27T20:33:26}[LockThread]Thread[37] lock result:false,current lock info:DistributeLockInfo{id=22354, lockKey='lock_test', lockValue='[192.168.8.8]-[node1]-[38]-[pool-1-thread-2]-[1627389195666]', createTime=2021-07-27T20:33:15, updateTime=2021-07-27T20:33:15, expireTime=2021-07-27T20:33:26}[LockThread]Thread[38] lock result:false,current lock info:DistributeLockInfo{id=22354, lockKey='lock_test', lockValue='[192.168.8.8]-[node1]-[38]-[pool-1-thread-2]-[1627389195666]', createTime=2021-07-27T20:33:15, updateTime=2021-07-27T20:33:15, expireTime=2021-07-27T20:33:26}[LockThread]Thread[38] lock result:false,current lock info:DistributeLockInfo{id=22354, lockKey='lock_test', lockValue='[192.168.8.8]-[node1]-[38]-[pool-1-thread-2]-[1627389195666]', createTime=2021-07-27T20:33:15, updateTime=2021-07-27T20:33:15, expireTime=2021-07-27T20:33:26}[LockCleanTask]The count of expired lock is 1![LockThread]Thread[37] lock result:true,current lock info:DistributeLockInfo{id=22362, lockKey='lock_test', lockValue='[192.168.8.8]-[node1]-[37]-[pool-1-thread-1]-[1627389224033]', createTime=2021-07-27T20:33:44, updateTime=2021-07-27T20:33:44, expireTime=2021-07-27T20:33:54}[LockThread]Thread[38] lock result:false,current lock info:DistributeLockInfo{id=22362, lockKey='lock_test', lockValue='[192.168.8.8]-[node1]-[37]-[pool-1-thread-1]-[1627389224033]', createTime=2021-07-27T20:33:44, updateTime=2021-07-27T20:33:44, expireTime=2021-07-27T20:33:54}[LockThread]Thread[37] lock result:true,current lock info:DistributeLockInfo{id=22364, lockKey='lock_test', lockValue='[192.168.8.8]-[node1]-[37]-[pool-1-thread-1]-[1627389224067]', createTime=2021-07-27T20:33:44, updateTime=2021-07-27T20:33:44, expireTime=2021-07-27T20:33:54}[LockThread]Thread[37] lock result:true,current lock info:DistributeLockInfo{id=22365, lockKey='lock_test', lockValue='[192.168.8.8]-[node1]-[37]-[pool-1-thread-1]-[1627389225085]', createTime=2021-07-27T20:33:45, updateTime=2021-07-27T20:33:45, expireTime=2021-07-27T20:33:55}[LockThread]Thread[39] lock result:false,current lock info:DistributeLockInfo{id=22365, lockKey='lock_test', lockValue='[192.168.8.8]-[node1]-[37]-[pool-1-thread-1]-[1627389225085]', createTime=2021-07-27T20:33:45, updateTime=2021-07-27T20:33:45, expireTime=2021-07-27T20:33:55}[LockCleanTask]The count of expired lock is 0![LockThread]Thread[38] lock result:false,current lock info:DistributeLockInfo{id=22365, lockKey='lock_test', lockValue='[192.168.8.8]-[node1]-[37]-[pool-1-thread-1]-[1627389225085]', createTime=2021-07-27T20:33:45, updateTime=2021-07-27T20:33:45, expireTime=2021-07-27T20:33:55}[LockThread]Thread[38] lock result:false,current lock info:DistributeLockInfo{id=22365, lockKey='lock_test', lockValue='[192.168.8.8]-[node1]-[37]-[pool-1-thread-1]-[1627389225085]', createTime=2021-07-27T20:33:45, updateTime=2021-07-27T20:33:45, expireTime=2021-07-27T20:33:55}[LockThread]Thread[39] lock result:false,current lock info:DistributeLockInfo{id=22365, lockKey='lock_test', lockValue='[192.168.8.8]-[node1]-[37]-[pool-1-thread-1]-[1627389225085]', createTime=2021-07-27T20:33:45, updateTime=2021-07-27T20:33:45, expireTime=2021-07-27T20:33:55}[LockThread]Thread[37] lock result:true,current lock info:DistributeLockInfo{id=22370, lockKey='lock_test', lockValue='[192.168.8.8]-[node1]-[37]-[pool-1-thread-1]-[1627389232106]', createTime=2021-07-27T20:33:52, updateTime=2021-07-27T20:33:52, expireTime=2021-07-27T20:34:02}[LockCleanTask]The count of expired lock is 0![LockThread]Thread[38] lock result:false,current lock info:DistributeLockInfo{id=22370, lockKey='lock_test', lockValue='[192.168.8.8]-[node1]-[37]-[pool-1-thread-1]-[1627389232106]', createTime=2021-07-27T20:33:52, updateTime=2021-07-27T20:33:52, expireTime=2021-07-27T20:34:02}[LockThread]Thread[38] lock result:false,current lock info:DistributeLockInfo{id=22370, lockKey='lock_test', lockValue='[192.168.8.8]-[node1]-[37]-[pool-1-thread-1]-[1627389232106]', createTime=2021-07-27T20:33:52, updateTime=2021-07-27T20:33:52, expireTime=2021-07-27T20:34:02}[LockThread]Thread[38] lock result:false,current lock info:DistributeLockInfo{id=22370, lockKey='lock_test', lockValue='[192.168.8.8]-[node1]-[37]-[pool-1-thread-1]-[1627389232106]', createTime=2021-07-27T20:33:52, updateTime=2021-07-27T20:33:52, expireTime=2021-07-27T20:34:02}

大家可以自己按照这个思路去调整一下代码验证一下。

三、结论

结论是通过MySQL我们是可以实现分布式锁的，而且十分简单，一张表，一点代码就可以搞定。但是，它的本质是通过数据库的锁来实现的，所以这么做会增加数据库的负担。而且数据库实现的锁性能是有瓶颈的，不能满足性能高的业务场景。所以，性能低的业务下玩玩是可以的。

OK，本篇MySQL实现分布式锁介绍结束，欢迎关注下一篇分布式锁V2的实现。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。