Utilizzare SELECT FOR UPDATE

Questa pagina descrive come utilizzare la clausola FOR UPDATE in Spanner.

Quando utilizzi la query SELECT per eseguire la scansione di una tabella, aggiungi una clausola FOR UPDATE per attivare i blocchi esclusivi a livello di granularità riga e colonna, noto anche come livello di cella. Il blocco rimane in vigore per tutta la durata della transazione di lettura/scrittura. Durante questo periodo, la clausola FOR UPDATE impedisce ad altre operazioni di modificare le celle bloccate fino al completamento dell'operazione in corso. Per saperne di più, consulta le guide di riferimento GoogleSQL e PostgreSQL FOR UPDATE.

Perché utilizzare la clausola FOR UPDATE

Nei database con livelli di isolamento meno rigidi, la clausola FOR UPDATE potrebbe essere necessaria per assicurarsi che una transazione concorrente non aggiorni i dati tra la lettura dei dati e l'esecuzione del commit della transazione. Poiché Spanner impone sempre la serializzabilità, è garantito che la transazione venga eseguita correttamente solo se i dati a cui si accede all'interno della transazione non sono obsoleti al momento del commit. Pertanto, la clausola FOR UPDATE non è necessaria per garantire la correttezza delle transazioni in Spanner.

Tuttavia, nei casi d'uso con una contesa di scrittura elevata, ad esempio quando più transazioni leggono e scrivono contemporaneamente negli stessi dati, le transazioni simultanee potrebbero causare un aumento degli aborti. Questo accade perché quando più transazioni simultanee acquisiscono blocchi condivisi e poi tentano di eseguire l'upgrade a blocchi esclusivi, le transazioni causano un deadlock. Spanner poi interrompe tutte le transazioni tranne una. Per ulteriori informazioni, consulta la sezione Blocco.

Una transazione che utilizza la clausola FOR UPDATE acquisisce il blocco esclusivo e procede con l'esecuzione, mentre le altre transazioni aspettano il loro turno per il blocco. Sebbene Spanner possa comunque limitare la produttività perché le transazioni in conflitto possono essere eseguite una alla volta, poiché Spanner esegue l'avanzamento di una sola transazione, consente di risparmiare il tempo che altrimenti verrebbe impiegato per interrompere e riprovare le transazioni.

Pertanto, se è importante ridurre il numero di transazioni interrotte in uno scenario di richiesta di scrittura simultanea, puoi utilizzare la clausola FOR UPDATE per ridurre il numero complessivo di interruzioni e aumentare l'efficienza di esecuzione del carico di lavoro.

Confronto con l'indicazione LOCK_SCANNED_RANGES

La clausola FOR UPDATE ha una funzione simile a quella dell'opzione LOCK_SCANNED_RANGES=exclusive.

Esistono due differenze principali:

  • Se utilizzi l'istruzione LOCK_SCANNED_RANGES, la transazione acquisisce i blocchi esclusivi sugli intervalli sottoposti a scansione per l'intero statement. Non puoi acquisire bloccati esclusivi su una sottoquery. L'utilizzo dell'indicazione di blocco potrebbe comportare l'acquisizione di più blocchi del necessario e contribuire alla contesa dei blocchi nel carico di lavoro. L'esempio seguente mostra come utilizzare un suggerimento di blocco:

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

    D'altra parte, puoi utilizzare la clausola FOR UPDATE in una sottoquery, come mostrato nell'esempio seguente:

    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;

  • Puoi utilizzare l'opzione LOCK_SCANNED_RANGES nelle istruzioni DML, mentre puoi utilizzare la clausola FOR UPDATE solo nelle istruzioni SELECT.

Semantica dei blocchi

Per ridurre le richieste di scrittura simultanee e il costo delle transazioni interrotte come risultato di un deadlock, Spanner blocca i dati a livello di cella, se possibile. Quando utilizzi la clausola FOR UPDATE, Spanner blocca le celle specifiche analizzate dalla query SELECT.

Nell'esempio seguente, la cella MarketingBudget nelle righe SingerId = 1 e AlbumId = 1 è bloccata in modo esclusivo nella tabella Albums, impedendo alle transazioni concorrenti di modificarla fino a quando questa transazione non viene committata o annullata. Tuttavia, le transazioni concorrenti possono comunque aggiornare la cellaAlbumTitle in quella riga.

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

Le transazioni concorrenti potrebbero bloccarsi durante la lettura dei dati bloccati

Quando una transazione ha acquisito i blocchi esclusivi su un intervallo sottoposto a scansione, le transazioni concorrenziali potrebbero bloccare la lettura di questi dati. Spanner applica la serializzabilità, pertanto i dati possono essere letti solo se è garantito che non sono stati modificati da un'altra transazione durante il ciclo di vita della transazione. Le transazioni concorrenti che tentano di leggere dati già bloccati potrebbero dover attendere che la transazione che detiene i blocchi venga eseguita o annullata.

Nell'esempio seguente, Transaction 1 blocca le celle MarketingBudget per 1 <= AlbumId < 5.

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

Transaction 2, che sta tentando di leggere il MarketingBudget per AlbumId = 1, è bloccato finché Transaction 1 non viene eseguito il commit o non viene eseguito il rollback.

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

-- Blocked by Transaction 1

Analogamente, una transazione che tenta di bloccare un intervallo sottoposto a scansione con FOR UPDATE viene bloccata da una transazione concorrente che blocca un intervallo sottoposto a scansione sovrapposto.

Anche Transaction 3 nell'esempio seguente è bloccato perché Transaction 1 ha bloccato le celle MarketingBudget per 3 <= AlbumId < 5, che è l'intervallo di scansione che si sovrappone a Transaction 3.

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

-- Blocked by Transaction 1

Leggere un indice

Una lettura simultanea potrebbe non essere bloccata se la query che ha bloccato l'intervallo sottoposto a scansione blocca le righe nella tabella di base, ma la transazione simultanea legge da un indice.

Il seguente Transaction 1 blocca le celle SingerId e SingerInfo per SingerId = 1.

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

La transazione di sola lettura Transaction 2 non è bloccata dalle acquisizioni di blocco in Transaction 1, perché esegue query su una tabella di indici.

-- Transaction 2
SELECT SingerId FROM Singers;

Le transazioni concorrenti non bloccano le operazioni DML sui dati già bloccati

Quando una transazione ha acquisito i blocchi su un intervallo di celle con un indicazione di blocco esclusivo, le transazioni concorrenti che tentano di eseguire una scrittura senza leggere prima i dati nelle celle bloccate possono procedere. La transazione si blocca sul commit finché la transazione che detiene i blocchi non esegue il commit o il rollback.

Il seguente Transaction 1 blocca le celle MarketingBudget per 1 <= AlbumId < 5.

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

Se Transaction 2 tenta di aggiornare la tabella Albums, non può farlo finché Transaction 1 non esegue il commit o il rollback.

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

> Query OK, 1 rows affected

COMMIT;

-- Blocked by Transaction 1

Le righe e gli spazi esistenti vengono bloccati quando un intervallo scansionato è bloccato

Quando una transazione ha acquisito i blocchi esclusivi su un intervallo sottoposto a scansione, le transazioni concorrenti non possono inserire dati nelle lacune all'interno di quell'intervallo.

Il seguente Transaction 1 blocca le celle MarketingBudget per 1 <= AlbumId < 10.

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

Se Transaction 2 tenta di inserire una riga per AlbumId = 9 che non esiste ancora, l'operazione viene bloccata fino a quando Transaction 1 non esegue il commit o il 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

Limitazioni dell'acquisizione di serrature

La semantica dei blocchi descritta fornisce indicazioni generali, ma non garantisce esattamente come potrebbero essere acquisiti i blocchi quando Spanner esegue una transazione che utilizza la clausola FOR UPDATE. I meccanismi di ottimizzazione delle query di Spanner possono anche influire su quali blocchi vengono acquisiti. La clausola impedisce ad altre transazioni di modificare le celle bloccate fino al completamento della transazione corrente.

Semantica delle query

Questa sezione fornisce indicazioni sulla semantica delle query quando si utilizza la clausola FOR UPDATE.

Utilizzo nelle istruzioni WITH

La clausola FOR UPDATE non acquisisce i blocchi per l'istruzione WITH quando specifichi FOR UPDATE nella query a livello esterno dell'istruzione WITH.

Nella query seguente, la tabella Singers non acquisisce alcun blocco perché l'intenzione di bloccare non viene propagata alla query delle espressioni di tabella comuni (CTE).

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

Se la clausola FOR UPDATE è specificata nella query CTE, l'intervallo sottoposto a scansione della query CTE acquisisce i blocchi.

Nell'esempio seguente, le celle SingerId e SingerInfo per le righe dove SingerId > 5 sono bloccate.

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

Utilizzo nelle sottoquery

Puoi utilizzare la clausola FOR UPDATE in una query di primo livello con una o più subquery. I blocchi vengono acquisiti dalla query di primo livello e all'interno delle sottoquery, tranne nelle sottoquery di espressioni.

La seguente query blocca le celle SingerId e SingerInfo per le righe in cui SingerId > 5.

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

La seguente query non blocca alcuna cella nella tabella Albums perché si trova all'interno di una sottoquery di espressione. Le celle SingerId e SingerInfo per le righe restituite dalla sottoquery dell'espressione sono bloccate.

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

Da utilizzare per eseguire query sulle visualizzazioni

Puoi utilizzare la clausola FOR UPDATE per eseguire una query su una vista, come mostrato nell'esempio seguente:

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

SELECT * FROM SingerBio WHERE SingerId = 5 FOR UPDATE;

Non puoi utilizzare la clausola FOR UPDATE quando definisci una visualizzazione.

Casi d'uso non supportati

I seguenti casi d'uso di FOR UPDATE non sono supportati:

  • Come meccanismo di mutua esclusione per l'esecuzione di codice al di fuori di Spanner: non utilizzare i blocchi in Spanner per garantire l'accesso esclusivo a una risorsa al di fuori di Spanner. Le transazioni potrebbero essere interrotte da Spanner, ad esempio se viene eseguito un nuovo tentativo per una transazione, esplicitamente dal codice dell'applicazione o implicitamente dal codice client, come il driver JDBC Spanner. È garantito solo che i blocchi vengano mantenuti durante il tentativo eseguito.
  • In combinazione con l'opzione LOCK_SCANNED_RANGES: non puoi utilizzare contemporaneamente la clausola FOR UPDATE e l'opzione LOCK_SCANNED_RANGES nella stessa query, altrimenti Spanner restituisce un errore.
  • Nelle query di ricerca a testo intero: non puoi utilizzare la clausola FOR UPDATE nelle query che utilizzano gli indici di ricerca a testo intero.
  • Nelle transazioni di sola lettura: la clausola FOR UPDATE è valida solo per le query eseguite all'interno di transazioni di lettura/scrittura.
  • All'interno delle istruzioni DDL: non puoi utilizzare la clausola FOR UPDATE nelle query all'interno delle istruzioni DDL, che vengono memorizzate per l'esecuzione successiva. Ad esempio, non puoi utilizzare la clausola FOR UPDATE quando definisci una visualizzazione. Se è necessario il blocco, la clausola FOR UPDATE può essere specificata quando viene eseguita una query sulla vista.

Passaggi successivi