Niveles de aislamiento de transacciones en Babelfish

Babelfish admite los niveles de aislamiento de transacciones READ UNCOMMITTED, READ COMMITTED y SNAPSHOT. A partir de la versión 3.4 de Babelfish, se admiten los niveles de aislamiento adicionales: REPETIBLE READ y SERIALIZABLE. Todos los niveles de aislamiento de Babelfish son compatibles con el comportamiento de los niveles de aislamiento correspondientes en PostgreSQL. SQL Server y Babelfish utilizan diferentes mecanismos subyacentes para implementar los niveles de aislamiento de las transacciones (bloqueo del acceso simultáneo, bloqueo de las transacciones, gestión de errores, etc.). Además, existen algunas diferencias sutiles en el posible funcionamiento del acceso simultáneo para diferentes cargas de trabajo. Para obtener más información sobre este comportamiento de PostgreSQL, consulte Aislamiento de transacciones.

Información general sobre los niveles de aislamiento de transacciones

Los niveles de aislamiento de transacciones originales de SQL Server se basan en un bloqueo pesimista en el que solo existe una copia de los datos y las consultas deben bloquear los recursos, por ejemplo las filas, antes de acceder a ellos. Más adelante, se introdujo una variación del nivel de aislamiento confirmado de lectura. Esto permite el uso de versiones de filas para proporcionar una mejor simultaneidad entre lectores y escritores mediante el acceso sin bloqueo. Además, está disponible un nuevo nivel de aislamiento denominado Instantánea. También utiliza versiones de filas para ofrecer una mayor simultaneidad que el nivel de aislamiento REPEATABLE READ, ya que evita el bloqueo compartido de los datos de lectura, que se retienen hasta el final de la transacción.

A diferencia de SQL Server, todos los niveles de aislamiento de transacciones de Babelfish se basan en el bloqueo positivo (MVCC). Cada transacción ve una instantánea de los datos al principio de la declaración (READ COMMITTED) o al principio de la transacción (REPEATABLE READ, SERIALIZABLE), independientemente del estado actual de los datos subyacentes. Por lo tanto, el comportamiento de ejecución de las transacciones simultáneas en Babelfish puede diferir del de SQL Server.

Por ejemplo, consideremos una transacción con un nivel de aislamiento SERIALIZABLE que inicialmente está bloqueada en SQL Server, pero que se realiza correctamente más adelante. Puede terminar fallando en Babelfish debido a un conflicto de serialización con una transacción simultánea que lee o actualiza las mismas filas. También puede haber casos en los que la ejecución de varias transacciones simultáneas arroje un resultado final diferente en Babelfish en comparación con SQL Server. Las aplicaciones que utilizan niveles de aislamiento deben probarse exhaustivamente para detectar escenarios de simultaneidad.

Niveles de aislamiento en SQL Server	Nivel de aislamiento de Babelfish	Nivel de aislamiento de PostgreSQL	Comentarios
READ UNCOMMITTED	READ UNCOMMITTED	READ UNCOMMITTED	Read Uncommitted es lo mismo que Read Committed en Babelfish/PostgreSQL
READ COMMITTED	READ COMMITTED	READ COMMITTED	El Read Committed de SQL Server se basa en bloqueos negativos, mientras que el Read Committed de Babelfish se basa en instantáneas (MVCC).
READ COMMITTED SNAPSHOT	READ COMMITTED	READ COMMITTED	Ambas están basadas en instantáneas (MVCC), pero no son exactamente iguales.
SNAPSHOT	SNAPSHOT	REPEATABLE READ	Exactamente igual.
REPEATABLE READ	REPEATABLE READ	REPEATABLE READ	El Repeatable Read de SQL Server se basa en el bloqueo negativo, mientras que el Repeatable Read de Babelfish se basa en instantáneas (MVCC).
SERIALIZABLE	SERIALIZABLE	SERIALIZABLE	El Serializable de SQL Server se basa en un aislamiento negativo y el Serializable de Babelfish se basa en instantáneas (MVCC).

nota

Las sugerencias de la tabla no son compatibles actualmente y su comportamiento se controla mediante la escotilla de escape predefinida de Babelfish escape_hatch_table_hints.

Configuración de los niveles de aislamiento de las transacciones

Utilice el siguiente comando para establecer el nivel de aislamiento de las transacciones:

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

Activación o desactivación de los niveles de aislamiento de transacciones

Los niveles de aislamiento de transacciones REPEATABLE READ y SERIALIZABLE están desactivados de forma predeterminada en Babelfish y hay que activarlos de manera explícita configurando la escotilla de escape babelfishpg_tsql.isolation_level_serializable o babelfishpg_tsql.isolation_level_repeatable_read como pg_isolation utilizando sp_babelfish_configure. Para obtener más información, consulte Administración de la gestión de errores de Babelfish con escotillas de escape.

A continuación se muestran ejemplos de cómo activar o desactivar el uso de REPEATABLE READ y SERIALIZABLE en la sesión actual configurando sus respectivas escotillas de escape. Si lo desea, puede incluir un parámetro server para establecer la escotilla de escape de la sesión actual y de todas las sesiones nuevas subsiguientes.

Para habilitar el uso de SET TRANSACTION ISOLATION LEVEL REPEATABLE READ solo en la sesión actual.

EXECUTE sp_babelfish_configure 'isolation_level_repeatable_read', 'pg_isolation'
	        

Para habilitar el uso de SET TRANSACTION ISOLATION LEVEL REPEATABLE READ en la sesión actual y en todas las nuevas sesiones simultáneas.

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

Para deshabilitar el uso de SET TRANSACTION ISOLATION LEVEL REPEATABLE READ en la sesión actual y en las nuevas sesiones simultáneas.

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

Para habilitar el uso de SET TRANSACTION ISOLATION LEVEL SERIALIZABLE solo en la sesión actual.

EXECUTE sp_babelfish_configure 'isolation_level_serializable', 'pg_isolation'
	        

Para habilitar el uso de SET TRANSACTION ISOLATION LEVEL SERIALIZABLE en la sesión actual y en todas las nuevas sesiones simultáneas.

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

Para deshabilitar el uso de SET TRANSACTION ISOLATION LEVEL SERIALIZABLE en la sesión actual y en las nuevas sesiones simultáneas.

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

Diferencias entre los niveles de aislamiento de Babelfish y SQL

A continuación se muestran algunos ejemplos sobre los matices de la forma en que SQL Server y Babelfish implementan los niveles de aislamiento ANSI.

nota

• Isolation Level Repeatable Read y Snapshot son iguales en Babelfish.

• Isolation Level Read Uncommitted y Read Committed son iguales en Babelfish.

En el ejemplo siguiente, se muestra cómo crear la tabla base para todos los ejemplos que se mencionan a continuación:

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);

Temas

• BABELFISH READ UNCOMMITTED frente a SQL SERVER READ UNCOMMITTED ISOLATION LEVEL
• BABELFISH READ COMMITTED frente a SQL SERVER READ COMMITTED ISOLATION LEVEL
• BABELFISH READ COMMITTED frente a SQL SERVER READ COMMITTED SNAPSHOT ISOLATION LEVEL
• BABELFISH REPEATABLE READ frente a SQL SERVER REPEATABLE READ ISOLATION LEVEL
• BABELFISH SERIALIZABLE frente a SQL SERVER SERIALIZABLE ISOLATION LEVEL

BABELFISH READ UNCOMMITTED frente a SQL SERVER READ UNCOMMITTED ISOLATION LEVEL

DIRTY READS IN SQL SERVER
Transacción 1	Transacción 2	Read Uncommitted de SQL Server	Read Uncommitted de Babelfish
BEGIN TRANSACTION	BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
	UPDATE employee SET age=0;	Actualización realizada correctamente.	Actualización realizada correctamente.
	INSERT INTO employee VALUES (4, 'D', 40);	La inserción se ha realizado correctamente.	La inserción se ha realizado correctamente.
SELECT * FROM employee;		La transacción 1 puede ver los cambios no confirmados de la transacción 2.	Igual que Read Committed en Babelfish. Los cambios no confirmados de la Transacción 2 no son visibles en la Transacción 1.
	COMMIT
SELECT * FROM employee;		Ve los cambios efectuados por la transacción 2.	Ve los cambios efectuados por la transacción 2.

BABELFISH READ COMMITTED frente a SQL SERVER READ COMMITTED ISOLATION LEVEL

BLOQUEO DE LECTURA / ESCRITURA
Transacción 1	Transacción 2	Read Committed de SQL Server	Read Committed de Babelfish
BEGIN TRANSACTION	BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;	SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT * FROM employee;
	UPDATE employee SET age=100 WHERE id = 1;	Actualización realizada correctamente.	Actualización realizada correctamente.
UPDATE employee SET age = 0 WHERE age IN (SELECT MAX(age) FROM employee);		Paso bloqueado hasta que se confirme la transacción 2.	Los cambios en la transacción 2 aún no están visibles. Actualiza la fila con id=3.
	COMMIT	La transacción 2 se confirma correctamente. La transacción 1 ahora está desbloqueada y recibe la actualización de la transacción 2.	La transacción 2 se confirma correctamente.
SELECT * FROM employee;		La transacción 1 actualiza la fila con el id = 1.	La transacción 1 actualiza la fila con el id = 3.

BABELFISH READ COMMITTED frente a SQL SERVER READ COMMITTED SNAPSHOT ISOLATION LEVEL

COMPORTAMIENTO DE BLOQUEO EN LAS NUEVAS FILAS INSERTADAS
Transacción 1	Transacción 2	Read Committed Snapshot de SQL Server	Read Committed de Babelfish
BEGIN TRANSACTION	BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;	SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
INSERT INTO employee VALUES (4, 'D', 40);
	UPDATE employee SET age = 99;	Paso bloqueado hasta que se confirme la transacción 1. La fila insertada está bloqueada por la transacción 1.	Se actualizaron tres filas. La fila recién insertada aún no está visible.
COMMIT		Confirmación realizada correctamente. La transacción 2 ahora está desbloqueada.	Confirmación realizada correctamente.
	SELECT * FROM employee;	Las 4 filas tienen una age=99.	La fila con un id = 4 tiene un valor de antigüedad de 40, ya que la transacción 2 no la pudo ver durante la consulta de actualización. Las demás filas se actualizan a una age=99.

BABELFISH REPEATABLE READ frente a SQL SERVER REPEATABLE READ ISOLATION LEVEL

COMPORTAMIENTO DE BLOQUEO DE LECTURA / ESCRITURA
Transacción 1	Transacción 2	Repeatable Read de SQL Server	Repeatable Read de Babelfish
BEGIN TRANSACTION	BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;	SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM employee;
UPDATE employee SET name='A_TXN1' WHERE id=1;
	SELECT * FROM employee WHERE id != 1;
	SELECT * FROM employee;	La transacción 2 se bloquea hasta que se confirme la transacción 1.	La transacción 2 se lleva a cabo con normalidad.
COMMIT
	SELECT * FROM employee;	La actualización de la transacción 1 está visible.	La actualización de la transacción 1 no está visible.
COMMIT
	SELECT * FROM employee;	ve la actualización de la transacción 1.	ve la actualización de la transacción 1.

COMPORTAMIENTO DE BLOQUEO DE ESCRITURA / ESCRITURA
Transacción 1	Transacción 2	Repeatable Read de SQL Server	Repeatable Read de Babelfish
BEGIN TRANSACTION	BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;	SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
UPDATE employee SET name='A_TXN1' WHERE id=1;
	UPDATE employee SET name='A_TXN2' WHERE id=1;	Transacción 2 bloqueada.	Transacción 2 bloqueada.
COMMIT		La confirmación se ha realizado correctamente y la transacción 2 se ha desbloqueado.	La confirmación se ha realizado correctamente y la transacción 2 falló debido a un error de tipo “No se pudo serializar el acceso debido a una actualización simultánea”.
	COMMIT	Confirmación realizada correctamente.	La transacción 2 ya se ha cancelado.
	SELECT * FROM employee;	La fila con id=1 tiene name='A_TX2'.	La fila con id=1 tiene name='A_TX1'.

LECTURA FANTASMA
Transacción 1	Transacción 2	Repeatable Read de SQL Server	Repeatable Read de Babelfish
BEGIN TRANSACTION	BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;	SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM employee;
	INSERT INTO employee VALUES (4, 'NewRowName', 20);	La transacción 2 se lleva a cabo sin ningún tipo de bloqueo.	La transacción 2 se lleva a cabo sin ningún tipo de bloqueo.
	SELECT * FROM employee;	La fila recién insertada está visible.	La fila recién insertada está visible.
	COMMIT
SELECT * FROM employee;		La nueva fila insertada por la transacción 2 está visible.	La nueva fila insertada por la transacción 2 no está visible.
COMMIT
SELECT * FROM employee;		La fila recién insertada está visible.	La fila recién insertada está visible.

RESULTADOS FINALES DIFERENTES
Transacción 1	Transacción 2	Repeatable Read de SQL Server	Repeatable Read de Babelfish
BEGIN TRANSACTION	BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;	SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
UPDATE employee SET age = 100 WHERE age IN (SELECT MIN(age) FROM employee);		La transacción 1 actualiza la fila con el id 1.	La transacción 1 actualiza la fila con el id 1.
	UPDATE employee SET age = 0 WHERE age IN (SELECT MAX(age) FROM employee);	La transacción 2 está bloqueada porque la instrucción SELECT intenta leer las filas bloqueadas por la consulta UPDATE en la transacción 1.	La transacción 2 se realiza sin ningún bloqueo, ya que la lectura nunca se bloquea, la instrucción SELECT se ejecuta y, finalmente, la fila con el id = 3 se actualiza, ya que los cambios en la transacción 1 aún no están visibles.
	SELECT * FROM employee;	Este paso se ejecuta después de que se haya confirmado la transacción 1. La fila con el id = 1 se actualiza con la transacción 2 del paso anterior y está visible aquí.	La fila con un id = 3 se actualiza mediante la transacción 2.
COMMIT		La transacción 2 ahora está desbloqueada.	Confirmación realizada correctamente.
	COMMIT
SELECT * FROM employee;		Ambas transacciones ejecutan una actualización en la fila con un id = 1.	Las distintas filas se actualizan por las transacciones 1 y 2.

BABELFISH SERIALIZABLE frente a SQL SERVER SERIALIZABLE ISOLATION LEVEL

BLOQUEOS DE RANGO EN SQL SERVER
Transacción 1	Transacción 2	Serializable en SQL Server	Serializable en Babelfish
BEGIN TRANSACTION	BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM employee;
	INSERT INTO employee VALUES (4, 'D', 35);	La transacción 2 se bloquea hasta que se confirme la transacción 1.	La transacción 2 se lleva a cabo sin ningún tipo de bloqueo.
	SELECT * FROM employee;
COMMIT		La transacción 1 se confirma correctamente. La transacción 2 ahora está desbloqueada.	La transacción 1 se confirma correctamente.
	COMMIT
SELECT * FROM employee;		La fila recién insertada está visible.	La fila recién insertada está visible.

RESULTADOS FINALES DIFERENTES
Transacción 1	Transacción 2	Serializable en SQL Server	Serializable en Babelfish
BEGIN TRANSACTION	BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
	INSERT INTO employee VALUES (4, 'D', 40);
UPDATE employee SET age =99 WHERE id = 4;		La transacción 1 se bloquea hasta que se confirme la transacción 2.	La transacción 1 se lleva a cabo sin ningún tipo de bloqueo.
	COMMIT	La transacción 2 se confirma correctamente. La transacción 1 ahora está desbloqueada.	La transacción 2 se confirma correctamente.
COMMIT
SELECT * FROM employee;		La fila recién insertada es visible con un valor de age = 99.	La fila recién insertada es visible con un valor de age = 40.

INSERCIÓN EN TABLA CON UNA RESTRICCIÓN ÚNICA
Transacción 1	Transacción 2	Serializable en SQL Server	Serializable en Babelfish
BEGIN TRANSACTION	BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
	INSERT INTO employee VALUES (4, 'D', 40);
INSERT INTO employee VALUES ((SELECT MAX(id)+1 FROM employee), 'E', 50);		La transacción 1 se bloquea hasta que se confirme la transacción 2.	La transacción 1 se bloquea hasta que se confirme la transacción 2.
	COMMIT	La transacción 2 se confirma correctamente. La transacción 1 ahora está desbloqueada.	La transacción 2 se confirma correctamente. La transacción 1 abortada por un error de tipo “el valor clave duplicado infringe una restricción única”.
COMMIT		La transacción 1 se confirma correctamente.	La confirmación de la transacción 1 falla y no se pudo serializar el acceso debido a las dependencias de lectura/escritura entre las transacciones.
SELECT * FROM employee;		se inserta la fila (5, 'E', 50).	Solo existen 4 filas.

En Babelfish, las transacciones simultáneas que se ejecutan con nivel de aislamiento Serializable fallarán y generarán un error de anomalía de serialización si la ejecución de estas transacciones no es coherente con todas las posibles ejecuciones en serie (una a la vez) de esas transacciones.

ANOMALÍA DE SERIALIZACIÓN
Transacción 1	Transacción 2	Serializable en SQL Server	Serializable en Babelfish
BEGIN TRANSACTION	BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM employee;
UPDATE employee SET age=5 WHERE age=10;
	SELECT * FROM employee;	La transacción 2 se bloquea hasta que se confirme la transacción 1.	La transacción 2 se lleva a cabo sin ningún tipo de bloqueo.
	UPDATE employee SET age=35 WHERE age=30;
COMMIT		La transacción 1 se confirma correctamente.	La transacción 1 se confirma primero y puede confirmarse correctamente.
	COMMIT	La transacción 2 se confirma correctamente.	La confirmación de la transacción 2 falla con un error de serialización, se revierte toda la transacción. Vuelva a intentar la transacción 2.
SELECT * FROM employee;		Los cambios de ambas transacciones son visibles.	La transacción 2 se revirtió. Solo se ven los cambios en la transacción 1.

En Babelfish, la anomalía de serialización solo es posible si todas las transacciones simultáneas se ejecutan en el nivel de aislamiento SERIALIZABLE. Por ejemplo, tomemos el ejemplo anterior, pero en su lugar pongamos la transacción 2 en el nivel de aislamiento REPEATABLE READ.

Transacción 1	Transacción 2	Niveles de aislamiento de SQL Server	Niveles de aislamiento de Babelfish
BEGIN TRANSACTION	BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;	SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM employee;
UPDATE employee SET age=5 WHERE age=10;
	SELECT * FROM employee;	La transacción 2 se bloquea hasta que se confirme la transacción 1.	La transacción 2 se lleva a cabo sin ningún tipo de bloqueo.
	UPDATE employee SET age=35 WHERE age=30;
COMMIT		La transacción 1 se confirma correctamente.	La transacción 1 se confirma correctamente.
	COMMIT	La transacción 2 se confirma correctamente.	La transacción 2 se confirma correctamente.
SELECT * FROM employee;		Los cambios de ambas transacciones son visibles.	Los cambios de ambas transacciones son visibles.