本文目录导读：
我来为您详细讲解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;
}
}
}
最佳实践建议
- 保持事务简短：缩短持锁时间
- 固定访问顺序：所有事务按相同顺序访问资源
- 使用适当的锁粒度：避免不必要的锁
- 设置合理的超时时间：避免无限等待
- 使用索引：减少锁定的行数
- 定期监控：使用性能监控工具及时发现死锁
- 实施重试机制：死锁后自动重试事务
数据库死锁是并发编程中常见的问题，通过理解死锁的原理和场景，采用合适的解决方案（如固定访问顺序、锁超时、乐观锁等），可以有效地避免和解决死锁问题，在实际开发中,建议结合业务特点选择合适的方案。
