Utilizzare SELECT FOR UPDATE nell'isolamento serializzabile

Questa pagina descrive come utilizzare la clausola FOR UPDATE nell'isolamento serializzabile.

Il meccanismo di blocco della clausola FOR UPDATE è diverso per lettura ripetibile e isolamento serializzabile. Utilizzando l'isolamento serializzabile, quando utilizzi la query SELECT per scansionare una tabella, l'aggiunta di una clausola FOR UPDATE consente blocchi esclusivi all'intersezione del livello di granularità di riga e colonna, altrimenti noto come a livello di cella. Il blocco rimane attivo per tutta la durata della transazione di lettura/scrittura. Durante questo periodo, la clausola FOR UPDATE impedisce ad altre transazioni di modificare le celle bloccate fino al completamento della transazione corrente.

Per scoprire come utilizzare la clausola FOR UPDATE, consulta le guide di riferimento di GoogleSQL e PostgreSQL FOR UPDATE.

Perché utilizzare la clausola FOR UPDATE

Nei database con livelli di isolamento meno rigorosi, la clausola FOR UPDATE potrebbe essere necessaria per garantire che una transazione simultanea non aggiorni i dati tra la lettura dei dati e il commit della transazione. Poiché Spanner applica la serializzabilità per impostazione predefinita, è 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 un'elevata contesa di scrittura, ad esempio quando più transazioni leggono e scrivono contemporaneamente gli stessi dati, le transazioni simultanee potrebbero causare un aumento degli annullamenti. Questo perché quando più transazioni simultanee acquisiscono blocchi condivisi e poi tentano di eseguire l'upgrade a blocchi esclusivi, le transazioni causano un deadlock. Il deadlock blocca definitivamente le transazioni perché ognuna è in attesa che l'altra rilasci la risorsa di cui ha bisogno. Per fare progressi, Spanner interrompe tutte le transazioni tranne una per risolvere il deadlock. Per ulteriori informazioni, vedi Blocco.

Una transazione che utilizza la clausola FOR UPDATE acquisisce il blocco esclusivo in modo proattivo e procede all'esecuzione, mentre le altre transazioni attendono il proprio turno per il blocco. Anche se Spanner potrebbe comunque limitare il throughput perché le transazioni in conflitto possono essere eseguite solo una alla volta, ma poiché Spanner fa progressi solo su una transazione, risparmia il tempo che altrimenti verrebbe speso 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 workload.

Confronto con il suggerimento LOCK_SCANNED_RANGES

La clausola FOR UPDATE svolge una funzione simile a quella del suggerimento LOCK_SCANNED_RANGES=exclusive.

Esistono due differenze principali:

  • Se utilizzi il suggerimento LOCK_SCANNED_RANGES, la transazione acquisisce blocchi esclusivi s sugli intervalli analizzati per l'intera istruzione. Non puoi acquisire blocchi esclusivi su una sottoquery. L'utilizzo del suggerimento di blocco potrebbe comportare l'acquisizione di più blocchi del necessario e contribuire alla contesa dei blocchi nel workload. Il seguente esempio 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 il suggerimento LOCK_SCANNED_RANGES nelle istruzioni DML, mentre puoi utilizzare solo la clausola FOR UPDATE nelle istruzioni SELECT.

Semantica dei blocchi

Per ridurre le richieste di scrittura simultanee e il costo delle transazioni interrotte a causa di un deadlock, Spanner blocca i dati a livello di cella, se possibile. Il livello di cella è il livello più granulare di dati all'interno di una tabella, ovvero un punto dati all'intersezione di una riga e una colonna. Quando utilizzi la clausola FOR UPDATE, Spanner blocca le celle specifiche scansionate dalla query SELECT.

Nell'esempio seguente, la cella MarketingBudget nella riga SingerId = 1 e AlbumId = 1 è bloccata in modo esclusivo nella tabella Albums, impedendo a transazioni simultanee di modificare la cella finché la transazione non viene eseguita o annullata. Tuttavia, le transazioni simultanee possono comunque aggiornare la cella AlbumTitle in quella riga.

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

Le transazioni simultanee potrebbero bloccarsi durante la lettura dei dati bloccati

Quando una transazione ha acquisito blocchi esclusivi su un intervallo analizzato, le transazioni simultanee potrebbero bloccare la lettura di questi dati. Spanner applica la serializzabilità in modo che i dati possano essere letti solo se è garantito che non vengano modificati da un'altra transazione durante la durata della transazione. Le transazioni simultanee che tentano di leggere dati già bloccati potrebbero dover attendere fino a quando la transazione che contiene i blocchi non viene eseguita, annullata o non va in timeout.

Nel seguente esempio, 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 MarketingBudget per AlbumId = 1, è bloccato finché Transaction 1 non viene eseguito o annullato.

-- 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 scansionato con FOR UPDATE viene bloccata da una transazione simultanea che blocca un intervallo scansionato sovrapposto.

Anche Transaction 3 nel seguente esempio è bloccato, poiché Transaction 1 ha bloccato le celle MarketingBudget per 3 <= AlbumId < 5, che è l'intervallo di scansione sovrapposto 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 scansionato blocca le righe nella tabella di base, ma la transazione simultanea legge da un indice.

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

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

La lettura Transaction 2 non è bloccata dai blocchi acquisiti in Transaction 1, perché esegue query su una tabella di indice.

-- Transaction 2
SELECT SingerId FROM Singers;

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

Quando una transazione ha acquisito blocchi su un intervallo di celle con un suggerimento di blocco esclusivo, le transazioni simultanee che tentano di eseguire una scrittura senza leggere prima i dati nelle celle bloccate possono procedere. Il blocco della transazione sul commit fino a quando la transazione che contiene i blocchi non viene eseguita o viene eseguito 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, l'operazione viene bloccata 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 viene bloccato

Quando una transazione ha acquisito blocchi esclusivi su un intervallo scansionato, le transazioni simultanee non possono inserire dati negli spazi vuoti all'interno di questo 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 finché 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

Avvertenze relative all'acquisizione del blocco

La semantica di blocco 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. Anche i meccanismi di ottimizzazione delle query di Spanner potrebbero influire sulle serrature acquisite. La clausola impedisce ad altre transazioni di modificare le celle bloccate fino al completamento della transazione corrente.

Sintassi delle query

Questa sezione fornisce indicazioni sulla sintassi delle query quando utilizzi la clausola FOR UPDATE.

L'utilizzo più comune è in un'istruzione SELECT di primo livello. Ad esempio:

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

Questo esempio mostra come utilizzare la clausola FOR UPDATE in un'istruzione SELECT per bloccare in modo esclusivo le celle SingerId e SingerInfo di WHERE SingerID = 5.

Utilizzo nelle istruzioni WITH

La clausola FOR UPDATE non acquisisce blocchi per l'istruzione WITH quando specifici FOR UPDATE nella query di livello esterno dell'istruzione WITH.

Nella query seguente, la tabella Singers non acquisisce blocchi perché l'intento di bloccare non viene propagato 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 analizzato della query CTE acquisisce i blocchi.

Nell'esempio seguente, le celle SingerId e SingerInfo per le righe in cui 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 livello esterno che contiene una o più subquery. I blocchi vengono acquisiti dalla query di primo livello e all'interno delle subquery, tranne nelle subquery 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 nessuna 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;

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

Casi d'uso non supportati

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

  • Come meccanismo di esclusione reciproca per l'esecuzione di codice al di fuori di Spanner: non utilizzare il blocco 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 una transazione viene ritentata, in modo esplicito dal codice dell'applicazione o implicito dal codice client, ad esempio il driver JDBC di Spanner, è garantito che i blocchi vengano mantenuti durante il tentativo di commit.
  • In combinazione con il suggerimento LOCK_SCANNED_RANGES: non puoi utilizzare sia la clausola FOR UPDATE sia il suggerimento LOCK_SCANNED_RANGES nella stessa query, altrimenti Spanner restituisce un errore.
  • Nelle query di ricerca full-text:non puoi utilizzare la clausola FOR UPDATE nelle query che utilizzano indici di ricerca full-text.
  • Nelle transazioni di sola lettura:la clausola FOR UPDATE è valida solo nelle 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 archiviate per l'esecuzione successiva. Ad esempio, non puoi utilizzare la clausola FOR UPDATE quando definisci una vista. Se è necessario il blocco, la clausola FOR UPDATE può essere specificata durante l'interrogazione della vista.

Passaggi successivi