Como faço para detectar e liberar bloqueios no Amazon Redshift?

Breve descrição

O bloqueio é um mecanismo de proteção que controla quantas sessões podem acessar uma tabela ao mesmo tempo. O bloqueio também determina quais operações podem ser realizadas nessas sessões. A maioria dos bancos de dados relacionais usa bloqueios no nível da linha. No entanto, o Amazon Redshift usa bloqueios no nível da tabela. Você pode enfrentar conflitos de bloqueio se executar instruções DDL frequentes em tabelas de usuários ou consultas DML.

O Amazon Redshift tem três modos de bloqueio:

• AccessExclusiveLock: adquirido principalmente durante operações de DDL, como ALTER TABLE, DROP ou TRUNCATE. O AccessExclusiveLock bloqueia todas as outras tentativas de bloqueio.
• AccessShareLock: adquirido durante as operações UNLOAD, SELECT, UPDATE ou DELETE. O AccessShareLock bloqueia somente as tentativas do AccessExclusiveLock. O AccessShareLock não bloqueia outras sessões que estão tentando ler ou gravar na tabela.
• ShareRowExclusiveLock: adquirido durante as operações COPY, INSERT, UPDATE ou DELETE. O ShareRowExclusiveLock bloqueia o AccessExclusiveLock e outras tentativas do ShareRowExclusiveLock, mas não bloqueia as tentativas do AccessShareLock.

Quando uma consulta ou transação adquire um bloqueio em uma tabela, o bloqueio permanece durante a consulta ou transação. Outras consultas ou transações que estão aguardando a aquisição do mesmo bloqueio são bloqueadas, exceto AccessShareLock. Para obter mais informações sobre por que uma consulta pode travar, consulte Consultas travadas.

Para resolver um problema de bloqueio, identifique a sessão (PID) que está mantendo o bloqueio e, em seguida, encerre a sessão. Se a sessão não terminar, reinicialize seu cluster.

Resolução

Execute uma consulta para identificar sessões que estão mantendo bloqueios:

select a.txn_owner, a.txn_db, a.xid, a.pid, a.txn_start, a.lock_mode, a.relation as table_id,nvl(trim(c."name"),d.relname) as tablename, a.granted,b.pid as blocking_pid ,datediff(s,a.txn_start,getdate())/86400||' days '||datediff(s,a.txn_start,getdate())%86400/3600||' hrs '||datediff(s,a.txn_start,getdate())%3600/60||' mins '||datediff(s,a.txn_start,getdate())%60||' secs' as txn_duration
from svv_transactions a
left join (select pid,relation,granted from pg_locks group by 1,2,3) b
on a.relation=b.relation and a.granted='f' and b.granted='t'
left join (select * from stv_tbl_perm where slice=0) c
on a.relation=c.id
left join pg_class d on a.relation=d.oid
where  a.relation is not null;

A saída é semelhante a:

txn_owner | txn_db |   xid   |  pid  |         txn_start          |      lock_mode      | table_id | tablename | granted | blocking_pid |        txn_duration         |
----------+--------+---------+-------+----------------------------+---------------------+----------+-----------+---------+--------------+-----------------------------+
 usr1     | db1    | 5559898 | 19813 | 2018-06-30 10:51:57.485722 | AccessExclusiveLock |   351959 | lineorder | t       |              | 0 days 0 hrs 0 mins 52 secs |
 usr1     | db1    | 5559927 | 20450 | 2018-06-30 10:52:19.761199 | AccessShareLock     |   351959 | lineorder | f       |        19813 | 0 days 0 hrs 0 mins 30 secs |
 usr1     | db1    | 5559898 | 19813 | 2018-06-30 10:51:57.485722 | AccessShareLock     |   351959 | lineorder | t       |              | 0 days 0 hrs 0 mins 52 secs |

Se o resultado na coluna concedido for f (falso), uma transação em outra sessão está mantendo o bloqueio. A coluna blocking_pid mostra o ID do processo da sessão que está mantendo o bloqueio. Neste exemplo, PID 19813 está mantendo o bloqueio.

Para liberar um bloqueio, aguarde a conclusão da transação que está mantendo o bloqueio. Você também pode encerrar manualmente a sessão executando o seguinte comando:

select pg_terminate_backend(PID);

O encerramento de um PID reverte todas as transações em execução e libera todos os bloqueios na sessão. Em seguida os bloqueios são reivindicados pelas outras transações que aguardam a aquisição do bloqueio.

Observação: o valor “1” retornado por PG_TERMINATE_BACKEND(PID) normalmente indica uma solicitação de encerramento bem-sucedida para o PID. No entanto, isso não garante que o PID realmente tenha sido encerrado. Em alguns casos, o PID não pode ser determinado dependendo de seu status interno. Portanto, é uma prática recomendada verificar também STV_SESSIONS (e outras tabelas relevantes do sistema) para confirmar se o PID foi realmente encerrado.

Se PG_TERMINATE_BACKEND(PID) não encerrar a sessão, reinicialize o cluster para encerrar o processo. A reinicialização pode ser feita ao reinicializar o cluster por meio do console do Amazon Redshift ou de uma reinicialização do banco de dados usando REBOOT_CLUSTER.

Informações relacionadas

BLOQUEIO (Amazon Redshift)

Possível situação de deadlock em transações de gravação simultâneas