Início / dba / Perguntas / 281846
Accepted
Slim
Asked: 2020-12-19 15:23:10 +0800 CST

Possível para duas atualizações simples na mesma tabela para o impasse?

  • 772

A tabela A é:

id integer
version varchar
data jsonb (large data 1mb)
fkToBid integer (references B.id constraint)

A Tabela B é:

id integer
other...

Os processos estão executando agressivamente as duas atualizações abaixo, em qualquer ordem e fora de qualquer transação.

Registros atualizados na tabela A às vezes se referem ao mesmo registro na tabela B. Além disso, às vezes o mesmo registro A é atualizado.

UPDATE A.version WHERE A.id=:id
and
UPDATE A.data WHERE A.id=:id

Por que, ou pode, esse impasse? É porque os registros atualizados na tabela A se referem à mesma linha na tabela B? Esse impasse pode ser por outro motivo?

Por que vejo um AccessShareLock no índice B pk para essas solicitações de atualização?

postgresql update

2 respostas

  1. Best Answer

    Por que vejo um AccessShareLock no índice B pk para essas solicitações de atualização?

    O Postgres pega esse bloqueio quando verifica a existência de chave estrangeira na tabela pai para uma inserção ou atualização. De RI_FKey_checkdentro src/backend/utils/adt/ri_triggers.c:

        /*
     * Get the relation descriptors of the FK and PK tables.
     *
     * pk_rel is opened in RowShareLock mode since that's what our eventual
     * SELECT FOR KEY SHARE will get on it.
     */
    fk_rel = trigdata->tg_relation;
    pk_rel = table_open(riinfo->pk_relid, RowShareLock);

    A questão é por que o relacionamento de chave estrangeira é verificado, já que nenhuma de suas atualizações afeta a chave estrangeira.

    A resposta mais provável é que o Postgres não pode detectar que a atualização não afetará o relacionamento. De RI_FKey_fk_upd_check_requiredno mesmo arquivo de origem:

        /*
     * If the original row was inserted by our own transaction, we must fire
     * the trigger whether or not the keys are equal.  This is because our
     * UPDATE will invalidate the INSERT so that the INSERT RI trigger will
     * not do anything; so we had better do the UPDATE check.  (We could skip
     * this if we knew the INSERT trigger already fired, but there is no easy
     * way to know that.)
     */
    xminDatum = slot_getsysattr(oldslot, MinTransactionIdAttributeNumber, &isnull);
    Assert(!isnull);
    xmin = DatumGetTransactionId(xminDatum);
    if (TransactionIdIsCurrentTransactionId(xmin))
        return true;

    Apesar do que o comentário do código diz, essa limitação se aplica a uma linha que é atualizada duas vezes, bem como ao caso declarado quando uma linha é inserida e atualizada.

    Reprodução:

      CREATE TABLE b
  (
    id   BIGINT PRIMARY KEY
  );

  INSERT INTO b (id)
  VALUES(1);

  CREATE TABLE a
  (
    id BIGINT PRIMARY KEY,
    version INTEGER NOT NULL,
    data INTEGER NOT NULL,
    b_id BIGINT NOT NULL CONSTRAINT fk_a_b_id REFERENCES b (id)
  );

  INSERT INTO a (id, version, data, b_id)
  VALUES (1, 1, 1, 1);

    explain (analyze, costs off)
UPDATE a
SET version = 2
WHERE id = 1;

explain (analyze, costs off)
UPDATE a
SET data = 3
WHERE id = 1;

    db<>demonstração de violino

    Observe que apenas a segunda explicação da atualização inclui:

    Trigger for constraint fk_a_b_id: time=0.386 calls=1

    (Sim, o Postgres usa gatilhos internos por linha para verificar e aplicar RI.)

    No Postgres 9.3 e posterior, essa verificação de RI é usada FOR KEY SHAREpara melhor simultaneidade, mas isso não significa que seja à prova de deadlock.

    De qualquer forma, apesar do que a pergunta afirma, parece que várias atualizações estão sendo enviadas em um comando. Isso parece ser necessário para que o Postgres execute desnecessariamente a verificação FK que produz o bloqueio da tabela pai visto.

    Se você tiver outros processos acessando a tabela pai com SELECT FOR UPDATE, tente alterá-lo SELECT FOR NO KEY UPDATEconforme recomendado nesta resposta do Stack Overflow pelo autor principal do patch Postgres 9.3 'Melhorar a simultaneidade do bloqueio de chave estrangeira'.

    • 1

  2. O que me vem à mente é um post antigo que escrevi sobre Uber usando PostgreSQL e depois abandonando.

    Aqui está esse post: Por que o MySQL pode lidar com várias atualizações simultaneamente e o PostgreSQL não?

    Nesse meu post antigo, escrevi como o PostgreSQL altera o ID interno ctidde cada linha ao atualizar uma coluna MESMO QUE A COLUNA NÃO ESTEJA INDEXADA.

    Vejo na pergunta que a Tabela A e a Tabela B têm uma relação de chave estrangeira entre elas.

    Se ambas as tabelas estiverem sendo atualizadas simultaneamente, o pai terá que esperar a atualização do filho ou talvez o filho tenha que esperar que o pai atualize. Esperar pelo quê ??? Para cada linha ctidmudar. Esse comportamento deve ser serializado em uma direção ( parent-> child) ou na outra ( child-> parent).

    Esse é o meu melhor palpite.

    • 0

relate perguntas