Livelli di isolamento delle transazioni in Babelfish

Babelfish supporta i livelli di isolamento delle transazioni READ UNCOMMITTED, READ COMMITTED e SNAPSHOT. A partire dalla versione Babelfish 3.4 sono supportati i livelli di isolamento aggiuntivi REPEATABLE READ e SERIALIZABLE. Tutti i livelli di isolamento in Babelfish sono supportati con il comportamento dei corrispondenti livelli di isolamento in PostgreSQL. SQL Server e Babelfish utilizzano diversi meccanismi di base per implementare i livelli di isolamento delle transazioni (blocco per l'accesso simultaneo, blocchi bloccati dalle transazioni, gestione degli errori ecc.). Inoltre, ci sono alcune sottili differenze nel modo in cui l'accesso simultaneo può funzionare per diversi carichi di lavoro. Per ulteriori informazioni su questo comportamento di PostgreSQL, consulta Transaction Isolation.

Panoramica dei livelli di isolamento delle transazioni

I livelli di isolamento delle transazioni di SQL Server originali si basano su un blocco pessimistico in cui esiste una sola copia dei dati e le query devono bloccare risorse come le righe prima di accedervi. Successivamente, è stata introdotta una variante del Read Commit Isolation Level. Ciò consente l'uso di versioni di riga per fornire una migliore concorrenza tra lettori e scrittori utilizzando un accesso non bloccante. Inoltre, è disponibile un nuovo livello di isolamento chiamato Snapshot. Utilizza inoltre versioni di riga per fornire una migliore concorrenza rispetto a REPEATABLE READ Isolation Level, evitando blocchi condivisi sui dati di lettura che vengono conservati fino alla fine della transazione.

A differenza di SQL Server, tutti i livelli di isolamento delle transazioni in Babelfish si basano sul blocco ottimistico (MVCC). Ogni transazione visualizza un'istantanea dei dati all'inizio della dichiarazione (READ COMMITTED) o all'inizio della transazione (REPEATABLE READ, SERIALIZABLE), indipendentemente dallo stato corrente dei dati sottostanti. Pertanto, il comportamento di esecuzione delle transazioni simultanee in Babelfish potrebbe differire da quello di SQL Server.

Ad esempio, si consideri una transazione con Isolation Level SERIALIZABLE inizialmente bloccata in SQL Server ma che ha esito positivo in seguito. Potrebbe finire per fallire in Babelfish a causa di un conflitto di serializzazione con una transazione simultanea che legge o aggiorna le stesse righe. Potrebbero esserci anche casi in cui l'esecuzione di più transazioni simultanee produca un risultato finale diverso in Babelfish rispetto a SQL Server. Le applicazioni che utilizzano i livelli di isolamento devono essere accuratamente testate per verificare la presenza di scenari di concorrenza.

Livelli di isolamento in SQL Server	Livello di isolamento Babelfish	Livello di isolamento PostgreSQL	Commenti
LEGGI SENZA IMPEGNO	LETTO SENZA IMPEGNO	LETTO SENZA IMPEGNO	Read Uncommitted è uguale a Read Commit in Babelfish/PostgreSQL
LEGGI COMMESSO	LEGGI IMPEGNATO	LEGGI IMPEGNATO	SQL Server Read Commit è basato sul blocco pessimistico, Babelfish Read Commit è basato su snapshot (MVCC).
LEGGI L'ISTANTANEA CONFERMATA	LEGGI COMMESSO	LEGGI IMPEGNATO	Entrambi sono basati su snapshot (MVCC) ma non esattamente uguali.
ISTANTANEA	ISTANTANEA	LETTURA RIPETIBILE	Esattamente lo stesso.
LETTURA RIPETIBILE	LETTURA RIPETIBILE	LETTURA RIPETIBILE	SQL Server Repeatable Read è basato sul blocco pessimistico, Babelfish Repeatable Read è basato su snapshot (MVCC).
SERIALIZZABILI	SERIALIZZABILI	SERIALIZZABILI	SQL Server Serializable è un sistema di isolamento pessimistico, Babelfish Serializable è basato su snapshot (MVCC).

Nota

I table hint non sono attualmente supportati e il loro comportamento è controllato utilizzando l'escape hatch predefinito di Babelfish. escape_hatch_table_hints

Impostazione dei livelli di isolamento delle transazioni

Utilizzate il seguente comando per impostare il livello di isolamento delle transazioni:

SET TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SNAPSHOT | SERIALIZABLE }
	    

Abilitazione o disabilitazione dei livelli di isolamento delle transazioni

I livelli di isolamento delle transazioni REPEATABLE READ e SERIALIZABLE sono disabilitati di default in Babelfish e devi abilitarli esplicitamente impostando o escape hatch su use. babelfishpg_tsql.isolation_level_serializable babelfishpg_tsql.isolation_level_repeatable_read pg_isolation sp_babelfish_configure Per ulteriori informazioni, consulta Gestione degli errori di Babelfish con escape hatch.

Di seguito sono riportati alcuni esempi per abilitare o disabilitare l'uso di REPEATABLE READ e SERIALIZABLE nella sessione corrente impostando i rispettivi escape hatch. Facoltativamente, includi il server parametro per impostare l'escape hatch per la sessione corrente e per tutte le nuove sessioni successive.

Per abilitare l'uso di SET TRANSACTION ISOLATION LEVEL REPEATABLE READ solo nella sessione corrente.

EXECUTE sp_babelfish_configure 'isolation_level_repeatable_read', 'pg_isolation'
	        

Per abilitare l'uso di SET TRANSACTION ISOLATION LEVEL REPEATABLE READ nella sessione corrente e in tutte le nuove sessioni successive.

EXECUTE sp_babelfish_configure 'isolation_level_repeatable_read', 'pg_isolation', 'server'
	        

Per disabilitare l'uso di SET TRANSACTION ISOLATION LEVEL REPEATABLE READ nella sessione corrente e nelle nuove sessioni successive.

EXECUTE sp_babelfish_configure 'isolation_level_repeatable_read', 'off', 'server'
	        

Per abilitare l'uso di SET TRANSACTION ISOLATION LEVEL SERIALIZABLE solo nella sessione corrente.

EXECUTE sp_babelfish_configure 'isolation_level_serializable', 'pg_isolation'
	        

Per abilitare l'uso di SET TRANSACTION ISOLATION LEVEL SERIALIZABLE nella sessione corrente e in tutte le nuove sessioni successive.

EXECUTE sp_babelfish_configure 'isolation_level_serializable', 'pg_isolation', 'server'
	        

Per disabilitare l'uso di SET TRANSACTION ISOLATION LEVEL SERIALIZABLE nella sessione corrente e nelle nuove sessioni successive.

EXECUTE sp_babelfish_configure 'isolation_level_serializable', 'off', 'server'
	        

Differenze tra i livelli di isolamento di Babelfish e SQL Server

Di seguito sono riportati alcuni esempi sulle sfumature del modo in cui SQL Server e Babelfish implementano i livelli di isolamento ANSI.

Nota

• Isolation Level Repeatable Read e Snapshot sono gli stessi in Babelfish.

• Il livello di isolamento Read Uncommitted e Read Commit sono gli stessi in Babelfish.

L'esempio seguente mostra come creare la tabella di base per tutti gli esempi indicati di seguito:

CREATE TABLE employee (
    id sys.INT NOT NULL PRIMARY KEY,
    name sys.VARCHAR(255)NOT NULL,
    age sys.INT NOT NULL
);
INSERT INTO employee (id, name, age) VALUES (1, 'A', 10);
INSERT INTO employee (id, name, age) VALUES (2, 'B', 20);
INSERT INTO employee (id, name, age) VALUES (3, 'C', 30);

Argomenti

• BABELFISH READ UNCOMMIT VS SQL SERVER READ UNCOMMIT LEVEL
• BABELFISH READ COMMIT VS SQL SERVER READ COMMIT (LIVELLO DI ISOLAMENTO)
• BABELFISH READ COMMIT VS SQL SERVER READ COMMIT (LIVELLO DI ISOLAMENTO DELLO SNAPSHOT)
• LETTURA RIPETIBILE BABELFISH VS LIVELLO DI ISOLAMENTO DI LETTURA RIPETIBILE DI SQL SERVER
• LIVELLO DI ISOLAMENTO SERIALIZZABILE BABELFISH VS SERIALIZZABILE DEL SERVER SQL

BABELFISH READ UNCOMMIT VS SQL SERVER READ UNCOMMIT LEVEL

LETTURE SPORCHE IN SQL SERVER
Transazione 1	Transazione 2	SQL Server Read Uncommit	Babelfish Read senza impegno
BEGIN TRANSACTION	BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
	AGGIORNA il set di età del dipendente =0;	Aggiornamento riuscito.	Aggiornamento riuscito.
	INSERISCI NEI VALORI DEI DIPENDENTI (4, 'D', 40);	Inserimento riuscito.	Inserimento riuscito.
SELEZIONA * DAL dipendente;		La Transazione 1 può visualizzare le modifiche non confermate dalla Transazione 2.	Uguale a Read Commit in Babelfish. Le modifiche non confermate dalla Transazione 2 non sono visibili nella Transazione 1.
	COMMIT
SELEZIONA * DAL DIPENDENTE;		Visualizza le modifiche apportate dalla Transazione 2.	Visualizza le modifiche apportate da Transaction 2.

BABELFISH READ COMMIT VS SQL SERVER READ COMMIT (LIVELLO DI ISOLAMENTO)

BLOCCO LETTURA-SCRITTURA
Transazione 1	Transazione 2	SQL Server Read Commit	Babelfish Read si è impegnato
BEGIN TRANSACTION	BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;	SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELEZIONA * DAL DIPENDENTE;
	AGGIORNA il set di età del dipendente = 100 DOVE id = 1;	Aggiornamento riuscito.	Aggiornamento riuscito.
AGGIORNA dipendente SET age = 0 WHERE age IN (SELECT MAX (age) FROM employee);		Fase bloccata fino al completamento della Transazione 2.	Le modifiche alla Transazione 2 non sono ancora visibili. Aggiorna la riga con id=3.
	COMMIT	La transazione 2 viene eseguita correttamente. La transazione 1 è ora sbloccata e vede l'aggiornamento da Transaction 2.	La transazione 2 viene eseguita correttamente.
SELEZIONA * DAL DIPENDENTE;		La transazione 1 aggiorna la riga con id = 1.	La transazione 1 aggiorna la riga con id = 3.

BABELFISH READ COMMIT VS SQL SERVER READ COMMIT (LIVELLO DI ISOLAMENTO DELLO SNAPSHOT)

COMPORTAMENTO DI BLOCCO SULLE NUOVE RIGHE INSERITE
Transazione 1	Transazione 2	SQL Server Read Committed Snapshot	Babelfish Read si è impegnato
BEGIN TRANSACTION	BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;	SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
INSERIRE NEI VALORI DEI DIPENDENTI (4, «D», 40);
	AGGIORNA l'età del set di dipendenti = 99 anni;	Il passaggio è bloccato fino al completamento della transazione 1. La riga inserita è bloccata dalla transazione 1.	Sono state aggiornate tre righe. La riga appena inserita non è ancora visibile.
COMMIT		Impegno riuscito. La transazione 2 è ora sbloccata.	Impegno riuscito.
	SELEZIONA * DAL DIPENDENTE;	Tutte e 4 le righe hanno età = 99.	La riga con id = 4 ha il valore di età 40 poiché non era visibile alla transazione 2 durante la query di aggiornamento. Le altre righe vengono aggiornate a age=99.

LETTURA RIPETIBILE BABELFISH VS LIVELLO DI ISOLAMENTO DI LETTURA RIPETIBILE DI SQL SERVER

COMPORTAMENTO DI BLOCCO DI LETTURA/SCRITTURA
Transazione 1	Transazione 2	Lettura ripetibile di SQL Server	Lettura ripetibile Babelfish
BEGIN TRANSACTION	BEGIN TRANSACTION
IMPOSTA IL LIVELLO DI ISOLAMENTO DELLE TRANSAZIONI (LETTURA RIPETIBILE);	IMPOSTA IL LIVELLO DI ISOLAMENTO DELLE TRANSAZIONI (LETTURA RIPETIBILE);
SELEZIONA * DAL DIPENDENTE;
AGGIORNA IL SET NAME='A_TXN1' DOVE id=1;
	SELEZIONA * FROM employee WHERE id! = 1;
	SELEZIONA * DAL DIPENDENTE;	La transazione 2 è bloccata fino al completamento della transazione 1.	La transazione 2 procede normalmente.
COMMIT
	SELEZIONA * DAL DIPENDENTE;	L'aggiornamento dalla transazione 1 è visibile.	L'aggiornamento dalla Transazione 1 non è visibile.
COMMIT
	SELEZIONA * DAL DIPENDENTE;	visualizza l'aggiornamento da Transaction 1.	vede l'aggiornamento da Transaction 1.

COMPORTAMENTO DI BLOCCO DI SCRITTURA/SCRITTURA
Transazione 1	Transazione 2	Lettura ripetibile di SQL Server	Lettura ripetibile Babelfish
BEGIN TRANSACTION	BEGIN TRANSACTION
IMPOSTA IL LIVELLO DI ISOLAMENTO DELLE TRANSAZIONI (LETTURA RIPETIBILE);	IMPOSTA IL LIVELLO DI ISOLAMENTO DELLE TRANSAZIONI (LETTURA RIPETIBILE);
AGGIORNA il nome del dipendente SET NAME='A_TXN1' DOVE id=1;
	AGGIORNA IL NOME SET del dipendente ='A_TXN2' DOVE id=1;	Transazione 2 bloccata.	Transazione 2 bloccata.
COMMIT		Il commit è riuscito e la transazione 2 è stata sbloccata.	Commit riuscito e la transazione 2 fallisce con errore, impossibile serializzare l'accesso a causa di un aggiornamento simultaneo.
	COMMIT	Esecuzione riuscita.	La transazione 2 è già stata interrotta.
	SELEZIONA * DAL DIPENDENTE;	La riga con id=1 ha name='A_TX2'.	La riga con id=1 ha name='a_TX1'.

LETTURA FANTASMA
Transazione 1	Transazione 2	Lettura ripetibile di SQL Server	Lettura ripetibile Babelfish
BEGIN TRANSACTION	BEGIN TRANSACTION
IMPOSTA IL LIVELLO DI ISOLAMENTO DELLE TRANSAZIONI (LETTURA RIPETIBILE);	IMPOSTA IL LIVELLO DI ISOLAMENTO DELLE TRANSAZIONI (LETTURA RIPETIBILE);
SELEZIONA * DAL DIPENDENTE;
	INSERISCI NEI VALORI DEI DIPENDENTI (4, NewRowName '', 20);	La transazione 2 procede senza alcun blocco.	La transazione 2 procede senza alcun blocco.
	SELEZIONA * DAL DIPENDENTE;	La riga appena inserita è visibile.	La riga appena inserita è visibile.
	COMMIT
SELEZIONA * DAL DIPENDENTE;		La nuova riga inserita dalla transazione 2 è visibile.	La nuova riga inserita dalla transazione 2 non è visibile.
COMMIT
SELEZIONA * DAL DIPENDENTE;		La riga appena inserita è visibile.	La riga appena inserita è visibile.

RISULTATI FINALI DIVERSI
Transazione 1	Transazione 2	Lettura ripetibile di SQL Server	Lettura ripetibile Babelfish
BEGIN TRANSACTION	BEGIN TRANSACTION
IMPOSTA IL LIVELLO DI ISOLAMENTO DELLE TRANSAZIONI (LETTURA RIPETIBILE);	IMPOSTA IL LIVELLO DI ISOLAMENTO DELLE TRANSAZIONI (LETTURA RIPETIBILE);
AGGIORNA dipendente IMPOSTA età = 100 DOVE ETÀ IN (SELEZIONA MIN (età) DAL dipendente);		La transazione 1 aggiorna la riga con id 1.	La transazione 1 aggiorna la riga con id 1.
	AGGIORNA dipendente IMPOSTA età = 0 DOVE ETÀ IN (SELECT MAX (age) FROM employee);	La transazione 2 è bloccata poiché l'istruzione SELECT tenta di leggere le righe bloccate dalla query UPDATE nella transazione 1.	La transazione 2 procede senza alcun blocco poiché la lettura non viene mai bloccata, l'istruzione SELECT viene eseguita e infine la riga con id = 3 viene aggiornata poiché le modifiche alla transazione 1 non sono ancora visibili.
	SELEZIONA * FROM EMPLOYEE;	Questo passaggio viene eseguito dopo il completamento della transazione 1. La riga con id = 1 viene aggiornata dalla transazione 2 nel passaggio precedente ed è visibile qui.	La riga con id = 3 viene aggiornata dalla Transazione 2.
COMMIT		La transazione 2 è ora sbloccata.	Impegno riuscito.
	COMMIT
SELEZIONA * DAL DIPENDENTE;		Entrambe le transazioni eseguono l'aggiornamento sulla riga con id = 1.	Le diverse righe vengono aggiornate dalle transazioni 1 e 2.

LIVELLO DI ISOLAMENTO SERIALIZZABILE BABELFISH VS SERIALIZZABILE DEL SERVER SQL

BLOCCHI DI INTERVALLO IN SQL SERVER
Transazione 1	Transazione 2	SQL Server serializzabile	Serializzabile Babelfish
BEGIN TRANSACTION	BEGIN TRANSACTION
IMPOSTA IL LIVELLO DI ISOLAMENTO DELLE TRANSAZIONI SERIALIZZABILE;	IMPOSTARE IL LIVELLO DI ISOLAMENTO DELLE TRANSAZIONI SERIALIZZABILE;
SELEZIONA * DAL DIPENDENTE;
	INSERISCI NEI VALORI DEI DIPENDENTI (4, 'D', 35);	La transazione 2 è bloccata fino al completamento della transazione 1.	La transazione 2 procede senza alcun blocco.
	SELEZIONA * DAL DIPENDENTE;
COMMIT		La transazione 1 viene eseguita correttamente. La transazione 2 è ora sbloccata.	La transazione 1 viene eseguita correttamente.
	COMMIT
SELEZIONA * DAL DIPENDENTE;		La riga appena inserita è visibile.	La riga appena inserita è visibile.

RISULTATI FINALI DIVERSI
Transazione 1	Transazione 2	SQL Server serializzabile	Serializzabile Babelfish
BEGIN TRANSACTION	BEGIN TRANSACTION
IMPOSTA IL LIVELLO DI ISOLAMENTO DELLE TRANSAZIONI SERIALIZZABILE;	IMPOSTARE IL LIVELLO DI ISOLAMENTO DELLE TRANSAZIONI SERIALIZZABILE;
	INSERIRE NEI VALORI DEI DIPENDENTI (4, 'D', 40);
AGGIORNA l'età impostata del dipendente = 99 DOVE id = 4;		La transazione 1 è bloccata fino al completamento della transazione 2.	La transazione 1 procede senza alcun blocco.
	COMMIT	La transazione 2 viene eseguita correttamente. La transazione 1 è ora sbloccata.	La transazione 2 viene eseguita correttamente.
COMMIT
SELEZIONA * DAL DIPENDENTE;		La riga appena inserita è visibile con valore di età = 99.	La riga appena inserita è visibile con valore di età = 40.

INSERIRE NELLA TABELLA CON VINCOLO UNIVOCO
Transazione 1	Transazione 2	SQL Server serializzabile	Serializzabile Babelfish
BEGIN TRANSACTION	BEGIN TRANSACTION
IMPOSTA IL LIVELLO DI ISOLAMENTO DELLE TRANSAZIONI SERIALIZZABILE;	IMPOSTARE IL LIVELLO DI ISOLAMENTO DELLE TRANSAZIONI SERIALIZZABILE;
	INSERIRE NEI VALORI DEI DIPENDENTI (4, 'D', 40);
INSERISCI NEI VALORI DEI DIPENDENTI ((SELECT MAX (id) +1 FROM employee), 'E', 50);		La transazione 1 è bloccata fino al completamento della transazione 2.	La transazione 1 è bloccata fino al commit della Transazione 2.
	COMMIT	La transazione 2 viene eseguita correttamente. La transazione 1 è ora sbloccata.	La transazione 2 viene eseguita correttamente. La transazione 1 interrotta con errore «valore chiave duplicato» viola un vincolo univoco.
COMMIT		La transazione 1 viene eseguita correttamente.	Il commit della transazione 1 ha esito negativo e non è stato possibile serializzare l'accesso a causa delle dipendenze di lettura/scrittura tra le transazioni.
SELEZIONA * FROM EMPLOYEE;		viene inserita la riga (5, 'E', 50).	Esistono solo 4 righe.

In Babelfish, le transazioni simultanee eseguite con Isolation Level serializable falliranno con un errore di anomalia di serializzazione se l'esecuzione di queste transazioni non è coerente con tutte le possibili esecuzioni seriali (una alla volta) di tali transazioni.

ANOMALIA DI SERIALIZZAZIONE
Transazione 1	Transazione 2	SQL Server serializzabile	Serializzabile Babelfish
BEGIN TRANSACTION	BEGIN TRANSACTION
IMPOSTA IL LIVELLO DI ISOLAMENTO DELLE TRANSAZIONI SERIALIZZABILE;	IMPOSTARE IL LIVELLO DI ISOLAMENTO DELLE TRANSAZIONI SERIALIZZABILE;
SELEZIONA * DAL DIPENDENTE;
AGGIORNA il set di età del dipendente = 5 DOVE età = 10;
	SELEZIONA * DAL DIPENDENTE;	La transazione 2 è bloccata fino al completamento della transazione 1.	La transazione 2 procede senza alcun blocco.
	AGGIORNA il set di età del dipendente = 35 anni DOVE età = 30;
COMMIT		La transazione 1 viene eseguita correttamente.	La transazione 1 viene confermata per prima ed è in grado di eseguirla con successo.
	COMMIT	La transazione 2 viene eseguita correttamente.	Il commit della transazione 2 non riesce a causa di un errore di serializzazione, l'intera transazione è stata annullata. Riprova la transazione 2.
SELEZIONA * DAL DIPENDENTE;		Le modifiche di entrambe le transazioni sono visibili.	La transazione 2 è stata annullata. Vengono visualizzate solo le modifiche alla transazione 1.

In Babelfish, l'anomalia di serializzazione è possibile solo se tutte le transazioni simultanee vengono eseguite al livello di isolamento SERIALIZABLE. Ad esempio, prendiamo l'esempio precedente ma impostiamo invece la transazione 2 sul livello di isolamento REPEATABLE READ.

Transazione 1	Transazione 2	Livelli di isolamento di SQL Server	Livelli di isolamento di Babelfish
BEGIN TRANSACTION	BEGIN TRANSACTION
IMPOSTARE IL LIVELLO DI ISOLAMENTO DELLE TRANSAZIONI SERIALIZZABILE;	IMPOSTA IL LIVELLO DI ISOLAMENTO DELLE TRANSAZIONI (LETTURA RIPETIBILE);
SELEZIONA * DAL DIPENDENTE;
AGGIORNA il set di età del dipendente = 5 DOVE età = 10;
	SELEZIONA * DAL DIPENDENTE;	La transazione 2 è bloccata fino al completamento della transazione 1.	La transazione 2 procede senza alcun blocco.
	AGGIORNA il set di età del dipendente = 35 anni DOVE età = 30;
COMMIT		La transazione 1 viene eseguita correttamente.	La transazione 1 viene eseguita correttamente.
	COMMIT	La transazione 2 viene eseguita correttamente.	La transazione 2 viene eseguita correttamente.
SELEZIONA * DAL DIPENDENTE;		Le modifiche di entrambe le transazioni sono visibili.	Le modifiche di entrambe le transazioni sono visibili.