Utiliser SELECT FOR UPDATE

Cette page explique comment utiliser la clause FOR UPDATE dans Spanner.

Lorsque vous utilisez la requête SELECT pour analyser une table, ajoutez une clause FOR UPDATE pour activer les verrous exclusifs au niveau de la granularité des lignes et des colonnes, également appelé niveau de la cellule. Le verrouillage reste en place pendant toute la durée de vie 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 se termine. Pour en savoir plus, consultez les guides de référence FOR UPDATE pour GoogleSQL et PostgreSQL.

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 toujours la sérialisation, il est garanti que la transaction ne s'effectue correctement que si les données auxquelles elle accède ne sont pas obsolètes au moment de la validation. Par conséquent, la clause FOR UPDATE n'est pas nécessaire pour garantir l'exactitude des transactions dans Spanner.

Toutefois, dans les cas d'utilisation où la contention d'écriture est é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, elles provoquent un blocage. Spanner abandonne ensuite toutes les transactions sauf une. Pour en savoir plus, consultez la section Verrouillage.

Une transaction qui utilise la clause FOR UPDATE acquiert le verrouillage exclusif et continue à s'exécuter, tandis que d'autres transactions attendent leur tour pour le verrouillage. Bien que Spanner puisse toujours limiter le débit, car les transactions en conflit ne peuvent être effectuées qu'une seule à la fois, comme Spanner ne progresse que sur une seule transaction, cela représente un gain de temps qui sinon serait utilisé pour annuler et réessayer les transactions.

Par conséquent, si vous souhaitez réduire le nombre de transactions abandonnées dans un scénario de requête d'écriture simultanée, vous pouvez utiliser la clause FOR UPDATE pour réduire le nombre global d'abandons et augmenter 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'indice LOCK_SCANNED_RANGES=exclusive.

Il existe deux différences principales :

  • Si vous utilisez l'indice 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'indice LOCK_SCANNED_RANGES dans les instructions DML, mais 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 interblocage, Spanner verrouille les données au niveau de la cellule, si possible. Lorsque vous utilisez la clause FOR UPDATE, Spanner verrouille des cellules spécifiques qui sont analysées par la requête SELECT.

Dans l'exemple suivant, la cellule MarketingBudget des lignes 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 bloquer la lecture des 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érialisation afin que les données ne puissent être lues que si elles ne sont pas 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 ou annulée.

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 le 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 est la plage d'analyse 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;

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

-- Transaction 2
SELECT SingerId FROM Singers;

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

Lorsqu'une transaction a acquis des verrous sur une plage de cellules avec un indice de verrouillage exclusif, les transactions simultanées qui tentent d'effectuer une écriture sans lire d'abord les données sur les cellules verrouillées peuvent continuer. La transaction se bloque sur le commit jusqu'à ce que la transaction qui détient 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 est bloqué 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 explorée, les transactions simultanées ne peuvent pas insérer de données dans les lacunes 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 est bloqué jusqu'à ce que Transaction 1 effectue un commit ou un rollback.

-- 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 des verrous 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. La clause empêche les autres transactions de modifier les cellules verrouillées jusqu'à la fin de la transaction en cours.

Sémantique des requêtes

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

Utiliser 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 supérieur de l'instruction WITH.

Dans la requête suivante, aucune serrure n'est acquise par la table Singers, car l'intention de verrouillage n'est pas propagée à la requête d'expression de table courante (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 de CTE, la plage analysée de la requête de CTE acquiert les verrous.

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

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

Utilisation dans des sous-requêtes

Vous pouvez utiliser la clause FOR UPDATE dans une requête de niveau externe contenant 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 lorsque vous définissez une vue.

Cas d'utilisation non compatibles

Les cas d'utilisation FOR UPDATE suivants 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 de 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 association 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, sinon 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 DDL:vous ne pouvez pas utiliser la clause FOR UPDATE dans les requêtes des instructions DDL, qui sont stockées pour être exécutées ultérieurement. Par exemple, vous ne pouvez pas utiliser la clause FOR UPDATE lorsque vous définissez 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