数据库基础体系 · 第 73/139 篇。文章以各产品官方稳定版本的公开语义为准；示例会明确引擎、事务与部署边界。

SQL 数据写入：INSERT、UPDATE、DELETE、MERGE、Upsert 与并发正确性

在数据库中，写入并不只是“把一行数据改成另一个值”。一次写操作通常同时涉及：

• 目标行如何被定位；
• 新值如何计算；
• 主键、唯一键、外键、检查约束如何验证；
• 其他事务同时修改相同行或相关行时如何协调；
• 语句失败后哪些修改可以保留；
• 多条语句如何共同构成一个不可分割的业务操作。

INSERT、UPDATE、DELETE、MERGE 和 Upsert 解决的问题不同。尤其是 Upsert，它不是一个统一的 SQL 标准关键字，而是一类“存在则更新，不存在则插入”的写入语义。真正决定正确性的，往往不是语句表面上长什么样，而是唯一约束、事务隔离级别、锁和冲突处理共同形成的边界。

本文示例分别标明 PostgreSQL 或 MySQL 8.4。除非特别说明，示例均假设使用单个数据库实例、同一数据库内的事务，并不讨论跨数据库分布式事务。

---

一、先建立写入的共同模型

设表中有一组行，数据库写入语句可以抽象为四个阶段：

1. 定位（match）：找到哪些现有行受影响；
2. 计算（derive）：根据旧值、输入值和表达式计算新值；
3. 验证（validate）：检查数据类型、NOT NULL、唯一键、外键、CHECK 等约束；
4. 提交（commit）：让修改成为事务对外可见的结果。

例如：

UPDATE account
SET balance = balance - 100
WHERE account_id = 1;

它并不是简单地“把 balance 改成某个常量”，而是：

• 定位 account_id = 1 的行；
• 读取该行当前版本的 balance；
• 计算 balance - 100；
• 检查数据类型和其他约束；
• 在事务提交后使新版本可见。

1. 语句原子性与事务原子性

一条 DML 语句通常具有语句级原子性：如果语句失败，语句自身产生的修改不会以部分结果留下。例如：

UPDATE account
SET balance = balance - 100
WHERE account_id IN (1, 2);

如果因为约束错误导致语句失败，不能依赖“第 1 行已经修改、第 2 行失败，所以第 1 行保留”这种行为。数据库通常会回滚整条语句的效果。

但多条语句是否作为一个整体成功，要由事务决定：

BEGIN;

UPDATE account
SET balance = balance - 100
WHERE account_id = 1;

INSERT INTO transfer_log(account_id, amount)
VALUES (1, 100);

COMMIT;

如果转账扣款成功但日志插入失败，应用必须回滚事务，而不是只处理其中一条语句。

语句原子性不等于跨语句原子性，事务原子性也不自动解决并发下的业务逻辑错误。

---

二、INSERT：创建新行，但“新行”必须经过约束和冲突处理

1. 基本语义

INSERT 向目标表创建行：

INSERT INTO users(user_id, email, display_name)
VALUES (1001, 'a@example.com', 'Alice');

这里至少有以下验证：

• user_id 是否满足类型和主键约束；
• email 是否满足 NOT NULL、唯一约束等；
• display_name 是否超出类型允许范围；
• 引用的其他表记录是否存在；
• 数据库生成的默认值、身份列、触发器逻辑如何参与。

多行插入的逻辑结果是一次语句插入多行：

INSERT INTO users(user_id, email, display_name)
VALUES
  (1001, 'a@example.com', 'Alice'),
  (1002, 'b@example.com', 'Bob');

如果第二行违反唯一约束，不能把这种写法理解为“第一行一定已经永久写入”。整个语句是否回滚、是否存在特殊的忽略冲突行为，取决于数据库语义和所使用的语法；默认的 PostgreSQL 和 MySQL 普通 INSERT 都不应被当作逐行独立提交。

2. INSERT ... SELECT 的关键边界

插入数据也可以来自查询：

INSERT INTO archive_orders(order_id, customer_id, total_amount)
SELECT order_id, customer_id, total_amount
FROM orders
WHERE created_at < TIMESTAMP '2024-01-01';

这会把“查询结果”作为输入集合。若目标表有唯一约束，查询结果内部重复、或目标表中已经存在相同键，都可能导致失败。

因此，下面的 NOT EXISTS 只能减少已知冲突，不能单独构成并发安全的“只插入一次”保证：

INSERT INTO users(user_id, email)
SELECT 1001, 'a@example.com'
WHERE NOT EXISTS (
  SELECT 1 FROM users WHERE user_id = 1001
);

两个并发事务可能同时读到“没有 user_id = 1001”，随后都执行插入。真正的最终防线应是数据库唯一约束：

ALTER TABLE users
ADD CONSTRAINT users_pkey PRIMARY KEY (user_id);

3. 默认值与显式 NULL

省略列和显式写入 NULL 不是一回事：

CREATE TABLE task (
  task_id     integer PRIMARY KEY,
  state       text NOT NULL DEFAULT 'pending',
  created_at  timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  remark      text
);

INSERT INTO task(task_id)
VALUES (1);

此时 state 和 created_at 使用默认值。

INSERT INTO task(task_id, state)
VALUES (2, NULL);

此时显式写入 NULL，会违反 state NOT NULL，不会因为存在默认值而自动改成 'pending'。

---

三、UPDATE：修改已有行，真正重要的是定位条件和新值表达式

1. UPDATE 修改的是匹配行的当前版本

UPDATE users
SET display_name = 'Alice Chen'
WHERE user_id = 1001;

WHERE 决定哪些行被修改。没有 WHERE 的 UPDATE 会修改整张表：

UPDATE users
SET state = 'active';

这不是语法错误，而是合法且高风险的操作。

SET 中的表达式通常基于该行更新前的值计算：

UPDATE account
SET balance = balance - 100
WHERE account_id = 1;

它表达的是“减少 100”，比应用先查询余额、再拼接一个绝对值更适合并发场景。

2. 条件更新是把业务条件放入写入动作

例如，只有余额足够时才扣款：

UPDATE account
SET balance = balance - 100
WHERE account_id = 1
  AND balance >= 100;

应用应检查受影响行数：

• 受影响 1 行：扣款成功；
• 受影响 0 行：账户不存在，或余额不足，或其他条件不满足。

这比下面的流程更可靠：

SELECT balance;
应用判断 balance >= 100；
UPDATE account SET balance = 新余额;

后者把判断和修改拆开，两个事务可能基于同一个旧余额作出决定。

3. “受影响行数为 0”不总是代表同一件事

在不同数据库、驱动和连接器配置下，受影响行数可能表示：

• 匹配到的行数；
• 实际值发生变化的行数；
• 插入或更新动作的合并结果。

例如：

UPDATE users
SET display_name = 'Alice'
WHERE user_id = 1001;

如果该行本来就是 'Alice'，数据库可能报告匹配到 1 行，也可能报告实际变化 0 行，具体取决于引擎和客户端协议。

因此，业务协议不能只依赖模糊的“affected rows”含义。需要区分“没有找到对象”“条件不满足”“值恰好相同”时，应设计明确的谓词、版本列或使用数据库提供的返回能力。

PostgreSQL 可以使用 RETURNING：

UPDATE account
SET balance = balance - 100
WHERE account_id = 1
  AND balance >= 100
RETURNING account_id, balance;

返回一行表示更新成功，返回空结果表示没有满足条件的行。MySQL 8.4 没有与 PostgreSQL UPDATE ... RETURNING 等价的通用 DML 语法，应用通常结合受影响行数和后续查询处理。

---

四、DELETE：删除行，不等于删除整个业务对象

1. 基本语义

DELETE FROM users
WHERE user_id = 1001;

DELETE 删除匹配的行。没有 WHERE 时会删除表中所有行：

DELETE FROM users;

它与 TRUNCATE 不是同一类操作：

• DELETE 是按行删除，支持条件；
• TRUNCATE 通常是更底层的表级清空操作，锁、触发器、日志和回滚细节依数据库而异；
• DROP TABLE 删除的是表对象本身。

2. 删除与外键

假设订单引用用户：

CREATE TABLE orders (
  order_id  bigint PRIMARY KEY,
  user_id   bigint NOT NULL REFERENCES users(user_id)
);

直接删除被引用的用户可能失败：

DELETE FROM users
WHERE user_id = 1001;

除非外键配置了 ON DELETE CASCADE、SET NULL 等动作，或先处理子表数据。

这不是 DELETE 语句“偶尔失败”，而是引用完整性约束阻止数据库留下悬空引用。级联删除必须明确评估范围：删除一个用户可能递归删除订单、明细、附件等大量数据。

3. 并发删除与更新

事务 A 正在更新某行，事务 B 删除同一行时，至少会涉及行级锁或写冲突。最终结果受隔离级别和提交顺序影响，但应用不应假设“已经查到的对象一定还能删除”。

较稳妥的删除形式是把版本或状态条件放进 WHERE：

DELETE FROM document
WHERE document_id = 10
  AND version = 7;

如果返回 0 行，说明对象不存在，或已经被其他事务修改。这样可以避免无意删除后来被别人更新的版本。

---

五、MERGE：按源表与目标表的匹配结果选择动作

1. MERGE 解决什么问题

MERGE 适合将一组源数据同步到目标表。它先根据 ON 条件判断源行是否匹配目标行，再根据 WHEN 子句执行插入、更新或删除。

PostgreSQL 从 15 版本开始支持 MERGE。以下示例使用 PostgreSQL 15 及更高版本：

CREATE TABLE product (
  sku       text PRIMARY KEY,
  price     numeric(10, 2) NOT NULL,
  stock     integer NOT NULL
);

CREATE TEMP TABLE incoming_product (
  sku   text,
  price numeric(10, 2),
  stock integer
);

INSERT INTO incoming_product VALUES
  ('A-100', 12.50, 8),
  ('B-200', 20.00, 3);

MERGE INTO product AS p
USING incoming_product AS s
ON p.sku = s.sku
WHEN MATCHED THEN
  UPDATE SET price = s.price, stock = s.stock
WHEN NOT MATCHED THEN
  INSERT (sku, price, stock)
  VALUES (s.sku, s.price, s.stock);

处理过程可以分解为：

1. 从 incoming_product 产生源行；
2. 对每个源行执行 p.sku = s.sku 匹配；
3. 若找到目标行，成为 MATCHED 候选；
4. 若找不到目标行，成为 NOT MATCHED 候选；
5. 对候选行按 WHEN 子句顺序选择第一个满足条件的动作；
6. 执行 UPDATE 或 INSERT。

例如可以增加条件：

MERGE INTO product AS p
USING incoming_product AS s
ON p.sku = s.sku
WHEN MATCHED AND s.stock = 0 THEN
  DELETE
WHEN MATCHED THEN
  UPDATE SET price = s.price, stock = s.stock
WHEN NOT MATCHED THEN
  INSERT (sku, price, stock)
  VALUES (s.sku, s.price, s.stock);

当库存为 0 时，匹配行走 DELETE 分支；否则走 UPDATE。WHEN 的顺序是语义的一部分，不能认为多个分支会依次执行。

2. MERGE 的限制和并发边界

MERGE 不是“所有数据库中的统一 Upsert 关键字”。

• PostgreSQL 15+ 支持 MERGE，但它与 INSERT ... ON CONFLICT 不是同一套语义；
• MySQL 8.4 没有通用的 SQL MERGE 语句，不能直接把 PostgreSQL 的 MERGE 示例复制到 MySQL；
• 不同数据库的 MERGE 在触发器、重复匹配、并发冲突和支持的动作上可能不同。

一个重要问题是源数据自身重复：

source:
sku = A-100
sku = A-100

如果两条源行都匹配同一目标行，不能简单假定目标会被“按顺序更新两次”。具体行为取决于引擎的 MERGE 语义，很多系统会报“一行被多个源行匹配”之类的错误，或者因唯一约束冲突而失败。因此，源数据通常应先按业务键去重，并明确选择最新记录：

-- PostgreSQL 示例：先为每个 sku 选一条源记录
WITH ranked AS (
  SELECT *,
         row_number() OVER (
           PARTITION BY sku
           ORDER BY received_at DESC
         ) AS rn
  FROM incoming_product
)
MERGE INTO product AS p
USING (
  SELECT sku, price, stock
  FROM ranked
  WHERE rn = 1
) AS s
ON p.sku = s.sku
...

并发下，MERGE 仍然受到唯一约束、锁和事务隔离级别影响。它不能自动保证“业务上每个键只处理一次”，也不能替代幂等键、唯一约束或冲突重试。

---

六、Upsert：不是一个关键字，而是一种冲突驱动的写入模式

1. 朴素的“先查再写”为什么有竞态

常见伪代码是：

SELECT user_id FROM users WHERE email = 'a@example.com';

如果存在：
    UPDATE users ...
否则：
    INSERT INTO users ...

设唯一键是 email，两个事务 T1、T2 同时执行：

时刻	T1	T2
1	查询不到 a@example.com
2		查询不到 a@example.com
3	执行 INSERT
4		执行 INSERT

如果有唯一约束，其中一个插入会失败；如果没有唯一约束，就会产生重复数据。

因此，SELECT 之后再由应用决定 INSERT 或 UPDATE，存在典型的检查—执行竞态（TOCTOU）。正确性不能依赖两个网络往返之间“没人改变数据”。

2. PostgreSQL：ON CONFLICT

PostgreSQL 使用唯一约束或唯一索引识别冲突：

CREATE TABLE user_profile (
  user_id      bigint PRIMARY KEY,
  email        text NOT NULL UNIQUE,
  display_name text NOT NULL
);

按主键 Upsert：

INSERT INTO user_profile(user_id, email, display_name)
VALUES (1001, 'a@example.com', 'Alice')
ON CONFLICT (user_id) DO UPDATE
SET email = EXCLUDED.email,
    display_name = EXCLUDED.display_name;

这里：

• user_profile 是目标表；
• EXCLUDED 表示本次准备插入、但因冲突未直接插入的那行；
• ON CONFLICT (user_id) 指定冲突仲裁键；
• DO UPDATE 表示冲突时更新目标已有行；
• 也可以使用 DO NOTHING 忽略冲突。

例如只在新版本更大时更新：

INSERT INTO user_profile(user_id, email, display_name, version)
VALUES (1001, 'a@example.com', 'Alice', 8)
ON CONFLICT (user_id) DO UPDATE
SET email = EXCLUDED.email,
    display_name = EXCLUDED.display_name,
    version = EXCLUDED.version
WHERE user_profile.version < EXCLUDED.version;

注意这里的 WHERE 是更新动作的条件。冲突本身仍然已经被识别和协调，只是条件不满足时不执行更新。

ON CONFLICT 要求冲突目标能够对应唯一约束或唯一索引。普通非唯一索引不能证明“这个业务键只能有一行”。

3. MySQL：ON DUPLICATE KEY UPDATE

MySQL 8.4 常用语法是：

CREATE TABLE user_profile (
  user_id      bigint PRIMARY KEY,
  email        varchar(255) NOT NULL UNIQUE,
  display_name varchar(255) NOT NULL
);

INSERT INTO user_profile(user_id, email, display_name)
VALUES (1001, 'a@example.com', 'Alice')
AS new
ON DUPLICATE KEY UPDATE
  email = new.email,
  display_name = new.display_name;

MySQL 8.4 推荐使用行别名 AS new 引用待插入值。旧代码中常见的 VALUES(column_name) 形式在较新 MySQL 版本中已被弃用，应根据目标版本调整。

MySQL 的“重复键”可能来自任意唯一索引，而不只是主键。如果一条插入数据同时违反多个唯一约束，最终选择哪个冲突进行更新不应被设计成业务规则；应避免让多个唯一键产生互相含糊的 Upsert 目标。

4. Upsert 不是无条件覆盖

下面这种写法可能覆盖较新的数据：

INSERT ... ON CONFLICT (...) DO UPDATE
SET value = EXCLUDED.value;

如果消息乱序、任务重试或多个来源同时写入，后到达的数据不一定更新。通常需要把版本、时间戳或来源序号纳入条件：

-- PostgreSQL
INSERT INTO item_state(item_id, value, event_version)
VALUES (10, 'new', 8)
ON CONFLICT (item_id) DO UPDATE
SET value = EXCLUDED.value,
    event_version = EXCLUDED.event_version
WHERE item_state.event_version < EXCLUDED.event_version;

这表达的是：

$$\text{只有 } v_{\text{incoming}} > v_{\text{stored}} \text{ 时才接受新值}$$

其中 v_incoming 是本次输入版本，v_stored 是数据库已有版本。这样可以抵抗乱序消息，但仍需处理相同版本重复到达、版本空洞和来源之间的版本生成问题。

---

七、并发正确性的核心：谁保护了哪个不变量

1. 先定义不变量

不变量是任何合法数据库状态都必须满足的条件。例如：

账户余额不能为负数
同一个 email 只能对应一个用户
同一个订单不能同时处于已取消和已支付状态
至少有一名值班医生

并发正确性不是“所有事务都按顺序执行”，而是并发执行后仍然满足业务不变量。

2. 单行条件更新为何有效

考虑两个事务同时从余额 100 中各扣 80：

UPDATE account
SET balance = balance - 80
WHERE account_id = 1
  AND balance >= 80;

理想的数据库执行过程是：

1. T1 找到余额 100，锁定并将其更新为 20；
2. T2 也要更新同一行，因此等待；
3. T1 提交；
4. T2 继续处理时，重新检查可见的当前行；
5. 当前余额为 20，不满足 balance >= 80；
6. T2 更新 0 行。

最终余额为 20，而不是 -60。

这里的关键不是“应用恰好先后查询了余额”，而是把业务条件放在同一条写语句中，并让数据库协调同一行的写冲突。PostgreSQL 在 Read Committed 下对并发更新有重新评估条件的行为；InnoDB 也会通过记录锁和当前读等机制协调写入。但不应把某个引擎的具体行为无条件推广到所有数据库。

3. 丢失更新：绝对值写入覆盖了别人修改

以下流程可能丢失更新：

T1: SELECT quantity = 10
T2: SELECT quantity = 10
T1: UPDATE item SET quantity = 11
T2: UPDATE item SET quantity = 12

T1 的增加被 T2 的绝对值写入覆盖。

更好的表达是增量更新：

UPDATE item
SET quantity = quantity + 1
WHERE item_id = 1;

或者使用乐观锁版本列：

UPDATE item
SET quantity = 12,
    version = version + 1
WHERE item_id = 1
  AND version = 3;

如果返回 0 行，表示版本已经不是 3，应用应重新读取并决定重试、合并或报告冲突。

4. 写偏差：每行都合法，但组合状态不合法

考虑值班医生表：

CREATE TABLE doctor_duty (
  doctor_id integer PRIMARY KEY,
  on_call   boolean NOT NULL
);

业务规则是：至少一名医生值班。

T1 查询到医生 A、B 都值班，然后关闭 A；T2 同时查询到 A、B 都值班，然后关闭 B：

T1: 读取到 A=true, B=true
T2: 读取到 A=true, B=true
T1: UPDATE A SET on_call=false
T2: UPDATE B SET on_call=false

每个事务只修改自己的行，因而不一定发生同一行写冲突；但提交后 A、B 都不值班，违反组合不变量。这就是写偏差。

解决方案取决于业务和数据库：

• 使用 SERIALIZABLE，让数据库检测并发事务无法串行化的情况，并回滚其中一个；
• 在事务中锁定共同保护对象，例如锁定一行“排班汇总”记录；
• 将规则改造成可由唯一约束或计数结构强制的形式；
• 使用显式锁定读取，并确保所有相关代码遵守相同锁顺序。

提高隔离级别通常意味着更多等待、死锁或序列化失败，应用必须具备重试策略。SERIALIZABLE 不是“自动成功”，而是“发现不安全的并发并拒绝其中一些执行”。

---

八、隔离级别、锁与 DML 的关系

1. 普通读取不一定锁住行

SELECT balance FROM account WHERE account_id = 1;

普通查询通常是快照读取，不等于“我读到的值会一直保留”。如果应用需要先读取再基于该行作出修改，可以考虑：

SELECT balance
FROM account
WHERE account_id = 1
FOR UPDATE;

FOR UPDATE 表示读取并锁定目标行，其他需要修改该行的事务通常必须等待。

但锁定读取也不是万能的：

• 锁的范围由索引、谓词、隔离级别和数据库实现决定；
• 锁住了已有行，不一定锁住“尚不存在的键”；
• 事务持锁时间越长，并发能力通常越差；
• 不同路径使用不同顺序加锁，可能产生死锁。

2. 唯一约束是并发控制的一部分

很多工程师把唯一约束只看成数据校验，但它同时是并发协调机制：

CREATE UNIQUE INDEX users_email_uq ON users(email);

在两个事务同时插入相同 email 时，数据库必须协调唯一性。没有唯一约束，任何应用层 SELECT 检查都无法从根本上阻止重复。

唯一约束保护的是精确的键相等关系。它不能自动表达：

同一个用户在任意时间段最多只能有一条有效预约
任意两个时间段不能重叠
总库存不能小于所有订单预留量

这类规则可能需要排他约束、锁、序列化事务、汇总行或专门的建模方式。

3. 隔离级别不是 DML 语法的属性

同一条 UPDATE 在不同隔离级别、不同引擎上可能有不同的等待、可见性和失败表现。必须同时说明：

• 数据库产品和版本；
• 存储引擎，例如 MySQL 的 InnoDB；
• 事务边界；
• 隔离级别；
• 是否自动提交；
• 是否存在触发器、级联外键或副作用。

例如 MySQL 的锁行为不能推广到使用非事务性存储引擎的表；PostgreSQL 的 MVCC 快照语义也不能直接当作 MySQL 所有隔离级别的定义。

---

九、失败、回滚、死锁与重试

1. 约束冲突是正常控制流的一部分

对于 Upsert，唯一键冲突不是异常路径，而可能正是“更新已有行”的分支。对于普通 INSERT，唯一键冲突则通常表示操作失败。

应用要区分：

• 可重试的死锁、序列化失败、临时连接错误；
• 可转换为业务结果的唯一键冲突；
• 不应重试的类型错误、非空约束错误、外键错误；
• 需要人工或数据修复的逻辑错误。

盲目重试所有数据库异常会放大错误。例如每次请求都重复发送一笔非幂等扣款，重试可能造成重复扣款。

2. 死锁不是“数据库错了”

两个事务按不同顺序获取锁：

T1: 锁定账户 1 -> 等待账户 2
T2: 锁定账户 2 -> 等待账户 1

形成环路后，数据库通常会主动回滚其中一个事务以打破死锁。

应用应：

1. 捕获明确的死锁错误；
2. 回滚整个事务；
3. 在有限次数和适当退避后重试整个业务事务；
4. 尽量让所有代码按一致顺序锁定资源。

只重试失败的最后一条 SQL 通常不正确，因为事务已经被回滚，前面的读取和业务判断也失效了。

3. 事务重试要求操作可重放

若事务包含：

生成随机业务编号
发送邮件
扣款
写数据库

数据库序列化失败后，重试数据库事务可能再次发送邮件或产生不同编号。事务重试前必须明确哪些副作用在事务内、哪些通过 Outbox 等机制异步发送，以及业务操作是否有幂等键。

---

十、批量写入与批处理中的正确性

批量写不是简单地把很多行拼进一条 SQL。需要同时考虑：

• 单条语句大小和参数数量；
• 事务持续时间；
• 锁持有时间；
• 失败后重试范围；
• 源数据内部重复；
• 是否需要断点恢复。

1. 批量 Upsert 的重复键

PostgreSQL：

INSERT INTO item_state(item_id, value, version)
VALUES
  (1, 'a', 3),
  (1, 'b', 4)
ON CONFLICT (item_id) DO UPDATE
SET value = EXCLUDED.value,
    version = EXCLUDED.version;

同一批输入中出现相同 item_id 时，一个目标行可能被同一条语句中的多个输入行要求更新。PostgreSQL 对这种“同一目标行被同一命令重复影响”的情况可能报错，而不是保证按输入顺序更新两次。

因此批量写入前应先保证源集合按冲突键唯一，例如在临时表中使用窗口函数去重，或在应用层先归并。

2. 批量删除应使用稳定边界

按时间批量删除：

DELETE FROM event_log
WHERE event_id IN (
  SELECT event_id
  FROM event_log
  WHERE created_at < TIMESTAMP '2024-01-01'
  ORDER BY event_id
  LIMIT 1000
);

这段语法不能直接跨 PostgreSQL 和 MySQL 保证兼容：两者对 DELETE 中的排序、限制和子查询写法存在差异。更稳妥的跨引擎思路是先取出一批稳定主键，再根据主键删除，并将取数和删除放在正确的事务边界内。

批处理的断点应使用单调且唯一的键，而不是只使用可能重复的时间戳：

SELECT event_id, created_at
FROM event_log
WHERE event_id > :last_event_id
ORDER BY event_id
LIMIT :batch_size;

处理成功并提交后，再持久化 last_event_id。如果两者不在同一事务或同一可靠状态机中，就必须接受重复处理，并让写入具备幂等性。

---

十一、如何选择这些写入形式

可以按问题本身选择语义，而不是按语句“看起来更强大”来选择：

使用 INSERT

适合明确要求“必须是新行”的场景：

• 创建订单；
• 写入事件；
• 注册不可重复的业务编号。

应依赖主键或唯一约束防止重复，并对重复错误作出明确业务处理。

使用 UPDATE

适合目标行必须已经存在的场景：

UPDATE job
SET state = 'running'
WHERE job_id = :id
  AND state = 'queued';

这同时表达了状态转换条件。返回 0 行时，不能简单认为是数据库故障，可能代表任务已被其他 worker 抢走。

使用 DELETE

适合真正删除数据的场景。若业务需要审计、恢复或保留历史，可能应改为状态更新或软删除：

UPDATE users
SET deleted_at = CURRENT_TIMESTAMP
WHERE user_id = :id
  AND deleted_at IS NULL;

软删除会带来新的唯一性问题，例如“已删除用户是否仍占用 email”。这需要通过部分唯一索引、额外状态键或其他建模方式明确表达，不能只把 DELETE 替换成一列更新。

使用 MERGE

适合一个源集合需要依据匹配关系执行多种动作，尤其是批量同步、条件更新和条件删除。使用前必须确认目标数据库版本和 MERGE 的具体语义，并先处理源数据重复。

使用 Upsert

适合目标键已经定义了唯一身份，且业务确实允许：

没有目标行 -> 创建
已有目标行 -> 按明确规则更新或忽略

不能把 Upsert 当作所有写入的默认形式。它可能掩盖数据来源错误、覆盖新数据，或在多个唯一键冲突时产生不清晰的业务含义。

---

十二、一个完整的并发安全写入示例

假设要接收用户资料同步消息，要求：

1. user_id 唯一；
2. 旧版本消息不能覆盖新版本；
3. 同一消息重复投递不会产生重复行；
4. 并发消息最终保留最高版本。

PostgreSQL 表：

CREATE TABLE user_profile (
  user_id       bigint PRIMARY KEY,
  email         text NOT NULL,
  display_name  text NOT NULL,
  event_version bigint NOT NULL
);

写入语句：

INSERT INTO user_profile(
  user_id, email, display_name, event_version
)
VALUES (
  1001, 'a@example.com', 'Alice', 8
)
ON CONFLICT (user_id) DO UPDATE
SET email = EXCLUDED.email,
    display_name = EXCLUDED.display_name,
    event_version = EXCLUDED.event_version
WHERE user_profile.event_version < EXCLUDED.event_version;

设已有版本为 10：

1. 输入版本 8；
2. 按 user_id 发现冲突；
3. 判断 10 < 8 为假；
4. 不执行更新；
5. 版本 10 的数据保留。

设两个事务同时提交版本 11 和 12：

• 两者都以 user_id = 1001 为冲突目标；
• 数据库协调同一唯一键上的写入；
• 最终哪一个先处理并不重要，因为版本条件保证低版本不能覆盖高版本；
• 若版本 12 先写入，版本 11 随后会被条件拒绝；
• 若版本 11 先写入，版本 12 随后会覆盖它。

这个设计同时依赖三件事：

• PRIMARY KEY 提供身份唯一性；
• ON CONFLICT 把冲突判断与写入合并；
• event_version 条件表达业务上的新旧顺序。

如果版本号并非同一实体内单调递增，或多个来源各自生成版本号，那么这套规则不再足以定义全局先后顺序，需要改用来源序号、逻辑时钟或由业务重新定义合并规则。

---

SQL 写入的可靠性最终来自“约束、写入条件和事务并发模型”三者一致。INSERT 负责创建，UPDATE 负责改变，DELETE 负责移除，MERGE 负责按匹配关系选择动作，Upsert 负责把唯一键冲突纳入写入流程；但它们都不能脱离唯一约束、隔离级别、锁、失败处理和幂等设计单独保证业务正确性。

---

系列导航与关联阅读

• 系列入口：数据库完整学习路线：从关系模型、事务索引到分布式与向量检索
• 上一篇：SQL 聚合与集合：GROUPING SETS、ROLLUP、CUBE、UNION 和去重
• 下一篇：SQL 批处理与分页：游标、Keyset、批量写、限速和断点恢复
• 延伸：数据库事务完整指南：ACID、隔离级别、异常现象与正确边界

官方资料

• PostgreSQL SQL Language
• MySQL Reference Manual

本文依据数据库官方文档重新梳理；正文、示例与生产检查清单由 WR BLOG 编写。