Babelfish READ UNCOMMITTED compared with SQL Server READ UNCOMMITTED isolation level Babelfish READ COMMITTED compared with SQL Server READ COMMITTED isolation level Babelfish READ COMMITTED compared with SQL Server READ COMMITTED SNAPSHOT isolation level Babelfish REPEATABLE READ compared with SQL Server REPEATABLE READ isolation level Babelfish SERIALIZABLE compared with SQL Server SERIALIZABLE isolation level

Comparing Babelfish and SQL Server isolation levels

Below are a few examples on the nuances in how SQL Server and Babelfish implement the ANSI isolation levels.

Note

Isolation level REPEATABLE READ and SNAPSHOT are the same in Babelfish.
Isolation level READ UNCOMMITTED and READ COMMITTED are the same in Babelfish.

The following example shows how to create the base table for all the examples mentioned below:


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

Topics

Babelfish READ UNCOMMITTED compared with SQL Server READ UNCOMMITTED isolation level
Babelfish READ COMMITTED compared with SQL Server READ COMMITTED isolation level
Babelfish READ COMMITTED compared with SQL Server READ COMMITTED SNAPSHOT isolation level
Babelfish REPEATABLE READ compared with SQL Server REPEATABLE READ isolation level
Babelfish SERIALIZABLE compared with SQL Server SERIALIZABLE isolation level

Babelfish `READ UNCOMMITTED` compared with SQL Server `READ UNCOMMITTED` isolation level

The following table provides details on the dirty reads when concurrent transactions are executed. It shows observed results when using the READ UNCOMMITTED isolation level in SQL Server compared to the Babelfish implementation.

Transaction 1	Transaction 2	SQL Server `READ UNCOMMITTED`	Babelfish `READ UNCOMMITTED`
`BEGIN TRANSACTION`	`BEGIN TRANSACTION`	None	None
`SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;`	`SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;`	None	None
Idle in transaction	`UPDATE employee SET age=0;`	Update successful.	Update successful.
Idle in transaction	`INSERT INTO employee VALUES (4, 'D', 40);`	Insert successful.	Insert successful.
`SELECT * FROM employee;`	Idle in transaction	Transaction 1 can see uncommitted changes from transaction 2.	Same as `READ COMMITTED` in Babelfish. Uncommitted changes from transaction 2 are not visible to transaction 1.
Idle in transaction	`COMMIT`	None	None
`SELECT * FROM employee;`	Idle in transaction	Sees the changes committed by transaction 2.	Sees the changes committed by transaction 2.

Babelfish `READ COMMITTED` compared with SQL Server `READ COMMITTED` isolation level

The following table provides details on the read-write blocking behavior when concurrent transactions are executed. It shows observed results when using the READ COMMITTED isolation level in SQL Server compared to the Babelfish implementation.

Transaction 1	Transaction 2	SQL Server `READ COMMITTED`	Babelfish `READ COMMITTED`
`BEGIN TRANSACTION`	`BEGIN TRANSACTION`	None	None
`SET TRANSACTION ISOLATION LEVEL READ COMMITTED;`	`SET TRANSACTION ISOLATION LEVEL READ COMMITTED;`	None	None
`SELECT * FROM employee;`	Idle in transaction	None	None
Idle in transaction	`UPDATE employee SET age=100 WHERE id = 1;`	Update successful.	Update successful.
`UPDATE employee SET age = 0 WHERE age IN (SELECT MAX(age) FROM employee);`	Idle in transaction	Step blocked until transaction 2 commits.	Transaction 2 changes is not visible yet. Updates row with id=3.
Idle in transaction	`COMMIT`	Transaction 2 commits successfully. Transaction 1 is now unblocked and sees the update from transaction 2.	Transaction 2 commits successfully.
`SELECT * FROM employee;`	Idle in transaction	Transaction 1 updates row with id = 1.	Transaction 1 updates row with id = 3.

Babelfish `READ COMMITTED` compared with SQL Server `READ COMMITTED SNAPSHOT` isolation level

The following table provides details on the blocking behavior of the newly inserted rows when concurrent transactions are executed. It shows observed results when using the READ COMMITTED SNAPSHOT isolation level in SQL Server compared to the READ COMMITTED Babelfish implementation.

Transaction 1	Transaction 2	SQL Server `READ COMMITTED SNAPSHOT`	Babelfish `READ COMMITTED`
`BEGIN TRANSACTION`	`BEGIN TRANSACTION`	None	None
`SET TRANSACTION ISOLATION LEVEL READ COMMITTED;`	`SET TRANSACTION ISOLATION LEVEL READ COMMITTED;`	None	None
`INSERT INTO employee VALUES (4, 'D', 40);`	Idle in transaction	None	None
Idle in transaction	`UPDATE employee SET age = 99;`	Step is blocked until transaction 1 commits. Inserted row is locked by transaction 1.	Updated three rows. The newly inserted row is not visible yet.
`COMMIT`	Idle in transaction	Commit successful. Transaction 2 is now unblocked.	Commit successful.
Idle in transaction	`SELECT * FROM employee;`	All 4 rows have age=99.	Row with id = 4 has age value 40 since it was not visible to transaction 2 during update query. Other rows are updated to age=99.

Babelfish `REPEATABLE READ` compared with SQL Server `REPEATABLE READ` isolation level

The following table provides details on the read-write blocking behavior when concurrent transactions are executed. It shows observed results when using the REPEATABLE READ isolation level in SQL Server compared to the REPEATABLE READ Babelfish implementation.

Transaction 1	Transaction 2	SQL Server `REPEATABLE READ`	Babelfish `REPEATABLE READ`
`BEGIN TRANSACTION`	`BEGIN TRANSACTION`	None	None
`SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;`	`SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;`	None	None
`SELECT * FROM employee;`	Idle in transaction	None	None
`UPDATE employee SET name='A_TXN1' WHERE id=1;`	Idle in transaction	None	None
Idle in transaction	`SELECT * FROM employee WHERE id != 1;`	None	None
Idle in transaction	`SELECT * FROM employee;`	Transaction 2 is blocked until transaction 1 commits.	Transaction 2 proceeds normally.
`COMMIT`	Idle in transaction	None	None
Idle in transaction	`SELECT * FROM employee;`	Update from transaction 1 is visible.	Update from transaction 1 is not visible.
`COMMIT`	Idle in transaction	None	None
Idle in transaction	`SELECT * FROM employee;`	sees the update from transaction 1.	sees the update from transaction 1.

The following table provides details on the write-write blocking behavior when concurrent transactions are executed. It shows observed results when using the REPEATABLE READ isolation level in SQL Server compared to the REPEATABLE READ Babelfish implementation.

Transaction 1	Transaction 2	SQL Server `REPEATABLE READ`	Babelfish `REPEATABLE READ`
`BEGIN TRANSACTION`	`BEGIN TRANSACTION`	None	None
`SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;`	`SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;`	None	None
`UPDATE employee SET name='A_TXN1' WHERE id=1;`	Idle in transaction	None	None
Idle in transaction	`UPDATE employee SET name='A_TXN2' WHERE id=1;`	Transaction 2 blocked.	Transaction 2 blocked.
`COMMIT`	Idle in transaction	Commit successful and transaction 2 has been unblocked.	Commit successful and transaction 2 fails with error could not serialize access due to concurrent update.
Idle in transaction	`COMMIT`	Commit successful.	Transaction 2 has already been aborted.
Idle in transaction	`SELECT * FROM employee;`	Row with id=1 has name='A_TX2'.	Row with id=1 has name='A_TX1'.

The following table provides details on the phantom reads behavior when concurrent transactions are executed. It shows observed results when using the REPEATABLE READ isolation level in SQL Server compared to the REPEATABLE READ Babelfish implementation.

Transaction 1	Transaction 2	SQL Server `REPEATABLE READ`	Babelfish `REPEATABLE READ`
`BEGIN TRANSACTION`	`BEGIN TRANSACTION`	None	None
`SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;`	`SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;`	None	None
`SELECT * FROM employee;`	Idle in transaction	None	None
Idle in transaction	`INSERT INTO employee VALUES (4, 'NewRowName', 20);`	Transaction 2 proceeds without any blocking.	Transaction 2 proceeds without any blocking.
Idle in transaction	`SELECT * FROM employee;`	Newly inserted row is visible.	Newly inserted row is visible.
Idle in transaction	`COMMIT`	None	None
`SELECT * FROM employee;`	Idle in transaction	New row inserted by transaction 2 is visible.	New row inserted by transaction 2 is not visible.
`COMMIT`	Idle in transaction	None	None
`SELECT * FROM employee;`	Idle in transaction	Newly inserted row is visible.	Newly inserted row is visible.

The following table provides details when concurrent transactions are executed and the different final results when using the REPEATABLE READ isolation level in SQL Server compared to the REPEATABLE READ Babelfish implementation.

Transaction 1	Transaction 2	SQL Server `REPEATABLE READ`	Babelfish `REPEATABLE READ`
`BEGIN TRANSACTION`	`BEGIN TRANSACTION`	None	None
`SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;`	`SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;`	None	None
`UPDATE employee SET age = 100 WHERE age IN (SELECT MIN(age) FROM employee);`	Idle in transaction	Transaction 1 updates row with id 1.	Transaction 1 updates row with id 1.
Idle in transaction	`UPDATE employee SET age = 0 WHERE age IN (SELECT MAX(age) FROM employee);`	Transaction 2 is blocked since the SELECT statement tries to read rows locked by UPDATE query in transaction 1.	Transaction 2 proceeds without any blocking since read is never blocked, SELECT statement executes and finally row with id = 3 is updated since transaction 1 changes are not visible yet.
Idle in transaction	`SELECT * FROM employee;`	This step is executed after transaction 1 has committed. Row with id = 1 is updated by transaction 2 in previous step and is visible here.	Row with id = 3 is updated by Transaction 2.
`COMMIT`	Idle in transaction	Transaction 2 is now unblocked.	Commit successful.
Idle in transaction	`COMMIT`	None	None
`SELECT * FROM employee;`	Idle in transaction	Both transaction execute update on row with id = 1.	Different rows are updated by transaction 1 and 2.

Babelfish `SERIALIZABLE` compared with SQL Server `SERIALIZABLE` isolation level

The following table provides details on the range locks when concurrent transactions are executed. It shows observed results when using the SERIALIZABLE isolation level in SQL Server compared to the SERIALIZABLE Babelfish implementation.

Transaction 1	Transaction 2	SQL Server `SERIALIZABLE`	Babelfish `SERIALIZABLE`
`BEGIN TRANSACTION`	`BEGIN TRANSACTION`	None	None
`SET TRANSACTION ISOLATION LEVEL SERILAIZABLE;`	`SET TRANSACTION ISOLATION LEVEL SERILAIZABLE;`	None	None
`SELECT * FROM employee;`	Idle in transaction	None	None
Idle in transaction	`INSERT INTO employee VALUES (4, 'D', 35);`	Transaction 2 is blocked until transaction 1 commits.	Transaction 2 proceeds without any blocking.
Idle in transaction	`SELECT * FROM employee;`	None	None
`COMMIT`	Idle in transaction	Transaction 1 commits successfully. Transaction 2 is now unblocked.	Transaction 1 commits successfully.
Idle in transaction	`COMMIT`	None	None
`SELECT * FROM employee;`	Idle in transaction	Newly inserted row is visible.	Newly inserted row is visible.

The following table provides details when concurrent transactions are executed and the different final results when using the SERIALIZABLE isolation level in SQL Server compared to the SERIALIZABLE Babelfish implementation.

Transaction 1	Transaction 2	SQL Server `SERIALIZABLE`	Babelfish `SERIALIZABLE`
`BEGIN TRANSACTION`	`BEGIN TRANSACTION`	None	None
`SET TRANSACTION ISOLATION LEVEL SERILAIZABLE;`	`SET TRANSACTION ISOLATION LEVEL SERILAIZABLE;`	None	None
Idle in transaction	`INSERT INTO employee VALUES (4, 'D', 40);`	None	None
`UPDATE employee SET age =99 WHERE id = 4;`	Idle in transaction	Transaction 1 is blocked until transaction 2 commits.	Transaction 1 proceeds without any blocking.
Idle in transaction	`COMMIT`	Transaction 2 commits successfully. Transaction 1 is now unblocked.	Transaction 2 commits successfully.
`COMMIT`	Idle in transaction	None	None
`SELECT * FROM employee;`	Idle in transaction	Newly inserted row is visible with age value = 99.	Newly inserted row is visible with age value = 40.

The following table provides details when you INSERT into a table with unique constraint. It shows observed results when using the SERIALIZABLE isolation level in SQL Server compared to the SERIALIZABLE Babelfish implementation.

Transaction 1	Transaction 2	SQL Server `SERIALIZABLE`	Babelfish `SERIALIZABLE`
`BEGIN TRANSACTION`	`BEGIN TRANSACTION`	None	None
`SET TRANSACTION ISOLATION LEVEL SERILAIZABLE;`	`SET TRANSACTION ISOLATION LEVEL SERILAIZABLE;`	None	None
Idle in transaction	`INSERT INTO employee VALUES (4, 'D', 40);`	None	None
`INSERT INTO employee VALUES ((SELECT MAX(id)+1 FROM employee), 'E', 50);`	Idle in transaction	Transaction 1 is blocked until transaction 2 commits.	Transaction 1 is blocked until transaction 2 commits.
Idle in transaction	`COMMIT`	Transaction 2 commits successfully. Transaction 1 is now unblocked.	Transaction 2 commits successfully. Transaction 1 aborted with error duplicate key value violates unique constraint.
`COMMIT`	Idle in transaction	Transaction 1 commits successfully.	Transaction 1 commits fails with could not serialize access due to read or write dependencies among transactions.
`SELECT * FROM employee;`	Idle in transaction	row (5, 'E', 50) is inserted.	Only 4 rows exists.

In Babelfish, concurrent transactions running with Isolation Level serializable will fail with serialization anomaly error if the execution of these transaction is inconsistent with all possible serial (one at a time) executions of those transactions.

The following tables provides details on serialization anomaly when concurrent transactions are executed. It shows observed results when using the SERIALIZABLE isolation level in SQL Server compared to the SERIALIZABLE Babelfish implementation.

Transaction 1	Transaction 2	SQL Server `SERIALIZABLE`	Babelfish `SERIALIZABLE`
`BEGIN TRANSACTION`	`BEGIN TRANSACTION`	None	None
`SET TRANSACTION ISOLATION LEVEL SERILAIZABLE;`	`SET TRANSACTION ISOLATION LEVEL SERILAIZABLE;`	None	None
`SELECT * FROM employee;`	Idle in transaction	None	None
`UPDATE employee SET age=5 WHERE age=10;`	Idle in transaction	None	None
Idle in transaction	`SELECT * FROM employee;`	Transaction 2 is blocked until transaction 1 commits.	Transaction 2 proceeds without any blocking.
Idle in transaction	`UPDATE employee SET age=35 WHERE age=30;`	None	None
`COMMIT`	Idle in transaction	Transaction 1 commits successfully.	Transaction 1 is committed first and is able to commit successfully.
Idle in transaction	`COMMIT`	Transaction 2 commits successfully.	Transaction 2 commit fails with serialization error, the whole transaction has been rolled back. Retry transaction 2.
`SELECT * FROM employee;`	Idle in transaction	Changes from both transactions are visible.	Transaction 2 was rolled back. Only transaction 1 changes are seen.

In Babelfish, serialization anomaly is only possible if all the concurrent transactions are executing at isolation level SERIALIZABLE. In the following table, lets take the above example but set transaction 2 to isolation level REPEATABLE READ instead.

Transaction 1	Transaction 2	SQL Server isolation levels	Babelfish isolation levels
`BEGIN TRANSACTION`	`BEGIN TRANSACTION`	None	None
`SET TRANSACTION ISOLATION LEVEL SERILAIZABLE;`	`SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;`	None	None
`SELECT * FROM employee;`	Idle in transaction	None	None
`UPDATE employee SET age=5 WHERE age=10;`	Idle in transaction	None	None
Idle in transaction	`SELECT * FROM employee;`	Transaction 2 is blocked until transaction 1 commits.	Transaction 2 proceeds without any blocking.
Idle in transaction	`UPDATE employee SET age=35 WHERE age=30;`	None	None
`COMMIT`	Idle in transaction	Transaction 1 commits successfully.	Transaction 1 commits successfully.
Idle in transaction	`COMMIT`	Transaction 2 commits successfully.	Transaction 2 commits successfully.
`SELECT * FROM employee;`	Idle in transaction	Changes from both transactions are visible.	Changes from both transactions are visible.

Javascript is disabled or is unavailable in your browser.

To use the Amazon Web Services Documentation, Javascript must be enabled. Please refer to your browser's Help pages for instructions.

Transaction isolation levels in Babelfish

Using Babelfish features with limited implementation