MariaDB 教程 / 用两个连接验证隔离级别、锁与死锁
隔离级别决定事务能读取哪些并发修改,锁则协调相互冲突的操作。使用两个独立连接并控制执行顺序,可以分别观察 REPEATABLE READ、READ COMMITTED、锁等待超时和死锁,而不是把它们混成一种连接故障。
准备独立连接 A 与 B
在同一学习服务器上打开两个 MariaDB 客户端连接,都选择 shopcourse。为两个窗口标记 A、B,分别执行下面的查询与设置保存语句,确认 CONNECTION_ID() 不同、DATABASE() 相同。把两边代码粘进同一客户端,不会产生并发。
SELECT CONNECTION_ID(),DATABASE(),@@SESSION.tx_isolation;
SET @saved_isolation=@@SESSION.tx_isolation;
SET @saved_wait=@@SESSION.innodb_lock_wait_timeout;
变量 @saved_isolation 和 @saved_wait 属于各自连接,必须在 A、B 分别保存。中途重新连接后这些变量会消失,应重新检查状态,不要继续使用旧连接的保存记录。实验只改 SESSION 设置,不需要改服务器全局隔离级别或等待时间。
只在 A 中创建练习表,先检查 SHOW TABLES 和已有对象;若 accounts_36_lab 已存在,查看定义与记录并保存需要的数据,不要覆盖未知用途的表:
USE shopcourse;
CREATE TABLE accounts_36_lab (
account_id INT PRIMARY KEY,balance INT NOT NULL
) ENGINE=InnoDB;
INSERT INTO accounts_36_lab VALUES(1,1000),(2,2000);
| 情形 | 读取或协调规则 | 需要区分 |
|---|---|---|
| 普通 REPEATABLE READ SELECT | 事务中的一致性读取快照 | 不同于 FOR UPDATE 等锁定读取 |
| 普通 READ COMMITTED SELECT | 各语句读取可见的已提交版本 | 同一事务重复查询可能变化 |
| 锁等待 | 等待冲突锁释放 | 超时不等于形成了死锁循环 |
先理解 事务的开始与结束,再执行并发步骤。语法和作用域可查阅官方 SET TRANSACTION与 FOR UPDATE 文档。请只在独立练习数据上操作,不要让实验锁阻塞业务请求。
按 A→B→A 顺序比较 REPEATABLE READ
从编号 1 余额为 1000 的初始样本开始。每次只执行指定窗口的一段,B 必须等 A 的首次读取完成后才修改。
A-1:开始事务并建立首次读取快照。
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT balance FROM accounts_36_lab WHERE account_id=1;
B-1:把余额改为 1100 并提交。
START TRANSACTION;
UPDATE accounts_36_lab SET balance=1100 WHERE account_id=1;
COMMIT;
A-2:不结束原事务,再次读取同一行。
SELECT balance FROM accounts_36_lab WHERE account_id=1;
A-1、A-2 都应读到 1000。这里比较的是普通 SELECT 的一致性读取,不是 FOR UPDATE 等当前锁定读取;不要只看到隔离级别名称,就把所有读取方式都解释成同一结果。
A-3:结束事务,再读取已提交值。
ROLLBACK;
SELECT balance FROM accounts_36_lab WHERE account_id=1;
最后应读到 1100,说明 B 的提交确实存在,只是 A 之前仍使用原事务的一致性读取视图。若首次结果就不是 1000,先检查是否按初始化状态开始,是否误在同一连接执行两边代码,或者 A 的事务已经被工具自动结束。
先明确重置,再比较 READ COMMITTED
B-0:结束残留工作,把上一轮余额明确恢复到 1000 并提交。
ROLLBACK;
START TRANSACTION;
UPDATE accounts_36_lab SET balance=1000 WHERE account_id=1;
COMMIT;
A-1:结束上一轮事务,设置本连接隔离级别,再开始首次读取。
ROLLBACK;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT balance FROM accounts_36_lab WHERE account_id=1;
B-1:把余额改为 1100 并提交。
START TRANSACTION;
UPDATE accounts_36_lab SET balance=1100 WHERE account_id=1;
COMMIT;
A-2:在同一事务再次读取,然后结束。
SELECT balance FROM accounts_36_lab WHERE account_id=1;
ROLLBACK;
A 第一次读到 1000,第二次读到 1100。READ COMMITTED 的普通一致性读取按语句取得可见的已提交版本,因此同一事务内再次读取可以变化。这个对照只改变隔离级别,且先重置数据,避免把上一轮留下的 1100 当成新的实验结果。
选择应用的隔离策略,还要结合写冲突、业务一致性与锁范围,不能只根据哪次读取“看到了新值”就判断哪一级更适合全部场景。
区分持锁等待与等待超时
A-1:在事务中锁住编号 1。
START TRANSACTION;
SELECT account_id,balance FROM accounts_36_lab
WHERE account_id=1 FOR UPDATE;
B-1:把本连接等待时间设为 3 秒,并尝试修改同一行。
SET SESSION innodb_lock_wait_timeout=3;
START TRANSACTION;
UPDATE accounts_36_lab SET balance=balance+1 WHERE account_id=1;
A 仍未结束时,B 应等待并在大约三秒后出现锁等待超时,常见错误码为 1205。它说明等待超过限制,不证明双方已经形成循环等待。实际经过时间可能有调度差异,不需要追求毫秒级一致。
不要假定超时自动取消 B 的整笔事务。应明确结束失败尝试,并恢复 B 保存的等待设置:
B-2:回滚并恢复等待时间。
ROLLBACK;
SET SESSION innodb_lock_wait_timeout=@saved_wait;
A-2:回滚,释放持有的锁。
ROLLBACK;
如果 B 立刻成功,检查 A 是否仍在事务内、FOR UPDATE 是否在正确连接中执行、两边是否实际指向同一数据库。不要为了让演示看起来符合预期而加长全局等待时间。
用四个交错步骤形成死锁循环
确认上一轮两边事务都已结束,再按 A-1→B-1→A-2→B-2 切换窗口。这不是一份可以在同一客户端顺序执行的脚本。实验依赖 InnoDB 死锁检测启用;若关闭检测,循环可能等待到超时,而不是立即返回 1213。
| 步骤 | 连接 | 操作 |
|---|---|---|
| 1 | A | 修改行 1,持有该行的锁 |
| 2 | B | 修改行 2,持有另一行的锁 |
| 3 | A | 请求行 2,等待 B |
| 4 | B | 请求行 1,形成循环等待 |
A-1:只修改第一行。
START TRANSACTION;
UPDATE accounts_36_lab SET balance=balance+1 WHERE account_id=1;
B-1:修改另一行。
START TRANSACTION;
UPDATE accounts_36_lab SET balance=balance+1 WHERE account_id=2;
A-2:请求 B 已持锁的行。
UPDATE accounts_36_lab SET balance=balance+1 WHERE account_id=2;
A 会在这里等待。不要等 A 完成才继续,因为那会使交错顺序无法形成;切换到 B 执行下一条:
B-2:请求 A 已持锁的行。
UPDATE accounts_36_lab SET balance=balance+1 WHERE account_id=1;
启用检测时,其中一笔事务会收到 Deadlock found,错误码 1213,数据库选择它作为回滚对象;具体哪一边不是固定的。另一边的等待语句可能随后继续完成。两边都应分别执行 ROLLBACK;,结束本次实验并撤销仍未确认的增量。
官方 1213 错误说明区分了死锁与一般失败。应用需要按完整事务设计有次数上限的重试,并防止重复业务操作;只重发最后一条 UPDATE,可能不再对应原来的一组修改。
恢复每个连接的设置并统一加锁顺序
按一致的键顺序访问多行、缩短事务持续时间,可以减少死锁机会。宽泛条件和缺少有用索引也可能扩大锁定范围。出现问题时应结合实际语句、事务顺序和诊断记录排查,不要直接把死锁当作服务器断线。
在 A、B 分别执行下面的恢复代码,核对隔离级别与等待时间回到各自保存值。两边的初始设置可能不同,所以不能只恢复一个窗口就算完成:
ROLLBACK;
SET SESSION tx_isolation=@saved_isolation;
SET SESSION innodb_lock_wait_timeout=@saved_wait;
SELECT @@SESSION.tx_isolation,@@SESSION.innodb_lock_wait_timeout;
本例代码使用 tx_isolation 名称;应结合服务器版本检查实际支持的系统变量。用户变量随连接结束而消失,重新打开连接后,应重新查询初始设置,而不是把空的保存变量作为恢复依据。
确认两边事务均已结束,再由一个连接删除已确认可丢弃的练习表。DROP TABLE 会删除定义与记录,普通 ROLLBACK 无法恢复;不要在另一边仍持锁时开始清理。
DROP TABLE shopcourse.accounts_36_lab;




