SQL怎样实现对并发更新同一行数据的版本号校验

sql触发器实现乐观锁需在before update中校验new.version = old.version，不匹配则抛异常；必须设version为not null且默认0，insert时显式赋值，并确保触发器兼容首次插入场景。

sql怎样实现对并发更新同一行数据的版本号校验_触发器实现乐观锁

触发器实现乐观锁的核心，是每次 UPDATE 时检查 version 字段是否匹配，匹配才更新并递增。不能依赖应用层传入新 version 值，必须由数据库保证原子性。

常见错误是把 version 更新写成 SET version = version + 1 却不校验原值——这实际变成无条件更新，失去乐观锁意义。

PostgreSQL / SQL Server：可用 BEFORE UPDATE 触发器，在触发器内读取 OLD.version 和语句中传入的 NEW.version，不匹配则抛异常（如 RAISE EXCEPTION 'version conflict'）
MySQL：不支持在 BEFORE UPDATE 中修改 NEW 行的其他字段以外的逻辑校验，更可靠的做法是用 AFTER UPDATE 配合 SELECT ... FOR UPDATE + 应用层重试，或直接放弃触发器、改用带条件的 UPDATE
关键点：触发器里不能只做 SET NEW.version = OLD.version + 1，必须先 IF NEW.version != OLD.version THEN ... END IF

比触发器更通用、更可控的方式，是在应用层发出的 UPDATE 语句中显式带上 version 条件。这是乐观锁的事实标准做法，兼容所有主流 SQL 数据库。

例如要更新用户余额和 version：

UPDATE users 
SET balance = 100, version = version + 1 
WHERE id = 123 AND version = 5;

执行后检查影响行数：rowcount == 1 表示更新成功；rowcount == 0 表示 version 不匹配，发生并发冲突。

用 updated_at 时间戳替代 version 整数字段，看似省事，但会引发隐蔽的并发问题。

典型现象：两个事务几乎同时提交，都读到相同的 updated_at 值（比如精度只到秒），WHERE 条件判断通过，结果后提交者覆盖前提交者的更新，且不报错。

数据库时间精度有限：MySQL DATETIME 默认秒级，PostgreSQL TIMESTAMP 虽支持微秒，但应用层生成时间、网络延迟、事务调度仍可能导致碰撞
无法区分“谁先改”：时间戳只反映写入时刻，不体现逻辑顺序；而 version 是严格单调递增的逻辑序号
索引效率略低：整数比较比时间戳比较稍快，且 version 字段通常更窄、更适合高频更新场景

即使触发器逻辑写对了，上线后仍可能因设计疏忽导致锁失效。最常被忽略的是初始值和 NULL 处理。

比如建表时定义 version INT DEFAULT NULL，第一次插入没显式赋值，后续 UPDATE 的 WHERE version = NULL 永远为 false（因为 NULL = NULL 是 unknown），导致首条更新必然失败。

version 字段必须设 NOT NULL，且默认值为 0 或 1（推荐 0，便于识别未更新状态）
INSERT 语句必须显式插入 version = 0，不能依赖 DEFAULT——某些 ORM 或批量导入工具会跳过 DEFAULT 字段
触发器需兼容 INSERT 场景：首次插入时，若业务要求 version 从 1 开始，则 INSERT 触发器应设 NEW.version = 1，而非沿用 UPDATE 的递增逻辑