本頁說明如何在可序列化隔離中使用 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子句。
後續步驟
- 瞭解如何在 GoogleSQL 和 PostgreSQL 中使用
FOR UPDATE子句。 - 瞭解如何在可重複讀取隔離中使用
SELECT FOR UPDATE。 - 瞭解
LOCK_SCANNED_RANGES提示。 - 瞭解 Spanner 的鎖定功能。
- 瞭解 Spanner 序列化能力。