CREATE TABLE `lock_table` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  -- 鎖的唯一標(biāo)識
  `lock_name` VARCHAR(255) NOT NULL,
  -- 持有鎖的線程標(biāo)識
  `holder_thread` VARCHAR(255),
  -- 鎖的重入次數(shù)，用于實現(xiàn)可重入性
  `reentry_count` INT DEFAULT 0
);

1. 開啟事務(wù)
2. 執(zhí)行 SQL：
   SELECT holder_thread, reentry_count 
   FROM lock_table 
   WHERE lock_name = ? FOR UPDATE;
   - 若記錄不存在：執(zhí)行 INSERT INTO lock_table (lock_name, holder_thread, reentry_count) VALUES (?, ?, 1)
   - 若記錄存在且持有者是當(dāng)前線程：執(zhí)行 UPDATE lock_table SET reentry_count = reentry_count + 1 WHERE lock_name = ?
3. 提交事務(wù)

1. 開啟事務(wù)
2. 執(zhí)行 SQL：
   SELECT holder_thread, reentry_count 
   FROM lock_table 
   WHERE lock_name = ? FOR UPDATE;
   - 若記錄存在、持有者是當(dāng)前線程且重入次數(shù) > 1：執(zhí)行 UPDATE lock_table SET reentry_count = reentry_count - 1 WHERE lock_name = ?
   - 若記錄存在、持有者是當(dāng)前線程且重入次數(shù) ≤ 1：執(zhí)行 DELETE FROM lock_table WHERE lock_name = ?
3. 提交事務(wù)

public boolean lock(String lockName, String threadName) {
    Connection conn = getConnection();
    try {
        // 1. 開啟事務(wù)
        conn.setAutoCommit(false);
        // 2. 查詢鎖狀態(tài)（加排他鎖）
        String querySql = "SELECT holder_thread, reentry_count FROM lock_table WHERE lock_name = ? FOR UPDATE";
        try (PreparedStatement ps = conn.prepareStatement(querySql)) {
            ps.setString(1, lockName);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                String holder = rs.getString("holder_thread");
                int count = rs.getInt("reentry_count");
                if (threadName.equals(holder)) {
                    // 可重入：重入次數(shù)+1
                    String updateSql = "UPDATE lock_table SET reentry_count = reentry_count + 1 WHERE lock_name = ?";
                    try (PreparedStatement updatePs = conn.prepareStatement(updateSql)) {
                        updatePs.setString(1, lockName);
                        updatePs.executeUpdate();
                    }
                } else {
                    // 被其他線程持有，獲取鎖失敗
                    conn.rollback();
                    return false;
                }
            } else {
                // 鎖不存在，直接加鎖
                String insertSql = "INSERT INTO lock_table (lock_name, holder_thread, reentry_count) VALUES (?, ?, 1)";
                try (PreparedStatement insertPs = conn.prepareStatement(insertSql)) {
                    insertPs.setString(1, lockName);
                    insertPs.setString(2, threadName);
                    insertPs.executeUpdate();
                }
            }
        }
        // 3. 提交事務(wù)
        conn.commit();
        return true;
    } catch (Exception e) {
        // 異?；貪L
        conn.rollback();
        return false;
    } finally {
        conn.setAutoCommit(true);
        closeConnection(conn);
    }
}

public boolean unlock(String lockName, String threadName) {
    Connection conn = getConnection();
    try {
        // 1. 開啟事務(wù)
        conn.setAutoCommit(false);
        // 2. 查詢鎖狀態(tài)
        String querySql = "SELECT holder_thread, reentry_count FROM lock_table WHERE lock_name = ? FOR UPDATE";
        try (PreparedStatement ps = conn.prepareStatement(querySql)) {
            ps.setString(1, lockName);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                String holder = rs.getString("holder_thread");
                int count = rs.getInt("reentry_count");
                if (!threadName.equals(holder)) {
                    // 不是鎖持有者，解鎖失敗
                    conn.rollback();
                    return false;
                }
                if (count > 1) {
                    // 重入次數(shù)>1，僅減1
                    String updateSql = "UPDATE lock_table SET reentry_count = reentry_count - 1 WHERE lock_name = ?";
                    try (PreparedStatement updatePs = conn.prepareStatement(updateSql)) {
                        updatePs.setString(1, lockName);
                        updatePs.executeUpdate();
                    }
                } else {
                    // 重入次數(shù)=1，刪除鎖記錄
                    String deleteSql = "DELETE FROM lock_table WHERE lock_name = ?";
                    try (PreparedStatement deletePs = conn.prepareStatement(deleteSql)) {
                        deletePs.setString(1, lockName);
                        deletePs.executeUpdate();
                    }
                }
            } else {
                // 鎖不存在，解鎖失敗
                conn.rollback();
                return false;
            }
        }
        // 3. 提交事務(wù)
        conn.commit();
        return true;
    } catch (Exception e) {
        conn.rollback();
        return false;
    } finally {
        conn.setAutoCommit(true);
        closeConnection(conn);
    }
}