如何通过SQL实现数据的版本化控制：增加版本号与更新策略

SQL中实现版本号字段的自动递增更新

说到给数据记录加版本号，最直接、也最可靠的方法，就是在UPDATE语句里写上version = version + 1。这招儿之所以管用，前提就两个：一是字段初始值得是0或1，二是表结构里得把version定义成整型，比如INT或BIGINT。可别想着依赖触发器或者让应用层去计数，在并发写入的场景下，那很容易引发脏读或者数据被意外覆盖。

这里有个常见的坑：虽然写了SET version = version + 1 WHERE id = ? AND version = ?，却在WHERE子句里忘了校验旧的版本号。这会导致“丢失更新”，也就是乐观锁最基础的逻辑失效了。记住，必须带上version这个条件，才能真正防止你的修改覆盖掉别人的成果。

• 语句执行后，务必检查影响行数。在MySQL里是ROW_COUNT()，PostgreSQL则是GET DIAGNOSTICS ROW_COUNT。如果返回是1，说明更新成功；如果是0，那大概率就是版本冲突了，这时候就需要重试或者明确报错。
• 要避免在UPDATE语句里混用SELECT MAX(version)+1这种操作。这会产生竞态条件，而且无法保证原子性。
• 即使用UUID做主键，也完全可以额外增加一个version字段。但千万别用UUID本身来充当版本标识，因为它既不单调递增，也不具备直接的可比性。

PostgreSQL中用GENERATED ALWAYS AS IDENTITY配合版本列

PostgreSQL 12及以上版本支持的GENERATED ALWAYS AS IDENTITY，确实好用，但它只适用于主键的自增场景，没法直接拿来当版本号用。版本号的控制权，必须牢牢握在业务逻辑手里。所以，实际做法依然是手动执行UPDATE ... SET version = version + 1。不过，PostgreSQL有个利器可以帮上忙，那就是RETURNING子句，它能让你一次性拿到更新后的新版本号：

UPDATE documents SET content = 'new text', version = version + 1 WHERE id = 123 AND version = 5 RETURNING version;

这条语句执行后，会直接返回更新后的version值（也就是6），应用层可以立刻拿它去写日志或者构造响应。但有一点需要特别注意：如果WHERE条件不匹配（比如在你更新的瞬间，别人已经把版本号升到6了），那么RETURNING将没有结果返回——这不是返回空值，而是零行记录。这个细节常常被忽略，容易导致程序误判更新成功。

MySQL中处理高并发下的版本更新失败重试

MySQL默认的事务隔离级别是REPEATABLE READ，但这个设置对乐观锁本身的冲突解决没有直接帮助。版本冲突本质上是应用层的逻辑问题，数据库不会自动介入。真正的挑战在于：当更新失败后，如何安全地进行重试。这里有三个关键点：

• 每次重试之前，都必须重新用SELECT ... FOR UPDATE查询当前的version和业务数据。否则，你的重试可能就是基于一个过期的数据快照进行的。
• 重试次数建议设置一个明确的上限，比如3次，避免陷入死循环。如果第3次还失败，就应该抛出一个类似OptimisticLockException的错误。
• 不要把多次UPDATE重试嵌套在一个长事务里。这会延长锁的持有时间，加剧资源争用。更佳实践是把“读-改-写”这一套操作，作为一个独立的事务单元来执行。

这里有个典型的反面模式：执行UPDATE t SET v=v+1 WHERE id=1时没带版本校验，然后试图通过SELECT v再来判断是否成功。这中间存在一个时间窗口，别人可能已经更新了两次，而你的计数器只加了一次，数据的一致性就无从谈起了。

SQLite中模拟乐观锁时要注意WAL模式的影响

SQLite在WAL（Write-Ahead Logging）模式下，确实支持“多读一写”，并发读取不会阻塞。但这并不意味着乐观锁就失效了。因为UPDATE操作会获取reserved lock，多个写请求最终会被串行化处理。问题在于，如果应用程序使用sqlite3_exec且没有显式开启事务，那么每次UPDATE都是一个独立的事务，SELECT和UPDATE两个步骤之间，就可能被其他连接插入的写操作干扰。

正确的做法，是显式地将整个操作包裹在BEGIN IMMEDIATE事务中：

BEGIN IMMEDIATE;
SELECT version, content FROM docs WHERE id = 42;
-- 应用层计算新内容与新version
UPDATE docs SET content = ?, version = ? WHERE id = 42 AND version = ?;
COMMIT;

这里的version = ?填入的，就是刚才SELECT出来的那个旧值。如果UPDATE的影响行数为0，那就执行ROLLBACK并重试。切记：不要使用BEGIN DEFERRED，因为它无法阻止其他写入操作提前获得锁，可能导致幻读问题。

说到底，实现数据版本化远不是简单加个字段就能万事大吉的。它要求每一次写操作，都必须携带对前置状态的校验，而这个校验，必须落在同一个事务、甚至最好是同一条SQL语句中，才能真正可靠。无论是漏掉WHERE子句里的版本条件，还是把SELECT和UPDATE拆分成两个独立的事务，都会让版本号彻底沦为一个无用的装饰品。