MYSQL笔记

1.多线程下，怎么保证数据的一致性（ACID）**

悲观锁 高并发写操作、冲突频繁 强一致性保证 性能开销大，可能死锁
使用SELECT … FOR UPDATE锁定目标数据行。

@Transactional public void updateWithPessimisticLock(int id) {
// 1. 加锁查询
String selectSql = “SELECT * FROM account WHERE id = ? FOR UPDATE”;
PreparedStatement stmt = conn.prepareStatement(selectSql);
stmt.setInt(1, id);
ResultSet rs = stmt.executeQuery();
// 2. 修改数据
String updateSql = “UPDATE account SET balance = balance - 100 WHERE id = ?”;
PreparedStatement updateStmt = conn.prepareStatement(updateSql);
updateStmt.setInt(1, id);
updateStmt.executeUpdate(); }

乐观锁 低冲突读多写少场景 无锁、高吞吐 需处理重试逻辑
通过版本号（Version）或时间戳实现无锁并发控制。

– 表结构添加version字段 ALTER TABLE account ADD version INT DEFAULT 0; java 复制 @Transactional public void updateWithOptimisticLock(int id) {
// 1. 查询当前版本号
String selectSql = “SELECT balance, version FROM account WHERE id = ?”;
PreparedStatement stmt = conn.prepareStatement(selectSql);
stmt.setInt(1, id);
ResultSet rs = stmt.executeQuery();
int currentVersion = rs.getInt(“version”);
int newBalance = rs.getInt(“balance”) - 100;
// 2. 更新时校验版本号
String updateSql = “UPDATE account SET balance = ?, version = version + 1 WHERE id = ? AND version = ?”;
PreparedStatement updateStmt = conn.prepareStatement(updateSql);
updateStmt.setInt(1, newBalance);
updateStmt.setInt(2, id);
updateStmt.setInt(3, currentVersion);
int rowsAffected = updateStmt.executeUpdate();
// 3. 更新失败重试或抛异常
if (rowsAffected == 0) {
throw new OptimisticLockException(“并发更新冲突!”);
} }

分布式锁 多节点部署、跨JVM 解决跨进程竞争 增加外部依赖，复杂度高
若应用部署在多个节点，需使用分布式锁（如Redis或ZooKeeper）。

Redis分布式锁示例

public boolean tryLock(String key, String value, int expireTime) {
return “OK”.equals(jedis.set(key, value, “NX”, “EX”, expireTime)); }

public void transferWithDistributedLock(int fromId, int toId, int
amount) {
String lockKey = “lock:account:” + fromId;
String requestId = UUID.randomUUID().toString();
try {
if (tryLock(lockKey, requestId, 10)) {
accountService.transferMoney(fromId, toId, amount);
} else {
throw new RuntimeException(“获取锁失败”);
}
} finally {
if (requestId.equals(jedis.get(lockKey))) {
jedis.del(lockKey);
}
} }

隔离级别控制 通用场景 数据库原生支持 高隔离级别可能降低并发性能

通过设置数据库事务隔离级别，防止脏读、不可重复读、幻读等问题。

设置MySQL隔离级别

– 修改全局或会话隔离级别（推荐使用REPEATABLE READ） SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Java代码中指定隔离级别（Spring声明式事务）

@Transactional(isolation = Isolation.REPEATABLE_READ) public void
transferMoney(int fromId, int toId, int amount) {
// 业务逻辑 }

2. MySQL 死锁的监控与解决指南

死锁是数据库系统中多个事务因竞争资源而相互等待的现象。MySQL 的 InnoDB 引擎会自动检测并回滚其中一个事务以解除死锁，但频繁的死锁会影响性能。以下是死锁的监控方法、分析步骤及解决方案。

一、监控死锁

1. 开启死锁日志记录

配置参数： 在 my.cnf 或 my.ini 中设置：

innodb_print_all_deadlocks = ON # 记录所有死锁到错误日志
log_error = /var/log/mysql/error.log # 指定错误日志路径

生效方式： 动态生效（无需重启）：
sql 复制 SET GLOBAL innodb_print_all_deadlocks = 1;

2. 查看最近的死锁信息

SHOW ENGINE INNODB STATUS\G

3. 定期分析错误日志

grep “DEADLOCK” /var/log/mysql/error.log

二、分析死锁原因

1. 死锁产生的典型场景
   场景1：交叉更新顺序
   事务A先更新表1再更新表2，事务B先更新表2再更新表1。

   场景2：索引缺失导致锁升级
   无合适索引时，行锁可能升级为表锁。

   场景3：Gap锁冲突
   范围查询（如 WHERE id BETWEEN 1 AND 10）可能锁定间隙，导致死锁。

2. 分析步骤
   定位冲突事务：通过日志中的 SQL 语句识别涉及的事务。

   检查锁持有情况：查看 HOLDS THE LOCK(S) 和 WAITING FOR 部分。

   确定资源竞争点：分析锁定的是哪些行或索引。

   复现问题：在测试环境模拟相同操作，验证死锁触发条件。

三、解决死锁的优化策略

1. 应用层优化
   统一访问顺序：确保所有事务以相同的顺序访问表和行。

   – 优化前：事务A更新表1→表2，事务B更新表2→表1
   – 优化后：所有事务先更新表1，再更新表2
   缩短事务时间：避免长事务，减少锁持有时间。

// 示例：Java 中尽快提交事务
@Transactional(timeout = 5) // 设置超时时间 public
void updateData() {
// 快速执行SQL操作
}

使用乐观锁：通过版本号减少锁竞争。

UPDATE table SET col = new_val, version = version + 1 WHERE id = 1 AND
version = old_version;

2. 数据库层优化
   添加合理索引：减少锁升级为表锁。

ALTER TABLE orders ADD INDEX idx_customer_id (customer_id);
为WHERE条件字段添加索引 降低隔离级别：从 REPEATABLE READ 改为 READ COMMITTED，减少 Gap 锁使用

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

禁用间隙锁（慎用）：
在 my.cnf 中设置：

innodb_locks_unsafe_for_binlog = 1 # 仅适用于特定场景（如无范围查询）

3. 自动重试机制
   在代码中捕获死锁异常（错误码 1213）并重试：

// Java 示例：重试3次 int maxRetries = 3; int retryCount = 0; while
(retryCount < maxRetries) {
try {
executeTransaction();
break;
} catch (SQLException e) {
if (e.getErrorCode() == 1213) { // 死锁错误码
retryCount++;
Thread.sleep(50 * retryCount); // 等待时间递增
} else {
throw e;
}
} }

四、预防死锁的最佳实践
实践 说明
统一资源访问顺序 所有事务按固定顺序访问表和行，避免交叉依赖。
避免长事务 使用短事务，必要时拆分大事务为多个小事务。
合理使用索引 为查询条件字段添加索引，减少全表扫描和锁范围。
监控与告警 定期检查死锁日志，配置报警（如 Prometheus + Alertmanager）。
压力测试 使用工具（如 sysbench）模拟高并发场景，提前发现潜在死锁问题。
五、工具推荐

pt-deadlock-logger（Percona Toolkit）：自动记录死锁信息到独立文件。


pt-deadlock-logger --user=root --password=xxx --socket=/tmp/mysql.sock
Innotop：实时监控 InnoDB 锁和事务状态。


innotop -uroot -pxxx
Prometheus + MySQL Exporter：可视化监控死锁频率。

通过以上方法，可有效监控、分析和解决MySQL死锁问题，提升数据库稳定性和性能。