Descrivere i livelli di isolamento
PostgreSQL ha tre livelli di isolamento delle transazioni che impediscono tre tipi di conflitti di concorrenza: letture dirty, letture non ripetibili e letture fantasma.
Tipi di conflitto di concorrenza
Letture dirty
Una lettura dirty si verifica quando una transazione legge la versione aggiornata dei dati che un'altra transazione sta modificando. Tuttavia per questo aggiornamento non viene mai eseguito il commit.
Ad esempio avviene una transazione su un conto di risparmio che porterebbe il saldo del conto sotto lo zero. Prima che venga eseguito il commit della transazione, il saldo del conto viene controllato e viene eseguito il rollback della transazione perché i conti di risparmio non possono riportare un saldo inferiore a zero. Allo stesso tempo, viene eseguito un report che mostra il saldo corrente di tutti i conti di risparmio. Se sono consentite le letture dirty, sarebbe restituito il saldo negativo anche se non venisse mai effettivamente eseguito il commit.
Letture non ripetibili
Una lettura non ripetibile si verifica se una transazione legge i dati, i quali vengono aggiornati da un'altra transazione e quindi la transazione iniziale li legge di nuovo e visualizza gli aggiornamenti.
La connessione A, ad esempio, avvia una transazione e legge il costo per unità e il numero di unità di un ordine, pari a un costo di 10 e 3 unità. La connessione B avvia quindi un'altra transazione e aggiorna lo stesso ordine per impostare il costo per unità su 12. Nella stessa transazione della query originale la connessione A calcola il costo totale dell'ordine. Se sono consentite le letture non ripetibili, la connessione A restituirà il costo per unità pari a 10, il numero di unità pari a 3 e il costo totale di 36, che ovviamente non ha senso.
Lettura di righe fantasma
Una lettura fantasma si verifica se una transazione legge i dati, un’altra transazione aggiunge una o più nuove righe e la transazione iniziale legge di nuovo i dati e visualizza i nuovi aggiornamenti.
Ad esempio, la connessione A avvia una transazione e conta il numero totale di fatture per il giorno a Parigi. Il conteggio presenta un totale di 1.100 fatture per tutti i 10 negozi di Parigi. La connessione B quindi avvia un'altra transazione e aggiunge un nuovo negozio al dettaglio a Parigi con 200 fatture per il giorno di apertura del nuovo negozio. Nella transazione della connessione A, il sistema conta ora il numero di negozi per calcolare il numero di fatture per ogni negozio. La connessione A ora dividerà le 1.100 transazioni per 11 archivi anziché i 10 originali esistenti al momento dell'esecuzione della query del conteggio delle fatture. Il calcolo ora restituisce una media non corretta di 100 sebbene il nuovo archivio abbia 200 fatture di cui la transazione della connessione A non aveva tenuto conto nel calcolo della media.
Livelli di isolamento
Database di Azure per PostgreSQL ha tre livelli di isolamento della transazione: lettura commit, lettura ripetibile e serializzabile. La lettura senza commit non è disponibile in Database di Azure per PostgreSQL.
Effetti dei livelli di isolamento sui conflitti di concorrenza:
| Livello di isolamento | Lettura dirty | Lettura non ripetibile | Lettura fantasma |
|---|---|---|---|
| Lettura senza commit* | Possibile | Possibile | Possibile |
| Read Committed | Non possibile | Possibile | Possibile |
| Lettura ripetibile | Non possibile | Non possibile | Possibile |
| Serializable | Non possibile | Non possibile | Non possibile |
* Non disponibile in PostgreSQL
La lettura commit è il livello di isolamento predefinito in Database di Azure per PostgreSQL. La lettura commit è la più appropriata per la maggior parte degli scenari perché impedisce le letture dirty e offre prestazioni ottimali. È possibile avere letture non ripetibili e letture fantasma, ma queste condizioni possono verificarsi solo in presenza di più istruzioni SELECT contemporaneamente, che eseguono query sugli stessi dati.
La lettura ripetibile differisce dalla lettura con commit perché più istruzioni SELECT all'interno di una transazione vedrebbero gli stessi risultati anche se un'altra transazione ha aggiornato le righe tra le due istruzioni SELECT. Se un'altra transazione inserisce nuove righe, queste non saranno visualizzate nei risultati della seconda istruzione SELECT.
Il livello di isolamento serializzabile offre il massimo livello di isolamento della transazione e funziona come se le diverse transazioni fossero eseguite in serie, ovvero una dopo l'altra. Lo svantaggio del livello di isolamento serializzabile è che se una transazione sta eseguendo un aggiornamento, altre transazioni potrebbero bloccarlo. La transazione serializzabile deve attendere il completamento della transazione bloccante, che influisce sulle prestazioni.
Inoltre, le transazioni serializzabili non possono apportare modifiche alle righe modificate da altre transazioni durante la transazione serializzabile. Se si verifica questa forma di conflitto, viene restituito un messaggio di errore ed è quindi importante avere una logica di retry integrata nelle applicazioni quando si usano transazioni serializzabili.
Per aggiornare i livelli di isolamento della transazione, usare il comando LIVELLO DI ISOLAMENTO DELLA TRANSAZIONE all'interno di una transazione.
Ad esempio:
BEGIN TRANSACTION
TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM humanresources.department
COMMIT;