主页 / dba / 问题 / 270328
Accepted
Gherman
Asked: 2020-07-04 03:00:16 +0800 CST

PostgreSQL 有时无法检测到序列化失败

  • 772

我有一个应用程序语言的 http-server，大致如下所示：

item = SELECT item FROM table WHERE field = 'value'
if (item) {
  UPDATE another field on item
} else {
  INSERT item VALUE field = 'value' and so on
}

该字段具有唯一约束。大约每隔几分钟就会有两个相同的请求同时value进入。他们都执行相同的代码，所以当然存在竞争条件。我决定如果我将它包装在具有 SERIALIZABLE 隔离级别的事务中并在每次序列化失败时重试，那么问题就解决了。在本地主机上它工作得很好。我确实看到了如预期的错误代码 40001 引发的序列化失败。

但是，由于某种原因，在负载下的数据库中的生产中Unique constraint violation偶尔会抛出代码 23505。它只是有时发生。我付出了很多努力在 localhost 上重现它，但失败了。每次我得到一个正常的 40001。我尝试在不同的进程中运行服务器代码，并在不同的点放置延迟以强制执行某些执行顺序。它只发生在生产中。所以我在 prod 中添加了广泛的日志记录。日志说，每次出现问题时，一item开始都没有发现，但当 INSERT 发生时，它就会触发Unique constraint violation。所以它看起来只是一个竞争条件。

但是为什么有时在某些情况下 Postgres SERIALIZABLE 事务没有按应有的方式检测到它呢？或者如何调试？在几十个案例中，它一天会发生好几次。

（几乎）准确的 SQL 如下所示：（我省略了详细的字段列表和准确的参数）

START TRANSACTION
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

SELECT *
FROM products
WHERE product_code = $1
LIMIT 1 -- product_code is a parameter

SELECT *
FROM licenses
WHERE license_key = $1
LIMIT 1 -- license_key is another and it's unique across the table
-- At this point SELECT did not return anything but it was inserted before this INSERT by exactly the same transaction

INSERT INTO licenses ("license_key", "product_id", "other_data",...)
VALUES ($1, $2, $3,...) RETURNING "id"
query failed: error: duplicate key value violates unique constraint "licenses_license_key_key"
postgresql transaction

2 个回答

  1. Best Answer

    除非存在错误，否则在序列化事务中执行此操作应该可行。

    作为一种解决方法，请使用此命令，它会尝试添加新许可证；如果许可证密钥已经存在，它将新小时添加到现有值：

    INSERT INTO licenses(license_key, product_id, hours)
VALUES ($1,
        (SELECT product_id FROM products WHERE product_code = $2 LIMIT 1),
        $3)
ON CONFLICT (license_key) DO UPDATE
SET hours = hours + EXCLUDED.hours
RETURNING id;

    文件

    • 1

  2. 此评论/答案由 Thomas Munro 发送给我：

    我的猜测是它们可能有多个唯一索引，并且可能遇到了这个问题。

    总结： 过去我们总是在检测到 SSI 故障之前报告唯一约束违规 (UCV)，其想法是错误无关紧要；SSI 只对提交的事务做出承诺。然后我们添加了一行调整，使其在引发 UCV 之前尝试检测 SSI 故障（将其视为错误优先级：恕我直言，SSI 错误需要更高的优先级，因为自动事务重试系统需要可靠的 SSI 错误；它们是什么？应该与UCV有关吗？）。但现在我们知道这还不够好。如该线程中所述，在报告 UCV 之前，我们可能需要四处奔波并找到 所有在我们报告 UCV 之前，您将插入的索引让他们有机会检查 SSI 错误。在该 -bugs 线程中报告的情况下，您可以通过更改定义 UNIQUE 约束的顺序来更改结果。啊。这需要对架构进行一些重新设计，可能包括一个新的索引 AM 条目功能，但我认为这是值得的（如果做得好，它还可以允许 SSI 错误比排除约束错误具有更高的优先级）。

    • 1

相关问题