Postgresql 中文操作指南

13.5. Serialization Failure Handling #

可重复读和串行隔离级别都会生成旨在防止序列化异常的错误。如前所述,使用这些级别的应用程序必须做好准备以重试由于序列化错误而失败的事务。此类错误的消息文本会根据具体情况而有所不同,但它始终具有 SQLSTATE 代码 40001 (serialization_failure)。

Both Repeatable Read and Serializable isolation levels can produce errors that are designed to prevent serialization anomalies. As previously stated, applications using these levels must be prepared to retry transactions that fail due to serialization errors. Such an error’s message text will vary according to the precise circumstances, but it will always have the SQLSTATE code 40001 (serialization_failure).

重试死锁错误也可能是明智的。这些错误具有 SQLSTATE 代码 40P01 (deadlock_detected)。

It may also be advisable to retry deadlock failures. These have the SQLSTATE code 40P01 (deadlock_detected).

在某些情况下,重试唯一键错误(具有 SQLSTATE 代码 23505 (unique_violation))和排除约束错误(具有 SQLSTATE 代码 23P01 (exclusion_violation))也是适当的。例如,如果应用程序在检查当前存储的键之后为主键列选择了新的值,它可能会得到唯一键错误,因为另一个应用程序实例同时选择了相同的新键。这实际上是一个序列化错误,但是服务器不会将其检测为序列化错误,因为它无法“看到”插入值与先前读取之间的连接。还有一些极端情况下,服务器将发出唯一键或排除约束错误,即使原则上它有足够的信息来确定序列化问题是根本原因。虽然建议无条件重试 serialization_failure 错误,但在重试这些其他错误代码时需要更加小心,因为它们可能表示持久错误条件而不是瞬态故障。

In some cases it is also appropriate to retry unique-key failures, which have SQLSTATE code 23505 (unique_violation), and exclusion constraint failures, which have SQLSTATE code 23P01 (exclusion_violation). For example, if the application selects a new value for a primary key column after inspecting the currently stored keys, it could get a unique-key failure because another application instance selected the same new key concurrently. This is effectively a serialization failure, but the server will not detect it as such because it cannot “see” the connection between the inserted value and the previous reads. There are also some corner cases in which the server will issue a unique-key or exclusion constraint error even though in principle it has enough information to determine that a serialization problem is the underlying cause. While it’s recommendable to just retry serialization_failure errors unconditionally, more care is needed when retrying these other error codes, since they might represent persistent error conditions rather than transient failures.

重试整个事务非常重要,包括所有决定使用哪个 SQL 和/或使用哪些值的逻辑。因此,PostgreSQL 不提供自动重试机制,因为它不能确保证正确执行。

It is important to retry the complete transaction, including all logic that decides which SQL to issue and/or which values to use. Therefore, PostgreSQL does not offer an automatic retry facility, since it cannot do so with any guarantee of correctness.

事务重试不能确保重试的事务会完成;可能需要多次重试。在竞争非常激烈的情况下,完成一项事务可能需要多次尝试。在涉及冲突的已准备事务的情况下,可能无法在已准备的事务提交或回滚之前取得进展。

Transaction retry does not guarantee that the retried transaction will complete; multiple retries may be needed. In cases with very high contention, it is possible that completion of a transaction may take many attempts. In cases involving a conflicting prepared transaction, it may not be possible to make progress until the prepared transaction commits or rolls back.