基于Mysql数据库（RDBMS）的分布式锁

矫情吗；* 2022-02-23 03:26 187阅读 0赞

**【订阅[专栏合集][Link 1],作者所有付费文章都能看(持续更新)】**

推荐【**Kafka教程**】[https://bigbird.blog.csdn.net/article/details/108770504][https_bigbird.blog.csdn.net_article_details_108770504]  
推荐【**rabbitmq教程**】[https://bigbird.blog.csdn.net/article/details/81436980][https_bigbird.blog.csdn.net_article_details_81436980]  
推荐【**Flink教程**】[https://blog.csdn.net/hellozpc/article/details/109413465][https_blog.csdn.net_hellozpc_article_details_109413465]  
推荐【**JVM面试与调优教程**】[https://bigbird.blog.csdn.net/article/details/113888604][https_bigbird.blog.csdn.net_article_details_113888604]  
推荐【**SpringBoot全套教程**】[https://blog.csdn.net/hellozpc/article/details/107095951][https_blog.csdn.net_hellozpc_article_details_107095951]  
推荐【**SpringCloud教程**】[https://blog.csdn.net/hellozpc/article/details/83692496][https_blog.csdn.net_hellozpc_article_details_83692496]  
推荐【**Mybatis教程**】[https://blog.csdn.net/hellozpc/article/details/80878563][https_blog.csdn.net_hellozpc_article_details_80878563]  
推荐【**SnowFlake教程**】[https://blog.csdn.net/hellozpc/article/details/108248227][https_blog.csdn.net_hellozpc_article_details_108248227]  
推荐【**并发限流教程**】[https://blog.csdn.net/hellozpc/article/details/107582771][https_blog.csdn.net_hellozpc_article_details_107582771]  
推荐【**Redis教程**】[https://bigbird.blog.csdn.net/article/details/81267030][https_bigbird.blog.csdn.net_article_details_81267030]  
推荐【**Netty教程**】[https://blog.csdn.net/hellozpc/category\_10945233.html][https_blog.csdn.net_hellozpc_category_10945233.html]

*  实现分布式锁有很多方案，常用的比如[redis][]， [ZooKeeper][]。  
    但是在简单场景（非高并发）下，我们不妨使用基于关系数据库的锁，实现简单，使用方便。
 *  锁本质上是一个标记。
 *  把这个标记放在关系数据库（RDBMS）中，我们就可以使用数据库的方式实现锁机制。比如设计一张锁表，表中有个字段state，state有两个值，分别表示锁定/未锁定状态；
 *  把这个标记放在zookeeper的节点中，我们就可以使用zk实现分布式锁；把这个标记放在内存中，比如设置一个 volatile变量state保存锁定/未锁定状态，就可以在java进程级别实现锁。

/**
     * 基于RDBMS的简单分布式锁
     *
     * @Author: zhoupengcheng
     */
    public interface LockService {
        public String lock(String lockName);
    
        public boolean unlock(String lockName,String lockNo);
    }

/**
     * @Author: zhoupengcheng
     */
    @Service
    public class LockServiceImpl implements LockService {
    
        @Resource
        SyncJobLockMapper syncJobLockMapper;
    
        @Override
        public String lock(String lockName) {
            SyncJobLock syncJobLock = new SyncJobLock();
            syncJobLock.setName(lockName);
            String lockNo = UuidUtil.genUuid();
            syncJobLock.setLockNo(lockNo);
            int i = syncJobLockMapper.updateByGetLock(syncJobLock);
            if (i == 1) {
                return lockNo;
            }
            return null;
        }
    
        @Override
        public boolean unlock(String lockName, String lockNo) {
            SyncJobLock syncJobLock = new SyncJobLock();
            syncJobLock.setName(lockName);
            syncJobLock.setLockNo(lockNo);
            int i = syncJobLockMapper.updateByUnLock(syncJobLock);
            if (i == 1) {
                return true;
            }
            return false;
        }
    }

数据库添加一张锁表：

create table t_locks
    (
    	id int unsigned auto_increment comment '自增id'
    		primary key,
    	name varchar(64) null comment '锁名',
    	status char default 'R' null comment '上锁标记,L=已锁,R=已释放',
    	remark varchar(64) null comment '备注',
    	lockNo varchar(64) null comment '锁序列号',
    	create_time timestamp default CURRENT_TIMESTAMP not null comment '创建时间',
    	update_time timestamp default CURRENT_TIMESTAMP not null comment '更新时间'
    )
    comment '分布式锁,job触发前先获取锁' charset=utf8;
    
    create index idx_name_status
    	on t_locks (name, status);

对应Java代码的Lock实体:

public class SyncJobLock {
        private Integer id;
    
        /**
         * 锁名
         */
        private String name;
    
        /**
         * 锁状态：L=已上锁,R=已释放
         */
        private String status;
    
        /**
         * 备注
         */
        private String remark;
    
        private Date createTime;
    
        private Date updateTime;
    
        /**
         * 锁序列号，用于解锁时判断(谁上的锁谁才能解)
         */
        private String lockNo;
    }

Mapper接口：

public interface SyncJobLockMapper {
    
        int updateByGetLock(SyncJobLock syncJobLock);
    
        int updateByUnLock(SyncJobLock syncJobLock);
    }

Mapper.xml文件：

<update id="updateByGetLock" parameterType="com.zpc.entity.SyncJobLock">
            UPDATE t_locks
            SET update_time = sysdate(),
                status = 'L',
                lockNo = #{lockNo}
            where name = #{name}
              and (status = 'R' or
                   (status = 'L' and (TIME_TO_SEC(SYSDATE()) - TIME_TO_SEC(update_time)) > 60))
        </update>
    
        <update id="updateByUnLock" parameterType="com.zpc.entity.SyncJobLock" >
            update t_locks t
            set t.status = 'R' , update_time = sysdate()
            where t.name = #{name}
              and t.status = 'L'
              and t.lockNo = #{lockNo}
        </update>

LockName 枚举，不同的业务可以定义不同的锁:

/**
     * 分布式环境下,每个job先获取锁再执行调度
     *
     * @Author: zhoupengcheng
     */
    public enum LockName {
    
        /**
         * 业务锁A
         */
        SYNC_SCHEDULE_LOCK("rollbackScheduleLock"),
    
        /**
         * 锁B
         */
        SYNC_DRIVERANDCAR_LOCK("syncDriverAndCarLock");
    
        private String name;
    
        LockName(String name) {
            this.name = name;
        }
    
        public String getName() {
            return name;
        }
    
    }

使用场景：比如分布式环境下的调度，防止调度没有执行完，另一台服务器又起了同样的调度，造成数据重复处理等异常情况。

@EnableScheduling
    	//业务使用
        @Scheduled(fixedDelay = 60000)
        private void startTransportationCapacity() {
            log.info("*************  startJob *************");
            long start = System.currentTimeMillis();
            boolean unlockable = true;//是否是有效的释放锁操作
            String lockNo = "";
            try {
                lockNo = lockService.lock(LockName.SYNC_DRIVERANDCAR_LOCK.getName());
                if (lockNo != null) {
                    log.info("获取锁成功,开始执行调度，lockname:{},lockNo:{}", LockName.SYNC_DRIVERANDCAR_LOCK.getName(), lockNo);
                    //这里写业务处理逻辑。。。。
                    //Thread.sleep(10000);
                } else {
                    log.info("获取锁失败，lockname:{}", LockName.SYNC_DRIVERANDCAR_LOCK.getName());
                    unlockable = false;
                }
            } catch (Exception e) {
                log.error("业务处理异常", e);
            } finally {
                if (unlockable) {
                    boolean unlockOk = lockService.unlock(LockName.SYNC_DRIVERANDCAR_LOCK.getName(), lockNo);
                    if (unlockOk) {
                        log.info("释放锁成功,lockname:{},lockNo:{}", LockName.SYNC_DRIVERANDCAR_LOCK.getName(), lockNo);
                    } else {
                        log.info("释放锁失败,lockname:{},lockNo:{}", LockName.SYNC_DRIVERANDCAR_LOCK.getName(), lockNo);
                    }
                }
            }
    
            long end = System.currentTimeMillis();
            log.info("*************  endJob,cost:{}ms *************", end - start);
        }

锁释放失败不用处理，在获取锁时加了过期时间判断（见sql）。

\*\*欢迎关注公众号\*\*

![20191121223121694.jpg][]

\*\*微信扫一扫\*\*

[Link 1]: https://blog.csdn.net/hellozpc/category_11127579.html
[https_bigbird.blog.csdn.net_article_details_108770504]: https://bigbird.blog.csdn.net/article/details/108770504
[https_bigbird.blog.csdn.net_article_details_81436980]: https://bigbird.blog.csdn.net/article/details/81436980
[https_blog.csdn.net_hellozpc_article_details_109413465]: https://blog.csdn.net/hellozpc/article/details/109413465
[https_bigbird.blog.csdn.net_article_details_113888604]: https://bigbird.blog.csdn.net/article/details/113888604
[https_blog.csdn.net_hellozpc_article_details_107095951]: https://blog.csdn.net/hellozpc/article/details/107095951
[https_blog.csdn.net_hellozpc_article_details_83692496]: https://blog.csdn.net/hellozpc/article/details/83692496
[https_blog.csdn.net_hellozpc_article_details_80878563]: https://blog.csdn.net/hellozpc/article/details/80878563
[https_blog.csdn.net_hellozpc_article_details_108248227]: https://blog.csdn.net/hellozpc/article/details/108248227
[https_blog.csdn.net_hellozpc_article_details_107582771]: https://blog.csdn.net/hellozpc/article/details/107582771
[https_bigbird.blog.csdn.net_article_details_81267030]: https://bigbird.blog.csdn.net/article/details/81267030
[https_blog.csdn.net_hellozpc_category_10945233.html]: https://blog.csdn.net/hellozpc/category_10945233.html
[redis]: https://blog.csdn.net/hellozpc/article/details/82953590
[ZooKeeper]: https://blog.csdn.net/hellozpc/article/details/80383883
[20191121223121694.jpg]: /images/20220223/8c226d04e4e7484a836df9f67f41d2ce.png