鎖定統計資料

本文說明如何使用 Spanner 鎖定統計資料內建資料表，找出特定時間範圍內導致資料庫交易鎖定衝突的資料列鍵和資料表資料欄。您可以使用 SQL 陳述式，從這些 SPANNER_SYS.LOCK_STATS* 資料表擷取統計資料。

如果交易使用開放式並行控制，則無法取得鎖定統計資料，因為交易執行期間不會取得鎖定。

查看鎖定統計資料

Spanner 會在 SPANNER_SYS 結構定義中提供鎖定統計資料。 您可以透過下列方式存取 SPANNER_SYS 資料：

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

• gcloud spanner databases execute-sql 指令。

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

• executeSql 或 executeStreamingSql 方法。

  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 呼叫的延遲時間可能會導致 Spanner 中的鎖定爭用，因為 API 呼叫會受到網路延遲和服務端延遲的影響。建議您盡可能在讀寫交易以外進行 API 呼叫。如果必須在讀寫交易中執行 API 呼叫，請務必監控 API 呼叫的延遲時間，盡量縮短鎖定取得期間。

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

• 如果應用程式不會發生寫入偏斜問題，建議您使用可重複讀取隔離層級，並搭配預設的樂觀並行控制，避免讀取/寫入內容發生爭用。詳情請參閱「可重複讀取隔離」。

• 對於讀寫爭用較低的工作負載，建議使用可序列化隔離和開放式並行控制，避免在交易中取得鎖定，直到認可時間為止，同時確保交易的所有讀取在認可時間都有效。如要進一步瞭解這個選項是否合適，請參閱「樂觀並行控制」。

後續步驟

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