Java数据库死锁案例

wen java案例 2026-08-03 197

本文目录导读：

Java数据库死锁案例

什么是数据库死锁
典型死锁案例
死锁的检测和诊断
解决方案
最佳实践建议

我来为您详细讲解Java数据库死锁的案例，包括代码示例、原因分析和解决方案。

什么是数据库死锁

死锁是指两个或多个事务互相持有对方需要的资源,导致所有事务都无法继续执行的情况。

典型死锁案例

案例1：经典的双向更新死锁

public class DeadlockExample1 {
    // 事务1：先更新账户A，再更新账户B
    public void transferMoney1(Connection conn, int accountA, int accountB, double amount) {
        try {
            conn.setAutoCommit(false);
            // 第一步：更新账户A
            String sql1 = "UPDATE accounts SET balance = balance - ? WHERE account_id = ?";
            try (PreparedStatement ps = conn.prepareStatement(sql1)) {
                ps.setDouble(1, amount);
                ps.setInt(2, accountA);
                ps.executeUpdate();
            }
            // 模拟业务处理，增加死锁概率
            Thread.sleep(100);
            // 第二步：更新账户B
            String sql2 = "UPDATE accounts SET balance = balance + ? WHERE account_id = ?";
            try (PreparedStatement ps = conn.prepareStatement(sql2)) {
                ps.setDouble(1, amount);
                ps.setInt(2, accountB);
                ps.executeUpdate();
            }
            conn.commit();
        } catch (Exception e) {
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }
    }
}

死锁场景：

事务1：更新账户A → 更新账户B
事务2：更新账户B → 更新账户A

案例2：批量插入导致死锁

public class DeadlockExample2 {
    // 两个事务同时插入相同范围的数据
    public void batchInsert(Connection conn, int startId, int endId) {
        try {
            conn.setAutoCommit(false);
            // 插入数据
            String sql = "INSERT INTO orders (order_id, customer_id, amount) VALUES (?, ?, ?)";
            try (PreparedStatement ps = conn.prepareStatement(sql)) {
                for (int i = startId; i <= endId; i++) {
                    ps.setInt(1, i);
                    ps.setInt(2, i % 100);
                    ps.setDouble(3, Math.random() * 1000);
                    ps.addBatch();
                }
                ps.executeBatch();
            }
            conn.commit();
        } catch (Exception e) {
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }
    }
}

案例3：索引导致的死锁

public class DeadlockExample3 {
    // 事务1：通过主键更新，同时插入新记录
    public void updateByPrimaryKey(Connection conn) {
        try {
            conn.setAutoCommit(false);
            // 更新现有记录
            String updateSql = "UPDATE products SET stock = stock - 1 WHERE product_id = ?";
            try (PreparedStatement ps = conn.prepareStatement(updateSql)) {
                ps.setInt(1, 1001);
                ps.executeUpdate();
            }
            // 插入新记录
            String insertSql = "INSERT INTO products (product_id, product_name, stock) VALUES (?, ?, ?)";
            try (PreparedStatement ps = conn.prepareStatement(insertSql)) {
                ps.setInt(1, 1002);
                ps.setString(2, "New Product");
                ps.setInt(3, 10);
                ps.executeUpdate();
            }
            conn.commit();
        } catch (Exception e) {
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }
    }
}

案例4：完整的死锁模拟程序

import java.sql.*;
import java.util.concurrent.*;
public class DeadlockSimulation {
    private static final String URL = "jdbc:mysql://localhost:3306/test";
    private static final String USER = "root";
    private static final String PASSWORD = "password";
    public static void main(String[] args) {
        // 初始化数据
        initData();
        // 模拟死锁场景
        ExecutorService executor = Executors.newFixedThreadPool(2);
        // 事务1：先更新account1，再更新account2
        executor.submit(() -> {
            transferMoney(1, 2, 100);
        });
        // 事务2：先更新account2，再更新account1
        executor.submit(() -> {
            transferMoney(2, 1, 200);
        });
        executor.shutdown();
    }
    private static void transferMoney(int fromAccount, int toAccount, double amount) {
        String sql = "UPDATE accounts SET balance = balance + ? WHERE account_id = ?";
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
            conn.setAutoCommit(false);
            // 获取连接锁
            try (PreparedStatement ps = conn.prepareStatement(sql)) {
                // 第一次更新
                ps.setDouble(1, -amount);
                ps.setInt(2, fromAccount);
                ps.executeUpdate();
                // 模拟业务处理，增加死锁概率
                Thread.sleep(200);
                // 第二次更新
                ps.setDouble(1, amount);
                ps.setInt(2, toAccount);
                ps.executeUpdate();
            }
            conn.commit();
            System.out.println("转账成功：" + fromAccount + " → " + toAccount + " 金额：" + amount);
        } catch (Exception e) {
            System.out.println("转账失败：" + fromAccount + " → " + toAccount);
            e.printStackTrace();
        }
    }
    private static void initData() {
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             Statement stmt = conn.createStatement()) {
            // 创建表
            stmt.executeUpdate("DROP TABLE IF EXISTS accounts");
            stmt.executeUpdate("CREATE TABLE accounts (" +
                    "account_id INT PRIMARY KEY," +
                    "balance DECIMAL(10,2))");
            // 插入数据
            stmt.executeUpdate("INSERT INTO accounts VALUES (1, 1000.00)");
            stmt.executeUpdate("INSERT INTO accounts VALUES (2, 2000.00)");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

死锁的检测和诊断

MySQL死锁检测

-- 查看最近一次死锁信息
SHOW ENGINE INNODB STATUS;
-- 查看当前正在运行的事务
SELECT * FROM information_schema.INNODB_TRX;
-- 查看锁的信息
SELECT * FROM information_schema.INNODB_LOCKS;

死锁日志示例

------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-01-01 10:00:00 0x7f7a3d3f4700
*** (1) TRANSACTION:
TRANSACTION 123456, ACTIVE 10 sec
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 12345, OS thread handle 140343833147136, query id 123456
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1
*** (2) TRANSACTION:
TRANSACTION 123465, ACTIVE 10 sec
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 12346, OS thread handle 140343833148672, query id 123466
UPDATE accounts SET balance = balance - 200 WHERE account_id = 2

解决方案

方案1：固定访问顺序

public class Solution1 {
    // 按照账户ID的固定顺序更新，避免死锁
    public void transferMoney(Connection conn, int fromAccount, int toAccount, double amount) {
        try {
            conn.setAutoCommit(false);
            // 确定更新顺序（按账户ID排序）
            int firstAccount = Math.min(fromAccount, toAccount);
            int secondAccount = Math.max(fromAccount, toAccount);
            // 先更新小ID的账户
            updateAccount(conn, firstAccount, firstAccount == fromAccount ? -amount : amount);
            updateAccount(conn, secondAccount, secondAccount == fromAccount ? -amount : amount);
            conn.commit();
        } catch (Exception e) {
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }
    }
    private void updateAccount(Connection conn, int accountId, double delta) throws SQLException {
        String sql = "UPDATE accounts SET balance = balance + ? WHERE account_id = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setDouble(1, delta);
            ps.setInt(2, accountId);
            ps.executeUpdate();
        }
    }
}

方案2：使用锁超时

public class Solution2 {
    public void transferMoney(Connection conn, int fromAccount, int toAccount, double amount) {
        try {
            // 设置锁等待超时时间（例如5秒）
            conn.setNetworkTimeout(null, 5000);
            // 设置事务隔离级别，减少锁范围
            conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
            conn.setAutoCommit(false);
            // 业务逻辑...
            conn.commit();
        } catch (Exception e) {
            // 处理死锁异常
            handleDeadlock(e);
        }
    }
    private void handleDeadlock(Exception e) {
        if (e.getMessage() != null && e.getMessage().contains("Deadlock")) {
            System.out.println("发生死锁，事务回滚，请重试");
        }
    }
}

方案3：使用SELECT FOR UPDATE

public class Solution3 {
    public void updateWithLock(Connection conn, int accountId, double amount) {
        try {
            conn.setAutoCommit(false);
            // 先锁定行，确保不会死锁
            String lockSql = "SELECT * FROM accounts WHERE account_id = ? FOR UPDATE";
            try (PreparedStatement ps = conn.prepareStatement(lockSql)) {
                ps.setInt(1, accountId);
                ResultSet rs = ps.executeQuery();
                if (rs.next()) {
                    // 获取到锁后再更新
                    String updateSql = "UPDATE accounts SET balance = ? WHERE account_id = ?";
                    try (PreparedStatement updatePs = conn.prepareStatement(updateSql)) {
                        double newBalance = rs.getDouble("balance") + amount;
                        updatePs.setDouble(1, newBalance);
                        updatePs.setInt(2, accountId);
                        updatePs.executeUpdate();
                    }
                }
            }
            conn.commit();
        } catch (Exception e) {
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }
    }
}

方案4：使用乐观锁

public class Solution4 {
    // 使用版本号实现乐观锁
    public boolean updateWithOptimisticLock(Connection conn, int accountId, double amount) {
        try {
            conn.setAutoCommit(false);
            // 读取当前版本号
            String selectSql = "SELECT balance, version FROM accounts WHERE account_id = ?";
            int version;
            double balance;
            try (PreparedStatement ps = conn.prepareStatement(selectSql)) {
                ps.setInt(1, accountId);
                ResultSet rs = ps.executeQuery();
                if (rs.next()) {
                    balance = rs.getDouble("balance");
                    version = rs.getInt("version");
                } else {
                    return false;
                }
            }
            // 更新时检查版本号
            String updateSql = "UPDATE accounts SET balance = ?, version = version + 1 " +
                              "WHERE account_id = ? AND version = ?";
            try (PreparedStatement ps = conn.prepareStatement(updateSql)) {
                ps.setDouble(1, balance + amount);
                ps.setInt(2, accountId);
                ps.setInt(3, version);
                int affectedRows = ps.executeUpdate();
                if (affectedRows == 0) {
                    // 版本号不匹配，说明数据已被修改
                    return false;
                }
            }
            conn.commit();
            return true;
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return false;
        }
    }
}