Grundlegendes zum Sperren
Multi-Version Concurrency Control (MVCC) stellt die entsprechenden Parallelitätseinstellungen für die meisten Szenarien bereit. Wenn eine Anwendung jedoch bestimmte Sperren erfordert, die genau steuern, welche Zeilen betroffen sind, und mit einer bestimmten Sperrebene dann explizite Sperrmodi dieses differenzierte Steuerelement aktivieren.
In Azure Database for PostgreSQL gibt es drei Arten von expliziten Sperren, nämlich Sperren auf Tabellenebene, Sperren auf Zeilenebene und Speeren auf Seitenebene. Die erste Transaktion fordert eine Sperre an, und wenn sie akzeptiert wird, wird die angeforderte Sperre zur vorhandenen Sperre. Wenn eine andere Transaktion versucht, eine Sperre für dieselben Daten zu setzen, wird die Sperre gewährt, wenn sie nicht mit der ursprünglichen Transaktion in Konflikt steht.
Zwei Transaktionen können beispielsweise die gleichen Daten zur selben Zeit mit einer SELECT-Anweisung abfragen. Diese Anforderungen würde eine ACCESS SHARE-Sperre verwenden und sie würden beide zulässig sein. In einem anderen Szenario fragt eine Transaktion Daten mit einer SELECT-Anweisung und einer ACCESS SHARE-Sperre ab, aber gleichzeitig versucht eine andere Transaktion, dieselbe Tabelle zu löschen. Das Löschen einer Tabelle erfordert eine ACCESS EXCLUSIVE-Sperre, die in diesem Szenario nicht gewährt werden würde.
Sperren auf Tabellenebene
Sperren auf Tabellenebene rufen Sperren für eine gesamte Tabelle ab, auch wenn ROW in ihrem Namen enthalten ist. Möglicherweise müssen Sie eine gesamte Tabelle sperren, wenn die Tabelle selbst geändert wird. Dies kann effizienter sein, als viele Sperren auf Zeilenebene zu entfernen.
Es gibt acht Arten von Sperre auf Tabellenebene in Azure Database for PostgreSQL und die SQL-Befehle, die diese Arten von Sperren erwerben, sind:
| Sperrmodus | Abgerufen von |
|---|---|
| ACCESS SHARE | SELECT-Befehl |
| ROW SHARE | SELECT FOR UPDATE- und SELECT FOR SHARE-Befehle |
| ROW EXCLUSIVE | UPDATE-, DELETE- und INSERT-Befehle |
| SHARE UPDATE EXCLUSIVE | ANALYZE-, CREATE INDEX CONCURRENTLY-, CREATE STATISTICS-, COMMENT ON- oder REINDEX CONCURRENTLY-Befehle, manche ALTER INDEX- und ALTER TABLE-Befehle und VACUUM (nicht FULL) |
| FREIGEBEN | CREATE INDEX-Befehl (nicht CONCURRENTLY) |
| SHARE ROW EXCLUSIVE | CREATE TRIGGER-Befehl und manche ALTER TABLE-Befehle |
| EXCLUSIVE | REFRESH MATERIALIZED VIEW CONCURRENTLY-Befehl |
| ACCESS EXCLUSIVE | DROP TABLE-, REINDEX-, TRUNCATE-, CLUSTER- und REFRESH MATERIALIZED VIEW-Befehle (nicht CONCURRENTLY), die meisten ALTER INDEX- und ALTER TABLE-Befehle und VACUUM FULL |
Jede Art von bestehender Sperre blockiert andere angeforderte Sperren, die abgerufen werden. In der folgenden Tabelle ist aufgeführt, welche Sperren andere Sperren blockieren, die erworben werden:
| -- | Vorhandene ACCESS SHARE-Sperre | Vorhandene ROW SHARE-Sperre | Vorhandene ROW EXCLUSIVE-Sperre | Vorhandene SHARE UPDATE EXCLUSIVE-Sperre | Vorhandene SHARE-Sperre | Vorhandene SHARE ROW EXCL-Sperre | Vorhandene EXCLUSIVE-Sperre | Vorhandene ACCESS EXCLUSIVE-Sperre |
|---|---|---|---|---|---|---|---|---|
| Angeforderte ACCESS SHARE-Sperre | Blockiert | |||||||
| Angeforderte ROW SHARE-Sperre | Blockiert | Blockiert | ||||||
| Angeforderte ROW EXCLUSIVE-Sperre | Blockiert | Blockiert | Blockiert | Blockiert | ||||
| Angeforderte SHARE UPDATE EXCLUSIVE-Sperre | Blockiert | Blockiert | Blockiert | Blockiert | Blockiert | |||
| Angeforderte SHARE-Sperre | Blockiert | Blockiert | Blockiert | Blockiert | Blockiert | |||
| Angeforderte SHARE ROW EXCLUSIVE-Sperre | Blockiert | Blockiert | Blockiert | Blockiert | Blockiert | Blockiert | ||
| Angeforderte EXCLUSIVE-Sperre | Blockiert | Blockiert | Blockiert | Blockiert | Blockiert | Blockiert | Blockiert | |
| Angeforderte ACCESS EXCLUSIVE-Sperre | Blockiert | Blockiert | Blockiert | Blockiert | Blockiert | Blockiert | Blockiert | Blockiert |
Sperren auf Zeilenebene
Sperren auf Zeilenebene sind deutlich präziser und wirken sich nur auf andere Transaktionen auf, wenn diese auf dieselbe Zeile zugreifen. Dieser Sperrtyp verbessert die Nebenläufigkeit. Das Abrufen und Löschen vieler Sperren wirkt sich jedoch negativ auf die Leistung aus. Sperren auf Zeilenebene werden automatisch von PostgreSQL abgerufen und nicht manuell angewendet.
Es gibt vier Arten von Sperren auf Zeilenebene in Azure Database for PostgreSQL. Sie werden abhängig davon abgerufen, welche anderen Arten von Sperren blockiert werden müssen:
| -- | Vorhandene FOR KEY SHARE-Sperre | Vorhandene FOR SHARE-Sperre | Vorhandene FOR NO KEY UPDATE-Sperre | Vorhandene FOR UPDATE-Sperre |
|---|---|---|---|---|
| Angeforderte FOR KEY SHARE-Sperre | Blockiert | |||
| Angeforderte FOR SHARE-Sperre | Blockiert | Blockiert | ||
| Angeforderte FOR NO KEY UPDATE-Sperre | Blockiert | Blockiert | Blockiert | |
| Angeforderte FOR UPDATE-Sperre | Blockiert | Blockiert | Blockiert | Blockiert |
Sperren auf Seitenebene
Sperren auf Seitenebene wirken sich auf eine Seite von Daten aus, die üblicherweise aus mehreren Zeilen besteht. Obwohl PostgreSQL-Prozesse Seitenebenensperren verwenden, benötigen Anwendungsentwickler in der Regel diese Art von Sperre nicht.
Manuelles Anwenden von Sperren und Anzeigen aktueller Sperren
Sie können den LOCK-Befehl mit dem erforderlichen Sperrmodus verwenden, um eine Sperre auf Tabellenebene manuell anzuwenden. Der LOCK-Befehl muss sich in einer Transaktion befinden, und die Sperren werden aufgehoben, wenn die Transaktion abgeschlossen ist. Beispiel:
BEGIN TRANSACTION;
LOCK TABLE humanresources.department IN ROW EXCLUSIVE MODE;
COMMIT;
Verwenden Sie pg_locks, um die Sperren anzuzeigen, die derzeit in der Datenbank gespeichert werden. Verwenden Sie beispielsweise den folgenden Befehl, um alle aktuellen Sperren anzuzeigen:
SELECT * FROM pg_locks;