postgres postgresql locks

Locks são necessários para controle de concorrência e para que várias aplicações acessem os mesmos dados. Eles podem adquiridos pelas transações quando executando comandos SQL (locks implícitos) ou podem ser requisitados pela aplicação dentro de uma transação (locks explícitos) e serem usados como primitivas de sincronização entre as aplicações.

Considerando que a maioria dos locks são liberados apenas no fim da transação em que foram adquiridos e que as transações podem ter durações longas por diversas razões (erro de implementação da aplicação, grande volume de dados afetado, _deadlocks_…), assim como o fato de que locks impedem outras transações de realizar operações (escritas e leituras) nos dados, é de extrema importância garantir que as transações sejam o mais curtas possível e que operações de longa duração sejam descobertas e resolvidas (possivelmente sendo canceladas e reiniciadas ou pelo cancelamento de outra operação que a esteja impedindo).

Para isso, o PostgreSQL fornece a visão pg_locks para inspeção dos locks ativos. As colunas mais importantes dela são:

• locktype: tipo de lock: relation, extend, page, tuple, transactionid, virtualxid, object, userlock ou advisory
• database: banco de dados
• relation: objeto do banco de dados
• page: página do objeto
• tuple: tupla da página
• virtualtransaction: transação
• pid: pid do processo de backend do PostgreSQL que adquiriu o lock
• mode: modo do lock
• granted: indica se a transação conseguiu adquirir (true) o lock ou se está aguardando outra transação liberá-lo (false)
• …

É possível obter mais informações sobre as transações mencionadas em pg_locks através de uma junção com a visão pg_stat_activity, como o exemplo:

SELECT * FROM pg_locks pl LEFT JOIN pg_stat_activity psa
    ON pl.pid = psa.pid;

Também é possível obter mais informações sobre transações que adquiriram locks e transações aguardando para adquirir eles fazendo a junção de pg_locks com ela própria. Contudo, a forma mais simples é usando a função pg_blocking_pids():

SELECT * FROM pg_locks pl LEFT JOIN pg_stat_activity psa
    ON psa.pid = ANY(pg_blocking_pids(pl.pid))
    WHERE NOT pl.granted;

Locks com longa duração podem ser logados com log_lock_waits. Deadlocks são sempre detectados, logados e resolvidos com a terminação de um dos participantes. O tempo de duração deles é definido por deadlock_timeout.

O PostgreSQL pode matar automaticamente transações ociosas de longa duração, definida pela configuração idle_in_transaction_session_timeout.

Também é possível cancelar o comando em andamento usando a função pg_cancel_backend() e matar o backend com a função pg_terminate_backend(), por exemplo:

[[local]:5432] postgres@postgres=# SELECT pg_terminate_backend(pid)
[[local]:5432] postgres@postgres-# WHERE state = 'idle in transaction' AND
[[local]:5432] postgres@postgres-#   xact_start < now() - interval '30 min';

1. Abra algumas sessões por psql para o mesmo banco de dados.
2. Em uma delas, crie uma tabela: CREATE TABLE tabela();
3. Em todas, inicie uma transação: BEGIN;
4. Em todas, tente adquirir um LOCK explícito na tabela: LOCK tabela;
5. Observe quantas conseguiram o lock daquele objeto pela visão pg_locks: SELECT * FROM pg_locks WHERE relation::regclass::text = 'tabela'.
6. Observe o que as outras estão fazendo com a consulta que junta pg_locks com pg_stat_activity através de pg_blocking_pids().
7. Termine a transação que adquiriu o lock com COMMIT; ou ROLLBACK;
8. Observe novamente as visões e o que elas dizem sobre as transações com e sem locks.