Utiliser SELECT FOR UPDATE dans l'isolation sérialisable

Cette page explique comment utiliser la clause FOR UPDATE dans l'isolation sérialisable.

Le mécanisme de verrouillage de la clause FOR UPDATE est différent pour l'isolation de lecture répétable et l'isolation sérialisable. Lorsque vous utilisez l'isolation sérialisable et la requête SELECT pour analyser une table, l'ajout d'une clause FOR UPDATE permet d'activer des verrous exclusifs au niveau de la granularité des lignes et des colonnes, également appelé niveau des cellules. Le verrou reste en place pendant toute la durée de la transaction en lecture-écriture. Pendant ce temps, la clause FOR UPDATE empêche les autres transactions de modifier les cellules verrouillées jusqu'à ce que la transaction en cours soit terminée.

Pour savoir comment utiliser la clause FOR UPDATE, consultez les guides de référence GoogleSQL et PostgreSQL pour FOR UPDATE.

Pourquoi utiliser la clause FOR UPDATE ?

Dans les bases de données dont les niveaux d'isolation sont moins stricts, la clause FOR UPDATE peut être nécessaire pour s'assurer qu'une transaction simultanée ne met pas à jour les données entre la lecture des données et la validation de la transaction. Étant donné que Spanner applique la sérialisabilité par défaut, la transaction n'est validée que si les données auxquelles elle accède ne sont pas obsolètes au moment de la validation. La clause FOR UPDATE n'est donc pas nécessaire pour garantir l'exactitude des transactions dans Spanner.

Toutefois, dans les cas d'utilisation avec une contention d'écriture élevée, par exemple lorsque plusieurs transactions lisent et écrivent simultanément dans les mêmes données, les transactions simultanées peuvent entraîner une augmentation des abandons. En effet, lorsque plusieurs transactions simultanées acquièrent des verrous partagés, puis tentent de passer à des verrous exclusifs, les transactions provoquent un blocage. L'interblocage bloque définitivement les transactions, car chacune attend que l'autre libère la ressource dont elle a besoin. Pour progresser, Spanner abandonne toutes les transactions sauf une afin de résoudre le blocage. Pour en savoir plus, consultez la section Verrouillage.

Une transaction qui utilise la clause FOR UPDATE acquiert le verrou exclusif de manière proactive et continue à s'exécuter, tandis que d'autres transactions attendent leur verrou. Bien que Spanner puisse toujours limiter le débit, car les transactions en conflit ne peuvent être effectuées qu'une seule à la fois, le fait que Spanner ne progresse que sur une seule transaction permet de gagner du temps qui serait autrement utilisé pour annuler et réessayer les transactions.

Par conséquent, si la réduction du nombre de transactions abandonnées dans un scénario de requête d'écriture simultanée est importante, vous pouvez utiliser la clause FOR UPDATE pour réduire le nombre total d'abandons et améliorer l'efficacité de l'exécution de la charge de travail.

Comparaison avec l'indice LOCK_SCANNED_RANGES

La clause FOR UPDATE a une fonction semblable à l'indication LOCK_SCANNED_RANGES=exclusive.

Il existe deux différences principales :

  • Si vous utilisez l'optimisation LOCK_SCANNED_RANGES, la transaction acquiert des verrous exclusifs sur les plages analysées pour l'ensemble de l'instruction. Vous ne pouvez pas acquérir de verrous exclusifs sur une sous-requête. L'utilisation de l'indice de verrouillage peut entraîner l'acquisition de plus de verrous que nécessaire et contribuer à la contention de verrouillage dans la charge de travail. L'exemple suivant montre comment utiliser un indice de verrouillage :

    @{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;

    En revanche, vous pouvez utiliser la clause FOR UPDATE dans une sous-requête, comme illustré dans l'exemple suivant :

    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;

  • Vous pouvez utiliser l'indication LOCK_SCANNED_RANGES dans les instructions LMD, alors que vous ne pouvez utiliser la clause FOR UPDATE que dans les instructions SELECT.

Sémantique de verrouillage

Pour réduire les requêtes d'écriture simultanées et le coût des transactions abandonnées en raison d'un blocage, Spanner verrouille les données au niveau des cellules si possible. Le niveau de cellule est le niveau de données le plus précis dans un tableau. Il s'agit d'un point de données à l'intersection d'une ligne et d'une colonne. Lorsque vous utilisez la clause FOR UPDATE, Spanner verrouille les cellules spécifiques analysées par la requête SELECT.

Dans l'exemple suivant, la cellule MarketingBudget de la ligne SingerId = 1 et AlbumId = 1 est verrouillée de manière exclusive dans la table Albums, ce qui empêche les transactions simultanées de modifier cette cellule jusqu'à ce que cette transaction soit validée ou annulée. Toutefois, les transactions simultanées peuvent toujours mettre à jour la cellule AlbumTitle de cette ligne.

SELECT MarketingBudget
FROM Albums
WHERE SingerId = 1 and AlbumId = 1
FOR UPDATE;

Les transactions simultanées peuvent être bloquées lors de la lecture de données verrouillées.

Lorsqu'une transaction a acquis des verrous exclusifs sur une plage analysée, les transactions simultanées peuvent bloquer la lecture de ces données. Spanner applique la sérialisabilité. Les données ne peuvent donc être lues que si elles sont garanties comme n'ayant pas été modifiées par une autre transaction pendant la durée de vie de la transaction. Les transactions simultanées qui tentent de lire des données déjà verrouillées peuvent devoir attendre que la transaction qui détient les verrous soit validée, annulée ou expire.

Dans l'exemple suivant, Transaction 1 verrouille les cellules MarketingBudget pour 1 <= AlbumId < 5.

-- Transaction 1
SELECT MarketingBudget
FROM Albums
WHERE SingerId = 1 and AlbumId >= 1 and AlbumId < 5
FOR UPDATE;

Transaction 2, qui tente de lire MarketingBudget pour AlbumId = 1, est bloqué jusqu'à ce que Transaction 1 soit validé ou annulé.

-- Transaction 2
SELECT MarketingBudget
FROM Albums
WHERE SingerId = 1 and AlbumId = 1;

-- Blocked by Transaction 1

De même, une transaction qui tente de verrouiller une plage analysée avec FOR UPDATE est bloquée par une transaction simultanée qui verrouille une plage analysée qui se chevauche.

Transaction 3 dans l'exemple suivant est également bloqué, car Transaction 1 a verrouillé les cellules MarketingBudget pour 3 <= AlbumId < 5, qui correspond à la plage analysée qui se chevauche avec Transaction 3.

-- Transaction 3
SELECT MarketingBudget
FROM Albums
WHERE SingerId = 1 and AlbumId >= 3 and AlbumId < 10
FOR UPDATE;

-- Blocked by Transaction 1

Lire un index

Une lecture simultanée peut ne pas être bloquée si la requête qui a verrouillé la plage analysée verrouille les lignes de la table de base, mais que la transaction simultanée lit à partir d'un index.

Le Transaction 1 suivant verrouille les cellules SingerId et SingerInfo pour SingerId = 1.

-- Transaction 1
SELECT SingerId, SingerInfo
FROM Singers
WHERE SingerId = 1
FOR UPDATE;

La lecture seule Transaction 2 n'est pas bloquée par les verrous acquis dans Transaction 1, car elle interroge une table d'index.

-- Transaction 2
SELECT SingerId FROM Singers;

Les transactions simultanées ne bloquent pas les opérations LMD sur les données déjà verrouillées.

Lorsqu'une transaction a acquis des verrous sur une plage de cellules avec un indice de verrou exclusif, les transactions simultanées qui tentent d'effectuer une écriture sans lire d'abord les données dans les cellules verrouillées peuvent se poursuivre. La transaction est bloquée sur le commit jusqu'à ce que la transaction contenant les verrous soit validée ou annulée.

Le Transaction 1 suivant verrouille les cellules MarketingBudget pour 1 <= AlbumId < 5.

-- Transaction 1
SELECT MarketingBudget
FROM Albums
WHERE SingerId = 1 and AlbumId >= 1 and AlbumId < 5
FOR UPDATE;

Si Transaction 2 tente de mettre à jour la table Albums, il en est empêché jusqu'à ce que Transaction 1 valide ou annule la transaction.

-- Transaction 2
UPDATE Albums
SET MarketingBudget = 200000
WHERE SingerId = 1 and AlbumId = 1;

> Query OK, 1 rows affected

COMMIT;

-- Blocked by Transaction 1

Les lignes et les écarts existants sont verrouillés lorsqu'une plage analysée est verrouillée.

Lorsqu'une transaction a acquis des verrous exclusifs sur une plage analysée, les transactions simultanées ne peuvent pas insérer de données dans les espaces vides de cette plage.

Le Transaction 1 suivant verrouille les cellules MarketingBudget pour 1 <= AlbumId < 10.

-- Transaction 1
SELECT MarketingBudget
FROM Albums
WHERE SingerId = 1 and AlbumId >= 1 and AlbumId < 10
FOR UPDATE;

Si Transaction 2 tente d'insérer une ligne pour AlbumId = 9 qui n'existe pas encore, il en est empêché jusqu'à ce que Transaction 1 valide ou annule la transaction.

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

Mises en garde concernant l'acquisition de serrures

La sémantique de verrouillage décrite fournit des conseils généraux, mais ne garantit pas exactement comment les verrous peuvent être acquis lorsque Spanner exécute une transaction qui utilise la clause FOR UPDATE. Les mécanismes d'optimisation des requêtes de Spanner peuvent également avoir une incidence sur les verrous acquis. Cette clause empêche d'autres transactions de modifier les cellules verrouillées jusqu'à ce que la transaction en cours soit terminée.

Syntaxe des requêtes

Cette section fournit des conseils sur la syntaxe des requêtes lorsque vous utilisez la clause FOR UPDATE.

L'utilisation la plus courante se fait dans une instruction SELECT de premier niveau. Exemple :

SELECT SingerId, SingerInfo
FROM Singers WHERE SingerID = 5
FOR UPDATE;

Cet exemple montre comment utiliser la clause FOR UPDATE dans une instruction SELECT pour verrouiller exclusivement les cellules SingerId et SingerInfo de WHERE SingerID = 5.

Utilisation dans les instructions WITH

La clause FOR UPDATE n'acquiert pas de verrous pour l'instruction WITH lorsque vous spécifiez FOR UPDATE dans la requête de niveau externe de l'instruction WITH.

Dans la requête suivante, aucun verrou n'est acquis par la table Singers, car l'intention de verrouillage n'est pas propagée à la requête d'expression de table commune (CTE).

WITH s AS (SELECT SingerId, SingerInfo FROM Singers WHERE SingerID > 5)
SELECT * FROM s
FOR UPDATE;

Si la clause FOR UPDATE est spécifiée dans la requête CTE, la plage analysée de la requête CTE acquiert les verrous.

Dans l'exemple suivant, les cellules SingerId et SingerInfo des lignes où SingerId > 5 sont verrouillées.

WITH s AS
  (SELECT SingerId, SingerInfo FROM Singers WHERE SingerId > 5 FOR UPDATE)
SELECT * FROM s;

Utilisation dans les sous-requêtes

Vous pouvez utiliser la clause FOR UPDATE dans une requête de niveau externe comportant une ou plusieurs sous-requêtes. Les verrous sont acquis par la requête de niveau supérieur et dans les sous-requêtes, sauf dans les sous-requêtes d'expression.

La requête suivante verrouille les cellules SingerId et SingerInfo pour les lignes où SingerId > 5..

(SELECT SingerId, SingerInfo FROM Singers WHERE SingerId > 5) AS t
FOR UPDATE;

La requête suivante ne verrouille aucune cellule de la table Albums, car elle se trouve dans une sous-requête d'expression. Les cellules SingerId et SingerInfo des lignes renvoyées par la sous-requête d'expression sont verrouillées.

SELECT SingerId, SingerInfo
FROM Singers
WHERE SingerId = (SELECT SingerId FROM Albums WHERE MarketingBudget > 100000)
FOR UPDATE;

Utiliser pour interroger les vues

Vous pouvez utiliser la clause FOR UPDATE pour interroger une vue, comme illustré dans l'exemple suivant :

CREATE VIEW SingerBio AS SELECT SingerId, FullName, SingerInfo FROM Singers;

SELECT * FROM SingerBio WHERE SingerId = 5 FOR UPDATE;

Vous ne pouvez pas utiliser la clause FOR UPDATE lors de la définition d'une vue.

Cas d'utilisation non compatibles

Les cas d'utilisation suivants de FOR UPDATE ne sont pas pris en charge :

  • En tant que mécanisme d'exclusion mutuelle pour l'exécution de code en dehors de Spanner : n'utilisez pas le verrouillage dans Spanner pour garantir un accès exclusif à une ressource en dehors de Spanner. Les transactions peuvent être annulées par Spanner, par exemple si une transaction est relancée, que ce soit explicitement par un code d'application ou implicitement par un code client, tel que le pilote JDBC Spanner. La seule garantie est le maintien des verrous pendant la tentative réellement effectuée.
  • En combinaison avec l'indice LOCK_SCANNED_RANGES : vous ne pouvez pas utiliser à la fois la clause FOR UPDATE et l'indice LOCK_SCANNED_RANGES dans la même requête. Dans le cas contraire, Spanner renvoie une erreur.
  • Dans les requêtes de recherche en texte intégral : vous ne pouvez pas utiliser la clause FOR UPDATE dans les requêtes utilisant des index de recherche en texte intégral.
  • Dans les transactions en lecture seule : la clause FOR UPDATE n'est valide que dans les requêtes exécutées dans des transactions en lecture-écriture.
  • Dans les instructions LDD : vous ne pouvez pas utiliser la clause FOR UPDATE dans les requêtes des instructions LDD, qui sont stockées pour être exécutées ultérieurement. Par exemple, vous ne pouvez pas utiliser la clause FOR UPDATE lors de la définition d'une vue. Si le verrouillage est nécessaire, la clause FOR UPDATE peut être spécifiée lors de l'interrogation de la vue.

Étape suivante