SELECT FOR UPDATE verwenden

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

Wenn Sie eine SELECT-Abfrage verwenden, um eine Tabelle zu scannen, fügen Sie eine FOR UPDATE-Klausel hinzu, um exklusive Sperren auf Zeilen- und Spaltenebene, auch als Zellenebene bezeichnet, zu aktivieren. 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. Weitere Informationen finden Sie in den FOR UPDATE-Referenzhandbüchern für GoogleSQL und PostgreSQL.

Vorteile der FOR UPDATE-Klausel

In Datenbanken mit weniger strengen Isolationsebenen ist die Klausel FOR UPDATE möglicherweise erforderlich, um sicherzustellen, dass eine gleichzeitige Transaktion keine Daten zwischen dem Lesen der Daten und dem Committen der Transaktion aktualisiert. Da Spanner die Datenbankserialisierbarkeit immer erzwingt, ist sichergestellt, dass die Transaktion nur dann erfolgreich abgeschlossen wird, wenn die Daten, auf die innerhalb der Transaktion zugegriffen wird, zum Zeitpunkt des Commits nicht veraltet sind. Daher ist die Klausel FOR UPDATE nicht erforderlich, um die Richtigkeit von Transaktionen in Spanner zu gewährleisten.

Bei Anwendungsfällen mit hoher Schreibkonfliktrate, z. B. wenn mehrere Transaktionen gleichzeitig dieselben Daten lesen und darin schreiben, können die gleichzeitigen Transaktionen zu mehr Abbruchsvorgängen führen. Wenn mehrere gleichzeitige Transaktionen gemeinsame Sperren erhalten und dann versuchen, ein Upgrade auf exklusive Sperren auszuführen, führen die Transaktionen zu einem Deadlock. Spanner bricht dann alle Transaktionen bis auf eine ab. Weitere Informationen finden Sie unter Sperren.

Eine Transaktion, die die Klausel FOR UPDATE verwendet, erhält die exklusive Sperre und wird ausgeführt, während andere Transaktionen auf die Sperre warten. Spanner kann den Durchsatz zwar weiterhin begrenzen, da die in Konflikt stehenden Transaktionen nur jeweils einzeln ausgeführt werden können. Da Spanner jedoch nur mit einer Transaktion fortfährt, wird Zeit gespart, die andernfalls mit dem Abbruch von Transaktionen und dem Versuch, Transaktionen zu wiederholen, aufgewendet werden würde.

Wenn Sie also die Anzahl der abgebrochenen Transaktionen in einem Szenario mit gleichzeitigen Schreibanfragen reduzieren möchten, können Sie die Klausel FOR UPDATE verwenden, um die Gesamtzahl der Abbruchvorgänge zu verringern und die Effizienz der Arbeitslastausführung zu erhöhen.

Vergleich mit dem Hinweis LOCK_SCANNED_RANGES

Die FOR UPDATE-Klausel dient einer ähnlichen 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, was zu Sperrkonflikten bei der Arbeitslast beiträgt. Das folgende Beispiel zeigt, wie ein Schlosshinweis 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 Klausel FOR UPDATE aber auch in einer untergeordneten Abfrage 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 LOCK_SCANNED_RANGES-Hinweis in DML-Anweisungen verwenden, die FOR UPDATE-Klausel jedoch nur in SELECT-Anweisungen.

Sperren-Semantik

Um gleichzeitige Schreibanfragen und die Kosten für abgebrochene Transaktionen aufgrund von Deadlocks zu reduzieren, sperrt Spanner Daten nach Möglichkeit auf Zellebene. Wenn Sie die FOR UPDATE-Klausel verwenden, sperrt Spanner bestimmte Zellen, die von der SELECT-Abfrage gescannt werden.

Im folgenden Beispiel ist die Zelle MarketingBudget in den Zeilen SingerId = 1 und AlbumId = 1 in der Tabelle Albums exklusiv gesperrt. Dadurch wird verhindert, dass diese Zelle von parallelen Transaktionen geändert wird, bis diese Transaktion committet oder rückgängig gemacht wird. Durch gleichzeitige Transaktionen kann die Zelle AlbumTitle in dieser Zeile jedoch trotzdem aktualisiert werden.

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

Gleichzeitige Transaktionen können das Lesen gesperrter Daten blockieren

Wenn eine Transaktion exklusive Sperren für einen gescannten Bereich erworben hat, können gleichzeitige Transaktionen das Lesen dieser Daten blockieren. Spanner erzwingt die Serialisierung, sodass Daten nur gelesen werden können, wenn sie innerhalb der Lebensdauer der Transaktion garantiert nicht von einer anderen Transaktion geändert wurden. Bei gleichzeitigen Transaktionen, die versuchen, bereits gesperrte Daten zu lesen, muss möglicherweise gewartet werden, bis die Transaktion, die die Sperren hält, committet oder rückgängig gemacht wird.

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

-- 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 rückgängig gemacht 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, von einer gleichzeitigen Transaktion blockiert, die einen sich überschneidenden gescannten Bereich sperrt.

Im folgenden Beispiel ist auch Transaction 3 blockiert, da Transaction 1 die Zellen MarketingBudget für 3 <= AlbumId < 5 gesperrt hat, was dem sich überschneidenden gescannten Bereich mit Transaction 3 entspricht.

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

Die schreibgeschützte Transaction 2 wird nicht von den in Transaction 1 erworbenen Sperren blockiert, da sie eine Indextabelle abfragt.

-- Transaction 2
SELECT SingerId FROM Singers;

Durch parallele Transaktionen werden keine DML-Vorgänge für bereits gesperrte Daten blockiert

Wenn eine Transaktion Sperren für einen Zellenbereich mit einem Hinweis auf eine exklusive Sperre erworben hat, können gleichzeitige Transaktionen, die versuchen, einen Schreibvorgang auszuführen, ohne zuerst die Daten in den gesperrten Zellen zu lesen, fortfahren. Die Transaktion wird beim Commit blockiert, bis die Transaktion, die die Sperren hält, committet oder zurückgerollt 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, ist dies erst möglich, wenn Transaction 1 ein Commit ausführt oder ein Rollback durchfü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 in den Lücken innerhalb dieses Bereichs keine Daten von gleichzeitigen Transaktionen 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 committet oder zurückgerollt wird.

-- 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 bei der Sperre

Die beschriebene Semaphoren-Semantik bietet eine allgemeine Orientierung, ist aber keine Garantie dafür, wie genau Sperren 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.

Suchanfrage-Semantik

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

In WITH-Anweisungen verwenden

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

In der folgenden Abfrage werden für die Tabelle Singers keine Sperren erworben, da die Sperrabsicht nicht an die Abfrage mit allgemeinen Tabellenausdrücken (Common Table Expressions, CTE) 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 erworben.

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

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 der äußeren Ebene mit einer oder mehreren untergeordneten Abfragen verwenden. Sperren werden von der Abfrage der obersten Ebene und innerhalb von Unterabfragen erworben, 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;

In 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 Ausdrucksunterabfrage zurückgegeben werden, sind gesperrt.

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

Zum Abfragen von Datenansichten 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;

Die FOR UPDATE-Klausel kann nicht beim Definieren einer Ansicht verwendet werden.

Nicht unterstützte Anwendungsfälle

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

  • Als Mechanismus für den gegenseitigen Ausschluss bei der 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 wiederholt wird, sei es explizit durch Anwendungscode oder implizit durch Clientcode wie den Spanner JDBC-Treiber. Es wird nur garantiert, dass die Sperren während des tatsächlichen Versuchs bestanden haben.
  • In Kombination mit dem LOCK_SCANNED_RANGES-Hinweis:Sie können in einer Abfrage nicht gleichzeitig die FOR UPDATE-Klausel und den LOCK_SCANNED_RANGES-Hinweis verwenden. Andernfalls gibt Spanner einen Fehler zurück.
  • In Volltextsuchanfragen:Die Klausel FOR UPDATE kann nicht in Abfragen mit Volltextsuchindexen verwendet werden.
  • In schreibgeschützten Transaktionen:Die Klausel FOR UPDATE ist nur in Abfragen gültig, die in Lese-Schreib-Transaktionen ausgeführt werden.
  • In DDL-Anweisungen:Die Klausel FOR UPDATE kann nicht in Abfragen in DDL-Anweisungen verwendet werden, die zur späteren Ausführung gespeichert werden. So können Sie beispielsweise die FOR UPDATE-Klausel nicht zum Definieren einer Ansicht verwenden. Wenn eine Sperrung erforderlich ist, kann die FOR UPDATE-Klausel bei der Abfrage der Ansicht angegeben werden.

Weitere Informationen