主页 / dba / 问题 / 327135
Accepted
Mio
Asked: 2023-05-15 22:23:38 +0800 CST

如何在 PostgreSQL 的更新上模拟 FOR UPDATE 锁？

  • 772

我正在尝试使用 PostgreSQL 14 和更新时的行级锁定。文档提到：

FOR UPDATE 锁模式也可以通过行上的任何 DELETE 获取，也可以通过修改某些列的值的 UPDATE 获取。目前，UPDATE 案例考虑的列集是那些具有可在外键中使用的唯一索引的列（因此不考虑部分索引和表达式索引），但这在未来可能会改变。

我创建了一个表documents，添加了一个user_id带有 UNIQ 约束的字段。

然后在一个会话

UPDATE documents SET user_id=2 WHERE id=1 AND pg_sleep(5) IS NOT NULL;

在第一个会话上按 Enter 后 1 秒后的另一个会话。

UPDATE documents SET user_id=1 WHERE id=1;

我看到的问题是没有获得锁。第二个查询立即执行，然后第一个查询覆盖提交。此外，慢速查询不遵守pg_sleep时间（在非约束列上更新时没有问题），查询需要 55-60 秒。

  1. 为什么没有获取到锁？
  2. 为什么更新这么慢？
postgresql

2 个回答

  1. Best Answer

    锁不是在语句开始时获取，而是在找到行时获取。pg_sleep(5)由于您在条件中使用WHERE，因此在找到该行并且 PostgreSQL 尝试锁定它之前需要五秒钟。到那时您的第二个语句已锁定该行，因此第一个语句必须等到运行第二个语句的事务完成。另外，不要忘记每行pg_sleep(5)都会执行一次，所以第一个语句可能会花费 5 秒以上的时间。

    • 1

  2. 同一文档页面指出：

    FOR UPDATE导致语句检索的行SELECT被锁定，就像要更新一样。

    您可以将FOR UPDATEhint 与SELECT语句一起使用，而不是与UPDATE. 它可以在多语句（即显式）事务中使用，以独占锁定正在读取的行，以便稍后在同一事务中更新它们。锁以独占模式获取。如果您不更新主键列，最好使用FOR NO KEY UPDATE模式。或者完全跳过所有这些并依赖 PostgreSQL 的默认 MVCC 操作模式。

    在https://www.postgresql.org/docs/current/applevel-consistency.html#NON-SERIALIZABLE-CONSISTENCY中有更多信息

    • 1

相关问题