并发更新、隔离级别与锁
🎯 引言
学完这篇文章,你能避免同时修改造成数据覆盖,并观察事务读取和等待行为。
🔢 避免两个请求都把 10 改成 11
两个请求同时读取计数 10,各自在应用里加一,再把 11 写回,最终只增加了一次。让数据库直接在当前值上计算,可以避免这种覆盖。
在 blog_demo 建立练习表:
CREATE TABLE counter (
id INT PRIMARY KEY,
value INT NOT NULL
);
INSERT INTO counter VALUES (1, 10);
每次增加时执行:
UPDATE counter SET value = value + 1 WHERE id = 1;
两个成功提交的请求各执行一次,最终从 10 变为 12。InnoDB 在修改同一行时协调先后顺序,不需要应用先读出 10 再计算。
🛡 带条件更新避免超过限制
假设数值不能超过 20,把检查和修改放在同一条语句:
UPDATE counter SET value = value + 1 WHERE id = 1 AND value < 20;
修改 1 行表示增加成功;0 行表示条件不成立或记录不存在。应用据此返回结果,比先查询是否小于 20、过一会儿再无条件更新可靠。
🔒 FOR UPDATE:查询时锁住记录,让其他修改先等待
在事务中查询一条记录后,如果还要根据查到的值完成后续修改,可以在 SELECT 后加 FOR UPDATE。它会锁住查到的记录,让其他请求先等着,不能在这段处理期间抢先修改同一条记录。
例如 A 查到编号 1 的计数为 10,并锁住这条记录;B 此时想给它加一,就要等 A 提交或回滚后才能继续。下面用两个窗口观察这个等待过程。前面的简单加一仍使用一条 UPDATE 即可,不需要额外查询加锁。
打开两个 MySQL 客户端窗口,都选择 blog_demo。先在自动提交状态把计数重置为 10:
UPDATE counter SET value = 10 WHERE id = 1;
窗口 A 执行:
START TRANSACTION;
SELECT value FROM counter WHERE id = 1 FOR UPDATE;
按本文步骤执行,查询结果为:
| value |
|---|
| 10 |
FOR UPDATE 表示读取并锁住要修改的记录。现在窗口 B 执行:
UPDATE counter SET value = value + 1 WHERE id = 1;
B 暂时等待。回到 A 执行 COMMIT 后,B 才能继续。如果一直不结束 A,B 可能等到超时报错。练习结束务必提交或回滚,不要把事务留着。
通过主键等值查找已经存在的一行,锁的目标比较明确;范围查询或缺少合适索引可能锁住更多记录,不能认为每条更新都只影响一个锁。
📝 表单保存时检查版本,避免覆盖别人的修改
两个用户同时打开一份资料,先后保存不同的城市,后保存的人可能覆盖先保存的结果。可以在 user 表记录版本号:
ALTER TABLE user ADD COLUMN version INT NOT NULL DEFAULT 1;
读取资料时同时读取 version。假设页面读到版本 1,保存时提交新城市和这个版本,服务端执行:
UPDATE user SET city = '武汉', version = version + 1
WHERE id = 1 AND version = 1;
首次匹配后,城市变为武汉,版本变为 2。另一份旧表单仍按版本 1 保存,就会影响 0 行;接口应提示资料已更新,让用户重新读取后决定,而不是直接覆盖。
这种保存时再检查是否被改过的方式叫乐观锁。所有修改同一份资料的入口都要遵守版本检查;若某个入口绕过版本规则,保护就不完整。它适合需要提示编辑冲突的表单,前面的加一计数则直接用数据库计算。
👀 可重复读:同一事务里,别人提交修改后仍能读到原来的值
假设计数原来是 10。A 开始一个事务,查到 10;B 把它改成 20 并提交;A 在尚未结束的同一个事务里再次查询,仍然得到 10。
这就是本节要观察的可重复读(REPEATABLE READ):在下面的实验里,A 的普通查询继续读取第一次查询时的数据,不因 B 后来的提交而改变。这样,A 需要连续做几次查询时,可以依据同一份数据完成处理。例如先统计用户数量,再读取用户明细,避免两次查询之间混入其他事务刚新增的用户。
准备两个独立连接
A、B 是两个 MySQL 客户端窗口,各自使用一条数据库连接,都选择 blog_demo。先结束两个窗口之前的事务。在 B 的自动提交状态下,将计数恢复为 10:
UPDATE counter SET value = 10 WHERE id = 1;
此时 counter 中编号 1 的记录为:
| id | value |
|---|---|
| 1 | 10 |
窗口 A:开始事务,第一次查到 10
隔离级别规定一个事务能看到其他事务哪些修改。先在 A 设置为可重复读:
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION 表示“修改当前连接的设置”,TRANSACTION ISOLATION LEVEL 表示“事务隔离级别”,REPEATABLE READ 表示“可重复读”。整句是在给 A 后续开启的事务选择读取规则,不会修改 B 的连接。
InnoDB 默认使用可重复读,这里明确设置,确保实验条件一致。随后在 A 开始事务:
START TRANSACTION;
仍在 A 查询,查询后先不要提交:
SELECT value FROM counter WHERE id = 1;
结果为:
| value |
|---|
| 10 |
窗口 B:把值改成 20,并提交
切换到 B 执行:
UPDATE counter SET value = 20 WHERE id = 1;
B 保持默认自动提交,所以执行成功后,20 已经保存。A 前面的 SELECT 没有加 FOR UPDATE,不会像上一节那样让 B 等待。
窗口 A:同一个事务再次查询,仍然得到 10
回到 A,直接执行同一条查询,不要重新开始事务:
SELECT value FROM counter WHERE id = 1;
结果仍为:
| value |
|---|
| 10 |
不是 B 修改失败,也不是 A 把数据改回了 10。 B 的 20 已提交,只是 A 还在原事务中,继续按第一次查询时的数据读取。
数据库保留了 A 读取所需的旧版本。这种读取方式叫快照读取,不是把数据库复制一份,也不表示所有连接都会读到旧值。
窗口 A:结束事务,再查询就得到 20
COMMIT;
SELECT value FROM counter WHERE id = 1;
结果为:
| value |
|---|
| 20 |
A 的原事务已结束,新查询可以看到 B 提交的修改。因此,判断为什么读到旧值时,要检查当前事务是否还没有结束。
本例中 A 只查询,没有自己修改数据,也没有使用 FOR UPDATE。A 能读到自己在事务内的修改;FOR UPDATE 也采用不同的读取规则,不能把这里的结果理解为“可重复读下所有查询永远不变”。
🔄 READ COMMITTED 每次读取已提交数据
如果希望 A 在同一个事务里再次查询时,能看到 B 刚刚提交的 20,可以把 A 后续事务的读取规则改为读已提交(READ COMMITTED)。
先结束 A 当前的事务,再在 A 窗口执行:
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
整句的意思是:把当前连接后续开启的事务,设置为“读已提交”隔离级别。 它不修改 counter 表中的数据,也不会自动开始事务。
| 语句中的部分 | 含义 | 在这里做什么 |
|---|---|---|
| SET | 修改设置 | 告诉 MySQL 这是一条设置命令 |
| SESSION | 当前连接 | 只设置 A 这个连接,不修改 B 的设置 |
| TRANSACTION ISOLATION LEVEL | 事务隔离级别 | 指定要调整的是事务读取其他事务数据的规则 |
| READ COMMITTED | 读已提交 | 每次普通查询都可以读取当时已经提交的数据 |
上一节结尾的 REPEATABLE READ 表示“可重复读”;这里换成 READ COMMITTED,其他部分的意思不变。这条设置会在 A 连接中持续生效,直到再次修改或断开连接,不是只对下一条 SELECT 生效。
接下来按以下顺序操作,不要再次执行上一节的 REPEATABLE READ 设置:
| 顺序 | 窗口 | 操作与结果 |
|---|---|---|
| 1 | B | 将 value 重置为 10,保持自动提交 |
| 2 | A | 执行 START TRANSACTION,然后查询 value,得到 10 |
| 3 | B | 将 value 更新为 20,自动提交 |
| 4 | A | 再查询 value,这次得到 20,最后执行 COMMIT |
READ COMMITTED 叫读已提交。A 的每次普通查询都能读取当时已经提交的数据,因此第二次可以看到 B 的修改。
选择隔离级别要结合读取要求和锁竞争,不能靠切换级别代替正确的条件更新。更改只对当前连接生效;实验结束可重新连接恢复服务默认配置。
🪤 死锁时处理整个事务
A 锁住记录 1 等待记录 2,B 锁住记录 2 等待记录 1,双方互相等,这叫死锁。InnoDB 通常会选择一个事务回滚,并返回死锁错误,让另一个继续。
应用收到死锁错误后,应重新执行整个事务,而不是只补做最后一条 SQL,并设置有限的重试次数。减少死锁的常用做法是:按相同编号顺序修改多行,让事务尽快结束。
锁等待超时与死锁不是一回事。超时后也应明确回滚当前事务,再决定是否重试,避免前面成功的语句意外被提交。
❓ 知识问答
Q1:普通 SELECT 会被上面的行锁一直挡住吗?
通常不会,普通查询可以读取符合当前隔离级别的已提交数据。FOR UPDATE 和修改操作才需要竞争对应的锁。
Q2:版本冲突后可以自动用新版本覆盖吗?
不应直接覆盖,那会失去冲突检查的意义。重新读取当前内容,让用户确认如何合并或保存。
Q3:事务已经开启,就不会发生业务覆盖了吗?
不能这样保证。先读后无条件覆盖仍可能出问题,要选条件更新、版本检查或合适的锁定读取。
🧪 小练习
用两个窗口完成等待实验,记录 B 何时继续。再分别验证两种隔离级别下,A 第二次普通查询得到的值。
-- 请在这里编写 SQL 或记录验证结果
