当前位置：首页 > news >正文

数据库死锁分析与解决实战

news 2026/6/3 2:51:43

数据库死锁分析与解决实战

一、死锁概述

死锁是指两个或多个事务在执行过程中，因互相等待对方持有的锁而陷入无限等待的状态。

1.1 死锁条件

条件	说明
互斥	资源只能被一个事务占用
请求与保持	事务持有资源并请求新资源
不可剥夺	资源不能被强制剥夺
循环等待	事务形成循环等待链

1.2 死锁示例

事务A: BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; 事务B: BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 2; 事务A: UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- 等待事务B 事务B: UPDATE accounts SET balance = balance + 100 WHERE id = 1; -- 等待事务A 结果：死锁！

二、死锁检测

2.1 MySQL死锁检测

-- 查看当前死锁 SHOW ENGINE INNODB STATUS\G -- 查看锁等待 SELECT * FROM performance_schema.data_locks; SELECT * FROM performance_schema.data_lock_waits; -- 查看事务 SELECT * FROM information_schema.INNODB_TRX;

2.2 死锁日志分析

------------------------ LATEST DETECTED DEADLOCK ------------------------ 2024-01-15 10:00:00 0x7f1234567890 *** (1) TRANSACTION: TRANSACTION 123456, ACTIVE 10 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 100, OS thread handle 0x7f1234567890, query id 500 localhost user updating UPDATE accounts SET balance = balance + 100 WHERE id = 2 *** (2) TRANSACTION: TRANSACTION 123457, ACTIVE 10 sec starting index read mysql tables in use 1, locked 1 3 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 101, OS thread handle 0x7f1234567891, query id 501 localhost user updating UPDATE accounts SET balance = balance + 100 WHERE id = 1 *** WE ROLL BACK TRANSACTION (1)

三、死锁预防

3.1 统一访问顺序

// 按主键顺序访问资源 public void transfer(Long fromId, Long toId, BigDecimal amount) { Long firstId = Math.min(fromId, toId); Long secondId = Math.max(fromId, toId); try (Connection conn = getConnection()) { conn.setAutoCommit(false); // 先更新ID较小的记录 String sql1 = "UPDATE accounts SET balance = balance - ? WHERE id = ?"; try (PreparedStatement pstmt = conn.prepareStatement(sql1)) { pstmt.setBigDecimal(1, amount); pstmt.setLong(2, firstId); pstmt.executeUpdate(); } // 再更新ID较大的记录 String sql2 = "UPDATE accounts SET balance = balance + ? WHERE id = ?"; try (PreparedStatement pstmt = conn.prepareStatement(sql2)) { pstmt.setBigDecimal(1, amount); pstmt.setLong(2, secondId); pstmt.executeUpdate(); } conn.commit(); } catch (SQLException e) { // 回滚处理 } }

3.2 缩短事务长度

@Transactional public void processOrder(Order order) { // 1. 验证订单 validateOrder(order); // 2. 更新库存（使用独立事务或延迟更新） updateInventoryAsync(order); // 3. 创建订单记录 orderRepository.save(order); // 4. 发送通知（异步处理） notificationService.sendAsync(order); }

3.3 使用乐观锁

@Transactional public boolean updateWithOptimisticLock(Long id, BigDecimal amount) { Account account = accountRepository.findById(id).orElseThrow(); if (account.getVersion() != expectedVersion) { throw new OptimisticLockingFailureException("Data has been modified"); } account.setBalance(account.getBalance().add(amount)); account.setVersion(account.getVersion() + 1); accountRepository.save(account); return true; }

四、死锁解决

4.1 超时机制

public void executeWithTimeout(Supplier<Void> operation, long timeoutMs) { ExecutorService executor = Executors.newSingleThreadExecutor(); Future<Void> future = executor.submit(() -> { operation.get(); return null; }); try { future.get(timeoutMs, TimeUnit.MILLISECONDS); } catch (TimeoutException e) { future.cancel(true); throw new RuntimeException("Operation timed out"); } catch (Exception e) { throw new RuntimeException("Operation failed", e); } finally { executor.shutdown(); } }

4.2 重试机制

@Retryable(maxAttempts = 3, backoff = @Backoff(delay = 100, multiplier = 2)) @Transactional public void transferWithRetry(Long fromId, Long toId, BigDecimal amount) { // 转账逻辑 accountRepository.debit(fromId, amount); accountRepository.credit(toId, amount); }

4.3 死锁检测与回滚

public void executeWithDeadlockRetry(Runnable operation) { int attempts = 0; int maxAttempts = 3; long backoffMs = 100; while (attempts < maxAttempts) { try { operation.run(); return; } catch (SQLException e) { if (isDeadlockError(e) && attempts < maxAttempts - 1) { attempts++; try { Thread.sleep(backoffMs * attempts); } catch (InterruptedException ie) { Thread.currentThread().interrupt(); throw new RuntimeException(ie); } } else { throw e; } } } }

五、最佳实践

5.1 索引优化

-- 确保WHERE条件有索引 CREATE INDEX idx_accounts_id ON accounts(id); -- 复合索引优化 CREATE INDEX idx_orders_user_status ON orders(user_id, status); -- 避免全表扫描 EXPLAIN SELECT * FROM orders WHERE status = 'PENDING';

5.2 隔离级别选择

// 使用合适的隔离级别 @Transactional(isolation = Isolation.READ_COMMITTED) public void processTransaction() { // 业务逻辑 }

隔离级别	说明	死锁风险
READ_UNCOMMITTED	最低级别	低
READ_COMMITTED	读已提交	中
REPEATABLE_READ	可重复读	高
SERIALIZABLE	串行化	最高

5.3 监控告警

# Prometheus告警规则 groups: - name: database.rules rules: - alert: DeadlockDetected expr: rate(innodb_deadlocks_total[5m]) > 0 for: 1m labels: severity: warning annotations: summary: "Deadlock detected in database" description: "{{ $value }} deadlocks detected in the last 5 minutes"