PostgreSQL默认READ_COMMITTED级别下，带WHERE子句的UPDATE能否防丢失更新？

假设你为了防止两名用户同时更新同一维基页面，尝试用以下SQL语句，期望仅当页面内容自用户开始编辑后未发生变化时才执行更新：

UPDATE wiki_pages SET content = 'new content'
  WHERE id = 1234 AND content = 'old content';

在PostgreSQL默认的READ_COMMITTED隔离级别下，这个操作是安全的，能够有效防止丢失更新。

原因如下：

• PostgreSQL执行UPDATE语句时，会先定位匹配WHERE条件的行，然后对这些行加排他锁（Exclusive Lock）。第一个事务执行这条语句时，会锁定id=1234且content='old content'的目标行。
• 当第二个事务执行相同的UPDATE时，会因无法获取目标行的锁进入等待状态，直到第一个事务结束：
  • 如果第一个事务提交，目标行的content已变为new content，第二个事务的WHERE条件不再匹配任何行，不会执行更新，也就不会覆盖前一个事务的修改。
  • 如果第一个事务回滚，锁会被释放，第二个事务就能正常匹配原始行并执行更新。

这种通过WHERE条件验证内容版本、结合PostgreSQL行锁机制的方式，在READ_COMMITTED级别下可可靠避免丢失更新问题。

内容的提问来源于stack exchange，提问作者Iakov Burtsev