Come posso rilevare e sbloccare i blocchi in Amazon Redshift?

Breve descrizione

Potrebbero verificarsi conflitti di blocco quando esegui frequenti istruzioni DDL (Data Definition Language) su tabelle utente o query DML (Data Manipulation Language).

Amazon Redshift ha le seguenti tre modalità di blocco:

• AccessExclusiveLock blocca tutti gli altri tentativi di blocco e viene creato principalmente durante le operazioni DDL, come ALTER TABLE, DROP o TRUNCATE.
• AccessShareLock blocca solo i tentativi di AccessExclusiveLock e viene creato durante le operazioni UNLOAD, SELECT, UPDATE o DELETE. AccessShareLock non blocca altre sessioni che tentano di leggere o scrivere nella tabella.
• ShareRowExclusiveLock blocca AccessExclusiveLock e altri tentativi di ShareRowExclusiveLock, ma non blocca i tentativi di AccessShareLock. ShareRowExclusiveLock viene creato durante le operazioni COPY, INSERT, UPDATE o DELETE.

Per risolvere l'errore, identifica i blocchi della tabella che creano il problema, individua la query che causa il problema (se del caso), quindi sblocca i blocchi della tabella identificati.

Risoluzione

Nota: se ricevi errori quando esegui i comandi dell'Interfaccia della linea di comando AWS (AWS CLI), consulta Risoluzione degli errori per AWS CLI. Inoltre, assicurati di utilizzare la versione più recente di AWS CLI.

Identifica i blocchi

Per identificare i processi che creano i blocchi, esegui questa query:

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';

Per rilevare i blocchi in una tabella specifica, sostituisci database name con il nome del tuo database e table name con il nome della tua tabella.

Nota: puoi eseguire la query precedente sia su un cluster con provisioning Amazon Redshift sia su Amazon Redshift serverless.

Esempio di output:

         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

Se il risultato nella colonna granted è f (false), la transazione è in attesa di blocchi perché il blocco è stato creato in un'altra transazione. La colonna blocking_pid mostra l'ID (PID) del processo della sessione che ha creato il blocco.

Individua la query che causa il problema

Se il problema del blocco per la specifica tabella si verifica regolarmente, utilizza SYS_QUERY_HISTORY per individuare la query che causa il problema.

SELECT * FROM SYS_QUERY_HISTORY WHERE transaction_id = transaction ID;

Sblocca i blocchi

Per sbloccare i blocchi, completa i seguenti passaggi:

1. Attendi che la transazione che ha creato il blocco sia sbloccata.
2. Esegui la funzione PG_TERMINATE_BACKEND per sbloccare il blocco manualmente.
   Nota: la query PG_TERMINATE_BACKEND(PID) restituisce il valore 1 quando il comando richiede correttamente l'arresto di un processo. È consigliabile controllare SYS_SESSION_HISTORY per verificare che il processo sia stato arrestato.
3. Per un cluster con provisioning Redshift, se PG_TERMINATE_BACKEND non arresta correttamente il processo, esegui la funzione REBOOT_CLUSTER.
   Nota: REBOOT_CLUSTER riavvia il cluster senza chiudere le connessioni.
4. Per un cluster con provisioning Redshift, se REBOOT_CLUSTER non arresta correttamente il processo, riavvia il cluster dalla console Amazon Redshift o esegui il comando AWS CLI reboot-cluster.
   **Nota: ** reboot-cluster chiude tutte le connessioni correnti.