MySQL 事务、隔离与并发

InnoDB 事务通过原子地提交相关更改，并控制并发语句如何观察这些更改，来保护数据库不变量。隔离性不能替代约束：对于模式能够表达的不变量，应使用主键、唯一约束、外键约束和检查约束；然后针对多语句决策选择合适的事务与锁定模式。

事务边界与自动提交 #

MySQL 默认启用自动提交，因此显式事务之外的每条语句都会作为一个事务提交或回滚。应显式启动多语句单元，并使其保持简短：

sql

START TRANSACTION;

UPDATE account
SET balance = balance - 100
WHERE id = 42 AND balance >= 100;

UPDATE account
SET balance = balance + 100
WHERE id = 84;

COMMIT;

在为目标账户入账之前，先检查第一条更新语句影响的行数。任何不变量或语句失败时都使用 ROLLBACK。连接池在归还连接之前必须恢复自动提交、隔离级别、访问模式和其他会话状态，因为这些属性会在事务之间保留。

许多数据定义语句和账户管理语句会隐式提交。原子 DDL 使受支持的模式语句具备崩溃安全性，但不会使该语句成为外围应用事务的一部分。

隔离级别配置 #

InnoDB 在 MySQL 8.4 中的默认隔离级别是 REPEATABLE READ。使用 --transaction-isolation 或 transaction_isolation 配置服务器默认值，并且仅在明确的作用域内覆盖它：

sql

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT @@GLOBAL.transaction_isolation, @@SESSION.transaction_isolation;

GLOBAL 为新会话建立默认值。SESSION 应用于该连接上的后续事务。不带限定词的 SET TRANSACTION 应用于下一个事务。也可以在同一条语句中选择 READ ONLY 和 READ WRITE。

MVCC 与一致性读取 #

只要活动快照仍可能需要，InnoDB 就会在 undo 数据中保留较早的行版本。普通 SELECT 通常执行非锁定一致性读取：

READ UNCOMMITTED 可能暴露未提交的版本。
READ COMMITTED 为每次一致性读取创建新的快照。
REPEATABLE READ 在事务中的第一次一致性读取时创建快照，并在后续一致性读取中复用该快照。
SERIALIZABLE 会在禁用自动提交时，将普通 SELECT 语句转换为共享锁定读取。

写入和显式锁定读取检查的是当前已提交状态，而不是事务较早的一致性读取快照。将快照读取与当前读取混合使用是有效的，但应用程序必须考虑两者之间的差异。长期存在的快照还会保留 undo 历史记录，并可能增加清理压力。

InnoDB 锁类型 #

InnoDB 会锁定语句所选择的索引记录和索引范围：

记录锁保护索引条目。
间隙锁保护索引条目前的区间，防止插入，但不会锁定该条目本身。
临键锁将记录锁与其前面的间隙组合起来。
意向锁在表级别声明事务持有或计划持有行级共享锁或排他锁，从而提高表锁兼容性检查的效率。

在 REPEATABLE READ 下，锁定范围扫描使用临键锁来防止幻读插入。能够找到一行的唯一索引等值搜索可能只使用记录锁。READ COMMITTED 会对大多数搜索禁用间隙锁，但 MySQL 为外键检查和重复键检查所需的情况除外。

访问路径决定 InnoDB 会检查并可能锁定哪些条目。因此，缺少索引或索引选择性不高会同时扩大工作量和竞争范围。应检查每个锁定谓词的执行计划。

当语句使用模式对象时，MySQL 还会获取元数据锁。访问过某张表的开放事务，即使不存在行冲突，也可能导致 ALTER TABLE 等待。Performance Schema 提供了元数据锁表和数据锁表，可用于诊断这些等待。

乐观 Compare-and-Swap #

乐观并发控制会在一次写入中同时执行业务条件检查和版本比较：

sql

UPDATE account
SET balance = balance - 100,
    version = version + 1
WHERE id = 42
  AND version = 17
  AND balance >= 100;

影响一行表示状态转换成功。影响零行表示该行、版本或业务谓词已不再匹配。只有在操作仍然有效时，才重新加载当前状态并重试；不要将每次冲突都变成无界重试。

悲观锁定读取 #

当事务必须在决定写入内容之前检查当前状态时，使用锁定读取：

sql

START TRANSACTION;

SELECT available
FROM inventory
WHERE sku = 'A-42'
FOR UPDATE;

UPDATE inventory
SET available = available - 1
WHERE sku = 'A-42' AND available > 0;

COMMIT;

FOR SHARE 获取共享锁，FOR UPDATE 获取排他锁。NOWAIT 会直接返回，而不是等待冲突锁。SKIP LOCKED 会省略已锁定的行，因此可以支持队列消费者，但它会呈现有意不一致的视图，不适用于一般业务查询。网络调用、远程服务和用户输入应置于锁定区间之外。

建议锁与表锁 #

GET_LOCK(name, timeout) 获取由连接拥有的、服务器范围内的命名建议锁。提交不会释放该锁；RELEASE_LOCK、连接终止或服务器关闭会释放它。建议锁不保护表行，因此所有参与者都必须遵守相同的命名约定：

sql

SELECT GET_LOCK('schema-migration', 10);
-- 执行有界且固定连接的工作。
SELECT RELEASE_LOCK('schema-migration');

建议锁应用于协作式单例工作，而不应替代数据库约束或行锁。连接池不得归还仍持有命名锁的连接。

LOCK TABLES 建立独立的、会话级的显式表锁边界，并且可能隐式提交。它会降低并发性，并且与事务表的交互方式不同于普通 InnoDB 行锁。应将其限制在有文档记录的管理或兼容性工作流中，并始终以 UNLOCK TABLES 结束该边界。

死锁、锁等待与重试 #

在并发环境下，应预期会发生死锁。InnoDB 会检测等待循环，并通过错误 1213 回滚其中一个事务。超过 innodb_lock_wait_timeout 的锁等待会返回错误 1205；具体是回滚失败的语句还是整个事务，取决于配置。应用程序应显式回滚，并从干净的事务边界开始任何重试。

通过按一致顺序锁定行、使用选择性高的索引，以及限制事务持续时间，减少可避免的冲突。使用 SHOW ENGINE INNODB STATUS 以及包括 data_locks、data_lock_waits 和 metadata_locks 在内的 Performance Schema 表，诊断反复出现的情况。

重试可能会在原始结果变得不确定后，再次执行数据库之外的工作。为对外可见的操作提供幂等键或持久化操作标识符，使用退避和抖动限制重试次数，并报告最终冲突，而不是无限重试。