在可序列化隔離中,使用 SELECT FOR UPDATE

本頁說明如何在可序列化隔離中使用 FOR UPDATE 子句。

可重複讀取和可序列化隔離的 FOR UPDATE 子句鎖定機制不同。使用可序列化隔離時,當您使用 SELECT 查詢掃描資料表時,新增 FOR UPDATE 子句會在資料列和資料欄精細程度層級 (也稱為儲存格層級) 的交集處啟用獨占鎖定。在讀寫交易的生命週期內,鎖定會保持不變。在此期間,FOR UPDATE 子句會防止其他交易修改鎖定的儲存格,直到目前交易完成為止。

如要瞭解如何使用 FOR UPDATE 子句,請參閱 GoogleSQL 和 PostgreSQL FOR UPDATE 參考指南。

使用 FOR UPDATE 子句的原因

在隔離等級較不嚴格的資料庫中,可能需要使用 FOR UPDATE 子句,確保並行交易不會在讀取資料和提交交易之間更新資料。在預設的可序列化隔離下,Spanner 會確保只有在存取的資料在提交時未過時,交易才會提交。因此,FOR UPDATE 子句並非確保可序列化隔離交易正確性的必要條件。

不過,在寫入爭用率較高的用途中 (例如多筆交易同時讀取及寫入相同資料),同時進行的交易可能會導致取消次數增加。這是因為當多個同時執行的交易取得共用鎖定,然後嘗試升級為獨占鎖定時,交易會導致死結。死結會永久封鎖交易,因為每筆交易都在等待其他交易釋出所需資源。為順利執行,Spanner 會取消所有交易,只保留一筆交易來解決死結。詳情請參閱「鎖定」。

使用 FOR UPDATE 子句的交易會主動取得專屬鎖定並繼續執行,其他交易則會等待鎖定。雖然 Spanner 可能仍會限制處理量,因為衝突的交易一次只能執行一項,但由於 Spanner 只會在一項交易上取得進展,因此可以節省原本用於取消和重試交易的時間。

因此,如果減少同時寫入要求情境中取消的交易數量很重要,您可以使用 FOR UPDATE 子句減少取消的總數,並提高工作負載執行效率。

可序列化隔離主要使用 FOR UPDATE 來減少寫入爭用造成的中止,而可重複讀取隔離則使用 FOR UPDATE 來防止讀寫衝突,並確保交易正確性。詳情請參閱「在可重複讀取隔離中使用 SELECT FOR UPDATE」。

與 LOCK_SCANNED_RANGES 提示的比較

FOR UPDATE 子句的作用與 LOCK_SCANNED_RANGES=exclusive 提示類似。

主要有兩點不同:

  • 如果使用 LOCK_SCANNED_RANGES 提示,交易會取得整個陳述式掃描範圍的專屬鎖定。您無法取得子查詢的獨占鎖定。使用鎖定提示可能會導致取得不必要的鎖定,並造成工作負載中的鎖定爭用。以下範例說明如何使用鎖定提示:

    @{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;
    

    另一方面,您可以在子查詢中使用 FOR UPDATE 子句,如以下範例所示:

    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;
    
  • 您可以在 DML 陳述式中使用 LOCK_SCANNED_RANGES 提示,但只能在 SELECT 陳述式中使用 FOR UPDATE 子句。

鎖定語意

為減少同時寫入要求,以及因死結而取消交易的成本,Spanner 會盡可能在儲存格層級鎖定資料。儲存格層級是資料表中最精細的資料層級,也就是資料列和資料欄交集處的資料點。使用 FOR UPDATE 子句時,Spanner 會鎖定 SELECT 查詢掃描的特定儲存格。

在以下範例中,SingerId = 1 和 AlbumId = 1 資料列中的 MarketingBudget 儲存格在 Albums 資料表中遭到獨占鎖定,因此在提交或復原這項交易之前,並行交易無法修改該儲存格。不過,並行交易仍可更新該資料列中的 AlbumTitle 儲存格。

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

並行交易可能會在讀取鎖定的資料時遭到封鎖

當一筆交易取得掃描範圍的專屬鎖定時,並行交易可能會封阻式讀取該資料。Spanner 會強制執行可序列化,因此只有在保證資料不會在交易生命週期內遭到其他交易變更時,才能讀取資料。如果並行交易嘗試讀取已鎖定的資料,可能需要等待持有鎖定的交易提交、復原或逾時。

在以下範例中,Transaction 1 會鎖定 1 <= AlbumId < 5 的 MarketingBudget 儲存格。

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

Transaction 2 嘗試讀取 AlbumId = 1 的 MarketingBudget,但遭到封鎖,直到 Transaction 1 提交或復原為止。

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

-- Blocked by Transaction 1

同樣地,如果交易嘗試使用 FOR UPDATE 鎖定掃描範圍,但並行交易鎖定了重疊的掃描範圍,則該交易會遭到封鎖。

在以下範例中,Transaction 3 也會遭到封鎖,因為 Transaction 1 已鎖定 3 <= AlbumId < 5 的 MarketingBudget 儲存格,這是與 Transaction 3 重疊的掃描範圍。

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

-- Blocked by Transaction 1

讀取索引

如果鎖定掃描範圍的查詢鎖定了基礎資料表中的資料列,但並行交易從索引讀取,則並行讀取可能不會遭到封鎖。

下列 Transaction 1 會鎖定 SingerId = 1 的 SingerId 和 SingerInfo 儲存格。

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

唯讀 Transaction 2 不會遭到 Transaction 1 中取得的鎖定封鎖,因為它會查詢索引資料表。

-- Transaction 2
SELECT SingerId FROM Singers;

並行交易不會封鎖已鎖定資料的 DML 作業

當一項交易透過獨占鎖定提示取得一系列儲存格的鎖定時,並行交易若嘗試執行寫入作業,而不先讀取鎖定儲存格中的資料,則可繼續進行。交易會封鎖提交作業,直到持有鎖定的交易提交或復原為止。

下列 Transaction 1 會鎖定 1 <= AlbumId < 5 的 MarketingBudget 儲存格。

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

如果 Transaction 2 嘗試更新 Albums 資料表,系統會禁止這項操作,直到 Transaction 1 提交或復原為止。

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

> Query OK, 1 rows affected

COMMIT;

-- Blocked by Transaction 1

鎖定掃描範圍時,現有資料列和間隔會鎖定

當某個交易取得掃描範圍的獨占鎖定時,並行交易就無法在該範圍的間隔內插入資料。

下列 Transaction 1 會鎖定 1 <= AlbumId < 10 的 MarketingBudget 儲存格。

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

如果 Transaction 2 嘗試為尚未存在的 AlbumId = 9 插入資料列,則在 Transaction 1 提交或復原之前,系統會禁止執行這項操作。

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

鎖定取得注意事項

上述鎖定語意提供一般指引,但無法保證 Spanner 執行使用 FOR UPDATE 子句的交易時,會如何取得鎖定。Spanner 的查詢最佳化機制也可能影響取得的鎖定。在目前的交易完成前,這個子句會防止其他交易修改鎖定的儲存格。

查詢語法

本節提供使用 FOR UPDATE 子句時的查詢語法指南。

最常見的用法是在頂層 SELECT 陳述式中。例如:

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

這個範例示範如何在 SELECT 陳述式中使用 FOR UPDATE 子句,以獨占方式鎖定 WHERE SingerID = 5 的 SingerId 和 SingerInfo 儲存格。

在 WITH 陳述式中使用

當您在 WITH 陳述式的外部層級查詢中指定 FOR UPDATE 時,FOR UPDATE 子句不會取得 WITH 陳述式的鎖定。

在下列查詢中,Singers 資料表不會取得任何鎖定,因為鎖定意圖不會傳播至一般資料表運算式 (CTE) 查詢。

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

如果在 CTE 查詢中指定 FOR UPDATE 子句,CTE 查詢的掃描範圍會取得鎖定。

在以下範例中,SingerId > 5 的資料列中,SingerId 和 SingerInfo 儲存格已鎖定。

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

在子查詢中使用

您可以在具有一或多個子查詢的外層查詢中使用 FOR UPDATE 子句。鎖定是由頂層查詢和子查詢取得,但運算式子查詢除外。

下列查詢會鎖定 SingerId > 5. 的資料列 SingerId 和 SingerInfo 儲存格

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

下列查詢位於運算式子查詢中,因此不會鎖定 Albums 資料表中的任何儲存格。運算式子查詢傳回的資料列中,SingerId 和 SingerInfo 儲存格會遭到鎖定。

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

用於查詢檢視表

您可以使用 FOR UPDATE 子句查詢檢視表,如以下範例所示:

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

SELECT * FROM SingerBio WHERE SingerId = 5 FOR UPDATE;

定義檢視表時,無法使用 FOR UPDATE 子句。

不支援的用途

不支援下列 FOR UPDATE 用途:

  • 做為在 Spanner 外部執行程式碼的互斥機制:請勿在 Spanner 中使用鎖定功能,確保對 Spanner 外部資源的專屬存取權。Spanner 可能會取消並重試交易,並在取消交易時釋出鎖定。詳情請參閱可重複讀取指南中的「不支援的使用案例」一節。
  • 與 LOCK_SCANNED_RANGES 提示合併使用:您無法在同一查詢中同時使用 FOR UPDATE 子句和 LOCK_SCANNED_RANGES 提示,否則 Spanner 會傳回錯誤。
  • 在全文搜尋查詢中:您無法在使用全文搜尋索引的查詢中使用 FOR UPDATE 子句。
  • 在唯讀交易中:FOR UPDATE 子句僅適用於在讀寫交易中執行的查詢。
  • 在 DDL 陳述式中:您無法在 DDL 陳述式的查詢中使用 FOR UPDATE 子句,這些陳述式會儲存起來供日後執行。舉例來說,定義檢視表時,您無法使用 FOR UPDATE 子句。如需鎖定,可在查詢檢視表時指定 FOR UPDATE 子句。

後續步驟