Documentazione di PostgreSQL 9.0 > Il linguaggio SQL > Controllo concorrente > Isolamento transazionale
PrecedenteControllo concorrenteLock esplicitoSuccessivo

13.2. Isolamento transazionale

Lo standard SQL definisce quattro livelli di isolamento transazionale in termini di tre fenomeni che devono essere preventivati tra transazioni concorrenti. Questi fenomeni indesiderati sono:

lettura sporca

Una transazione legge i dati scritti da una transazione concorrente che non ha fatto un commit.

lettura non ripetibile

Una transazione legge di nuovo dati che ha letto precedentemente e scopre che i dati sono stati modificati da un'altra transazione (che ha eseguito un commit dopo la lettura iniziale).

lettura fantasma

Una transazione esegue di nuovo una query che restituisce un insieme di righe che soddisfa una condizione di ricerca e scopre che l'insieme di righe che soddisfano la condizione è cambiata a causa di un'altra transazione che ha eseguito un commit di recente.

I quattro livelli di isolamento transazionale e i comportamenti corrispondenti sono descritti in Tabella 13.1, «Livelli di isolamento di transazione SQL».

Tabella 13.1. Livelli di isolamento di transazione SQL

Livello di isolamento Lettura sporca Lettura non ripetibile Lettura fantasma
Lettura che non ha fatto commit Possibile Possibile Possibile
Lettura che ha fatto commit Non possibile Possibile Possibile
Lettura ripetibile Non possibile Non possibile Possibile
Serializzabile Non possibile Non possibile Non possibile

In PostgreSQL™, è possibile richiedere uno qualsiasi dei quattro livelli standard di isolamento di transazione. Ma internalmente, ci sono solo due distinti livelli di isolamento, che corrispondono ai livelli Read Committed e Serializable. Quando si sceglie il livello Read Uncommitted in realtà si otterrà Read Committed, e quando si sceglie Repeatable Read in realtà si otterrà Serializable, così il livello di isolamento effettivo potrebbe essere più stretto di quello scelto. Questo è permesso dallo standard SQL: i quattro livelli di isolamento definiscono solo quali fenomeni non devono accadere, non definiscono quali fenomeni devono accadere. La ragione per la quale PostgreSQL™ fornisce solo due livelli di isolamento è che questo è l'unico modo sensato per gestire i livelli di isolamento standard nell'architettura di controllo concorrente. Il comportamento dei livelli di isolamento è spiegato in dettaglio nelle sottosezioni seguenti.

Per impostare il livello di isolamento transazionale di una transazione, usare il comando SET TRANSACTION(7).

13.2.1. Livello di isolamento Read Committed

Read Committed è il livello di isolamento predefinito in PostgreSQL™. Quando una transazione usa questo livello di isolamento, una query SELECT (senza clausola FOR UPDATE/SHARE) vede solo i dati che sono stati sottoposti a commit prima che query iniziasse; essa non vede mai nè i dati non sottoposti a commit nè i cambiamenti sottoposti a commit da transazioni concorrenti durante l'esecuzione della query. In effetti, una query SELECT vede un'istantanea del database nell'istante in cui è partita. Ad ogni modo, SELECT vede gli effetti di update precedenti eseguite all'interno della propria transazione, anche se non sono ancora stati sottoposti a commit. Notare inoltre che due comandi SELECT possono vedere dati differenti, anche se sono all'interno di una singola transazione, se altre transazioni eseguono cambiamenti durante l'esecuzione della prima SELECT.

[Nota]

Nota

Nota per il revisore: TODO

UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave the same as SELECT in terms of searching for target rows: they will only find target rows that were committed as of the command start time. However, such a target row might have already been updated (or deleted or locked) by another concurrent transaction by the time it is found. In this case, the would-be updater will wait for the first updating transaction to commit or roll back (if it is still in progress). If the first updater rolls back, then its effects are negated and the second updater can proceed with updating the originally found row. If the first updater commits, the second updater will ignore the row if the first updater deleted it, otherwise it will attempt to apply its operation to the updated version of the row. The search condition of the command (the WHERE clause) is re-evaluated to see if the updated version of the row still matches the search condition. If so, the second updater proceeds with its operation using the updated version of the row. In the case of SELECT FOR UPDATE and SELECT FOR SHARE, this means it is the updated version of the row that is locked and returned to the client.

A causa della regola sopra, è possibile, per un comando di aggiornamento, vedere un'istantanea inconsistene: può vedere gli effetti di comandi di aggiornamento concorrenti sulle stesse righe che sta tentando di aggiornare, ma non vede gli effetti di quei comandi sulle altre righe nel database. Questo comportamento rende la modalità Read Committed inadatta a comandi che coinvolgono condizioni di ricerca complesse; in ogni caso, va bene per casi più semplici. Per esempio, considerare l'aggiornamento di saldi bancari con transazioni tipo:

BEgin;
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345;
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 7534;
COMMIT;

Se due di quelle transazioni provano a cambiare concorrentemente il saldo dell'account 12345, chiaramente si vorrà che la seconda transazione cominci con la versione aggiornata della riga di account. Dato che ogni comando incluisce solamente su una riga predetermiata, lasciargli vedere la versione aggiornata della riga non crea nessun problema di inconsistenza.

Un utilizzo più complesso può produrre risultati non desiderati in modalità Read Committed. Per esempio, considerare un comando DELETE che opera su dati che sono stati sia aggiunti che rimossi dai suoi criteri di restrizione da un altro comando, per es., sia website una tabella con due righe con website.hits uguale a 9 e 10:

BEgin;
UPDATE website SET hits = hits + 1;
-- eseguito da un'altra sessione:  DELETE FROM website WHERE hits = 10;
COMMIT;

DELETE non avrà effetti dato che c'è una riga website.hits = 10 prima e dopo l'UPDATE. Questo accade perchè il valore di riga precedentemente aggiornato 9 viene saltato, e quando l'UPDATE è completo e DELETE ottiene un lock, il nuovo valore di riga non è più 10 ma 11, che non corrisponde più ai criteri di ricerca.

Dato che la modalità Read Committed comincia ogni comando con una nuova istantanea che include tutte le transazioni sottoposte a commit fino a quell'istante, i comandi successivi nella stessa transazione vedranno gli effetti della transazione concorrente sottoposta a commit in ogni caso. Il punto della questione è se un singolo comando ha o meno una visione assolutamente consistente del database.

L'isolamento transazionale parziale fornito dalla modalità Read Committed è adeguato per molte applicazioni, e questa modalità è veloce e semplice da usare; comunque, non è sufficiente per tutti i casi. Le applicazioni che fanno query e aggiornamenti complessi potrebbero richiedere una visione consistente del database più rigorosa che quella fornita dalla modalità Read Committed.

13.2.2. Livello di isolamento serializzabile

Il livello di isolamento Serializable fornisce l'isolamento transazionale più rigido. Questo livello emula l'esecuzione di transazioni seriali, come se le transazioni fossero state eseguite una dopo l'altra, in maniera seriale, invece che concorrentemente. Comunque, applicazioni che usano questo livello devono essere preparate a ritentare le transazioni dopo fallimenti della serializzazione.

Quando una transazione usa il livello serializzabile, una query SELECT vede i dati sottoposti a commit prima che la transazione sia iniziata; non vede mai nè dati non sottoposti a commit nè cambiamenti sottoposti a commit durante l'esecuzione di transazione da parte di transazioni concorrenti. (Comunque, la query vede gli effetti di aggiornamenti precedenti eseguiti all'interno della propria transazione, anche se non sono ancora state sottoposte a commit). Questo è diverso dal Read Committed nel quale una query in una transazione serializzabile vede un'istantanea all'inizio della transazione, non all'inizio della quey corrente all'interno della transazione. Così, comandi SELECT successivi all'interno di una singola transazione vedono gli stessi dati, per es., non vedono cambiamenti fatti da altre transazioni che sono stati sottoposti a commit dopo che le proprie transazioni sono partite. (Questo comportamento può essere ideale per applicazioni di riportistica).

I comandi UPDATE, DELETE, SELECT FOR UPDATE, e SELECT FOR SHARE si comportano come SELECT in termini di ricerca di righe: trovano solo righe che sono state sottoposte a commit dal momento di inizio della transazione. Comunque, quella riga potrebbe essere già stata aggiornata (o cancellata, o sottoposta a lock) da un'altra transazione concorrente, nel momento in cui è stata trovata. In questo caso, la transazione serializzabile aspetterà che la prima transazione esegua un commit o un rool back (se è ancora in corso). Se la prima transazione esegue un rollback, allora i suoi effetti sono negati e la transazione serializzabile può procedere con l'aggiornamento della riga trovata originariamente. Ma se la prima transazione esegue un commit (ed effettivamente aggiorna o cancella la riga, non esegue semplicemente un lock) allora la transazione serializzabile sarà sottoposta a rollback con il messaggio

ERROR:  could not serialize access due to concurrent update

dato che una transazione serializzabile non può modificare e eseguire lock su righe cambiate da altre transzioni dopo che la transazione serializzabile è iniziata.

Quando l'applicazione riceve questo messaggio di errore, dovrebbe annullare la transazione corrente e riprovare l'intera transazione dall'inizio. La seconda volta, la transazione vedrà il cambiamento sottoposto a commit precedentemente come parte della sua visione iniziale del database, così non c'è conflitto logico nell'usare la nuova versione della riga come punto di partenza per la nuova transazione.

Notare che solo transazioni di aggiornamento potrebbero necessitare di essere riprovate; transazioni a sola lettura non avranno mai conflitti di serializzazione.

La modalità Serializable fornisce una garanzia rigorosa che ogni transazione abbia una visione completamente consistente del database. Comunque, l'applicazione dev'essere preparata a ritentare le transazioni quando aggiornamenti concorrenti rendono impossibile l'illusione di esecuzione seriale. Dato che il costo di rifare transazioni complesse può essere significativo, la modalità Serializable è raccomandata solo quando transazioni di aggiornamento contengono logiche sufficientemente complesse che potrebbero fornire risposte sbagliate in modalità Read Committed. Di solito, la modalità Serializable è necessaria quando una transazione esegue diversi comandi successivi che devono avere visioni identiche del database.

13.2.2.1. Isolamento serializzabile contro serializzabilità reale

Il significato intuitivo (e la definiziona matematica) di esecuzione «serializzabile» è che qualsiasi due transazioni che hanno effettuato un commit con successo appariranno come se fossero state eseguite in maniera strettamente seriale, una dopo l'altra - sebbene quale appaia come eseguita prima potrebbe non essere prevedibile in anticipo. È importante rendersi conto che vietare i comportamenti indesiderati elencati nella Tabella 13.1, «Livelli di isolamento di transazione SQL» non è sufficiente a garantire una vera serializzazione, e infatti la modalità Serializable di PostgreSQLnon garantisce l'esecuzione serializzabile in questo senso. Come esempio, considerare una tabella mytab, contenente inizialmente:

 class | value 
-------+-------
     1 |    10
     1 |    20
     2 |   100
     2 |   200

Supporre che la transazione serializzabile A calcoli:

SELECT SUM(value) FROM mytab WHERE class = 1;

e poi inserisce il risultato (30) come valore in una nuova riga con class = 2. Concorrentemente, la transazione serializzabile B calcola:

SELECT SUM(value) FROM mytab WHERE class = 2;

e ottiene il risultato 300, che inserisce in una nuova riga con class = 1. Quindi entrambe le transazioni fanno il commit. Nessuno dei comportamenti indesiderati elencati si è presentato, ma si otterrà un risultato che non potrebbe essere successo in nessun ordine serialmente. Se A è stata eseguita prima di B, B avrebbe calcolato la somma 330, non 300, e similmente l'altro ordine sarebbe risultato in una somma diversa di quella calcolata da A.

Per garantire vera serializzazione matematica, è necessario per un sistema di database assicurare il predicate locking, che significa che una transazione non può inserire o modificare una riga che potrebbe corrispondere a una condizione WHERE di una query in un'altra transazione concorrente. Per esempio, una volta che la transazione A ha eseguito la query SELECT ... WHERE class = 1, un sistema di predicate locking eviterebbe che la transzione B inserisse qualsiasi nuova riga con class 1 finchè A ha fatto commit. [8] Tale sistema di locking è complesso da implementare e estremamente costoso in esecuzione, dato che ogni sessione deve fare attenzione ai dettagli di ogni query eseguita da ogni transazione concorrente. E questa grande spesa è per la maggior parte inutile, dato che in pratica la maggior parte della applicazioni non fanno il tipo di cose che potrebbero generare problemi. (Certamente l'esempio sopra è abbastanza forzato ed è improbabile che rappresenti software reale). Per queste ragioni, PostgreSQL™ non implementa il predicate locking.

In casi dove la possibilità di esecuzione non-serializzabile è realmente un azzardo, è possibile prevenire i problemi con un uso appropriato di lock esplicito. Maggiori dettagli appaiono nelle sezioni seguenti.



[8] Essenzialmente, un sistema di predicate locking previene letture fantasma restringendo cosa viene scritto, mentre MVCC le previene restringendo cosa viene letto.

Documentazione di PostgreSQL 9.0 > Il linguaggio SQL > Controllo concorrente > Isolamento transazionale
PrecedenteControllo concorrenteLock esplicitoSuccessivo