Verrouiller les statistiques

Ce document explique comment utiliser les tables intégrées de statistiques de verrouillage Spanner pour identifier les clés de ligne et les colonnes de table qui ont provoqué des conflits de verrouillage de transaction dans votre base de données au cours d'une période spécifique. Vous pouvez récupérer les statistiques de ces tables SPANNER_SYS.LOCK_STATS* à l'aide d'instructions SQL.

Les transactions qui utilisent le contrôle de simultanéité optimiste n'acquièrent pas de verrous lorsqu'elles exécutent des lectures et des requêtes. Au lieu de cela, elles acquièrent des verrous au moment du commit. Les statistiques de verrouillage de ces transactions n'incluent donc que les conflits qui se produisent pendant le commit.

Accéder aux statistiques de verrouillage

Spanner fournit les statistiques de verrouillage dans le schéma SPANNER_SYS. Vous pouvez accéder aux données SPANNER_SYS de différentes manières :

  • Page Spanner Studio d'une base de données dans la console Google Cloud

  • La commande gcloud spanner databases execute-sql.

  • Le tableau de bord Insights sur les verrouillages.

  • La méthode executeSql ou executeStreamingSql.

    Les méthodes de lecture unique suivantes fournies par Spanner ne sont pas compatibles avec SPANNER_SYS :

    • effectuer une lecture forte à partir d'une ou de plusieurs lignes d'une table ;
    • effectuer une lecture non actualisée à partir d'une ou de plusieurs lignes d'une table ;
    • lire à partir d'une ou de plusieurs lignes d'un index secondaire.

Verrouiller les statistiques par clé de ligne

Les tableaux ci-dessous suivent la clé de ligne présentant le temps d'attente le plus élevé :

  • SPANNER_SYS.LOCK_STATS_TOP_MINUTE : clés de ligne avec les temps d'attente de verrouillage les plus élevés pendant des intervalles d'une minute.

  • SPANNER_SYS.LOCK_STATS_TOP_10MINUTE : clés de ligne avec les temps d'attente de verrouillage les plus élevés pendant des intervalles de 10 minutes.

  • SPANNER_SYS.LOCK_STATS_TOP_HOUR : clés de ligne avec les temps d'attente de verrouillage les plus élevés pendant des intervalles d'une heure.

Ces tables ont les propriétés suivantes :

  • Chaque table contient des données pour des intervalles de temps qui ne se chevauchent pas, dont la durée est spécifiée par le nom de la table.

  • Les intervalles sont définis selon l'heure réelle. Les intervalles d'une minute se terminent à la minute, les intervalles de 10 minutes se terminent toutes les 10 minutes à partir de l'heure, et les intervalles d'une heure se terminent à l'heure. Après chaque intervalle, Spanner collecte les données de tous les serveurs, puis les met à disposition dans les tables SPANNER_SYS peu de temps après.

    Par exemple, à 11:59:30, les intervalles les plus récents disponibles pour les requêtes SQL sont les suivants :

    • 1 minute : 11:58:00 – 11:58:59
    • 10 minutes : 11:40:00 – 11:49:59
    • 1 heure : 10:00:00 – 10:59:59
  • Spanner regroupe les statistiques par plage de clés de ligne de début.

  • Chaque ligne contient des statistiques sur le temps d'attente total du verrouillage d'une plage de clés de ligne de départ particulière pour laquelle Spanner capture des statistiques pendant l'intervalle spécifié.

  • Si Spanner ne parvient pas à stocker les informations sur chaque plage de clés de ligne pour les attentes de verrouillage pendant l'intervalle, le système donne la priorité à la plage de clés de ligne avec le temps d'attente de verrouillage le plus élevé pendant l'intervalle spécifié.

  • Toutes les colonnes des tables peuvent accepter une valeur nulle.

Schéma de la table

Nom de la colonne Type Description
INTERVAL_END TIMESTAMP Fin de l'intervalle de temps au cours duquel les conflits de verrouillage inclus ont eu lieu.
ROW_RANGE_START_KEY BYTES(MAX) Clé de ligne où le conflit de verrouillage a eu lieu. Lorsque le conflit implique une plage de lignes, cette valeur représente la clé de départ de cette plage. Le signe plus (+) correspond à une plage. Pour plus d'informations, consultez la section Qu'est-ce qu'une clé de début de plage de lignes ?
LOCK_WAIT_SECONDS FLOAT64 Le temps d'attente de verrouillage cumulé des conflits de verrouillage enregistrés pour toutes les colonnes de la plage de clés de ligne est exprimé en secondes.
SAMPLE_LOCK_REQUESTS ARRAY<STRUCT<
  column STRING,
  lock_mode STRING,
   transaction_tag STRING>>
Chaque entrée de ce tableau correspond à un exemple de requête de verrouillage qui a contribué au conflit de verrouillage, soit en attendant un verrouillage, soit en empêchant d'autres transactions de prendre le verrouillage, sur la clé de ligne (plage) donnée. Le nombre maximal d'échantillons dans ce tableau est de 20.

Chaque échantillon contient les trois champs suivants :
  • lock_mode : mode de verrouillage demandé. Pour en savoir plus, consultez Modes de verrouillage.
  • column : colonne ayant rencontré le conflit de verrouillage. Le format de cette valeur est tablename.columnname.
  • transaction_tag : tag de la transaction qui a émis la requête. Pour en savoir plus sur l'utilisation des tags, consultez la page Résoudre les problèmes liés aux tags de transaction.
Toutes les requêtes de verrouillage ayant contribué à des conflits de verrouillage sont échantillonnées de manière uniforme et aléatoire. Il est donc possible qu'une seule moitié d'un conflit (le détenteur ou le demandeur) soit enregistrée dans ce tableau.
SAMPLE_LOCK_REQUESTS_JSON_STRING STRING Représentation sous forme de chaîne compatible JSON de la colonne SAMPLE_LOCK_REQUESTS. La chaîne JSON est un objet JSON ayant la même structure que le STRUCT défini dans la colonne SAMPLE_LOCK_REQUESTS.

Cette colonne est compatible avec les bases de données utilisant le dialecte GoogleSQL et celles utilisant le dialecte PostgreSQL.

LOCK_WAIT_DISTRIBUTION ARRAY<STRUCT>

Histogramme des temps d'attente de verrouillage individuels pour les conflits de verrouillage enregistrés dans la plage de clés de ligne. Contrairement aux autres colonnes de latence de ces tableaux, les valeurs de cette colonne sont exprimées en microsecondes. Pour les convertir en secondes, divisez-les par 1 000 000.

Le tableau contient un seul élément et est du type suivant :
ARRAY<STRUCT<
  COUNT INT64,
  MEAN FLOAT64,
  SUM_OF_SQUARED_DEVIATION FLOAT64,
  NUM_FINITE_BUCKETS INT64,
  GROWTH_FACTOR FLOAT64,
  SCALE FLOAT64,
  BUCKET_COUNTS ARRAY<INT64>>>
.

Pour en savoir plus sur les valeurs, consultez Distribution et Exponentiel.

Pour calculer les centiles de temps d'attente de verrouillage à partir de la distribution, utilisez la fonction SPANNER_SYS.DISTRIBUTION_PERCENTILE(distribution, n FLOAT64).

LOCK_WAIT_DISTRIBUTION_JSON_STRING STRING

Représentation sous forme de chaîne compatible JSON de la colonne LOCK_WAIT_DISTRIBUTION. La chaîne JSON est un objet JSON ayant la même structure que le STRUCT de cette colonne. Comme pour la colonne LOCK_WAIT_DISTRIBUTION, les valeurs de cette colonne sont exprimées en microsecondes.

Cette colonne est compatible avec les bases de données utilisant le dialecte GoogleSQL et celles utilisant le dialecte PostgreSQL.

Modes de verrouillage

Les opérations Spanner acquièrent des verrous lorsqu'elles font partie d'une transaction en lecture-écriture. Les transactions en lecture seule n'acquièrent pas de verrous. Spanner utilise différents modes de verrouillage pour maximiser le nombre de transactions ayant accès à une cellule de données particulière à un moment donné. Les verrous présentent des caractéristiques différentes. Par exemple, certains verrous peuvent être partagés entre plusieurs transactions, tandis que d'autres ne le peuvent pas.

Un conflit de verrouillage peut se produire lorsque vous tentez d'acquérir l'un des modes de verrouillage suivants dans une transaction.

  • ReaderShared : verrou qui permet à d'autres lectures d'accéder aux données jusqu'à ce que votre transaction soit prête à être validée. Ce verrou partagé est acquis lorsqu'une transaction en lecture-écriture lit des données.

  • WriterShared : ce verrou est acquis lorsqu'une transaction en lecture-écriture tente de valider une écriture.

  • Verrou Exclusive : un verrou exclusif est acquis lorsqu'une transaction en lecture-écriture, qui a déjà acquis un verrou ReaderShared, tente d'écrire des données une fois la lecture terminée. Un verrou exclusif est une mise à niveau d'un verrou ReaderShared. Un verrou exclusif est un cas particulier de transaction contenant à la fois le verrou ReaderShared et le verrou WriterShared. Aucune autre transaction ne peut acquérir de verrou sur la même cellule.

  • Verrou WriterSharedTimestamp : type spécial de verrou WriterShared acquis lors de l'insertion de nouvelles lignes dans une table dont la clé primaire inclut un code temporel de commit. Ce type de verrou empêche les participants à la transaction de créer exactement la même ligne et, par conséquent, d'entrer en conflit les uns avec les autres. Spanner met à jour la clé de la ligne insérée pour qu'elle corresponde au code temporel de commit de la transaction qui a effectué l'insertion.

Pour plus d'informations sur les types de transactions et les types de verrous disponibles, consultez la page Transactions.

Conflits de mode de verrouillage

Le tableau suivant présente les conflits possibles entre les différents modes de verrouillage.

Modes de verrouillage ReaderShared WriterShared Exclusive WriterSharedTimestamp
ReaderShared Non Oui Oui Oui
WriterShared Oui Non Oui Non applicable
Exclusive Oui Oui Oui Non applicable
WriterSharedTimestamp Oui Non applicable Non applicable Oui

Les verrous WriterSharedTimestamp ne sont utilisés que lorsque vous insérez de nouvelles lignes dont la clé primaire contient un horodatage. Les verrous WriterShared et Exclusive sont utilisés lors de l'écriture dans des cellules existantes ou de l'insertion de nouvelles lignes sans horodatage. Par conséquent, WriterSharedTimestamp ne peut pas entrer en conflit avec d'autres types de verrous, et ces scénarios sont désignés comme Non applicables dans le tableau précédent.

La seule exception est ReaderShared, qui peut être appliqué aux lignes qui n'existent pas encore et peut donc entrer en conflit avec WriterSharedTimestamp. Par exemple, une analyse complète de la table verrouille l'intégralité de la table même pour les lignes qui n'ont pas été créées. Il est donc possible que ReaderShared entre en conflit avec WriterSharedTimestamp.

Qu'est-ce qu'une clé de début de plage de lignes ?

La colonne ROW_RANGE_START_KEY identifie la clé primaire composite, ou clé de départ d'une plage de lignes, qui présente des conflits de verrouillage. Le schéma suivant permet d'illustrer un exemple.

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);

Comme le montre le tableau suivant qui regroupe des de clés de ligne et des plages de clés de ligne, une plage est représentée par un signe plus (+) dans la clé. Dans ce cas, la clé représente la clé de départ d'une plage de clés dans laquelle un conflit de verrouillage est survenu.

ROW_RANGE_START_KEY Explication
singers(2) Table "Singers" à la clé SingerId=2
albums(2,1) Table "Albums" à la clé SingerId=2, AlbumId=1
songs(2,1,5) Table "Songs" à la clé SingerId=2, AlbumId=1, trackId=5
songs(2,1,5+) Plage de clés de la table "Songs" commençant par SingerId=2, AlbumId=1, trackId=5
albums(2,1+) Plage de clés de la table "Albums" commençant par SingerId=2, AlbumId=1
users(3, 2020-11-01 12:34:56.426426+00:00) Table "Users" à la clé KeyId=3, LastAccess=commit_timestamp

Statistiques globales

SPANNER_SYS contient également des tables permettant de stocker les données agrégées des statistiques de verrouillage capturées par Spanner sur une période spécifique :

  • SPANNER_SYS.LOCK_STATS_TOTAL_MINUTE : statistiques agrégées pour toutes les attentes de verrouillage par intervalles d'une minute.

  • SPANNER_SYS.LOCK_STATS_TOTAL_10MINUTE: statistiques globales pour tous les temps d'attente de verrouillage pendant des intervalles de 10 minutes.

  • SPANNER_SYS.LOCK_STATS_TOTAL_HOUR : statistiques globales pour tous les temps d'attente de verrouillage pendant des intervalles d'une heure.

Les tableaux de statistiques globales présentent les propriétés suivantes :

  • Chaque table contient des données pour des intervalles de temps qui ne se chevauchent pas, dont la durée est spécifiée par le nom de la table.

  • Les intervalles sont définis selon l'heure réelle. Les intervalles d'une minute se terminent toutes les minutes, les intervalles de 10 minutes s'achèvent toutes les 10 minutes à partir de l'heure juste, et les intervalles d'une heure prennent fin toutes les heures.

    Par exemple, à 11:59:30, les intervalles les plus récents disponibles pour les requêtes SQL sur les statistiques de verrouillage globales sont les suivants :

    • 1 minute : 11:58:00 – 11:58:59
    • 10 minutes : 11:40:00 – 11:49:59
    • 1 heure : 10:00:00 – 10:59:59
  • Chaque ligne contient les statistiques de toutes les attentes de verrouillage sur la base de données pendant l'intervalle spécifié, regroupées. Il n'y a par conséquent qu'une seule ligne par intervalle de temps.

  • Les statistiques capturées dans les tables SPANNER_SYS.LOCK_STATS_TOTAL_* incluent les attentes de verrouillage que Spanner n'a pas capturées dans les tables SPANNER_SYS.LOCK_STATS_TOP_*.

  • Certaines colonnes de ces tables sont exposées en tant que métriques dans Cloud Monitoring. Les métriques exposées sont les suivantes :

    • Temps d'attente pour le verrouillage

    Pour en savoir plus, consultez Métriques Spanner.

Schéma de la table

Nom de la colonne Type Description
INTERVAL_END TIMESTAMP Fin de l'intervalle de temps au cours duquel le conflit de verrouillage s'est produit.
TOTAL_LOCK_WAIT_SECONDS FLOAT64 Temps d'attente total pour les conflits de verrouillage enregistrés pour l'ensemble de la base de données, en secondes.
LOCK_WAIT_DISTRIBUTION ARRAY<STRUCT> Histogramme des temps d'attente de verrouillage individuels pour les conflits de verrouillage enregistrés pour l'ensemble de la base de données, en microsecondes et non en secondes. La structure est identique à celle de la colonne LOCK_WAIT_DISTRIBUTION dans les tables SPANNER_SYS.LOCK_STATS_TOP_*. Les bases de données de dialecte PostgreSQL ne sont pas compatibles avec cette colonne. Utilisez plutôt la colonne LOCK_WAIT_DISTRIBUTION_JSON_STRING.
LOCK_WAIT_DISTRIBUTION_JSON_STRING STRING Représentation sous forme de chaîne compatible JSON de la colonne LOCK_WAIT_DISTRIBUTION. Ses valeurs sont exprimées en microsecondes. Cette colonne est compatible avec les bases de données utilisant le dialecte GoogleSQL et celles utilisant le dialecte PostgreSQL.

Exemples de requêtes

Voici un exemple d'instruction SQL que vous pouvez utiliser pour récupérer des statistiques de verrouillage. Vous pouvez exécuter ces instructions SQL à l'aide des bibliothèques clientes, de gcloud spanner ou de la consoleGoogle Cloud .

Répertorier les statistiques de verrouillage pour l'intervalle d'une minute précédent

La requête suivante renvoie les informations d'attente de verrouillage pour chaque clé de ligne en conflit de verrouillage, en incluant la fraction du nombre total de conflits de verrouillage, au cours du dernier intervalle de temps d'une minute.

La fonction CAST() convertit le champ BYTES "row_range_start_key" en valeur de type 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;
Sortie de la requête
row_range_start_key total_lock_wait_seconds lock_wait_seconds frac_of_total sample_lock_requests
Songs(2,1,1) 2,37 1,76 0.7426 LOCK_MODE: ReaderShared

COLUMN: Singers.SingerInfo

LOCK_MODE: WriterShared

COLUMN: Singers.SingerInfo
Users(3, 2020-11-01 12:34:56.426426+00:00) 2,37 0.61 0.2573 LOCK_MODE: ReaderShared

COLUMN: users._exists1

LOCK_MODE: WriterShared

COLUMN: users._exists1

1 _exists est un champ interne utilisé pour vérifier si une ligne existe ou non.

Conservation des données

Au minimum, Spanner conserve les données de chaque table pendant les périodes suivantes :

  • SPANNER_SYS.LOCK_STATS_TOP_MINUTE et SPANNER_SYS.LOCK_STATS_TOTAL_MINUTE : intervalles couvrant les six heures précédentes

  • SPANNER_SYS.LOCK_STATS_TOP_10MINUTE et SPANNER_SYS.LOCK_STATS_TOTAL_10MINUTE : intervalles couvrant les quatre derniers jours.

  • SPANNER_SYS.LOCK_STATS_TOP_HOUR et SPANNER_SYS.LOCK_STATS_TOTAL_HOUR : intervalles couvrant les 30 jours précédents.

Résoudre les conflits de verrouillage dans votre base de données à l'aide des statistiques de verrouillage

Vous pouvez utiliser SQL ou le tableau de bord Insights sur les verrouillages pour afficher les conflits de verrouillage dans votre base de données.

Les rubriques suivantes expliquent comment examiner ces conflits de verrouillage à l'aide du code SQL.

Sélectionnez une période à examiner

Vous examinez les métriques de latence de votre base de données Spanner et découvrez une période pendant laquelle votre application subit une latence et une utilisation du processeur élevées. Par exemple, le problème a commencé à se produire vers 22h50 le 12 novembre 2020.

Déterminez si la latence de validation des transactions a augmenté en même temps que le temps d'attente de verrouillage au cours de la période sélectionnée.

Les verrous sont acquis par des transactions. Ainsi, en cas de conflits de verrouillage entraînant de longs temps d'attente, nous devrions constater une augmentation de la latence de commit des transactions ainsi qu'une augmentation du temps d'attente de verrouillage.

Après avoir sélectionné une période pour démarrer notre enquête, nous regroupons les statistiques de transaction TXN_STATS_TOTAL_10MINUTE et les statistiques de verrouillage LOCK_STATS_TOTAL_10MINUTE autour de cette période pour nous aider à mieux comprendre si l'augmentation de la latence de commit moyenne est due à l'augmentation du temps d'attente.

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;

Prenons les données suivantes comme exemple des résultats que nous obtenons de notre requête.

interval_end avg_commit_latency_seconds total_lock_wait_seconds
2020-11-12 21:40:00-07:00 0.002 0.090
2020-11-12 21:50:00-07:00 0.003 0.110
2020-11-12 22:00:00-07:00 0.002 0.100
2020-11-12 22:10:00-07:00 0.002 0.080
2020-11-12 22:20:00-07:00 0.030 0.240
2020-11-12 22:30:00-07:00 0.034 0.220
2020-11-12 22:40:00-07:00 0.034 0.218
2020-11-12 22:50:00-07:00 3.741 780.193
2020-11-12 23:00:00-07:00 0.042 0.240
2020-11-12 23:10:00-07:00 0.038 0.129
2020-11-12 23:20:00-07:00 0.021 0.128
2020-11-12 23:30:00-07:00 0.038 0.231

Les résultats précédents montrent une augmentation spectaculaire de avg_commit_latency_seconds et de total_lock_wait_seconds au cours de la même période, de 2020-11-12 22:40:00 à 2020-11-12 22:50:00, puis une baisse. Notez que avg_commit_latency_seconds correspond au temps moyen passé uniquement sur l'étape de commit. En revanche, total_lock_wait_seconds correspond au temps de verrouillage cumulé pour la période. Le temps semble donc beaucoup plus long que le temps de commit de la transaction.

Maintenant que nous avons confirmé que le temps d'attente du verrouillage est étroitement lié à l'augmentation de la latence d'écriture, nous allons examiner à l'étape suivante quelles lignes et colonnes sont à l'origine de ce long temps d'attente.

Découvrez quelles clés de ligne et colonnes ont enregistré des temps d'attente de verrouillage longs au cours de la période sélectionnée.

Pour déterminer quelles clés de ligne et colonnes ont souffert d'un temps d'attente de verrouillage élevé au cours de la période examinée, nous interrogeons LOCK_STAT_TOP_10MINUTE qui répertorie les clés de ligne et les colonnes contribuant le plus au temps d'attente de verrouillage.

La fonction CAST() de la requête suivante convertit le champ BYTES "row_range_start_key" en valeur de type 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;
row_range_start_key total_lock_wait_seconds lock_wait_seconds frac_of_total sample_lock_requests
Singers(32) 780.193 780.193 1 LOCK_MODE: WriterShared

COLUMN: Singers.SingerInfo

LOCK_MODE: ReaderShared

COLUMN: Singers.SingerInfo

À partir de cette table de résultats, nous pouvons constater le conflit dans la table Singers à la clé SingerId=32. Singers.SingerInfo est la colonne où le conflit de verrouillage s'est produit entre ReaderShared et WriterShared.

Il s'agit d'un type de conflit courant lorsqu'une transaction tente de lire une cellule et que l'autre transaction tente d'écrire dans la même cellule. Nous connaissons maintenant la cellule de données exacte pour laquelle les transactions se disputent le verrou. À l'étape suivante, nous allons identifier les transactions qui se disputent les verrous.

Identifier les transactions qui accèdent aux colonnes impliquées dans le conflit de verrouillage

Pour identifier les transactions qui subissent une latence de commit importante dans un intervalle de temps spécifique en raison de conflits de verrouillage, vous devez interroger les colonnes suivantes de la table SPANNER_SYS.TXN_STATS_TOTAL_10MINUTE :

  • fprint
  • read_columns
  • write_constructive_columns
  • avg_commit_latency_seconds

Vous devez filtrer les colonnes verrouillées identifiées à partir de la table SPANNER_SYS.LOCK_STATS_TOP_10MINUTE :

  • Transactions qui lisent une colonne ayant entraîné un conflit de verrouillage lors de la tentative d'acquisition du verrou ReaderShared.

  • Transactions qui écrivent dans une colonne ayant rencontré un conflit de verrouillage lors de la tentative d'acquisition d'un verrou 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;

Le résultat de la requête est trié par la colonne avg_commit_latency_seconds afin que vous voyiez d'abord la transaction qui subit la latence de commit la plus élevée.

fprint read_columns write_constructive_columns avg_commit_latency_seconds
1866043996151916800


['Singers.SingerInfo',
'Singers.FirstName',
'Singers.LastName',
'Singers._exists']
['Singers.SingerInfo'] 4.89
4168578515815911936 [] ['Singers.SingerInfo'] 3.65

Les résultats de la requête montrent que deux transactions ont tenté d'accéder à la colonne Singers.SingerInfo, qui est la colonne ayant rencontré des conflits de verrouillage au cours de la période. Une fois que vous avez identifié les transactions à l'origine des conflits de verrouillage, vous pouvez les analyser à l'aide de leur empreinte, fprint, afin d'identifier les problèmes potentiels qui ont contribué au conflit de verrouillage.

Après avoir examiné la transaction avec fprint=1866043996151916800, vous pouvez utiliser les colonnes read_columns et write_constructive_columns pour identifier la partie du code de votre application qui a déclenché la transaction. Vous pouvez ensuite afficher le LMD sous-jacent qui ne filtre pas sur la clé primaire, SingerId. Cela a entraîné une analyse complète de la table et l'a verrouillée jusqu'à ce que la transaction soit validée.

Pour résoudre le conflit de verrouillage, vous pouvez procéder comme suit :

  1. Utilisez une transaction en lecture seule pour identifier les valeurs SingerId requises.
  2. Utilisez une transaction en lecture-écriture distincte pour mettre à jour les lignes des valeurs SingerId requises.

Appliquer les bonnes pratiques pour réduire les conflits de verrouillage

Dans notre exemple de scénario, nous avons utilisé les statistiques de verrouillage et de transaction pour identifier le problème : une transaction n'utilisait pas la clé primaire de notre table lors des mises à jour. Nous avons proposé des idées pour améliorer la transaction selon que nous connaissions ou non les clés des lignes que nous voulions mettre à jour au préalable.

Lorsque vous examinez les problèmes potentiels liés à votre solution, ou même lors de la conception de votre solution, tenez compte de ces bonnes pratiques afin de réduire le nombre de conflits de verrouillage dans votre base de données.

  • Évitez les opérations de lecture de grande taille dans les transactions en lecture-écriture.

  • Utilisez des transactions en lecture seule chaque fois que possible, car elles n'acquièrent pas de verrous.

  • Évitez les analyses de table complète dans une transaction en lecture-écriture. Cela inclut l'écriture d'une instruction LMD conditionnelle sur la clé primaire ou l'attribution d'une plage de clés spécifique lors de l'utilisation de l'API de lecture.

  • Maintenez la période de verrouillage courte en validant la modification dès que possible après la lecture des données dans une transaction en lecture-écriture. Une transaction en lecture-écriture garantit que les données restent inchangées après leur lecture jusqu'à ce que vous validiez la modification. Pour ce faire, la transaction nécessite de verrouiller les cellules de données pendant la lecture et pendant la validation. Par conséquent, si vous pouvez maintenir une période de verrouillage courte, les transactions sont moins susceptibles de présenter des conflits de verrouillage.

  • Privilégiez les petites transactions par rapport aux transactions volumineuses ou envisagez l'utilisation de LMD partitionné pour les transactions LMD de longue durée. Une transaction de longue durée acquiert un verrou pendant une longue période. Envisagez donc de diviser une transaction qui touche des milliers de lignes en plusieurs transactions plus petites qui mettent à jour des centaines de lignes chaque fois que cela est possible.

  • Si vous n'avez pas besoin de la garantie fournie par une transaction en lecture-écriture, évitez de lire les données dans la transaction de lecture-écriture avant de valider la modification. Pour ce faire, vous pouvez lire les données dans une transaction en lecture seule distincte. La plupart des conflits de verrouillage résultent de la garantie forte selon laquelle les données restent inchangées entre la lecture et le commit. Par conséquent, si la transaction en lecture-écriture ne lit aucune donnée, elle n'a pas besoin de verrouiller les cellules pendant une longue période.

  • Spécifiez uniquement l'ensemble minimal de colonnes requis dans une transaction en lecture-écriture. Comme les verrous Spanner sont appliqués par cellule de données, lorsqu'une transaction en lecture-écriture lit un nombre excessif de colonnes, elle acquiert un verrou ReaderShared sur ces cellules. Cela peut entraîner des conflits de verrouillage lorsque d'autres transactions acquièrent un verrou WriterShared sur les écritures dans les colonnes excessives. Par exemple, envisagez de spécifier un ensemble de colonnes au lieu de * lors de la lecture.

  • Réduisez au maximum les appels d'API dans une transaction en lecture-écriture. La latence des appels d'API peut entraîner des conflits de verrouillage dans Spanner, car les appels d'API sont soumis à des retards réseau ainsi qu'à des retards côté service. Dans la mesure du possible, nous vous recommandons d'effectuer des appels d'API en dehors des transactions en lecture-écriture. Si vous devez exécuter des appels d'API dans une transaction en lecture/écriture, veillez à surveiller la latence de vos appels d'API afin de minimiser l'impact sur la période d'acquisition du verrou.

  • Suivez les bonnes pratiques de conception des schémas.

  • Si la possibilité d'un décalage d'écriture n'est pas un problème pour votre application, envisagez d'utiliser l'isolation de lecture reproductible avec sa simultanéité optimiste par défaut pour éviter les conflits de lecture-écriture. Pour en savoir plus, consultez Isolation de lecture reproductible.

  • Pour les charges de travail présentant un faible conflit de lecture/écriture, envisagez d'utiliser l'isolation sérialisable avec un contrôle de simultanéité optimiste pour éviter d'acquérir des verrous dans les transactions jusqu'au moment de la validation, tout en vous assurant que toutes les lectures de la transaction sont valides au moment de la validation. Pour en savoir plus sur l'adéquation de cette option, consultez Contrôle de simultanéité optimiste.

Étapes suivantes