主页 / dba / 问题 / 223926
Accepted
GollyJer
Asked: 2018-12-02 15:56:00 +0800 CST

为什么这个看似简单的insert触发函数会导致死锁呢？

  • 772

这是我先前问题的简化版本。我消除了多对多的复杂性，但仍然存在死锁。它发生的频率较低，但仍然会发生。?

情况...
我有一张tweet表，其中一列接收到一个array[]::text[]url。
表上有一个触发器函数，可以将 url 插入url_starting表中。

url_starting表如下所示。

CREATE TABLE public.url_starting(
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    url text NOT NULL,
    CONSTRAINT url_starting_pk PRIMARY KEY (id),
    CONSTRAINT url_starting_ak_1 UNIQUE (url)
);

推文表触发器看起来像这样。

CREATE OR REPLACE FUNCTION public.create_tweet_relationships()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    INSERT INTO url_starting (url) 
    SELECT DISTINCT UNNEST(NEW.urls)
    ORDER BY 1
    ON CONFLICT DO NOTHING;

    RETURN NULL;
END
$function$;

有时我会遇到这样的死锁错误。

deadlock detected
DETAIL:  Process 4540 waits for ShareLock on transaction 4709; blocked by process 4531.
Process 4531 waits for ShareLock on transaction 4710; blocked by process 4540.
HINT:  See server log for query details.
CONTEXT:  while inserting index tuple (2314,101) in relation "url_starting"
SQL statement "INSERT INTO url_starting (url) 
    SELECT DISTINCT UNNEST(NEW.urls)
    ORDER BY 1
    ON CONFLICT DO NOTHING"
PL/pgSQL function create_tweet_relationships() line 12 at SQL statement

Error causing transaction rollback (deadlocks, serialization failures, etc).

在黑暗中拍摄......?‍♂️

这可能是由 UNNEST 引起的吗？我在语法上做错了什么吗？

当表中没有关系时， 为什么错误说关系“url_starting” ？

有数千条推文同时提交到数据库。如果设置正确应该没关系，不是吗？

postgresql deadlock

2 个回答

  1. 在大量并发写入访问下，防止死锁的方法是在所有写入查询中以一致的顺序处理行。

    您可能已经在对表的所有写入查询中正确地对行进行了排序tweet- 是吗？（我从您的相关问题中知道该表。）

    而且您显然正在对要url_starting在触发器函数中“插入”的行进行排序。

    对于将单行插入tweet到要更新的多个 URL的事务，应该这样做url_starting。

    但是，在向中插入多行时tweet，每行都包含任意 URL 数组，要更新的行在事务中的顺序url_starting仍然不一致。URL 仅按行排序，tweet而不是针对整个事务（甚至命令）。您需要取消嵌套相同的所有URL INSERT，对它们进行一致排序，然后更新插入url_starting。这不能用触发器来实现FOR EACH ROW。我认为在插入具有任意 URL 数组的多行时，您根本无法使用当前方法解决此问题。对两个表的并发写访问本质上是冲突的。

    单行插入tweet应该没问题（每个都在它自己的事务中）——尽管可能要贵得多。也许您可以完全摆脱触发器并重新组织工作流程（使用数据修改 CTE）：将排序的 URL 列表url_starting写入 ，然后写入tweet...

    数组在关系表设计中通常是有问题的。完全规范化可能是另一种方法——用相关表替换数组列。不确定是不是这样...

    或者你让你的事务尽可能短和快，让死锁成为罕见的例外，并准备好你的应用程序以在出现错误时重试。

    • 2
  2. Best Answer

    当埃尔文在他的回答中提到...

    但是，在向中插入多行时tweet，每行都包含任意 URL 数组，要更新的行在事务中的顺序url_starting仍然不一致。URL 仅按行排序，tweet而不是针对整个事务（甚至命令）。

    我脑子里的闪光灯熄灭了。呸！我没有对url_starting.

    也许您可以完全摆脱触发器并重新组织工作流程（使用数据修改 CTE）：将排序的 URL 列表url_starting写入 ，然后写入tweet...

    所以我做到了。我进入了我的 python 代码并进行了重构。它不是简单地将成批的推文发送到推文表，而是发送推文并通过data-modifying CTE.

    伟大的！代码运行了一分钟没有任何问题，然后 ???！
    同样的错误！然后又来了几次。它们比触发器少，但不多。?

    我仍然无法理解的一件事是为什么错误提到了“索引元组”。

    while inserting index tuple (2314,101) in relation "url_starting"

    然后我想起了两件事。

    1. 每当使用INSERT INTO主ON CONFLICT DO NOTHING键时，每次尝试都会递增，即使通过 DO NOTHING 跳过了条目，也会在自动递增模式中留下间隙。
    2. PostgreSQL 使用索引元组进行内部跟踪。从文档...

      系统使用的最终标识符类型是 tid，或元组标识符（行标识符）。这是系统列ctid 的数据类型。元组 ID 是一对（块编号，块内的元组索引），用于标识行在其表中的物理位置。

    考虑到这一点，我想......也许由于一次处理/跳过数千个并发重复项，这个内部索引系统正在发生竞争条件？

    这个想法导致了一个测试，我在尝试向url_upsert表中添加任何内容之前过滤掉重复项。tid/ctid通过消除跳过/浪费的内部元组 ID ( )的创建，我不希望出现内部竞争条件。

    这个想法导致了这个查询（通过 psycopg2 中的 execute_many() 函数发送）。?

    WITH cte_data (twitter_id, created_at, contents, search_hits, urls) AS (
    VALUES
    (NULL::text, NULL::timestamp, NULL::text, NULL::text[], NULL::text[]),
    %s
    OFFSET 1
)
, upserted_tweets AS (
    INSERT INTO tweet (twitter_id, created_at, contents, search_hits)
        SELECT twitter_id, created_at, contents, search_hits
        FROM cte_data
        ORDER BY 1
    ON CONFLICT DO NOTHING
    RETURNING id, twitter_id
    )
, upserted_tweets_with_urls AS (
    SELECT id, urls
    FROM upserted_tweets
    JOIN cte_data USING (twitter_id)
)
, unique_urls AS (
    SELECT DISTINCT UNNEST(urls) url
    FROM cte_data
)
, new_urls AS (
    SELECT url
    FROM url_starting
    RIGHT JOIN unique_urls USING (url)
    WHERE id IS NULL
    ORDER BY 1
)
, inserted_urls AS (
    INSERT INTO url_starting (url)
        TABLE new_urls
    ON CONFLICT DO NOTHING
    RETURNING id, url
)
INSERT INTO tweet_x_url_starting (id_tweet, id_url_starting)
    SELECT ut.id, iu.id
    FROM upserted_tweets_with_urls ut
    JOIN inserted_urls iu
        ON (iu.url = ANY (ut.urls))
    ORDER BY 1, 2
ON CONFLICT DO NOTHING;

    这个坏男孩跑了大约 30 分钟，然后没有错误???！新错误！?‍♂️

    ON CONFLICT DO UPDATE 命令不能第二次影响行 提示：确保在同一命令中建议插入的行没有重复的约束值。

    幸运的是，这次没什么大不了的。我将每 30 分钟服用一次，而不是每 30 秒服用一次。

    我很乐意通过完全理解问题并修复它来消除所有错误。但是，就目前而言，我可以忍受每 30 分钟左右出现一次错误并重新运行该批次。?

    删除线！以上不再正确。我从桌子上取下ON CONFLICT DO UPDATE了upserted_tweets。似乎ctid问题也出现在那里。

    幸运的是，我真的不需要更新任何东西，所以这实际上只是一个巨大的插入。

    它现在运行 10 多个并发连接，每个连接同时向数据库添加数千个条目。?

    • 2

相关问题