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 die Isolation wiederholbarer Lesevorgänge und die serialisierbare Isolation. Wenn Sie die serialisierbare Isolation verwenden und die Tabelle mit der Abfrage SELECT scannen, können Sie mit einer FOR UPDATE-Klausel exklusive Sperren auf der Schnittmenge der Zeilen- und Spaltengranularitätsebene aktivieren, auch bekannt als Zellebene. Die Sperre bleibt für die Dauer der Lese-/Schreibtransaktion 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 Referenzhandbüchern zu GoogleSQL und PostgreSQL für 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 keine Daten zwischen dem Lesen der Daten und dem Commit der Transaktion aktualisiert. Bei der standardmäßigen serialisierbaren Isolation sorgt Spanner dafür, dass eine Transaktion nur dann festgeschrieben wird, wenn die Daten, auf die zugegriffen wird, zum Zeitpunkt des Commits nicht veraltet sind. Daher ist die FOR UPDATE-Klausel nicht erforderlich, um die Korrektheit der Transaktion bei serialisierbarer Isolation zu gewährleisten.

In Anwendungsfällen mit hoher Schreibkonkurrenz, z. B. wenn mehrere Transaktionen gleichzeitig dieselben Daten lesen und schreiben, können die gleichzeitigen Transaktionen jedoch zu einer Erhöhung der Abbruchrate führen. Das liegt daran, dass mehrere gleichzeitige Transaktionen freigegebene Sperren abrufen und dann versuchen, ein Upgrade auf exklusive Sperren durchzuführen. Dadurch kommt es zu einem Deadlock. Der Deadlock blockiert die Transaktionen dauerhaft, da jede Transaktion darauf wartet, dass die andere die benötigte Ressource freigibt. Um Fortschritte zu erzielen, bricht Cloud Spanner alle Transaktionen bis auf eine ab, um den Deadlock aufzulösen. Weitere Informationen finden Sie unter Sperren.

Eine Transaktion, die die FOR UPDATE-Klausel verwendet, erwirbt die exklusive Sperre proaktiv und wird ausgeführt, während andere Transaktionen auf die Sperre warten. Cloud Spanner kann den Durchsatz zwar immer noch einschränken, da die in Konflikt stehenden Transaktionen nur nacheinander ausgeführt werden können. Da Cloud Spanner jedoch nur eine Transaktion verarbeitet, wird Zeit gespart, die sonst für das Abbrechen und Wiederholen von Transaktionen aufgewendet werden müsste.

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 reduzieren und die Effizienz der Arbeitslastausführung zu erhöhen.

Während die serialisierbare Isolation FOR UPDATE hauptsächlich verwendet, um Abbrüche aufgrund von Schreibkonflikten zu reduzieren, wird die Isolation für wiederholbare Lesevorgänge mit FOR UPDATE verwendet, um Lese-/Schreibkonflikte zu verhindern und die Korrektheit von Transaktionen zu gewährleisten. Weitere Informationen finden Sie unter SELECT FOR UPDATE in der Isolation wiederholbarer Lesevorgänge verwenden.

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, erhält die Transaktion exklusive Sperren für die gescannten Bereiche für die gesamte Anweisung. Sie können keine exklusiven Sperren für eine Unterabfrage erwerben. Die Verwendung des Sperrhinweises kann dazu führen, dass mehr Sperren als nötig erworben werden und die Sperrkonflikte in der Arbeitslast zunehmen. 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;
    

    Sie können die FOR UPDATE-Klausel jedoch 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 Klausel FOR UPDATE nur in SELECT-Anweisungen verwenden können.

Sperrsemantik

Um gleichzeitige Schreibanfragen und die Kosten für Transaktionen zu reduzieren, die aufgrund eines Deadlocks abgebrochen werden, sperrt Spanner Daten nach Möglichkeit auf Zellebene. Die Zellebene ist die detaillierteste Datenebene in einer Tabelle – ein Datenpunkt am Schnittpunkt einer Zeile und einer Spalte. Wenn Sie die FOR UPDATE-Klausel verwenden, sperrt Spanner bestimmte Zellen, die von der SELECT-Abfrage gescannt werden.

Im folgenden Beispiel wird 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 zurückgesetzt wird. Gleichzeitige Transaktionen können jedoch weiterhin die Zelle AlbumTitle in dieser Zeile aktualisieren.

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. Cloud Spanner erzwingt die Serialisierbarkeit, sodass Daten nur gelesen werden können, wenn sichergestellt ist, dass sie während der Lebensdauer der Transaktion nicht von einer anderen Transaktion geändert werden. Gleichzeitige Transaktionen, die versuchen, bereits gesperrte Daten zu lesen, müssen möglicherweise warten, bis die Transaktion, die die Sperren hält, festgeschrieben, zurückgesetzt oder abgebrochen wird.

Im folgenden Beispiel sperrt Transaction 1 die MarketingBudget-Zellen für 1 <= AlbumId < 5.

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

Transaction 2, die versucht, MarketingBudget für AlbumId = 1 zu lesen, wird blockiert, bis Transaction 1 entweder festgeschrieben 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 überlappenden gescannten Bereich sperrt.

Transaction 3 im folgenden Beispiel wird ebenfalls blockiert, da Transaction 1 die MarketingBudget-Zellen für 3 <= AlbumId < 5 gesperrt hat. Dies 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 Index-Tabelle 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 Bereich von Zellen mit einem exklusiven Sperrhinweis erworben hat, können gleichzeitige Transaktionen, die einen Schreibvorgang ohne vorheriges Lesen der Daten in den gesperrten Zellen ausführen, fortgesetzt werden. Die Transaktion wird beim Commit blockiert, bis die Transaktion, die die Sperren enthält, ein Commit oder Rollback durchführt.

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 blockiert, 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 gleichzeitige Transaktionen keine Daten in die Lücken innerhalb dieses Bereichs einfügen.

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 ein 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

Einschränkungen beim Erwerb von Sperren

Die beschriebene Sperrsemantik bietet allgemeine Hinweise, ist aber keine Garantie dafür, wie Sperren genau erworben werden, wenn Spanner eine Transaktion mit der FOR UPDATE-Klausel ausführt. Die Abfrageoptimierungsmechanismen von Spanner können sich auch darauf auswirken, welche Sperren erworben werden. Die 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 FOR UPDATE-Klausel.

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

Die FOR UPDATE-Klausel ruft keine Sperren für die WITH-Anweisung ab, wenn Sie FOR UPDATE in der Abfrage der äußeren Ebene der WITH-Anweisung angeben.

In der folgenden Abfrage werden von der Tabelle Singers keine Sperren erworben, da die Absicht, zu 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 abgerufen.

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

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 äußerer Ebene mit einer oder mehreren Unterabfragen verwenden. Sperren werden von der Abfrage auf oberster Ebene und in Unterabfragen erworben, außer in Ausdrucksunterabfragen.

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

(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 Ausdrucksunterabfrage 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 nicht verwenden, wenn Sie eine Ansicht definieren.

Nicht unterstützte Anwendungsfälle

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

  • Als Mechanismus für den gegenseitigen Ausschluss beim Ausführen von Code außerhalb von Spanner: Verwenden Sie keine Sperren in Spanner, um exklusiven Zugriff auf eine Ressource außerhalb von Spanner zu gewährleisten. Spanner bricht eine Transaktion möglicherweise ab und wiederholt sie. Die Sperren werden aufgehoben, wenn die Transaktion abgebrochen wird. Weitere Informationen finden Sie im Leitfaden zu wiederholbaren Lesevorgängen unter Nicht unterstützte Anwendungsfälle.
  • In Kombination mit dem Hinweis LOCK_SCANNED_RANGES: Sie können nicht sowohl die FOR UPDATE-Klausel als auch den Hinweis LOCK_SCANNED_RANGES in derselben Abfrage verwenden, da Spanner sonst einen Fehler zurückgibt.
  • In Volltextsuchanfragen: Sie können die FOR UPDATE-Klausel nicht in Suchanfragen verwenden, die Volltextsuchindexe verwenden.
  • In schreibgeschützten Transaktionen: Die FOR UPDATE-Klausel ist nur in Abfragen gültig, die in Lese-/Schreibtransaktionen ausgeführt werden.
  • In DDL-Anweisungen: Sie können die FOR UPDATE-Klausel nicht in Abfragen innerhalb von DDL-Anweisungen verwenden, die für eine spätere Ausführung gespeichert werden. Sie können die FOR UPDATE-Klausel beispielsweise nicht beim Definieren einer Ansicht verwenden. Wenn eine Sperrung erforderlich ist, kann die FOR UPDATE-Klausel beim Abfragen der Ansicht angegeben werden.

Weitere Informationen