Início / dba / Perguntas / 223926
Accepted
GollyJer
Asked: 2018-12-02 15:56:00 +0800 CST

Por que essa função de gatilho de inserção aparentemente simples está causando deadlocks?

  • 772

Esta é uma versão simplificada da minha pergunta anterior . Eu removi a complexidade de muitos para muitos e ainda impasses. Isso acontece com menos frequência, mas ainda acontece. ?

A situação...
Tenho uma tweettabela e uma das colunas recebe uma array[]::text[]das urls.
Há uma função de gatilho na tabela que insere as urls em uma url_startingtabela.

A tabela url_starting se parece com isso.

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)
);

E a tabela de tweets, o gatilho se parece com isso.

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$;

Às vezes eu recebo erros de deadlock como este.

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).

Tiros no escuro... ?‍♂️

Isso pode ser causado pelo UNNEST? Estou fazendo algo errado na sintaxe?

Por que o erro diz em relação "url_starting" quando não há relacionamento na tabela?

Existem milhares de tweets comprometidos com o banco de dados simultaneamente. Não importa se configurado corretamente, não é?

postgresql deadlock

2 respostas

  1. Sob acesso de gravação simultâneo pesado, a defesa contra deadlocks é processar linhas em ordem consistente em todas as consultas de gravação.

    Você pode ter classificado as linhas corretamente em todas as suas consultas de escrita na tabela tweet- não é? (Eu sei sobre essa tabela da sua pergunta relacionada .)

    E você obviamente está classificando as linhas para serem "inseridas" na url_startingfunção de gatilho.

    Isso deve ser feito para transações que inserem uma única linha tweetcom vários URLs a serem inseridos em url_starting.

    Mas ao inserir várias linhas em tweet, cada uma com uma matriz arbitrária de URLs, as linhas a serem inseridas url_startingainda estão em ordem inconsistente para a transação. Os URLs são classificados apenas por linha em tweet, não para toda a transação (ou mesmo comando). Você precisaria desaninhar todos os URLs do mesmo INSERT, classificá-los de forma consistente e, em seguida, upsert url_starting. Isso não pode ser alcançado com um gatilho FOR EACH ROW. Eu não acho que você possa resolver isso com sua abordagem atual ao inserir várias linhas com matrizes arbitrárias de URLs. O acesso simultâneo de gravação a ambas as tabelas é inerentemente conflitante.

    As inserções de linha única tweetdevem ser boas (cada uma em sua própria transação) - embora possivelmente substancialmente mais caras. Talvez você possa se livrar completamente do gatilho e reorganizar o fluxo de trabalho (com CTEs de modificação de dados): escreva uma lista ordenada de URLs para url_starting, depois escreva para tweet...

    As matrizes geralmente são problemáticas em um design de tabela relacional. A normalização completa pode ser outra abordagem - substituir a coluna da matriz por uma tabela relacionada. Não tenho certeza se é assim...

    Ou você mantém suas transações o mais curtas e rápidas possível, tornando os deadlocks uma rara exceção, e prepara seu aplicativo para tentar novamente em caso de erro.

    • 2
  2. Best Answer

    Quando Erwin mencionou em sua resposta...

    Mas ao inserir várias linhas em tweet, cada uma com uma matriz arbitrária de URLs, as linhas a serem inseridas url_startingainda estão em ordem inconsistente para a transação. Os URLs são classificados apenas por linha em tweet, não para toda a transação (ou mesmo comando).

    Flashes explodiram na minha cabeça. Duh! Não estou classificando todos os itens sendo upserted em url_starting.

    Talvez você possa se livrar completamente do gatilho e reorganizar o fluxo de trabalho (com CTEs de modificação de dados): escreva uma lista ordenada de URLs para url_starting, depois escreva para tweet...

    E assim eu fiz. Entrei no meu código python e refatorei. Em vez de simplesmente enviar lotes de tweets para a tabela de tweets, ele agora envia os tweets e atualiza todos os URLs (classificando-os primeiro) por meio de um arquivo data-modifying CTE.

    Excelente! O código funcionou por um minuto sem problemas e depois ???!
    O MESMO ERRO! E depois mais algumas vezes. Eles eram menos do que o gatilho, mas não muito. ?

    A única coisa que eu ainda não conseguia identificar era por que o erro mencionava uma "tupla de índice".

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

    Então me lembrei de 2 coisas.

    1. Sempre que o uso INSERT INTOcom ON CONFLICT DO NOTHINGa chave primária é incrementado a cada tentativa, mesmo que a entrada tenha sido ignorada via DO NOTHING, deixando lacunas no padrão de incremento automático.
    2. O PostgreSQL usa uma tupla de índice para seu rastreamento interno. Dos documentos...

      Um tipo de identificador final usado pelo sistema é tid, ou identificador de tupla (identificador de linha). Este é o tipo de dados da coluna do sistema ctid. Um ID de tupla é um par (número de bloco, índice de tupla dentro do bloco) que identifica a localização física da linha em sua tabela.

    Com isso em mente, pensei... talvez a condição de corrida esteja acontecendo neste sistema de indexação interno por causa dos milhares de duplicatas simultâneas sendo processadas/ignoradas ao mesmo tempo?

    Esse pensamento levou a um teste em que eu filtro as duplicatas antes de tentar adicionar qualquer coisa à url_upserttabela. Eu não queria nenhuma chance de uma condição de corrida interna eliminando a criação de ids de tupla internos ignorados/desperdiçados ( tid/ctid).

    Esse pensamento resultou nesta consulta ( enviada através da função execute_many() em psycopg2 ). ?

    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;

    Esse bad boy correu por cerca de 30 minutos sem erros então ???! NOVO ERRO! ?‍♂️

    O comando ON CONFLICT DO UPDATE não pode afetar a linha uma segunda vez DICA: Certifique-se de que nenhuma linha proposta para inserção dentro do mesmo comando tenha valores restritos duplicados.

    Felizmente desta vez não foi grande coisa. Vou tomar um a cada 30 minutos em vez de um a cada 30 segundos.

    Eu adoraria eliminar todos os erros entendendo completamente o problema e corrigindo-o. Mas, por enquanto, posso conviver com um erro a cada 30 minutos ou mais e executar novamente esse lote. ?

    Tachado! O acima não é mais verdade. Tirei o ON CONFLICT DO UPDATEda upserted_tweetsmesa. Parece que o ctidproblema aparece lá também.

    Felizmente, eu realmente não preciso atualizar nada, então esta é realmente apenas uma inserção gigante.

    Ele agora é executado com mais de 10 conexões simultâneas, cada uma adicionando milhares de entradas ao banco de dados simultaneamente. ?

    • 2

relate perguntas