使用 SELECT FOR UPDATE

本頁說明如何在 Spanner 中使用 FOR UPDATE 子句。

使用 SELECT 查詢掃描資料表時，請新增 FOR UPDATE 子句，在資料列和資料欄精細程度層級啟用排他性鎖定 (又稱為儲存格層級)。在讀寫交易的整個生命週期中，鎖定會維持在原處。在此期間，FOR UPDATE 子句會防止其他交易修改已鎖定的儲存格，直到目前交易完成為止。如需更多資訊，請參閱 GoogleSQL 和 PostgreSQL FOR UPDATE 參考指南。

使用 FOR UPDATE 子句的原因

在隔離層級較不嚴格的資料庫中，您可能需要使用 FOR UPDATE 子句，確保在讀取資料和提交交易之間，並行交易不會更新資料。由於 Spanner 一律會強制執行可順序化，因此系統會保證，只有在交易中存取的資料在提交時並未過時，交易才會順利提交。因此，您不需要使用 FOR UPDATE 子句，即可確保 Spanner 中的交易正確性。

不過，在寫入爭用情形嚴重的用途中 (例如，多個交易同時讀取及寫入相同資料)，同時進行的交易可能會導致中止次數增加。這是因為當多個同時進行的交易取得共用鎖定，然後嘗試升級為專屬鎖定時，交易會導致鎖死。接著，Spanner 會取消所有交易，僅保留一筆交易。詳情請參閱「鎖定」一文。

使用 FOR UPDATE 子句的交易會取得專屬鎖定並繼續執行，而其他交易則會等待鎖定。雖然 Spanner 可能仍會限制總處理量，因為衝突的交易只能一次執行一筆，但由於 Spanner 只會處理一筆交易，因此可節省原本用於中止及重試交易的時間。

因此，如果您希望在同時寫入要求情境中減少中止的交易數，可以使用 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 1 已鎖定 3 <= AlbumId < 5 的 MarketingBudget 儲存格 (與 Transaction 3 重疊的掃描範圍)，因此下例中的 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 執行更新作業。

-- 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 子句時的查詢語意指南。

在 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 和 SingerInfo 儲存格會鎖定 SingerId > 5 所在的資料列。

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

在子查詢中使用

您可以在包含一個或多個子查詢的外層查詢中使用 FOR UPDATE 子句。頂層查詢和子查詢會取得鎖定，但運算式子查詢除外。

下列查詢會鎖定 SingerId 和 SingerInfo 儲存格，這些儲存格位於 SingerId > 5.

(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 JDBC 驅動程式) 執行，Spanner 可能會中止交易，但只保證在嘗試提交時會保留鎖定。
  • 搭配 LOCK_SCANNED_RANGES 提示：您無法在同一個查詢中同時使用 FOR UPDATE 子句和 LOCK_SCANNED_RANGES 提示，否則 Spanner 會傳回錯誤。
  • 全文搜尋查詢：您無法在使用全文搜尋索引的查詢中使用 FOR UPDATE 子句。
  • 在唯讀交易中：FOR UPDATE 子句僅在讀取/寫入交易中執行的查詢中有效。
  • 在 DDL 陳述式中：您無法在 DDL 陳述式中的查詢中使用 FOR UPDATE 子句，因為這些查詢會儲存起來，以便日後執行。舉例來說，您無法在定義檢視畫面時使用 FOR UPDATE 子句。如果需要鎖定，您可以在查詢檢視畫面時指定 FOR UPDATE 子句。

後續步驟