Wie kann ich Sperren in Amazon Redshift erkennen und freigeben?

Kurzbeschreibung

Es kann zu Sperrkonflikten kommen, wenn du häufig Data Definition Language (DDL)-Anweisungen für Benutzertabellen oder Data Manipulation Language (DML)-Abfragen ausführst.

Amazon Redshift verfügt über die folgenden drei Sperrmodi:

• AccessExclusiveLock blockiert alle anderen Sperrversuche und wird hauptsächlich bei DDL-Operationen wie ALTER TABLE, DROP oder TRUNCATE abgerufen.
• AccessShareLock blockiert nur AccessExclusiveLock-Versuche und wird bei den Operationen UNLOAD, SELECT, UPDATE oder DELETE erfasst. AccessShareLock blockiert keine anderen Sitzungen, die versuchen, die Tabelle zu lesen oder darin zu schreiben.
• ShareRowExclusiveLock blockiert AccessExclusiveLock und andere ShareRowExclusiveLock-Versuche, blockiert jedoch keine AccessShareLock-Versuche. **ShareRowExclusiveLock ** wird bei den Operationen COPY, INSERT, UPDATE oder DELETE abgerufen.

Um dieses Problem zu beheben, identifiziere die Sperren der Problemtabelle, identifiziere die Problemabfrage (falls erforderlich) und hebe dann die Sperren der Problemtabelle auf.

Lösung

Hinweis: Wenn du beim Ausführen von AWS Command Line Interface (AWS CLI)-Befehlen Fehlermeldungen erhältst, findest du weitere Informationen dazu unter Problembehandlung bei der AWS CLI. Stelle außerdem sicher, dass du die neueste Version der AWS CLI verwendest.

Sperren erkennen

Führe die folgende Abfrage aus, um Prozesse zu identifizieren, die Sperren halten:

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

Hinweis: Du kannst die vorherige Abfrage sowohl auf einem von Amazon Redshift bereitgestellten Cluster als auch auf Amazon Redshift Serverless ausführen.

Beispielausgabe:

         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

Wenn das Ergebnis in der Spalte Erteilt f (falsch) ist, wartet die Transaktion auf Sperren, da die Sperre von einer anderen Transaktion gehalten wird. In der Spalte blocking_pid wird die Prozess-ID (PID) der Sitzung angezeigt, die die Sperre hält.

Problemabfrage ermitteln

Wenn das Sperrproblem für die bestimmte Tabelle konsistent auftritt, verwende SYS_QUERY_HISTORY, um zu sehen, welche Abfrage das Problem verursacht.

SELECT * FROM SYS_QUERY_HISTORY WHERE transaction_id = transaction ID;

Sperren lösen

Gehe wie folgt vor, um die Sperren aufzuheben:

1. Warte, bis die Transaktion, die die Sperre enthält, aufgehoben wird.
2. Führe die Funktion PG_TERMINATE_BACKEND aus, um die Sperre manuell aufzuheben.
   Hinweis: Die Abfrage PG_TERMINATE_BACKEND(PID) gibt den Wert „1“ zurück, wenn der Befehl erfolgreich einen Prozessstopp anfordert. Es empfiehlt sich, SYS_SESSION_HISTORY zu überprüfen, um zu bestätigen, dass der Prozess gestoppt wurde.
3. Wenn PG_TERMINATE_BACKEND den Prozess bei einem von Redshift bereitgestellten Cluster nicht erfolgreich beendet, führe die Funktion REBOOT_CLUSTER aus.
   Hinweis: REBOOT_CLUSTER startet den Cluster neu, ohne die Verbindungen zu schließen.
4. Wenn REBOOT_CLUSTER den Prozess bei einem von Redshift bereitgestellten Cluster nicht erfolgreich beendet, starte den Cluster über die Amazon Redshift-Konsole neu oder führe den AWS-CLI-Befehl reboot-cluster aus.
   Hinweis: reboot-cluster schließt alle aktuellen Verbindungen.