Início / dba / Perguntas / 323040
Accepted
ChrisJ
Asked: 2023-02-04 18:08:01 +0800 CST

Evitando deadlocks ao bloquear várias linhas sem usar NOWAIT

  • 772

Em algumas respostas , fui informado de que NOWAITé necessário evitar impasses, o que me surpreendeu porque não é mencionado na documentação do Postgres sobre bloqueios e impasses

Dadas as 3 sessões seguintes:

CREATE TABLE my_table (id int primary key);

-- Session 1
BEGIN;
SELECT id FROM my_table
WHERE id IN (1,2,5)
ORDER BY id
FOR UPDATE

-- Session 2, started after the select in session 1 (before it is committed)
BEGIN
SELECT id FROM my_table
WHERE id IN (4,5,6)
ORDER BY id
FOR UPDATE

-- Session 3, started after the select in session 2 (before 1 or 2 are committed)
BEGIN
SELECT id FROM my_table
WHERE id IN (5,6,7)
ORDER BY id
FOR UPDATE

Eu pensei que cada consulta esperaria uma vez que chegasse a uma linha bloqueada e continuaria bloqueando na ordem especificada por ORDER BY. No entanto, com base nessas respostas, parece que a ordem de bloqueio não é garantida mesmo com um ORDER BYe é possível que essas instruções possam travar?

Espero evitar o uso NOWAITporque o cenário em que estou trabalhando faz parte de uma transação bastante grande e, presumivelmente, o erro resultante invalidaria a transação e eu teria que revertê-la e começar tudo de novo.

As transações são relativamente rápidas, então não me importo de esperar se puder evitar impasses.

Se for verdade que o acima não garante o pedido, a única maneira que vejo de fazer isso seria

SELECT id FROM my_table WHERE id = 1 FOR UPDATE;
SELECT id FROM my_table WHERE id = 2 FOR UPDATE;
...etc

O que parece tedioso. Existe uma maneira melhor?

postgresql

1 respostas

  1. Best Answer

    Como discutimos nesta resposta relacionada , as linhas podem ser bloqueadas fora de ordem se, e somente se ...

    colunas que contribuem para isso ORDER BYpodem ser atualizadas por transações simultâneas

    Isso não deve se aplicar ao seu exemplo com ORDER BY id. Sua coluna "id" parece uma coluna PK substituta que nunca é atualizada em primeiro lugar. Então você está seguro.

    Por que ainda pode haver impasses?

    A defesa recomendada contra impasses é obter bloqueios em uma ordem de classificação consistente e determinística.

    Mas há uma brecha nesse regime confiável: SELECT ... FOR UPDATEbloqueios após a aplicação de filtros ( WHERE) e classificação ( ORDER BY). Se um concorrente UPDATEtiver bloqueado uma ou mais das linhas qualificadas, a primeira consulta deverá aguardar (a menos que seja instruído de outra forma com NOWAIT).
    Se isso UPDATEfor confirmado, a primeira consulta agora prosseguirá e obterá os bloqueios de linha conforme planejado. Mas não classifica as linhas novamente. Se qualquer valor de coluna envolvido em ORDER BYfoi alterado de forma disruptiva, uma linha que anteriormente teria sido bloqueada em ordem, está efetivamente bloqueada fora de ordem agora, reintroduzindo assim a possibilidade de impasses. Normalmente, isso ainda é extremamente improvável de acontecer. Mas alguns padrões de acesso são mais suscetíveis do que outros.

    Considere este caso de teste mínimo. Execute as etapas em três sessões separadas para reproduzir o efeito:

    CREATE TABLE tbl (
  id int PRIMARY KEY
, sort real NOT NULL
);

INSERT INTO tbl (id, sort) VALUES (1,1), (2,2), (3,3);


-- Session 1
BEGIN;
UPDATE tbl
SET    sort = 4
WHERE  sort = 2;

               -- Session 2
               BEGIN;
               SELECT sort
               FROM   tbl
               ORDER  BY sort
               FOR    UPDATE;

                           -- Session 3
                           BEGIN;
                           SELECT sort
                           FROM   tbl
                           WHERE  sort = 3
                           FOR    UPDATE;
                           -- wait
COMMIT;

                           SELECT sort
                           FROM   tbl
                           WHERE  sort = 4
                           FOR    UPDATE;

    A sessão 1 atualiza a linha 2, bloqueando-a.
    A sessão 2 tenta bloquear as linhas em ordem, mas, depois de bloquear a linha 1, tem que esperar pela linha 2.
    A sessão 3 bloqueia a linha 3.
    A sessão 1 confirma. Isso aciona a retomada da sessão 2. A linha 2 agora tem sort = 4, mas está bloqueada de qualquer maneira. Em seguida, ele espera pela linha 3, bloqueada pela sessão 3.
    A sessão 3 tenta bloquear a linha 4, mantendo a ordem de classificação necessária para bloqueios, mas agora entra em conflito de qualquer maneira.
    Boom ... ? impasse!

    Eu recebo isso da sessão 3:

    ERROR:  deadlock detected
DETAIL:  Process 9700 waits for ShareLock on transaction 4412; blocked by process 9663.
Process 9663 waits for ShareLock on transaction 4413; blocked by process 9700.
HINT:  See server log for query details.
CONTEXT:  while locking tuple (0,4) in relation "tbl"

    ("Funciona" com apenas duas transações também. É mais fácil realizar com uma terceira.)

    Relacionado:

    Mas não podemos prever com segurança se a sessão 2 ou a sessão 3 será inicializada.

    Analise se você pode evitar o problema completamente. Idealmente, você não atualiza as colunas envolvidas na ordem de classificação - ou classifica por colunas que não atualiza (como um PK substituto). Então você está seguro. Caso contrário, você terá que pesar os custos e as consequências. Você fala de uma transação "bastante grande" e de transações "relativamente rápidas"...

    Para a transação "bastante grande", você pode usar um SAVEPOINTem uma transação SQL. Ou use uma EXCEPTIONcláusula em um bloco de código PL/pgSQL. Em seguida, tente obter os bloqueios necessários com NOWAIT, pegue o código SQLSTATE (código de erro) 55P03( lock_not_available), em um loop até obter sucesso. Ou saia NOWAITe pegue 40P01( deadlock_detected). Isso adiciona uma sobrecarga considerável, mas pode valer a pena para proteger uma transação "bastante grande". Geralmente, se ocorrerem impasses com sua carga de trabalho, esteja preparado para repetir as transações que falham.

    • 2

relate perguntas