死锁、锁与 MVCC
了解数据库如何避免冲突，以及锁机制与快照机制之间的取舍。
死锁、锁与 MVCC 是 CoddyKit 上的免费 SQL Interview Prep 课时。 这是第 4 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。 这是 SQL Interview Prep 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 SQL Interview Prep 课程共包含 4 节课。
数据库实际上如何实现隔离
隔离级别是承诺；锁定和 MVCC 是兑现这一承诺的机制。面试官会询问这些内容，以确认您是否理解事务发生冲突时底层会发生什么。
主要有两种策略：
- 悲观（锁定）：阻塞冲突访问，直到锁被释放。
- 乐观 / MVCC：让所有事务读取一致的快照，并在提交时检测冲突。
本课将介绍锁、死锁和 MVCC，以及它们之间的权衡。
共享锁与排他锁
经典的锁定机制使用两种主要模式：
- 共享锁（S）用于读取。多个事务可以同时在同一行上持有共享锁。
- 排他锁（X）用于写入。只有一个事务可以持有排他锁，而且它会阻塞该行上的所有其他锁。
规则是：S 与 S 兼容，但 X 与任何锁都不兼容。写入者必须等待所有读取者，读取者必须等待写入者。
使用 SELECT FOR UPDATE 进行显式锁定
您可以为只读取的行请求写锁，以防止其他事务在您执行操作前修改这些行。这是避免读写周期中丢失更新的标准方法。
SELECT ... FOR UPDATE 会获取排他行锁；这些行会一直保持锁定，直到您执行 COMMIT 或 ROLLBACK。
BEGIN;
-- lock the row so no one else can modify it concurrently
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT; -- lock released here什么是死锁
当两个或更多事务各自持有对方需要的锁，形成任何事务都无法继续的循环时，就会发生死锁。
教科书式的情况是：T1 锁定第 A 行，然后需要第 B 行；T2 锁定第 B 行，然后需要第 A 行。双方都在无限等待对方。
数据库通过等待图检测死锁。发现循环后，引擎会选择一个牺牲事务并终止它，返回死锁错误，让其他事务继续执行。
死锁：时间线
请注意锁的获取顺序相互交错。T1 先锁定第 1 行，然后请求第 2 行；T2 先锁定第 2 行，然后请求第 1 行。双方都不释放锁，因此引擎会终止其中一个事务。
被终止的事务会看到类似 deadlock detected 的错误，并且必须重试。另一个事务则会正常提交。
-- T1 | -- T2
BEGIN; | BEGIN;
UPDATE accounts SET balance=balance-10 | UPDATE accounts SET balance=balance-10
WHERE id=1; -- locks row 1 | WHERE id=2; -- locks row 2
UPDATE accounts SET balance=balance+10 | UPDATE accounts SET balance=balance+10
WHERE id=2; -- waits for T2 | WHERE id=1; -- waits for T1 -> CYCLE
-- one transaction is chosen as victim and rolled back防止死锁
您无法彻底消除死锁，但可以让它们变得少见。面试中的标准答案包括：
- 一致的锁定顺序：始终按相同顺序获取行（例如，按标识符升序排列）。这样可以打破循环。
- 缩短事务持续时间：尽可能缩短持有锁的时间。
- 在安全时降低隔离级别：减少锁和冲突。
- 添加重试逻辑：成为死锁牺牲者的事务应自动重试。
一致的锁定顺序是最有效的解决方法，也是面试官最希望首先听到的答案。
锁粒度
锁可以作用于不同的范围，这是并发性与开销之间的权衡：
- 行级锁允许较高的并发性，但管理成本更高。
- 页级或表级锁更易于跟踪，但会阻塞更多事务。
当事务涉及的行数过多时，一些引擎会将行锁升级为表锁（锁升级）。了解这一点有助于解释为什么一次大批量 UPDATE 可能会突然阻塞所有人。
MVCC：快照方法
MVCC（多版本并发控制）是 Postgres、Oracle 和 InnoDB 避免大多数读锁的方式。数据库不采用锁定，而是为每一行保留多个版本。
它的主要优势，也是面试中经常使用的一句话是：读取者不会阻塞写入者，写入者也不会阻塞读取者。
每个事务都会看到某个时间点的一致快照，而写入者会创建新的行版本，而不是原地覆盖数据。
MVCC 的底层工作原理
更新某一行时，MVCC 会写入新版本并保留旧版本。每个版本都带有事务标识符元数据（在 Postgres 中是 xmin 和 xmax），用于标记它何时变得可见，以及何时被新版本取代。
事务的快照决定它能看到哪个版本。任何事务都无法继续看到的旧版本会变成无效元组，之后由清理过程回收。在 Postgres 中，这个过程是 VACUUM；不运行它会导致表膨胀，这是一个常见的后续问题。
锁定与 MVCC：权衡
请简明地总结两者的比较：
- 纯锁定：正确性简单可靠，但读取者和写入者会相互阻塞，从而降低并发性。
- MVCC：读取并发性出色，不需要读锁，但代价是版本存储和清理（VACUUM、膨胀），而且写入冲突仍然需要锁。
即使是 MVCC 引擎，在写入时也会加锁：两个事务更新同一行时必须串行化。MVCC 消除了读写竞争，但不会消除写写竞争。
乐观锁定与版本列
除了引擎级别的 MVCC，应用程序通常还会针对持续时间较长的用户会话中的读写周期添加乐观锁定。您可以添加一个 version 列，读取它，并在更新时要求版本匹配，同时将其递增。
如果另一个事务先更新了该行，版本就不再匹配，受影响的行数为零，您的代码便知道需要重新加载并重试。用户思考期间不会持有任何锁，因此并发性仍然很高。对于“如何处理两个用户同时编辑同一条记录？”这个问题，面试官很喜欢听到这种方案。
-- read: SELECT id, data, version FROM items WHERE id = 1; -- version = 7
UPDATE items
SET data = 'new value', version = version + 1
WHERE id = 1 AND version = 7;
-- if rows affected = 0, someone else changed it: reload and retry快速检查
检验您对 MVCC 核心要点的理解。
总结：锁、死锁与 MVCC
现在您已经可以解释隔离背后的机制：
- 共享锁/排他锁协调访问；
SELECT FOR UPDATE获取显式写锁。 - 死锁是锁形成的循环；引擎会终止一个牺牲事务，而一致的锁定顺序可以防止大多数死锁。
- MVCC保留行版本，让读取者和写入者不会相互阻塞，但需要付出清理成本（VACUUM、膨胀）。
将这些机制与前面课程中的隔离级别和异常结合起来，您就可以完整应对并发面试中的各种问题。
常见问题解答
「死锁、锁与 MVCC」课时是免费的吗？
是的 — 「死锁、锁与 MVCC」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 SQL Interview Prep 课程的其余内容，请升级到 CoddyKit PRO。 SQL Interview Prep 课程共包含 4 节课。
「死锁、锁与 MVCC」这节课中我会学到什么？
了解数据库如何避免冲突，以及锁机制与快照机制之间的取舍。 你通过在浏览器中直接运行的动手代码来练习 SQL Interview Prep，全天候 AI 导师会在你学习这节课的过程中回答你的问题。
学习 SQL Interview Prep 需要有经验吗？
无需任何先前经验。CoddyKit 上的 SQL Interview Prep 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 4 节课，共 4 节。
「死锁、锁与 MVCC」课时需要多长时间？
大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。
我能在这节 SQL Interview Prep 课中编写并运行代码吗？
能。每节 SQL Interview Prep 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。
此课程中的所有课时
- ACID 属性详解
- 四种隔离级别
- 脏读、不可重复读与幻读
- 死锁、锁与 MVCC