Transaction Isolation Levels in Babelfish - Amazon Aurora
Overview of the Transaction Isolation LevelsSetting up the Transaction Isolation LevelsEnabling or disabling Transaction Isolation LevelsDifferences between Babelfish and SQL Server Isolation Levels
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Transaction Isolation Levels in Babelfish

Babelfish supports Transaction Isolation Levels READ UNCOMMITTED, READ COMMITTED and SNAPSHOT. Starting from Babelfish 3.4 version additional Isolation Levels REPEATABLE READ and SERIALIZABLE are supported. All the Isolation Levels in Babelfish are supported with the behavior of corresponding Isolation Levels in PostgreSQL. SQL Server and Babelfish use different underlying mechanisms for implementing Transaction Isolation Levels (blocking for concurrent access, locks held by transactions, error handling etc). And, there are some subtle differences in how concurrent access may work out for different workloads. For more information on this PostgreSQL behavior, see Transaction Isolation.

Overview of the Transaction Isolation Levels

The original SQL Server Transaction Isolation Levels are based on pessimistic locking where only one copy of data exists and queries must lock resources such as rows before accessing them. Later, a variation of the Read Committed Isolation Level was introduced. This enables the use of row versions to provide better concurrency between readers and writers using non-blocking access. In addition, a new Isolation Level called Snapshot is available. It also uses row versions to provide better concurrency than REPEATABLE READ Isolation Level by avoiding shared locks on read data that are held till the end of the transaction.

Unlike SQL Server, all Transaction Isolation Levels in Babelfish are based on optimistic Locking (MVCC). Each transaction sees a snapshot of the data either at the beginning of the statement (READ COMMITTED) or at the beginning of the transaction (REPEATABLE READ, SERIALIZABLE), regardless of the current state of the underlying data. Therefore, the execution behavior of concurrent transactions in Babelfish might differ from SQL Server.

For example, consider a transaction with Isolation Level SERIALIZABLE that is initially blocked in SQL Server but succeeds later. It may end up failing in Babelfish due to a serialization conflict with a concurrent transaction that reads or updates the same rows. There could also be cases where executing multiple concurrent transactions yields a different final result in Babelfish as compared to SQL Server. Applications that use Isolation Levels, should be thoroughly tested for concurrency scenarios.

Isolation Levels in SQL Server Babelfish Isolation Level PostgreSQL Isolation Level Comments

READ UNCOMMITTED

READ UNCOMMITTED

READ UNCOMMITTED

Read Uncommitted is same as Read Committed in Babelfish/PostgreSQL

READ COMMITTED

READ COMMITTED

READ COMMITTED

SQL Server Read Committed is pessimistic locking based, Babelfish Read Committed is snapshot (MVCC) based.

READ COMMITTED SNAPSHOT

READ COMMITTED

READ COMMITTED

Both are snapshot (MVCC) based but not exactly same.

SNAPSHOT

SNAPSHOT

REPEATABLE READ

Exactly same.

REPEATABLE READ

REPEATABLE READ

REPEATABLE READ

SQL Server Repeatable Read is pessimistic locking based, Babelfish Repeatable Read is snapshot (MVCC) based.

SERIALIZABLE

SERIALIZABLE

SERIALIZABLE

SQL Server Serializable is pessimistic isolation, Babelfish Serializable is snapshot (MVCC) based.
Note

The table hints are not currently supported and their behavior is controlled by using the Babelfish predefined escape hatch escape_hatch_table_hints.

Setting up the Transaction Isolation Levels

Use the following command to set Transaction Isolation Level:


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

Enabling or disabling Transaction Isolation Levels

Transaction Isolation Levels REPEATABLE READ and SERIALIZABLE are disabled by default in Babelfish and you have to explicitly enable them by setting the babelfishpg_tsql.isolation_level_serializable or babelfishpg_tsql.isolation_level_repeatable_read escape hatch to pg_isolation using sp_babelfish_configure. For more information, see Managing Babelfish error handling with escape hatches.

Below are examples for enabling or disabling the use of REPEATABLE READ and SERIALIZABLE in the current session by setting their respective escape hatches. Optionally include server parameter to set the escape hatch for the current session as well as for all subsequent new sessions.

To enable the use of SET TRANSACTION ISOLATION LEVEL REPEATABLE READ in current session only. 

EXECUTE sp_babelfish_configure 'isolation_level_repeatable_read', 'pg_isolation'

To enable the use of SET TRANSACTION ISOLATION LEVEL REPEATABLE READ in current session and all consequent new sessions. 

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

To disable the use of SET TRANSACTION ISOLATION LEVEL REPEATABLE READ in current session and consequent new sessions. 

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

To enable the use of SET TRANSACTION ISOLATION LEVEL SERIALIZABLE in current session only. 

EXECUTE sp_babelfish_configure 'isolation_level_serializable', 'pg_isolation'

To enable the use of SET TRANSACTION ISOLATION LEVEL SERIALIZABLE in current session and all consequent new sessions. 

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

To disable the use of SET TRANSACTION ISOLATION LEVEL SERIALIZABLE in current session and consequent new sessions. 

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

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

BABELFISH READ UNCOMMITTED VS SQL SERVER READ UNCOMMITTED ISOLATION LEVEL

DIRTY READS IN SQL SERVER
Transaction 1 Transaction 2 SQL Server Read Uncommitted Babelfish Read Uncommitted

BEGIN TRANSACTION

BEGIN TRANSACTION

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

UPDATE employee SET age=0;

Update successful.

Update successful.

INSERT INTO employee VALUES (4, 'D', 40);

Insert successful.

Insert successful.

SELECT * FROM employee;

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.

COMMIT

SELECT * FROM employee;

Sees the changes committed by Transaction 2.

Sees the changes committed by Transaction 2.

BABELFISH READ COMMITTED VS SQL SERVER READ COMMITTED ISOLATION LEVEL

READ - WRITE BLOCKING
Transaction 1 Transaction 2 SQL Server Read Committed Babelfish Read Committed

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;

Update successful.

Update successful.

UPDATE employee SET age = 0 WHERE age IN (SELECT MAX(age) FROM employee);

Step blocked until Transaction 2 commits.

Transaction 2 changes is not visible yet. Updates row with id=3.

COMMIT

Transaction 2 commits successfully. Transaction 1 is now unblocked and sees the update from Transaction 2.

Transaction 2 commits successfully.

SELECT * FROM employee;

Transaction 1 updates row with id = 1.

Transaction 1 updates row with id = 3.

BABELFISH READ COMMITTED VS SQL SERVER READ COMMITTED SNAPSHOT ISOLATION LEVEL

BLOCKING BEHAVIOUR ON NEW INSERTED ROWS
Transaction 1 Transaction 2 SQL Server Read Committed Snapshot Babelfish Read Committed

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;

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

Commit successful. Transaction 2 is now unblocked.

Commit successful.

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 VS SQL SERVER REPEATABLE READ ISOLATION LEVEL

READ / WRITE BLOCKING BEHAVIOR
Transaction 1 Transaction 2 SQL Server Repeatable Read Babelfish Repeatable Read

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;

Transaction 2 is blocked until Transaction 1 commits.

Transaction 2 proceeds normally.

COMMIT

SELECT * FROM employee;

Update from Transaction 1 is visible.

Update from Transaction 1 is not visible.

COMMIT

SELECT * FROM employee;

sees the update from Transaction 1.

sees the update from Transaction 1.
WRITE / WRITE BLOCKING BEHAVIOR
Transaction 1 Transaction 2 SQL Server Repeatable Read Babelfish Repeatable Read

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;

Transaction 2 blocked.

Transaction 2 blocked.

COMMIT

Commit successful and transaction 2 has been unblocked.

Commit successful and transaction 2 fails with error could not serialize access due to concurrent update.

COMMIT

Commit successful.

Transaction 2 has already been aborted.

SELECT * FROM employee;

Row with id=1 has name='A_TX2'.

Row with id=1 has name='A_TX1'.
PHANTOM READ
Transaction 1 Transaction 2 SQL Server Repeatable Read Babelfish Repeatable Read

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

Transaction 2 proceeds without any blocking.

Transaction 2 proceeds without any blocking.

SELECT * FROM employee;

Newly inserted row is visible.

Newly inserted row is visible.

COMMIT

SELECT * FROM employee;

New row inserted by transaction 2 is visible.

New row inserted by transaction 2 is not visible.

COMMIT

SELECT * FROM employee;

Newly inserted row is visible.

Newly inserted row is visible.
DIFFERENT FINAL RESULTS
Transaction 1 Transaction 2 SQL Server Repeatable Read Babelfish Repeatable Read

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

Transaction 1 updates row with id 1.

Transaction 1 updates row with id 1.

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.

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

Transaction 2 is now unblocked.

Commit successful.

COMMIT

SELECT * FROM employee;

Both transaction execute update on row with id = 1.

Different rows are updated by transaction 1 and 2.

BABELFISH SERIALIZABLE VS SQL SERVER SERIALIZABLE ISOLATION LEVEL

RANGE LOCKS IN SQL SERVER
Transaction 1 Transaction 2 SQL Server Serializable Babelfish Serializable

BEGIN TRANSACTION

BEGIN TRANSACTION

SET TRANSACTION ISOLATION LEVEL SERILAIZABLE;

SET TRANSACTION ISOLATION LEVEL SERILAIZABLE;

SELECT * FROM employee;

INSERT INTO employee VALUES (4, 'D', 35);

Transaction 2 is blocked until Transaction 1 commits.

Transaction 2 proceeds without any blocking.

SELECT * FROM employee;

COMMIT

Transaction 1 commits successfully. Transaction 2 is now unblocked.

Transaction 1 commits successfully.

COMMIT

SELECT * FROM employee;

Newly inserted row is visible.

Newly inserted row is visible.
DIFFERENT FINAL RESULTS
Transaction 1 Transaction 2 SQL Server Serializable Babelfish Serializable

BEGIN TRANSACTION

BEGIN TRANSACTION

SET TRANSACTION ISOLATION LEVEL SERILAIZABLE;

SET TRANSACTION ISOLATION LEVEL SERILAIZABLE;

INSERT INTO employee VALUES (4, 'D', 40);

UPDATE employee SET age =99 WHERE id = 4;

Transaction 1 is blocked until Transaction 2 commits.

Transaction 1 proceeds without any blocking.

COMMIT

Transaction 2 commits successfully. Transaction 1 is now unblocked.

Transaction 2 commits successfully.

COMMIT

SELECT * FROM employee;

Newly inserted row is visible with age value = 99.

Newly inserted row is visible with age value = 40.
INSERT INTO TABLE WITH UNIQUE CONSTRAINT
Transaction 1 Transaction 2 SQL Server Serializable Babelfish Serializable

BEGIN TRANSACTION

BEGIN TRANSACTION

SET TRANSACTION ISOLATION LEVEL SERILAIZABLE;

SET TRANSACTION ISOLATION LEVEL SERILAIZABLE;

INSERT INTO employee VALUES (4, 'D', 40);

INSERT INTO employee VALUES ((SELECT MAX(id)+1 FROM employee), 'E', 50);

Transaction 1 is blocked until Transaction 2 commits.

Transaction 1 is blocked until Transaction 2 commits.

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

Transaction 1 commits successfully.

Transaction 1 commits fails with could not serialize access due to read/write dependencies among transactions.

SELECT * FROM employee;

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.

SERIALIZATION ANOMALY
Transaction 1 Transaction 2 SQL Server Serializable Babelfish Serializable

BEGIN TRANSACTION

BEGIN TRANSACTION

SET TRANSACTION ISOLATION LEVEL SERILAIZABLE;

SET TRANSACTION ISOLATION LEVEL SERILAIZABLE;

SELECT * FROM employee;

UPDATE employee SET age=5 WHERE age=10;

SELECT * FROM employee;

Transaction 2 is blocked until Transaction 1 commits.

Transaction 2 proceeds without any blocking.

UPDATE employee SET age=35 WHERE age=30;

COMMIT

Transaction 1 commits successfully.

Transaction 1 is committed first and is able to commit successfully.

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;

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. For example 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

SET TRANSACTION ISOLATION LEVEL SERILAIZABLE;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SELECT * FROM employee;

UPDATE employee SET age=5 WHERE age=10;

SELECT * FROM employee;

Transaction 2 is blocked until transaction 1 commits.

Transaction 2 proceeds without any blocking.

UPDATE employee SET age=35 WHERE age=30;

COMMIT

Transaction 1 commits successfully.

Transaction 1 commits successfully.

COMMIT

Transaction 2 commits successfully.

Transaction 2 commits successfully.

SELECT * FROM employee;

Changes from both transactions are visible.

Changes from both transactions are visible.