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

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

FOR UPDATE 子句的鎖定機制在可重複讀取和可序列化隔離中有所不同。使用可序列化隔離時，如果使用 SELECT 查詢掃描資料表，加入 FOR UPDATE 子句即可在資料列和資料欄的細微程度層級 (又稱儲存格層級) 啟用專屬鎖定。鎖定會在讀寫交易的生命週期內維持不變。在此期間，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 會鎖定 MarketingBudget 儲存格的 1 <= AlbumId < 5。

-- 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 會鎖定 MarketingBudget 儲存格的 1 <= AlbumId < 10。

-- 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 和 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 子句。

後續步驟