PostgreSQL带函数与行锁的查询执行顺序及锁机制技术问询

问题概述 #

假设PostgreSQL中有表t（仅一行数据）和函数f，执行select f(t.id) from t for update时，函数f运行期间该行是否已被锁定？通过含sleep的函数测试发现：f休眠时其他连接可更新该行，之后f会再次执行，此时事务持有行锁；无并发更新时f仅执行一次。

核心执行流程 #

SELECT f(t.id) FROM t FOR UPDATE的执行分为三个关键阶段：

1. 初始行扫描与函数调用：PostgreSQL先以普通SELECT逻辑读取事务启动时已提交的行版本，此时未加行锁，直接调用函数f处理该行数据。
2. 尝试加锁：函数执行完成后，系统尝试对该行加FOR UPDATE排他锁。若此时该行已被其他并发事务修改或锁定，当前事务会进入等待状态，直到对方事务提交或回滚。
3. 冲突处理与重试：
   • 若并发事务回滚：行版本恢复为原始状态，当前事务无需重试，直接锁定原始行并返回结果。
   • 若并发事务提交：
     • 若该行已被删除，当前事务忽略该行，无结果返回。
     • 若该行仍存在，系统会读取最新行版本，重新调用函数f处理新数据，然后锁定最新行版本并返回结果。

行为依赖的关键因素 #

• 事务隔离级别：仅在默认的READ COMMITTED隔离级别下会触发重试逻辑；若使用REPEATABLE READ或更高隔离级别，遇到行冲突会直接抛出could not serialize access due to concurrent update错误，不会重试函数。
• 并发操作类型：如果并发事务是对该行执行更新/删除，会触发当前事务的全流程重试；如果只是加锁（如另一个FOR UPDATE），当前事务只需等待锁释放，无需重新执行函数。
• 函数属性：即使函数标记为STABLE（承诺同一输入返回相同结果），由于行版本发生变化，系统仍会重新执行函数，因为输入的行数据已改变。

官方文档细节补充 #

针对官方文档中关于SELECT FOR UPDATE的描述，补充关键逻辑：

UPDATE、DELETE、SELECT FOR UPDATE和SELECT FOR SHARE命令在查找目标行时的行为与SELECT一致：仅能找到命令启动时已提交的目标行。但当找到目标行时，该行可能已被其他并发事务更新（或删除、锁定）。这种情况下，后续操作的事务会等待首个更新事务提交或回滚（若其仍在执行）。若首个更新事务回滚，其操作效果被撤销，后续事务可继续对原始行执行操作；若首个更新事务提交，若该行已被删除则后续事务忽略该行，否则会尝试对该行的更新版本执行操作，并重评估命令的搜索条件（WHERE子句）以判断更新后的行是否仍匹配条件，若匹配则使用更新后的行继续操作。对于SELECT FOR UPDATE和SELECT FOR SHARE，意味着锁定并返回给客户端的是更新后的行版本。

这段描述的核心是：SELECT FOR UPDATE的锁行动作晚于初始行扫描和函数调用，这就是函数运行时行未被锁定的原因。当并发更新导致行版本变化时，系统会重新执行整个行处理流程（包括函数调用），最终锁定最新的行版本。

权威资源推荐（除源码外） #

• PostgreSQL官方文档的SELECT语法章节和并发控制章节：虽然细节分散，但结合阅读能覆盖SELECT FOR UPDATE的核心执行逻辑，尤其是READ COMMITTED隔离级别下的重试机制。
• 专业技术书籍：如《PostgreSQL实战》《PostgreSQL修炼之道》，这类书籍会结合实际场景解析锁机制与执行流程，比官方文档更具象。
• PostgreSQL官方邮件列表（pgsql-general）：核心开发者会在列表中解答底层执行问题，是获取权威细节的可靠渠道。

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