使用 SELECT FOR UPDATE

重要事項： 請勿使用鎖定功能，確保在 Spanner 以外的資源中擁有專屬存取權。詳情請參閱「不支援的使用案例」。

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

使用 SELECT 查詢掃描資料表時，請新增 FOR UPDATE 子句，在資料列和資料欄的細微程度層級啟用專屬鎖定，也就是所謂的儲存格層級。在讀寫交易的生命週期內，鎖定狀態會維持不變。在此期間，FOR UPDATE 子句會防止其他交易修改鎖定的儲存格，直到目前的交易完成為止。詳情請參閱 GoogleSQL 和 PostgreSQL FOR UPDATE 參考指南。

為什麼要使用 FOR UPDATE 子句

在隔離等級較寬鬆的資料庫中，可能需要使用 FOR UPDATE 子句，確保並行交易不會在讀取資料和提交交易之間更新資料。由於 Spanner 一律會強制執行可序列化作業，因此保證只有在交易中存取的資料於提交時未過時，交易才會成功提交。因此，在 Spanner 中，FOR UPDATE 子句並非確保交易正確性的必要條件。

不過，在寫入爭用率高的用途中 (例如多筆交易同時讀取及寫入相同資料時)，同步交易可能會導致中止次數增加。這是因為當多個同步交易取得共用鎖定，然後嘗試升級為專屬鎖定時，交易會造成死結。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 查詢掃描的特定儲存格。

在以下範例中，Albums 表格中的 MarketingBudget 儲存格位於 SingerId = 1 和 AlbumId = 1 列，因此會遭到專屬鎖定，防止並行交易修改該儲存格，直到這項交易完成或回溯為止。不過，並行交易仍可更新該列中的 AlbumTitle 儲存格。

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

並行交易可能會封鎖讀取鎖定的資料

如果某項交易已取得掃描範圍的專屬鎖定，並行交易可能會封鎖讀取該資料。Spanner 會強制執行可序列化，因此只有在交易生命週期內，保證資料不會因其他交易而變更時，才能讀取資料。嘗試讀取已鎖定資料的並行交易可能必須等待，直到持有鎖定的交易完成或回溯為止。

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

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

用於 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 和 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 中止，無論是應用程式碼明確重試交易，還是用戶端程式碼 (例如 Spanner JDBC 驅動程式) 隱含重試交易，系統都只會保證在已提交的嘗試期間保留鎖定。
• 與 LOCK_SCANNED_RANGES 提示搭配使用：您無法在同一個查詢中同時使用 FOR UPDATE 子句和 LOCK_SCANNED_RANGES 提示，否則 Spanner 會傳回錯誤。
• 在全文搜尋查詢中：使用全文搜尋索引的查詢無法使用 FOR UPDATE 子句。
• 在唯讀交易中：FOR UPDATE 子句僅在讀寫交易中執行的查詢中有效。
• 在 DDL 陳述式中：您無法在 DDL 陳述式中的查詢使用 FOR UPDATE 子句，這些查詢會儲存以供日後執行。舉例來說，您無法在定義檢視區塊時使用 FOR UPDATE 子句。如需鎖定，查詢檢視區塊時可以指定 FOR UPDATE 子句。

後續步驟

• 瞭解如何在 GoogleSQL 和 PostgreSQL 中使用 FOR UPDATE 子句。
• 瞭解 LOCK_SCANNED_RANGES 提示。
• 瞭解 Spanner 中的鎖定。
• 瞭解 Spanner 可序列化。