MySQL分布式鎖可以解決多個進程或者多個服務器間相互競爭同一個資源而導致資源沖突問題。在Java中可以通過使用MySQL的一個表來實現該功能。

CREATE TABLE `distributed_lock` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`resource_name` varchar(255) DEFAULT NULL COMMENT '資源名稱',
`current_owner` varchar(32) DEFAULT NULL COMMENT '當前持有者',
`expire_time` bigint(20) DEFAULT NULL COMMENT '失效時間',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='分布式鎖表';

該表中的字段含義：

• id：主鍵
• resource_name：資源名稱
• current_owner：當前持有者
• expire_time：失效時間

Java實現過程如下：

public boolean lock(String resourceName, String ownerId, long expireSeconds) {
long expireTime = System.currentTimeMillis() + expireSeconds * 1000;
String querySql = "SELECT * FROM distributed_lock WHERE resource_name='" + resourceName + "' LIMIT 1;";
DistributedLockObject lockObject = getLockObjectFromDB(querySql);
if (lockObject == null) {
String insertSql = "INSERT INTO distributed_lock (resource_name, current_owner, expire_time) VALUES ('" + resourceName + "','" + ownerId + "'," + expireTime + ");";
return executeSql(insertSql);
} else {
if (lockObject.getCurrentOwner().equals(ownerId)) {
// 當前持有者是自己，則續約
String updateSql = "UPDATE distributed_lock SET expire_time=" + expireTime + " WHERE id=" + lockObject.getId() + ";";
return executeSql(updateSql);
} else {
// 當前持有者不是自己，則說明鎖已經被其他進程或者服務器持有
if (lockObject.getExpireTime()< System.currentTimeMillis()) {
// 鎖已經過期，則可以爭奪鎖
String updateSql = "UPDATE distributed_lock SET current_owner='" + ownerId + "',expire_time=" + expireTime + " WHERE id=" + lockObject.getId() + ";";
return executeSql(updateSql);
} else {
// 鎖未過期，則無法爭奪到鎖
return false;
}
}
}
}
public boolean unlock(String resourceName, String ownerId) {
String querySql = "SELECT * FROM distributed_lock WHERE resource_name='" + resourceName + "' LIMIT 1;";
DistributedLockObject lockObject = getLockObjectFromDB(querySql);
if (lockObject == null) {
// 分布式鎖不存在
return true;
}
if (lockObject.getCurrentOwner().equals(ownerId)) {
String deleteSql = "DELETE FROM distributed_lock WHERE id=" + lockObject.getId() + ";";
return executeSql(deleteSql);
} else {
// 當前持有者不是自己，則無法解鎖
return false;
}
}
private DistributedLockObject getLockObjectFromDB(String querySql) {
try (Connection conn = dataSource.getConnection();
PreparedStatement ps = conn.prepareStatement(querySql);
ResultSet rs = ps.executeQuery()) {
if (rs.next()) {
return new DistributedLockObject(rs.getLong("id"), rs.getString("resource_name"), rs.getString("current_owner"), rs.getLong("expire_time"));
} else {
return null;
}
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
private boolean executeSql(String sql) {
try (Connection conn = dataSource.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
int result = ps.executeUpdate();
return result == 1;
} catch (Exception e) {
e.printStackTrace();
return false;
}
}

使用上述Java代碼可以實現MySQL分布式鎖的獲取和釋放。