SELECT FOR UPDATE verwenden

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

Wenn Sie mit der SELECT-Abfrage eine Tabelle scannen, fügen Sie eine FOR UPDATE-Klausel hinzu, um exklusive Sperren auf Zeilen- und Spaltenebene zu aktivieren, auch als Zellebene bezeichnet. Die Sperre bleibt für die gesamte 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. Weitere Informationen finden Sie in den GoogleSQL- und PostgreSQL-FOR UPDATE-Referenzanleitungen.

Vorteile der FOR UPDATE-Klausel

In Datenbanken mit weniger strengen Isolationsebenen kann die FOR UPDATE-Klausel erforderlich sein, um sicherzustellen, dass eine gleichzeitige Transaktion Daten nicht zwischen dem Lesen der Daten und dem Committen der Transaktion aktualisiert. Da Spanner immer 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 Richtigkeit von Transaktionen 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. Spanner bricht dann alle Transaktionen bis auf eine ab. Weitere Informationen finden Sie unter Sperren.

Eine Transaktion, die die FOR UPDATE-Klausel verwendet, erhält die exklusive Sperre 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 immer nur jeweils eine Transaktion ausführt.

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, erhält die Transaktion für die gesamte Anweisung exklusive Sperren für die gescannten Bereiche. 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 Sperrkonflikten 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. Bei Verwendung der FOR UPDATE-Anweisung 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 per Rollback zurückgesetzt wird.

Im folgenden Beispiel werden die Zellen MarketingBudget für 1 <= AlbumId < 5 durch Transaction 1 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 ist ebenfalls gesperrt, 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 exklusiven Sperrhinweis erworben 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 gleichzeitige Transaktionen keine Daten in die Lücken in diesem Bereich 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 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 Sperrsemantik 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 abgerufen werden. Diese Klausel verhindert, dass andere Transaktionen die gesperrten Zellen ändern, bis die aktuelle Transaktion abgeschlossen ist.

Semantik von Anfragen

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

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 abgerufen.

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 Klausel FOR UPDATE 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 Versuchs bestanden haben, der tatsächlich committet wurde.
  • In Kombination mit dem Hinweis LOCK_SCANNED_RANGES:Sie können die FOR UPDATE-Klausel und den Hinweis LOCK_SCANNED_RANGES nicht 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-/Schreibtransaktionen 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