分布式锁Mysql实现

使用MySQL实现分布式锁可以借助于数据库的事务和行级锁来实现。下面是一个简单的Java代码示例，演示如何在MySQL中实现分布式锁。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class DistributedLockMySQL {

    private static final String DB_URL = "jdbc:mysql://localhost:3306/your_database";
    private static final String DB_USER = "your_username";
    private static final String DB_PASSWORD = "your_password";

    private static Connection connection;

    public static void main(String[] args) {
        // Simulating multiple threads trying to acquire the lock
        for (int i = 0; i < 5; i++) {
            new Thread(() -> {
                try {
                    boolean acquired = acquireLock("my_lock");
                    if (acquired) {
                        System.out.println(Thread.currentThread().getName() + " acquired the lock.");
                        // Do some work while holding the lock
                        Thread.sleep(2000);
                        releaseLock("my_lock");
                        System.out.println(Thread.currentThread().getName() + " released the lock.");
                    } else {
                        System.out.println(Thread.currentThread().getName() + " failed to acquire the lock.");
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }).start();
        }
    }

    public static synchronized boolean acquireLock(String lockName) throws SQLException {
        if (connection == null || connection.isClosed()) {
            connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
        }

        // Use a transaction and SELECT ... FOR UPDATE to acquire the lock
        String selectForUpdateQuery = "SELECT * FROM locks WHERE lock_name = ? FOR UPDATE";
        try (PreparedStatement preparedStatement = connection.prepareStatement(selectForUpdateQuery)) {
            preparedStatement.setString(1, lockName);
            preparedStatement.executeQuery(); // This will lock the row until the transaction is completed
            return true; // Lock acquired
        } catch (SQLException e) {
            return false; // Lock not acquired
        }
    }

    public static synchronized void releaseLock(String lockName) throws SQLException {
        // Use a transaction to release the lock
        String deleteQuery = "DELETE FROM locks WHERE lock_name = ?";
        try (PreparedStatement preparedStatement = connection.prepareStatement(deleteQuery)) {
            preparedStatement.setString(1, lockName);
            preparedStatement.executeUpdate();
        }
    }
}

在这个示例中，我们通过数据库表 locks 中的一行来表示分布式锁，lock_name 是锁的名称。acquireLock 方法使用 SELECT ... FOR UPDATE 语句来获取锁，并将锁的信息插入到数据库中。releaseLock 方法通过删除对应的行来释放锁。

需要注意的是，这只是一个简单的示例，实际应用中还需要处理数据库连接的异常、事务的回滚等情况。另外，在高并发情况下，数据库的性能可能会成为瓶颈，因此在选择分布式锁的实现方式时需要综合考虑系统的需求和性能。