Utilizzare SELECT FOR UPDATE

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

Quando utilizzi la query SELECT per scansionare una tabella, aggiungi una clausola FOR UPDATE per attivare i blocchi esclusivi a 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 saperne di più, consulta le guide di riferimento di 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 garantire che una transazione simultanea non aggiorni i dati tra la lettura dei dati e il commit della transazione. Poiché Spanner applica sempre la serializzabilità, è garantito che la transazione venga eseguita 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. Spanner interrompe tutte le transazioni tranne una. Per ulteriori informazioni, vedi Blocco.

Una transazione che utilizza la clausola FOR UPDATE acquisisce il blocco esclusivo 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, poiché Spanner fa progressi solo su una transazione, risparmia 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.

Le differenze principali sono due:

  • 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 carico di lavoro. Il seguente esempio mostra come utilizzare un suggerimento per il 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. Quando utilizzi la clausola FOR UPDATE, Spanner blocca celle specifiche analizzate 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é questa transazione non viene confermata o sottoposta a rollback. 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 siano stati modificati da un'altra transazione durante la durata della transazione. Le transazioni simultanee che tentano di leggere dati già bloccati potrebbero dover attendere fino al commit o al rollback della transazione che contiene i blocchi.

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

Transaction 3 nell'esempio seguente è bloccato anche perché 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;

Transaction 2 di sola lettura non è bloccato dai blocchi acquisiti in Transaction 1, perché esegue una 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. La transazione viene bloccata sul commit finché la transazione che contiene i blocchi non viene eseguita o non 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 della serratura

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.

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 blocchi per l'istruzione WITH quando specifica FOR UPDATE nella query di livello esterno dell'istruzione WITH.

Nella query seguente, la tabella Singers non acquisisce blocchi 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 scansionato 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 espressione.

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

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 solo 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 delle 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 visualizzazione.

Passaggi successivi