SELECT FOR UPDATE in serialisierbarer Isolation verwenden

Auf dieser Seite wird beschrieben, wie Sie die FOR UPDATE-Klausel in der serialisierbaren Isolation verwenden.

Der Sperrmechanismus der FOR UPDATE-Klausel unterscheidet sich für wiederholbare Lesevorgänge und serialisierbare Isolation. Wenn Sie die serialisierbare Isolation verwenden und mit der SELECT-Abfrage eine Tabelle scannen, können Sie mit einer FOR UPDATE-Klausel exklusive Sperren auf der Ebene der Zeilen- und Spaltengranularität (Zellebene) hinzufügen. Die Sperre bleibt für die gesamte Dauer der Lese-Schreib-Transaktion bestehen. Während dieser Zeit verhindert die FOR UPDATE-Klausel, dass andere Transaktionen die gesperrten Zellen ändern, bis die aktuelle Transaktion abgeschlossen ist.

Informationen zur Verwendung der FOR UPDATE-Klausel finden Sie in den Referenzanleitungen für GoogleSQL und PostgreSQL.FOR UPDATE

Vorteile der FOR UPDATE-Klausel

In Datenbanken mit weniger strengen Isolationsebenen ist die FOR UPDATE-Klausel möglicherweise erforderlich, um sicherzustellen, dass eine gleichzeitige Transaktion Daten nicht zwischen dem Lesen der Daten und dem Committen der Transaktion aktualisiert. Da Spanner standardmäßig die Serialisierbarkeit erzwingt, wird die Transaktion nur dann erfolgreich committet, wenn die in der Transaktion aufgerufenen Daten zum Zeitpunkt des Commits nicht veraltet sind. Daher ist die FOR UPDATE-Klausel nicht erforderlich, um die Transaktionsrichtigkeit in Spanner zu gewährleisten.

Bei Anwendungsfällen mit hohem Schreibkonflikt, z. B. wenn mehrere Transaktionen gleichzeitig dieselben Daten lesen und schreiben, können die gleichzeitigen Transaktionen jedoch zu einer Zunahme der Abbrüche führen. Das liegt daran, dass die Transaktionen einen Deadlock verursachen, wenn mehrere gleichzeitige Transaktionen gemeinsame Sperren erhalten und dann versuchen, ein Upgrade auf exklusive Sperren auszuführen. Der Deadlock blockiert die Transaktionen dauerhaft, da jede auf die andere wartet, um die benötigte Ressource freizugeben. Damit es weitergeht, bricht Spanner alle Transaktionen bis auf eine ab, um die Blockierung zu beheben. Weitere Informationen finden Sie unter Sperren.

Eine Transaktion, die die FOR UPDATE-Klausel verwendet, ruft die exklusive Sperre proaktiv ab und wird ausgeführt, während andere Transaktionen auf die Sperre warten. Obwohl Spanner den Durchsatz möglicherweise weiterhin begrenzt, da die in Konflikt stehenden Transaktionen nur jeweils einzeln ausgeführt werden können, spart Spanner Zeit, die andernfalls mit dem Abbruch von Transaktionen und dem Versuch, Transaktionen zu wiederholen, verbracht werden würde, da Spanner nur mit einer Transaktion gleichzeitig arbeitet.

Wenn es also wichtig ist, die Anzahl der abgebrochenen Transaktionen in einem Szenario mit gleichzeitigen Schreibanfragen zu reduzieren, können Sie die FOR UPDATE-Klausel verwenden, um die Gesamtzahl der Abbrüche zu verringern und die Effizienz der Arbeitslastausführung zu steigern.

Vergleich mit dem Hinweis „LOCK_SCANNED_RANGES“

Die FOR UPDATE-Klausel hat eine ähnliche Funktion wie der Hinweis LOCK_SCANNED_RANGES=exclusive.

Es gibt zwei Hauptunterschiede:

  • Wenn Sie den Hinweis LOCK_SCANNED_RANGES verwenden, werden für die Transaktion für die gesamte Anweisung exklusive Sperren für die gescannten Bereiche abgerufen. Sie können keine exklusiven Sperren für eine Unterabfrage abrufen. Wenn Sie den Sperrhinweis verwenden, werden möglicherweise mehr Sperren als nötig abgerufen, was zu Sperrenkonflikten in der Arbeitslast führen kann. Das folgende Beispiel zeigt, wie ein Sperrhinweis verwendet wird:

    @{lock_scanned_ranges=exclusive}
SELECT s.SingerId, s.FullName FROM Singers AS s
JOIN (SELECT SingerId FROM Albums WHERE MarketingBudget > 100000)
AS a ON a.SingerId = s.SingerId;

    Andererseits können Sie die Klausel FOR UPDATE in einer Unterabfrage verwenden, wie im folgenden Beispiel gezeigt:

    SELECT s.SingerId, s.FullName FROM Singers AS s
JOIN (SELECT SingerId FROM Albums WHERE MarketingBudget > 100000)
FOR UPDATE AS a ON a.SingerId = s.SingerId;

  • Sie können den Hinweis LOCK_SCANNED_RANGES in DML-Anweisungen verwenden, während Sie die FOR UPDATE-Klausel nur in SELECT-Anweisungen verwenden können.

Semantik für Sperren

Um gleichzeitige Schreibanfragen und die Kosten für Transaktionen zu reduzieren, die aufgrund von Deadlocks abgebrochen werden, sperrt Spanner Daten nach Möglichkeit auf Zellebene. Die Zellebene ist die detaillierteste Datenebene in einer Tabelle. Ein Datenpunkt befindet sich an der Schnittstelle zwischen einer Zeile und einer Spalte. Bei Verwendung der FOR UPDATE-Klausel sperrt Spanner bestimmte Zellen, die von der SELECT-Abfrage gescannt werden.

Im folgenden Beispiel ist die Zelle MarketingBudget in der Zeile SingerId = 1 und AlbumId = 1 in der Tabelle Albums exklusiv gesperrt. Dadurch wird verhindert, dass gleichzeitige Transaktionen diese Zelle ändern, bis diese Transaktion festgeschrieben oder ein Rollback ausgeführt wird. Bei gleichzeitigen Transaktionen kann die Zelle AlbumTitle in dieser Zeile jedoch weiterhin aktualisiert werden.

SELECT MarketingBudget
FROM Albums
WHERE SingerId = 1 and AlbumId = 1
FOR UPDATE;

Gleichzeitige Transaktionen können beim Lesen gesperrter Daten blockiert werden

Wenn eine Transaktion exklusive Sperren für einen gescannten Bereich erworben hat, können gleichzeitige Transaktionen das Lesen dieser Daten blockieren. Spanner erzwingt die Serialisierbarkeit, sodass Daten nur gelesen werden können, wenn garantiert ist, dass sie während der Lebensdauer der Transaktion nicht durch eine andere Transaktion geändert werden. Gleichzeitige Transaktionen, die versuchen, bereits gesperrte Daten zu lesen, müssen möglicherweise warten, bis die Transaktion, die die Sperren enthält, per Commit übertragen oder zurückgesetzt wird oder ein Zeitlimit überschritten wird.

Im folgenden Beispiel werden mit Transaction 1 die Zellen MarketingBudget für 1 <= AlbumId < 5 gesperrt.

-- Transaction 1
SELECT MarketingBudget
FROM Albums
WHERE SingerId = 1 and AlbumId >= 1 and AlbumId < 5
FOR UPDATE;

Transaction 2, das versucht, die MarketingBudget für AlbumId = 1 zu lesen, wird blockiert, bis Transaction 1 entweder committet oder zurückgesetzt wird.

-- Transaction 2
SELECT MarketingBudget
FROM Albums
WHERE SingerId = 1 and AlbumId = 1;

-- Blocked by Transaction 1

Ebenso wird eine Transaktion, die versucht, einen gescannten Bereich mit FOR UPDATE zu sperren, durch eine gleichzeitige Transaktion blockiert, die einen sich überschneidenden gescannten Bereich sperrt.

Transaction 3 im folgenden Beispiel wird ebenfalls blockiert, da Transaction 1 die MarketingBudget-Zellen für 3 <= AlbumId < 5 gesperrt hat. 3 <= AlbumId < 5 ist der überlappende gescannte Bereich mit Transaction 3.

-- Transaction 3
SELECT MarketingBudget
FROM Albums
WHERE SingerId = 1 and AlbumId >= 3 and AlbumId < 10
FOR UPDATE;

-- Blocked by Transaction 1

Index lesen

Ein gleichzeitiger Lesevorgang wird möglicherweise nicht blockiert, wenn die Abfrage, die den gescannten Bereich gesperrt hat, die Zeilen in der Basistabelle sperrt, die gleichzeitige Transaktion jedoch aus einem Index liest.

Mit dem folgenden Transaction 1 werden die Zellen SingerId und SingerInfo für SingerId = 1 gesperrt.

-- Transaction 1
SELECT SingerId, SingerInfo
FROM Singers
WHERE SingerId = 1
FOR UPDATE;

Der schreibgeschützte Transaction 2 wird nicht durch die in Transaction 1 erworbenen Sperren blockiert, da er eine Indextabelle abfragt.

-- Transaction 2
SELECT SingerId FROM Singers;

Gleichzeitige Transaktionen blockieren keine DML-Vorgänge für bereits gesperrte Daten

Wenn eine Transaktion Sperren für einen Zellbereich mit einem Hinweis auf eine exklusive Sperre erhalten hat, können gleichzeitige Transaktionen, die versuchen, Daten in die gesperrten Zellen zu schreiben, ohne die Daten zuerst zu lesen, fortgesetzt werden. Die Transaktion wird beim Commit blockiert, bis die Transaktion, die die Sperren enthält, committet oder zurückgesetzt wird.

Mit dem folgenden Transaction 1 werden die MarketingBudget-Zellen für 1 <= AlbumId < 5 gesperrt.

-- Transaction 1
SELECT MarketingBudget
FROM Albums
WHERE SingerId = 1 and AlbumId >= 1 and AlbumId < 5
FOR UPDATE;

Wenn Transaction 2 versucht, die Tabelle Albums zu aktualisieren, wird dies verhindert, bis Transaction 1 einen Commit oder Rollback ausführt.

-- Transaction 2
UPDATE Albums
SET MarketingBudget = 200000
WHERE SingerId = 1 and AlbumId = 1;

> Query OK, 1 rows affected

COMMIT;

-- Blocked by Transaction 1

Vorhandene Zeilen und Lücken werden gesperrt, wenn ein gescannter Bereich gesperrt wird.

Wenn eine Transaktion exklusive Sperren für einen gescannten Bereich erworben hat, können mit gleichzeitigen Transaktionen keine Daten in die Lücken innerhalb dieses Bereichs eingefügt werden.

Mit dem folgenden Transaction 1 werden die MarketingBudget-Zellen für 1 <= AlbumId < 10 gesperrt.

-- Transaction 1
SELECT MarketingBudget
FROM Albums
WHERE SingerId = 1 and AlbumId >= 1 and AlbumId < 10
FOR UPDATE;

Wenn Transaction 2 versucht, eine Zeile für AlbumId = 9 einzufügen, die noch nicht vorhanden ist, wird dies blockiert, bis Transaction 1 einen Commit oder Rollback ausführt.

-- Transaction 2
INSERT INTO Albums (SingerId, AlbumId, AlbumTitle, MarketingBudget)
VALUES (1, 9, "Hello hello!", 10000);

> Query OK, 1 rows affected

COMMIT;

-- Blocked by Transaction 1

Vorsichtsmaßnahmen beim Erwerb von Schlössern

Die beschriebene Sperrensemantik bietet eine allgemeine Orientierung, ist aber keine Garantie dafür, wie genau Sperren abgerufen werden, wenn Spanner eine Transaktion ausführt, in der die FOR UPDATE-Klausel verwendet wird. Die Mechanismen zur Abfrageoptimierung von Spanner können sich auch darauf auswirken, welche Sperren erworben werden. Diese Klausel verhindert, dass andere Transaktionen die gesperrten Zellen ändern, bis die aktuelle Transaktion abgeschlossen ist.

Abfragesyntax

In diesem Abschnitt finden Sie Informationen zur Abfragesyntax bei Verwendung der Klausel FOR UPDATE.

Am häufigsten wird sie in einer SELECT-Anweisung auf oberster Ebene verwendet. Beispiel:

SELECT SingerId, SingerInfo
FROM Singers WHERE SingerID = 5
FOR UPDATE;

In diesem Beispiel wird gezeigt, wie Sie die FOR UPDATE-Klausel in einer SELECT-Anweisung verwenden, um die Zellen SingerId und SingerInfo von WHERE SingerID = 5 exklusiv zu sperren.

In WITH-Anweisungen verwenden

Mit der FOR UPDATE-Klausel werden keine Sperren für die WITH-Anweisung abgerufen, wenn Sie FOR UPDATE in der Abfrage auf oberster Ebene der WITH-Anweisung angeben.

In der folgenden Abfrage werden keine Sperren für die Tabelle Singers abgerufen, da die Absicht zum Sperren nicht an die CTE-Abfrage (Common Table Expression) weitergegeben wird.

WITH s AS (SELECT SingerId, SingerInfo FROM Singers WHERE SingerID > 5)
SELECT * FROM s
FOR UPDATE;

Wenn die FOR UPDATE-Klausel in der CTE-Abfrage angegeben ist, werden die Sperren für den gescannten Bereich der CTE-Abfrage übernommen.

Im folgenden Beispiel sind die Zellen SingerId und SingerInfo für die Zeilen gesperrt, in denen SingerId > 5.

WITH s AS
  (SELECT SingerId, SingerInfo FROM Singers WHERE SingerId > 5 FOR UPDATE)
SELECT * FROM s;

In Unterabfragen verwenden

Sie können die FOR UPDATE-Klausel in einer Abfrage auf oberster Ebene verwenden, die eine oder mehrere Unterabfragen enthält. Sperren werden von der Abfrage auf oberster Ebene und in Unterabfragen abgerufen, mit Ausnahme von Ausdrucksunterabfragen.

Mit der folgenden Abfrage werden die Zellen SingerId und SingerInfo für Zeilen gesperrt, in denen SingerId > 5.

(SELECT SingerId, SingerInfo FROM Singers WHERE SingerId > 5) AS t
FOR UPDATE;

Mit der folgenden Abfrage werden keine Zellen in der Tabelle Albums gesperrt, da sie sich in einer Unterabfrage für Ausdrücke befindet. Die Zellen SingerId und SingerInfo für die Zeilen, die von der Unterabfrage des Ausdrucks zurückgegeben werden, sind gesperrt.

SELECT SingerId, SingerInfo
FROM Singers
WHERE SingerId = (SELECT SingerId FROM Albums WHERE MarketingBudget > 100000)
FOR UPDATE;

Zum Abfragen von Ansichten verwenden

Sie können die FOR UPDATE-Klausel verwenden, um eine Ansicht abzufragen, wie im folgenden Beispiel gezeigt:

CREATE VIEW SingerBio AS SELECT SingerId, FullName, SingerInfo FROM Singers;

SELECT * FROM SingerBio WHERE SingerId = 5 FOR UPDATE;

Sie können die FOR UPDATE-Klausel beim Definieren einer Ansicht nicht verwenden.

Nicht unterstützte Anwendungsfälle

Die folgenden FOR UPDATE-Anwendungsfälle werden nicht unterstützt:

  • Als gegenseitiger Ausschlussmechanismus für die Ausführung von Code außerhalb von Spanner:Verwenden Sie keine Sperren in Spanner, um den exklusiven Zugriff auf eine Ressource außerhalb von Spanner zu gewährleisten. Transaktionen können von Spanner abgebrochen werden, z. B. wenn eine Transaktion entweder explizit durch Anwendungscode oder implizit durch Clientcode wie den JDBC-Treiber von Spanner wiederholt wird. In diesem Fall wird nur garantiert, dass die Sperren während des tatsächlichen Versuchs bestanden haben.
  • In Kombination mit dem Hinweis LOCK_SCANNED_RANGES:Sie können nicht sowohl die FOR UPDATE-Anweisung als auch den Hinweis LOCK_SCANNED_RANGES in derselben Abfrage verwenden. Andernfalls gibt Spanner einen Fehler zurück.
  • In Volltextsuchanfragen:Die FOR UPDATE-Klausel kann nicht in Abfragen mit Volltextsuchindexen verwendet werden.
  • In schreibgeschützten Transaktionen:Die FOR UPDATE-Klausel ist nur in Abfragen gültig, die in Lese-Schreib-Transaktionen ausgeführt werden.
  • In DDL-Anweisungen:Die FOR UPDATE-Klausel kann nicht in Abfragen in DDL-Anweisungen verwendet werden, die für die spätere Ausführung gespeichert werden. Sie können beispielsweise die FOR UPDATE-Klausel beim Definieren einer Ansicht nicht verwenden. Wenn eine Sperrung erforderlich ist, kann die FOR UPDATE-Klausel beim Abfragen der Ansicht angegeben werden.

Weitere Informationen