基于MySQL实现分布式锁的方案设计及问题解决咨询

一、锁表设计 #

先创建锁表，必须给lock_key加唯一索引，这是实现原子性的核心前提：

CREATE TABLE `distributed_lock` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `lock_key` VARCHAR(64) NOT NULL COMMENT '锁唯一标识',
  `holder` VARCHAR(128) NOT NULL COMMENT '锁持有者（建议用UUID+线程ID生成）',
  `expired_time` DATETIME NOT NULL COMMENT '锁过期时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_lock_key` (`lock_key`) COMMENT '唯一索引，确保同个锁只能存在一条记录'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='分布式锁表';

二、核心方法实现 #

1. lock() 方法（阻塞直到获取锁） #

核心逻辑：用INSERT ... ON DUPLICATE KEY UPDATE实现原子性的“不存在则插入，存在则仅在锁过期时更新持有者和过期时间”，循环重试直到成功获取锁。

// 常量定义：锁过期时间（单位：秒），根据业务最大耗时设置
private static final int LOCK_EXPIRE_SECONDS = 30;

public void lock(String lockKey) {
    String holder = generateUniqueHolder();
    while (true) {
        try {
            int affectedRows = jdbcTemplate.update(
                "INSERT INTO distributed_lock (lock_key, holder, expired_time) " +
                "VALUES (?, ?, DATE_ADD(NOW(), INTERVAL ? SECOND)) " +
                "ON DUPLICATE KEY UPDATE " +
                "holder = IF(expired_time < NOW(), ?, holder), " +
                "expired_time = IF(expired_time < NOW(), DATE_ADD(NOW(), INTERVAL ? SECOND), expired_time)",
                lockKey, holder, LOCK_EXPIRE_SECONDS,
                holder, LOCK_EXPIRE_SECONDS
            );
            if (affectedRows > 0) {
                // 二次验证，确保当前线程确实拿到锁
                String currentHolder = jdbcTemplate.queryForObject(
                    "SELECT holder FROM distributed_lock WHERE lock_key = ? FOR UPDATE",
                    new Object[]{lockKey}, String.class
                );
                if (holder.equals(currentHolder)) {
                    return;
                }
            }
        } catch (Exception e) {
            // 捕获死锁异常，直接重试
            if (e.getCause() instanceof MySQLTransactionRollbackException) {
                continue;
            }
            throw new RuntimeException("获取锁失败", e);
        }
        // 重试前短暂休眠，避免CPU空转
        try {
            TimeUnit.MILLISECONDS.sleep(100);
        } catch (InterruptedException ignored) {
            Thread.currentThread().interrupt();
            throw new RuntimeException("线程中断", ignored);
        }
    }
}

2. tryLock() 方法（非阻塞，立即返回结果） #

核心逻辑：仅执行一次原子操作，验证是否成功获取锁，失败直接返回false。

public boolean tryLock(String lockKey) {
    String holder = generateUniqueHolder();
    try {
        int affectedRows = jdbcTemplate.update(
            "INSERT INTO distributed_lock (lock_key, holder, expired_time) " +
            "VALUES (?, ?, DATE_ADD(NOW(), INTERVAL ? SECOND)) " +
            "ON DUPLICATE KEY UPDATE " +
            "holder = IF(expired_time < NOW(), ?, holder), " +
            "expired_time = IF(expired_time < NOW(), DATE_ADD(NOW(), INTERVAL ? SECOND), expired_time)",
            lockKey, holder, LOCK_EXPIRE_SECONDS,
            holder, LOCK_EXPIRE_SECONDS
        );
        if (affectedRows == 0) {
            return false;
        }
        String currentHolder = jdbcTemplate.queryForObject(
            "SELECT holder FROM distributed_lock WHERE lock_key = ? FOR UPDATE",
            new Object[]{lockKey}, String.class
        );
        return holder.equals(currentHolder);
    } catch (Exception e) {
        if (e.getCause() instanceof MySQLTransactionRollbackException) {
            return false;
        }
        throw new RuntimeException("尝试获取锁失败", e);
    }
}

3. tryLock(long time, TimeUnit unit) 方法（阻塞指定时间，超时返回） #

核心逻辑：计算超时截止时间，循环尝试获取锁，直到超时或成功。

public boolean tryLock(String lockKey, long time, TimeUnit unit) throws InterruptedException {
    long endTime = System.currentTimeMillis() + unit.toMillis(time);
    String holder = generateUniqueHolder();
    while (System.currentTimeMillis() < endTime) {
        try {
            int affectedRows = jdbcTemplate.update(
                "INSERT INTO distributed_lock (lock_key, holder, expired_time) " +
                "VALUES (?, ?, DATE_ADD(NOW(), INTERVAL ? SECOND)) " +
                "ON DUPLICATE KEY UPDATE " +
                "holder = IF(expired_time < NOW(), ?, holder), " +
                "expired_time = IF(expired_time < NOW(), DATE_ADD(NOW(), INTERVAL ? SECOND), expired_time)",
                lockKey, holder, LOCK_EXPIRE_SECONDS,
                holder, LOCK_EXPIRE_SECONDS
            );
            if (affectedRows > 0) {
                String currentHolder = jdbcTemplate.queryForObject(
                    "SELECT holder FROM distributed_lock WHERE lock_key = ? FOR UPDATE",
                    new Object[]{lockKey}, String.class
                );
                if (holder.equals(currentHolder)) {
                    return true;
                }
            }
        } catch (Exception e) {
            if (!(e.getCause() instanceof MySQLTransactionRollbackException)) {
                throw new RuntimeException("尝试获取锁失败", e);
            }
        }
        // 休眠后重试，避免CPU空转
        TimeUnit.MILLISECONDS.sleep(100);
        if (Thread.currentThread().isInterrupted()) {
            throw new InterruptedException();
        }
    }
    return false;
}

4. unlock() 方法（释放锁） #

核心逻辑：仅允许锁持有者删除锁记录，防止误释放其他线程的锁。

public void unlock(String lockKey) {
    String holder = generateUniqueHolder();
    int affectedRows = jdbcTemplate.update(
        "DELETE FROM distributed_lock WHERE lock_key = ? AND holder = ?",
        lockKey, holder
    );
    if (affectedRows == 0) {
        throw new IllegalStateException("当前线程不是锁持有者，无法释放锁");
    }
}

三、关键细节说明 #

• 唯一持有者生成：generateUniqueHolder()方法建议返回UUID.randomUUID().toString() + "-" + Thread.currentThread().getId()，确保每个线程的持有者标识唯一。
• 锁过期时间：LOCK_EXPIRE_SECONDS需设置为业务最大执行耗时+冗余时间，避免线程崩溃导致锁永久无法释放。
• 事务隔离：锁操作建议使用独立事务（可通过PROPAGATION_REQUIRES_NEW传播级别），不要和业务事务绑定，防止事务提交延迟导致锁释放不及时。
• 死锁处理：所有锁操作中，死锁异常直接重试或返回失败，其他异常抛出运行时异常。

内容的提问来源于stack exchange，提问作者Emiya Elien