Início / dba / Perguntas / 278736
Accepted
Anentropic
Asked: 2020-10-28 04:15:58 +0800 CST

Qual é o comportamento de bloqueio do Postgres quando a cláusula UPDATE WHERE faz uma varredura de tabela?

  • 772

Digamos que você tenha uma grande tabela com dezenas de milhões de linhas.

Você deseja UPDATE large_table SET col=value WHERE col=other_value... mas colnão está indexado e EXPLAINmostra que essa consulta fará uma varredura seq em toda a tabela.

Qual é o comportamento de bloqueio aqui? De acordo com a maioria das contas, o Postgres bloqueia apenas as linhas afetadas de uma consulta UPDATE e não possui escalonamento de bloqueio. Então, ele procura as linhas para atualizar primeiro e depois bloqueia apenas as linhas encontradas? Parece que potencialmente haveria problemas de outras consultas atualizando linhas simultaneamente nesse caso. Ele bloqueia cada linha "como as encontra", ou seja, bloqueia as linhas progressivamente à medida que passa pela varredura seq?

Portanto, acho que o melhor caso aqui é bloquear as linhas à medida que as encontra, e as linhas afetadas (somente) serão bloqueadas pelo tempo que a consulta UPDATE levar para ser concluída.

Mas estou preocupado que essa consulta possa acabar bloqueando todas as gravações na tabela até que seja concluída.

Eu li isso: https://habr.com/en/company/postgrespro/blog/503008/ e acho que o pior caso não vai acontecer, mas aqui https://blog.heroku.com/curious-case-table -locking-update-query é uma representação possivelmente imprecisa de informações semelhantes que me dão algumas dúvidas.

O aplicativo usa apenas SELECT, SELECT FOR UPDATEe UPDATEconsultas (ou seja, nenhum outro bloqueio explícito separado desses). A tabela possui chaves estrangeiras para outras tabelas e outras tabelas possuem chaves estrangeiras para esta tabela.

Estamos no Postgres 11.

postgresql lock-escalation

1 respostas

  1. Best Answer

    Para a discussão, vamos supor que seu plano de execução se parece com

            QUERY PLAN        
--------------------------
 Update on mytab
   ->  Seq Scan on mytab
         Filter: (id = 1)

    Também presumo que você esteja usando o READ COMMITTEDnível de isolamento padrão.

    Então o PostgreSQL lerá sequencialmente a tabela.

    Sempre que encontrar uma linha que corresponda ao filtro, essa linha será bloqueada e atualizada.

    Se o bloqueio de uma linha for bloqueado por uma consulta simultânea, o PostgreSQL espera até que o bloqueio desapareça. Em seguida, ele reavalia a condição do filtro e avança (se a condição não se aplicar mais devido a uma modificação simultânea) ou bloqueia e atualiza a linha modificada.

    Veja a documentação :

    UPDATE, DELETE, SELECT FOR UPDATEe SELECT FOR SHAREcomandos se comportam da mesma forma queSELECTem termos de busca de linhas de destino: eles só encontrarão linhas de destino que foram confirmadas na hora de início do comando. No entanto, essa linha de destino pode já ter sido atualizada (ou excluída ou bloqueada) por outra transação simultânea no momento em que for encontrada. Nesse caso, o suposto atualizador aguardará que a primeira transação de atualização seja confirmada ou revertida (se ainda estiver em andamento). Se o primeiro atualizador reverter, seus efeitos serão negados e o segundo atualizador poderá prosseguir com a atualização da linha originalmente encontrada. Se o primeiro atualizador confirmar, o segundo atualizador ignorará a linha se o primeiro atualizador a excluiu, caso contrário, ele tentará aplicar sua operação à versão atualizada da linha. A condição de pesquisa do comando (oWHEREcláusula) é reavaliada para ver se a versão atualizada da linha ainda corresponde à condição de pesquisa. Em caso afirmativo, o segundo atualizador prossegue com sua operação usando a versão atualizada da linha.

    Em particular, é possível que duas UPDATEinstruções, cada uma modificando várias linhas, entrem em deadlock, uma vez que adquirem bloqueios à medida que prosseguem e os bloqueios são sempre mantidos até o final da transação.

    • 5

relate perguntas