Postgresql 中文操作指南

64.4. Index Locking Considerations #

索引访问方法必须处理多个进程对索引的并发更新。核心 PostgreSQL 系统在索引扫描期间在索引上获取 AccessShareLock，并在更新索引（包括普通 VACUUM）时获取 RowExclusiveLock。由于这些锁类型不冲突，因此访问方法负责处理可能需要的任何细粒度锁。只有在创建索引、销毁索引或 REINDEX 时才对整个索引进行 ACCESS EXCLUSIVE 锁定（SHARE UPDATE EXCLUSIVE 而是带有 CONCURRENTLY 获取的）。

Index access methods must handle concurrent updates of the index by multiple processes. The core PostgreSQL system obtains AccessShareLock on the index during an index scan, and RowExclusiveLock when updating the index (including plain VACUUM). Since these lock types do not conflict, the access method is responsible for handling any fine-grained locking it might need. An ACCESS EXCLUSIVE lock on the index as a whole will be taken only during index creation, destruction, or REINDEX (SHARE UPDATE EXCLUSIVE is taken instead with CONCURRENTLY).

构建支持并发更新的索引类型通常需要对所需行为进行大量的细致分析。对于 b 树和哈希索引类型，您可以在 src/backend/access/nbtree/README 和 src/backend/access/hash/README 中阅读有关设计决策的内容。

Building an index type that supports concurrent updates usually requires extensive and subtle analysis of the required behavior. For the b-tree and hash index types, you can read about the design decisions involved in src/backend/access/nbtree/README and src/backend/access/hash/README.

除了索引自身的内部一致性要求之外，并发更新还会在父表（heap）和索引之间创建一致性问题。由于 PostgreSQL 将堆的访问和更新与索引的访问和更新分开，因此存在索引可能与堆不一致的时间窗口。我们使用以下规则处理这个问题：

Aside from the index’s own internal consistency requirements, concurrent updates create issues about consistency between the parent table (the heap) and the index. Because PostgreSQL separates accesses and updates of the heap from those of the index, there are windows in which the index might be inconsistent with the heap. We handle this problem with the following rules:

如果没有第三条规则，索引读取器就有可能在 VACUUM 删除索引条目之前看到该条目，然后在 VACUUM 删除了相应的堆条目之后到达该条目。如果读者在到达该条目时该条目号仍未使用，则不会产生严重问题，因为 heap_fetch() 将忽略空条目槽。但是，如果第三个后端已经将此项目槽重新用于其他内容，该怎么办？当使用符合 MVCC 的快照时，没有问题，因为时隙的新使用者肯定太新而无法通过快照测试。但是，对于不符合 MVCC 的快照（例如 SnapshotAny），就有可能接受并返回实际上与扫描键不匹配的行。在所有情况下，我们可以通过要求将扫描键与堆行重新检查来防御这种情况，但这代价太大了。相反，我们使用索引页面上的 pin 作为代理，表示读取器可能仍在从索引条目到匹配堆条目的“途中”。让 ambulkdelete 在此类 pin 上阻塞可确保 VACUUM 在读者完成使用堆条目之前无法删除该堆条目。此解决方案在运行时成本很低，并且只有在实际发生冲突时才会增加阻塞开销。

Without the third rule, it is possible for an index reader to see an index entry just before it is removed by VACUUM, and then to arrive at the corresponding heap entry after that was removed by VACUUM. This creates no serious problems if that item number is still unused when the reader reaches it, since an empty item slot will be ignored by heap_fetch(). But what if a third backend has already re-used the item slot for something else? When using an MVCC-compliant snapshot, there is no problem because the new occupant of the slot is certain to be too new to pass the snapshot test. However, with a non-MVCC-compliant snapshot (such as SnapshotAny), it would be possible to accept and return a row that does not in fact match the scan keys. We could defend against this scenario by requiring the scan keys to be rechecked against the heap row in all cases, but that is too expensive. Instead, we use a pin on an index page as a proxy to indicate that the reader might still be “in flight” from the index entry to the matching heap entry. Making ambulkdelete block on such a pin ensures that VACUUM cannot delete the heap entry before the reader is done with it. This solution costs little in run time, and adds blocking overhead only in the rare cases where there actually is a conflict.

此解决方案要求索引扫描为“同步”：我们必须在扫描相应索引条目后立即获取每个堆元祖。由于多种原因，这开销很大。一种“异步”扫描，其中我们从索引中收集许多 TID，并且仅在稍后的某个时间访问堆元祖，需要较小的索引锁定开销，并且可以允许更有效的堆访问模式。根据上述分析，我们必须对不兼容 MVCC 的快照使用同步方法，但对于使用 MVCC 快照的查询，异步扫描是可行的。

This solution requires that index scans be “synchronous”: we have to fetch each heap tuple immediately after scanning the corresponding index entry. This is expensive for a number of reasons. An “asynchronous” scan in which we collect many TIDs from the index, and only visit the heap tuples sometime later, requires much less index locking overhead and can allow a more efficient heap access pattern. Per the above analysis, we must use the synchronous approach for non-MVCC-compliant snapshots, but an asynchronous scan is workable for a query using an MVCC snapshot.

在 amgetbitmap 索引扫描中，访问方法不会对任何返回元祖保留索引 pin。因此，只有使用 MVCC 兼容快照才能安全使用此类扫描。

In an amgetbitmap index scan, the access method does not keep an index pin on any of the returned tuples. Therefore it is only safe to use such scans with MVCC-compliant snapshots.

当 ampredlocks 标志未设置时，使用串行事务中的该索引访问方法的任何扫描都将在整个索引上获取非阻塞谓词锁。这将与并发串行事务将任何元祖插入该索引的行为生成读写冲突。如果在并发串行事务中检测到某些模式的读写冲突，则可能取消其中一个事务以保护数据完整性。当设置该标志时，表示索引访问方法实现了更精细的谓词锁定，该谓词锁定将倾向于降低此类事务取消的频率。

When the ampredlocks flag is not set, any scan using that index access method within a serializable transaction will acquire a nonblocking predicate lock on the full index. This will generate a read-write conflict with the insert of any tuple into that index by a concurrent serializable transaction. If certain patterns of read-write conflicts are detected among a set of concurrent serializable transactions, one of those transactions may be canceled to protect data integrity. When the flag is set, it indicates that the index access method implements finer-grained predicate locking, which will tend to reduce the frequency of such transaction cancellations.