鎖定統計資料

Spanner 提供鎖定統計資料，可讓您找出在特定時間範圍內，資料庫中交易鎖定衝突的主要來源資料列鍵和資料表欄。您可以使用 SQL 陳述式，從 SPANNER_SYS.LOCK_STATS* 系統表擷取這些統計資料。

可用性

SPANNER_SYS 資料只能透過 SQL 介面取得，例如：

• Google Cloud 控制台中資料庫的 Spanner Studio 頁面

• gcloud spanner databases execute-sql 指令

• 「鎖定深入分析」資訊主頁

• executeQuery API

Spanner 提供的其他單一讀取方法不支援 SPANNER_SYS。

依資料列索引鍵鎖定統計資料

下表會追蹤等待時間最長的資料列鍵：

• SPANNER_SYS.LOCK_STATS_TOP_MINUTE：在 1 分鐘間隔內，鎖定等待時間最高的行鍵。

• SPANNER_SYS.LOCK_STATS_TOP_10MINUTE：在 10 分鐘間隔期間內，鎖定等待時間最高的資料列索引鍵。

• SPANNER_SYS.LOCK_STATS_TOP_HOUR：在 1 小時間隔內，鎖定等待時間最高的行鍵

這些資料表具備下列屬性：

• 每個資料表都包含資料表名稱所指定的非重疊時間間隔長度。

• 間隔是基於時鐘時間。1 分鐘間隔的結束時間是目前這一分鐘、10 分鐘間隔的結束時間是目前這個小時的每 10 分鐘，而 1 小時間隔的結束時間是目前這個小時。每個間隔結束後，Spanner 會從所有伺服器收集資料，並在稍後將資料提供在 SPANNER_SYS 資料表中。

  舉例來說，在上午 11:59:30，SQL 查詢的最近可用間隔如下：

  • 1 分鐘：上午 11:58:00–11:58:59
  • 10 分鐘：上午 11:40:00–11:49:59
  • 1 小時：上午 10:00:00–10:59:59

• Spanner 會依照起始資料列索引鍵範圍將統計資料分組。

• 每個資料列都包含特定起始資料列索引值範圍的總鎖定等待時間統計資料，Spanner 會在指定間隔期間擷取這些統計資料。

• 如果 Spanner 無法儲存間隔期間鎖定等待的每個資料列索引鍵範圍資訊，系統會優先處理在指定間隔期間鎖定等待時間最長的資料列索引鍵範圍。

• 資料表中的所有欄均可為空值。

資料表結構定義

鎖定模式

當 Spanner 作業屬於讀寫交易時，就會取得鎖定。唯讀交易不會取得鎖定。Spanner 會使用不同的鎖定模式，在特定時間內，盡可能讓交易能夠存取特定資料儲存格。不同鎖具的特性各異。舉例來說，某些鎖定可在多個交易中共用，但有些則不行。

在交易中嘗試取得下列其中一種鎖定模式時，可能會發生鎖定衝突。

• ReaderShared 鎖定 - 讓其他讀取作業仍能存取資料，直到交易準備好修訂為止。讀寫交易讀取資料時，系統會取得這項共用鎖定。

• WriterShared 鎖定 - 讀寫交易嘗試提交寫入作業時，系統會取得此鎖定。

• Exclusive 鎖定 - 讀寫交易已取得 ReaderShared 鎖定，在讀取完成後嘗試寫入資料時，會取得專屬鎖定。專屬鎖定是從 ReaderShared 鎖定升級而來。獨佔式鎖定是指同時保留 ReaderShared 鎖定和 WriterShared 鎖定的交易的特殊情況。其他交易無法取得相同儲存格的任何鎖定。

• WriterSharedTimestamp 鎖定 - 特殊類型的 WriterShared 鎖定，會在將新資料列插入資料表時取得，該資料表的修訂時間戳記是主鍵的一部分。這類鎖定可防止交易參與者建立完全相同的資料列，進而產生衝突。Spanner 會更新插入資料列的鍵，以符合執行插入作業的交易的修訂時間戳記。

如要進一步瞭解交易類型和可用的鎖定類型，請參閱「交易」。

鎖定模式衝突

下表列出不同鎖定模式之間可能發生的衝突。

只有在插入含有時間戳記做為主鍵一部分的新資料列時，才會使用 WriterSharedTimestamp 鎖定。在寫入現有儲存格或插入不含時間戳記的新資料列時，會使用 WriterShared 和 Exclusive 鎖定機制。因此，WriterSharedTimestamp 不會與其他類型的鎖定機制衝突，這些情況在前述表格中顯示為「不適用」。

唯一的例外狀況是 ReaderShared，因為它可套用至不存在的資料列，因此可能會與 WriterSharedTimestamp 發生衝突。舉例來說，即使是尚未建立的資料列，完整資料表掃描作業也會鎖定整個資料表，因此 ReaderShared 可能會與 WriterSharedTimestamp 發生衝突。

什麼是資料列範圍起始索引鍵？

ROW_RANGE_START_KEY 欄會指出有鎖定衝突的複合式主鍵，或資料列範圍的起始主鍵。以下是用於說明示例的結構定義。

CREATE TABLE Singers (
  SingerId   INT64 NOT NULL,
  FirstName  STRING(1024),
  LastName   STRING(1024),
  SingerInfo BYTES(MAX),
) PRIMARY KEY (SingerId);

CREATE TABLE Albums (
  SingerId     INT64 NOT NULL,
  AlbumId      INT64 NOT NULL,
  AlbumTitle   STRING(MAX),
) PRIMARY KEY (SingerId, AlbumId),
  INTERLEAVE IN PARENT Singers ON DELETE CASCADE;

CREATE TABLE Songs (
  SingerId     INT64 NOT NULL,
  AlbumId      INT64 NOT NULL,
  TrackId      INT64 NOT NULL,
  SongName     STRING(MAX),
) PRIMARY KEY (SingerId, AlbumId, TrackId),
  INTERLEAVE IN PARENT Albums ON DELETE CASCADE;

CREATE TABLE Users (
  UserId     INT64 NOT NULL,
  LastAccess TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true),
  ...
) PRIMARY KEY (UserId, LastAccess);

如以下表格所示，在資料列鍵和資料列鍵範圍中，範圍會以加號 '+' 符號表示。在這些情況下，鍵代表發生鎖定衝突的鍵範圍起始鍵。

匯總統計資料

SPANNER_SYS 也包含資料表，用於儲存 Spanner 在特定時間範圍內擷取的鎖定統計資料匯總資料：

• SPANNER_SYS.LOCK_STATS_TOTAL_MINUTE：1 分鐘間隔期間內所有鎖定等待的匯總統計資料。

• SPANNER_SYS.LOCK_STATS_TOTAL_10MINUTE：10 分鐘間隔期間內所有鎖定等待的匯總統計資料。

• SPANNER_SYS.LOCK_STATS_TOTAL_HOUR：1 小時間隔期間內所有鎖定等待的匯總統計資料。

匯總統計資料表格具有下列屬性：

• 每個資料表都包含資料表名稱所指定的非重疊時間間隔長度。

• 間隔是基於時鐘時間。1 分鐘間隔的結束時間是目前這一分鐘、10 分鐘間隔的結束時間是目前這個小時的每 10 分鐘，而 1 小時間隔的結束時間是目前這個小時。

  舉例來說，在上午 11:59:30，SQL 查詢的最近可用間隔如下：

  • 1 分鐘：上午 11:58:00–11:58:59
  • 10 分鐘：上午 11:40:00–11:49:59
  • 1 小時：上午 10:00:00–10:59:59

• 每個資料列都會包含指定間隔期間，資料庫上所有鎖定等待時間的統計資料匯總。每個時間間隔只有一個資料列。

• SPANNER_SYS.LOCK_STATS_TOTAL_* 資料表中擷取的統計資料包括 Spanner 未在 SPANNER_SYS.LOCK_STATS_TOP_* 資料表中擷取的鎖定等待時間。

• 這些資料表中的部分欄會在 Cloud Monitoring 中顯示為指標。公開的指標如下：

  • 鎖定等待時間

  詳情請參閱「Spanner 指標」。

資料表結構定義

查詢範例

以下是可用於擷取鎖定統計資料的 SQL 陳述式範例。您可以使用用戶端程式庫、gcloud spanner 或 Google Cloud 主控台執行這些 SQL 陳述式。

列出上一個 1 分鐘區間的鎖定統計資料

下列查詢會傳回最近 1 分鐘時間間隔內，每個有鎖定衝突的資料列鍵的鎖定等待資訊，包括總鎖定衝突的百分比。

CAST() 函式會將 row_range_start_key BYTES 欄位轉換為 STRING。

SELECT CAST(s.row_range_start_key AS STRING) AS row_range_start_key,
       t.total_lock_wait_seconds,
       s.lock_wait_seconds,
       s.lock_wait_seconds/t.total_lock_wait_seconds frac_of_total,
       s.sample_lock_requests
FROM spanner_sys.lock_stats_total_minute t, spanner_sys.lock_stats_top_minute s
WHERE t.interval_end =
  (SELECT MAX(interval_end)
   FROM spanner_sys.lock_stats_total_minute)
AND s.interval_end = t.interval_end
ORDER BY s.lock_wait_seconds DESC;

查詢輸出

¹ _exists 是用於檢查特定資料列是否存在的內部欄位。

資料保留

Spanner 至少會在下列時間範圍保留每個資料表的資料：

• SPANNER_SYS.LOCK_STATS_TOP_MINUTE 和 SPANNER_SYS.LOCK_STATS_TOTAL_MINUTE：涵蓋前 6 個小時的間隔。

• SPANNER_SYS.LOCK_STATS_TOP_10MINUTE 和 SPANNER_SYS.LOCK_STATS_TOTAL_10MINUTE：涵蓋前 4 天的間隔。

• SPANNER_SYS.LOCK_STATS_TOP_HOUR 和 SPANNER_SYS.LOCK_STATS_TOTAL_HOUR：涵蓋前 30 天的間隔。

使用鎖定統計資料排解資料庫中的鎖定衝突問題

您可以使用 SQL 或鎖定深入分析資訊主頁，查看資料庫中的鎖定衝突。

下列主題說明如何使用 SQL 程式碼調查這類鎖定衝突。

選取要調查的時間範圍

您檢查 Spanner 資料庫的延遲指標，並發現應用程式出現高延遲和 CPU 使用率的時間範圍。舉例來說，問題是在 2020 年 11 月 12 日晚上 10 點 50 分左右開始發生。

判斷在所選期間內，交易提交延遲時間是否隨著鎖定等待時間而增加

鎖定會由交易取得，因此如果鎖定衝突導致等待時間拉長，我們應該會看到交易確認延遲時間增加，以及鎖定等待時間增加。

選定要開始調查的時間範圍後，我們會將交易統計資料 TXN_STATS_TOTAL_10MINUTE 與該時間點附近的鎖定統計資料 LOCK_STATS_TOTAL_10MINUTE 合併，以便瞭解平均提交延遲時間增加是否與鎖定等待時間增加有關。

SELECT t.interval_end, t.avg_commit_latency_seconds, l.total_lock_wait_seconds
FROM spanner_sys.txn_stats_total_10minute t
LEFT JOIN spanner_sys.lock_stats_total_10minute l
ON t.interval_end = l.interval_end
WHERE
  t.interval_end >= "2020-11-12T21:50:00Z"
  AND t.interval_end <= "2020-11-12T23:50:00Z"
ORDER BY interval_end;

以下資料為範例，說明我們從查詢中取得的結果。

上述結果顯示，在 2020-11-12 22:40:00 到 2020-11-12 22:50:00 之間的時間範圍內，avg_commit_latency_seconds 和 total_lock_wait_seconds 的數量大幅增加，但之後就下降了。值得注意的是，avg_commit_latency_seconds 是指只在提交步驟上花費的平均時間。另一方面，total_lock_wait_seconds 是該期間的總計鎖定時間，因此時間看起來比交易提交時間長得多。

我們已確認鎖定等待時間與寫入延遲時間的增加密切相關，因此在下一個步驟中，我們將調查哪些資料列和資料欄導致等待時間過長。

找出在所選期間，哪些資料列鍵和資料欄的鎖定等待時間過長

為了找出在調查期間哪些資料列鍵和資料欄出現較長的鎖定等待時間，我們查詢了 LOCK_STAT_TOP_10MINUTE 資料表，列出最常導致鎖定等待時間的資料列鍵和資料欄。

下列查詢中的 CAST() 函式會將 row_range_start_key BYTES 欄位轉換為 STRING。

SELECT CAST(s.row_range_start_key AS STRING) AS row_range_start_key,
       t.total_lock_wait_seconds,
       s.lock_wait_seconds,
       s.lock_wait_seconds/t.total_lock_wait_seconds frac_of_total,
       s.sample_lock_requests
FROM spanner_sys.lock_stats_total_10minute t, spanner_sys.lock_stats_top_10minute s
WHERE
  t.interval_end = "2020-11-12T22:50:00Z" and s.interval_end = t.interval_end;

從這個結果表格，我們可以看到 Singers 資料表發生衝突，衝突發生在 SingerId=32 鍵上。Singers.SingerInfo 是 ReaderShared 和 WriterShared 之間發生鎖定衝突的資料欄。

當一個交易嘗試讀取特定儲存格，而另一個交易嘗試寫入相同儲存格時，就會發生這類常見的衝突。我們現在知道交易爭奪鎖定項目的確切資料儲存格，因此在下一個步驟中，我們會找出爭奪鎖定的交易。

找出哪些交易會存取鎖定衝突的資料欄

如要找出因鎖定衝突而在特定時間間隔內發生重大提交延遲的交易，您需要從 SPANNER_SYS.TXN_STATS_TOTAL_10MINUTE 資料表中查詢下列資料欄：

• fprint
• read_columns
• write_constructive_columns
• avg_commit_latency_seconds

您需要篩選從 SPANNER_SYS.LOCK_STATS_TOP_10MINUTE 表格中找出的鎖定欄：

• 嘗試取得 ReaderShared 鎖定時，讀取任何發生鎖定衝突的資料欄的交易。

• 嘗試取得 WriterShared 鎖定時，寫入任何資料欄的交易會導致鎖定衝突。

SELECT
  fprint,
  read_columns,
  write_constructive_columns,
  avg_commit_latency_seconds
FROM spanner_sys.txn_stats_top_10minute t2
WHERE (
  EXISTS (
    SELECT * FROM t2.read_columns columns WHERE columns IN (
      SELECT DISTINCT(req.COLUMN)
      FROM spanner_sys.lock_stats_top_10minute t, t.SAMPLE_LOCK_REQUESTS req
      WHERE req.LOCK_MODE = "ReaderShared" AND t.interval_end ="2020-11-12T23:50:00Z"))
OR
  EXISTS (
    SELECT * FROM t2.write_constructive_columns columns WHERE columns IN (
      SELECT DISTINCT(req.COLUMN)
      FROM spanner_sys.lock_stats_top_10minute t, t.SAMPLE_LOCK_REQUESTS req
      WHERE req.LOCK_MODE = "WriterShared" AND t.interval_end ="2020-11-12T23:50:00Z"))
)
AND t2.interval_end ="2020-11-12T23:50:00Z"
ORDER BY avg_commit_latency_seconds DESC;

查詢結果會依 avg_commit_latency_seconds 欄排序，讓您先看到發生最高提交延遲的交易。

查詢結果顯示，有兩筆交易嘗試存取 Singers.SingerInfo 資料欄，也就是在該時間範圍內發生鎖定衝突的資料欄。找出導致鎖定衝突的交易後，您可以使用交易的指紋 fprint 進行分析，找出導致鎖定衝突的潛在問題。

查看 fprint=1866043996151916800 的交易後，您可以使用 read_columns 和 write_constructive_columns 欄，找出應用程式程式碼的哪個部分觸發了交易。接著，您可以查看未根據主索引鍵 SingerId 篩選的基礎 DML。這會導致完整的資料表掃描作業，並在交易提交前鎖定資料表。

如要解決鎖定衝突，您可以採取下列做法：

1. 使用唯讀交易來識別必要的 SingerId 值。
2. 使用個別的讀取/寫入交易，更新必要 SingerId 值的資料列。

採用最佳做法來減少鎖定爭用

在我們的範例情境中，我們可以使用鎖定統計資料和交易統計資料，將問題縮小到在更新時未使用資料表主鍵的交易。我們想到了一些改善交易的想法，這取決於我們是否事先知道要更新的資料列鍵。

在查看解決方案的潛在問題，甚至在設計解決方案時，請考慮採用這些最佳做法，減少資料庫中的鎖定衝突數量。

• 避免在讀寫交易中執行大量讀取作業。

• 盡可能使用唯讀交易，因為這些交易不會取得任何鎖定。

• 避免在讀寫交易中執行完整資料表掃描。這包括根據主鍵寫入 DML 條件，或在使用 Read API 時指派特定鍵範圍。

• 在讀寫交易中盡可能在讀取資料後立即提交變更，以縮短鎖定期間。讀寫交易可確保在讀取資料後，資料會維持不變，直到您成功修訂變更為止。為達成這項目標，交易必須在讀取和提交期間鎖定資料儲存格。因此，如果您能縮短鎖定期間，交易發生鎖定衝突的機率就會降低。

• 請盡量使用小型交易，而非大型交易，或者考慮使用分區 DML 來處理長時間執行的 DML 交易。長時間執行的交易會長時間取得鎖定，因此請考慮將涉及數千個資料列的交易分解為多個較小的交易，並盡可能更新數百個資料列。

• 如果您不需要讀寫交易提供的保證，請避免在提交變更前讀取讀寫交易中的任何資料，例如在單獨的唯讀交易中讀取資料。大多數的鎖定衝突都是因為強制保證而發生，目的是確保資料在讀取和提交之間保持不變。因此，如果讀寫交易未讀取任何資料，就不需要長時間鎖定儲存格。

• 請只指定讀寫交易中所需的資料欄最小組合。由於 Spanner 鎖定是針對每個資料儲存格，因此當讀取/寫入交易讀取過多資料欄時，會取得這些儲存格的 ReaderShared 鎖定。當其他交易取得對多列寫入作業的 WriterShared 鎖定時，這可能會導致鎖定衝突。舉例來說，請考慮在讀取時指定一組資料欄，而非 *。

• 盡量減少讀寫交易中的 API 呼叫。由於 API 呼叫會受到網路延遲和服務端延遲的影響，因此 API 呼叫的延遲可能會導致 Spanner 中的鎖定爭用情形。建議您盡可能在讀寫交易之外呼叫 API。如果您必須在讀寫交易中執行 API 呼叫，請務必監控 API 呼叫的延遲時間，盡可能減少對鎖定取得期間的影響。

• 遵循結構定義設計最佳做法。

後續步驟

• 瞭解其他內省工具。
• 瞭解 Spanner 為每個資料庫儲存的其他資訊。這些資訊儲存在資料庫的資訊結構定義資料表中。
• 進一步瞭解 Spanner 的 SQL 最佳做法。