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

Breve descrição

É possível enfrentar conflitos de bloqueio ao executar instruções frequentes da Linguagem de Definição de Dados (DDL) em tabelas de usuários ou consultas de Linguagem de Manipulação de Dados (DML).

O Amazon Redshift possui os três modos de bloqueio seguintes:

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

Para resolver esse problema, identifique os bloqueios da tabela de problemas, identifique a consulta do problema (se necessário) e em seguida, libere os bloqueios da tabela de problemas.

Resolução

Observação: se você receber erros ao executar comandos da AWS Command Line Interface (AWS CLI), consulte Solução de problemas da AWS CLI. Além disso, verifique se está utilizando a versão mais recente do AWS CLI.

Detectar os bloqueios

A fim de identificar processos que estão bloqueados, execute a seguinte consulta:

SELECT
  a.txn_start,
  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,
  a.txn_owner,
  a.txn_db,
  a.pid,
  a.xid,
  a.lock_mode,
  a.relation AS table_id,
  nvl(trim(c."table"),d.relname) AS tablename,
  a.granted,
  b.pid AS blocking_pid
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 SVV_TABLE_INFO) c
  ON a.relation = c.table_id
LEFT JOIN PG_CLASS d
  ON a.relation = d.oid
WHERE
  a.relation IS NOT NULL
  AND txn_db = 'database name'
  AND tablename = 'table name';

Para detectar os bloqueios em uma tabela específica, deve-se substituir database name pelo nome do seu próprio banco de dados e substituir table name pelo nome da sua própria tabela.

**Observação:**É possível executar a consulta anterior em um cluster provisionado do Amazon Redshift e no Amazon Redshift Sem Servidor.

Exemplo de saída:

         txn_start         |        txn_duration         | txn_owner | txn_db |    pid     |   xid   |      lock_mode      | table_id | tablename | granted | blocking_pid
---------------------------+-----------------------------+-----------+--------+------------+---------+---------------------+----------+-----------+---------+--------------
 2025-02-07 15:22:54.62833 | 0 days 0 hrs 3 mins 46 secs | admin     | dev    | 1073905801 | 3950326 | AccessExclusiveLock |  1410058 | abctbl    | t       |             
 2025-02-07 15:22:57.67816 | 0 days 0 hrs 3 mins 43 secs | admin     | dev    | 1073963119 | 3950380 | AccessShareLock     |  1410058 | abctbl    | f       |   1073905801

Caso o resultado na coluna granted for f (false), a transação então estará aguardando bloqueios, porque o bloqueio será retido por outra transação. A coluna blocking_pid mostra o ID do processo (PID) da sessão que está mantendo o bloqueio.

Detectar a consulta do problema

Caso o problema de bloqueio da tabela específica ocorrer de forma consistente, utilize SYS_QUERY_HISTORY para ver qual consulta está causando o problema.

SELECT * FROM SYS_QUERY_HISTORY WHERE transaction_id = transaction ID;

Liberar os bloqueios

Para liberar os bloqueios, conclua as seguintes etapas:

1. Espere até que a transação que contém o bloqueio seja liberada.
2. Execute a função PG_TERMINATE_BACKEND para liberar o bloqueio manualmente.
   Observação: a consulta PG_TERMINATE_BACKEND(PID) retornará o valor 1 assim que o comando solicitar com êxito uma interrupção do processo. É recomendado verificar a SYS_SESSION_HISTORY para confirmar se o processo foi interrompido.
3. Em um cluster provisionado pelo Redshift, caso o PG_TERMINATE_BACKEND não interrompa o processo com êxito, execute a função REBOOT_CLUSTER.
   **Observação:****REBOOT\ _CLUSTER ** reinicializará o cluster sem fechar as conexões.
4. Em um cluster provisionado pelo Redshift, caso REBOOT_CLUSTER não interrompa o processo com êxito, reinicialize o cluster a partir do console do Amazon Redshift ou execute o comando reboot-cluster da AWS CLI.
   Nota: reboot-cluster fechará todas as conexões atuais.