# Differences between Babelfish for Aurora PostgreSQL and SQL Server
<a name="babelfish-compatibility"></a>

Babelfish is an evolving Aurora PostgreSQL feature, with new functionality added in each release since the initial offering in Aurora PostgreSQL 13.4. It's designed to provide T-SQL semantics on top of PostgreSQL through the T-SQL dialect using the TDS port. Each new version of Babelfish adds features and functions that better align with T-SQL functionality and behavior, as shown in the [Supported functionalities in Babelfish by version](babelfish-compatibility.supported-functionality-table.md) table. For best results when working with Babelfish, we recommend that you understand the differences that currently exist between the T-SQL supported by SQL Server and Babelfish for the latest version. To learn more, see [T-SQL differences in Babelfish](babelfish-compatibility.tsql.limitations.md).

In addition to the differences between T-SQL supported by Babelfish and SQL Server, you might also need to consider interoperability issues between Babelfish and PostgreSQL in the context of the Aurora PostgreSQL DB cluster. As mentioned previously, Babelfish supports T-SQL semantics on top of PostgreSQL through the T-SQL dialect using the TDS port. At the same time, the Babelfish database can also be accessed through the standard PostgreSQL port with PostgreSQL SQL statements. If you're considering using both PostgreSQL and Babelfish functionality in a production deployment, you need to be aware of the potential interoperability issues between schema names, identifiers, permissions, transactional semantics, multiple result sets, default collations, and so on. In simple terms, when PostgreSQL statements or PostgreSQL access occur in the context of Babelfish, interference between PostgreSQL and Babelfish can occur and can potentially affecting syntax, semantics, and compatibility when new versions of Babelfish are released. For complete information and guidance about all the considerations, see the [Guidance on Babelfish Interoperability](https://babelfishpg.org/docs/usage/interoperability/) in the Babelfish for PostgreSQL documentation. 

**Note**  
Before using both PostgreSQL native functionality and Babelfish functionality in the same application context, we strongly recommend that you consider the issues discussed in the [Guidance on Babelfish Interoperability](https://babelfishpg.org/docs/usage/interoperability/) in the Babelfish for PostgreSQL documentation. These interoperability issues (Aurora PostgreSQL and Babelfish) are relevant only if you plan to use the PostgreSQL database instance in the same application context as Babelfish. 

**Topics**
+ [Babelfish dump and restore](#babelfish-compatibility.dumprestore)
+ [T-SQL differences in Babelfish](babelfish-compatibility.tsql.limitations.md)
+ [Transaction isolation levels in Babelfish](babelfish-transaction.md)

## Babelfish dump and restore
<a name="babelfish-compatibility.dumprestore"></a>

Starting with version 4.0.0 and 3.4.0, Babelfish users can now utilize the dump and restore utilities to backup and restore their databases. For more information, see [Babelfish dump and restore](https://github.com/babelfish-for-postgresql/babelfish-for-postgresql/wiki/Babelfish-Dump-and-Restore). This feature is built on top of PostgreSQL dump and restore utilities. For more information, see [pg\$1dump](https://www.postgresql.org/docs/current/app-pgdump.html) and see [pg\$1restore](https://www.postgresql.org/docs/current/app-pgrestore.html). In order to effectively use this feature in Babelfish, you need to use PostgreSQL-based tools that are specifically adapted for Babelfish. The backup and restore feature for Babelfish differs significantly from that of SQL Server. For more information on these differences, see [Dump and restore functionality differences : Babelfish and SQL Server](https://github.com/babelfish-for-postgresql/babelfish-for-postgresql/wiki/Babelfish-Dump-and-Restore#differences). Babelfish for Aurora PostgreSQL provides additional capabilities for backing up and restoring Amazon Aurora PostgreSQL DB clusters. For more information, see [Backing up and restoring an Amazon Aurora DB cluster](BackupRestoreAurora.md).

# T-SQL differences in Babelfish
<a name="babelfish-compatibility.tsql.limitations"></a>

Following, you can find a table of T-SQL functionality as supported in the current release of Babelfish with some notes about differences in the behavior from that of SQL Server.

For more information about support in various versions, see [Supported functionalities in Babelfish by version](babelfish-compatibility.supported-functionality-table.md). For information about features that currently aren't supported, see [Unsupported functionalities in Babelfish](babelfish-compatibility.tsql.limitations-unsupported.md). 

Babelfish is available with Aurora PostgreSQL-Compatible Edition. For more information about Babelfish releases, see the [https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/Welcome.html](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/Welcome.html).


| Functionality or syntax | Description of behavior or difference | 
| --- | --- | 
| \$1 (line continuation character) | The line continuation character (a backslash prior to a newline) for character and hexadecimal strings isn't currently supported. For character strings, the backslash-newline is interpreted as characters in the string. For hexadecimal strings, backslash-newline results in a syntax error.  | 
| @@version | The format of the value returned by `@@version` is slightly different from the value returned by SQL Server. Your code might not work correctly if it depends on the formatting of `@@version`. | 
| Aggregate functions | Aggregate functions are partially supported (AVG, COUNT, COUNT\$1BIG, GROUPING, MAX, MIN, STRING\$1AGG, and SUM are supported). For a list of unsupported aggregate functions, see [Functions that aren't supported](babelfish-compatibility.tsql.limitations-unsupported.md#babelfish-compatibility.tsql.limitations-unsupported-list4). | 
|  ALTER TABLE  | Supports adding or dropping a single column or constraint only.  | 
|  ALTER TABLE..ALTER COLUMN  | NULL and NOT NULL can't currently be specified. To change the nullability of a column, use the postgreSQL statement ALTER TABLE..\$1SET\$1DROP\$1 NOT NULL.  | 
|  AT TIME ZONE  | During the transition from Daylight Saving Time (DST) to Standard Time, the overlapping period is displayed using the Standard Time offset. To clarify, consider the following example:  <pre>SELECT CONVERT(DATETIME2(0), '2022-10-30T02:00:00', 126) AT TIME ZONE 'Central European Standard Time';<br />GO;<br />Result: 2022-10-30 02:00:00 +01:00</pre>  | 
| Blank column names with no column alias | The `sqlcmd` and `psql` utilities handle columns with blank names differently: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/babelfish-compatibility.tsql.limitations.html)  | 
| CHECKSUM function | Babelfish and SQL Server use different hashing algorithms for the CHECKSUM function. As a result, the hash values generated by CHECKSUM function in Babelfish might be different from those generated by CHECKSUM function in SQL Server. | 
| Column default | When creating a column default, the constraint name is ignored. To drop a column default, use the following syntax: `ALTER TABLE...ALTER COLUMN..DROP DEFAULT...` | 
| Constraint\$1name | In SQL Server, constraint names must be unique within the schema to which the table belongs. However, in Babelfish, this applies only to PRIMARY KEY and UNIQUE constraints. Other types of constraints are not subject to this restriction. | 
| Constraints | PostgreSQL doesn't support turning on and turning off individual constraints. The statement is ignored and a warning is raised. | 
| Constraints with IGNORE\$1DUP\$1KEY | Constraints are created without this property. | 
| CREATE, ALTER, DROP SERVER ROLE |  ALTER SERVER ROLE is supported only for `sysadmin`. All other syntax is unsupported. The T-SQL user in Babelfish has an experience that is similar to SQL Server for the concepts of a login (server principal), a database, and a database user (database principal).  | 
| CREATE, ALTER LOGIN clauses are supported with limited syntax | The CREATE LOGIN... PASSWORD clause, ...DEFAULT\$1DATABASE clause, and ...DEFAULT\$1LANGUAGE clause are supported. The ALTER LOGIN... PASSWORD clause is supported, but ALTER LOGIN... OLD\$1PASSWORD clause isn't supported. Only a login that is a sysadmin member can modify a password. | 
| CREATE DATABASE case-sensitive collation  | Case-sensitive collations aren't supported with the CREATE DATABASE statement. | 
| CREATE DATABASE keywords and clauses | Options except COLLATE and CONTAINMENT=NONE aren't supported. The COLLATE clause is accepted and is always set to the value of `babelfishpg_tsql.server_collation_name`. | 
| CREATE SCHEMA... supporting clauses | You can use the CREATE SCHEMA command to create an empty schema. Use additional commands to create schema objects. | 
| Database ID values are different on Babelfish  |  The master and tempdb databases won't be database IDs 1 and 2.  | 
|  FORMAT date type function is supported with the following limitations | Single character meridian isn't supported. "yyy" format in SQL server returns 4 digits for year above 1000, but only 3 digits for others. "g" and "R" formats aren't supported "vi-VN" locale translation is slightly different. | 
| Identifiers exceeding 63 characters | PostgreSQL supports a maximum of 63 characters for identifiers. Babelfish converts identifiers longer than 63 characters to a name that includes a hash of the original name. For example, a table created as "AB(ABC1234567890123456789012345678901234567890123456789012345678901234567890" might be converted to "ABC123456789012345678901234567890123456789012345678901234567890".  | 
| IDENTITY columns support | IDENTITY columns are supported for data types tinyint, smallint, int, bigint. numeric, and decimal. SQL Server supports precision to 38 places for data types `numeric` and `decimal` in IDENTITY columns.PostgreSQL supports precision to 19 places for data types `numeric` and `decimal` in IDENTITY columns. | 
| Indexes with IGNORE\$1DUP\$1KEY | Syntax that creates an index that includes IGNORE\$1DUP\$1KEY creates an index as if this property is omitted. | 
| Indexes with more than 32 columns | An index can't include more than 32 columns. Included index columns count toward the maximum in PostgreSQL but not in SQL Server. | 
| Indexes (clustered) | Clustered indexes are created as if NONCLUSTERED was specified. | 
| Index clauses | The following clauses are ignored: FILLFACTOR, ALLOW\$1PAGE\$1LOCKS, ALLOW\$1ROW\$1LOCKS, PAD\$1INDEX, STATISTICS\$1NORECOMPUTE, OPTIMIZE\$1FOR\$1SEQUENTIAL\$1KEY, SORT\$1IN\$1TEMPDB, DROP\$1EXISTING, ONLINE, COMPRESSION\$1DELAY, MAXDOP, and DATA\$1COMPRESSION | 
| JSON support |  Order of the name-value pairs isn't guaranteed. But the array type remains unaffected. | 
| LOGIN objects | All options for LOGIN objects are not supported except for PASSWORD, DEFAULT\$1DATABASE, DEFAULT\$1LANGUAGE, ENABLE, DISABLE. | 
| NEWSEQUENTIALID function | Implemented as NEWID; sequential behavior isn't guaranteed. When calling `NEWSEQUENTIALID`, PostgreSQL generates a new GUID value. | 
| OUTPUT clause is supported with the following limitations | OUTPUT and OUTPUT INTO aren't supported in the same DML query. References to non-target table of UPDATE or DELETE operations in an OUTPUT clause aren't supported. OUTPUT... DELETED \$1, INSERTED \$1 aren't supported in the same query. | 
| Procedure or function parameter limit | Babelfish supports a maximum of 100 parameters for a procedure or function. | 
| ROWGUIDCOL | This clause is currently ignored. Queries referencing `$GUIDGOL` cause a syntax error. | 
| SEQUENCE object support | SEQUENCE objects are supported for the data types tinyint, smallint, int, bigint, numeric, and decimal. Aurora PostgreSQL supports precision to 19 places for data types numeric and decimal in a SEQUENCE. | 
| Server-level roles | The `sysadmin` server-level role is supported. Other server-level roles (other than `sysadmin`) aren't supported. | 
| Database-level roles other than `db_owner` | The `db_owner` database-level roles and user-defined database-level roles are supported. Other database-level roles (other than db\$1owner) aren't supported. | 
| SQL keyword SPARSE | The keyword SPARSE is accepted and ignored. | 
| SQL keyword clause `ON filegroup` | This clause is currently ignored. | 
| SQL keywords `CLUSTERED` and `NONCLUSTERED` for indexes and constraints | Babelfish accepts and ignores the `CLUSTERED` and `NONCLUSTERED` keywords. | 
| `sysdatabases.cmptlevel` | `sysdatabases.cmptlevel` is always set to 120. | 
| tempdb isn't reinitialized at restart | Permanent objects (like tables and procedures) created in tempdb aren't removed when the database is restarted. | 
| TEXTIMAGE\$1ON filegroup | Babelfish ignores the `TEXTIMAGE_ON` *`filegroup`* clause. | 
| Time precision | Babelfish supports 6-digit precision for fractional seconds. No adverse effects are anticipated with this behavior. | 
| Transaction isolation levels | READUNCOMMITTED is treated the same as READCOMMITTED. | 
| Virtual computed columns (non-persistent) | Virtual computed columns are created as persistent. | 
| Without SCHEMABINDING clause | This clause isn't supported in functions, procedures, triggers, or views. The object is created, but as if WITH SCHEMABINDING was specified. | 

# Transaction isolation levels in Babelfish
<a name="babelfish-transaction"></a>

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](https://www.postgresql.org/docs/current/transaction-iso.html). 

**Topics**
+ [Overview of the transaction isolation levels](#babelfish-transaction.overview)
+ [Setting up the transaction isolation levels](#babelfish-transaction.setting)
+ [Enabling or disabling transaction isolation levels](#babelfish-transaction.enabling)
+ [Comparing Babelfish and SQL Server isolation levels](babelfish-transaction.examples.md)

## Overview of the transaction isolation levels
<a name="babelfish-transaction.overview"></a>

 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 or 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
<a name="babelfish-transaction.setting"></a>

Use the following command to set transaction isolation level:

**Example**  

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

## Enabling or disabling transaction isolation levels
<a name="babelfish-transaction.enabling"></a>

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](babelfish-strict.md).

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. 

**Example**  

```
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. 

**Example**  

```
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. 

**Example**  

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

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

**Example**  

```
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. 

**Example**  

```
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. 

**Example**  

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

# Comparing Babelfish and SQL Server isolation levels
<a name="babelfish-transaction.examples"></a>

 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-transaction.examples.unc)
+ [Babelfish `READ COMMITTED` compared with SQL Server `READ COMMITTED` isolation level](#babelfish-transaction.examples.com)
+ [Babelfish `READ COMMITTED` compared with SQL Server `READ COMMITTED SNAPSHOT` isolation level](#babelfish-transaction.examples.snapshot)
+ [Babelfish `REPEATABLE READ` compared with SQL Server `REPEATABLE READ` isolation level](#babelfish-transaction.examples.read)
+ [Babelfish `SERIALIZABLE` compared with SQL Server `SERIALIZABLE` isolation level](#babelfish-transaction.examples.serialize)

## Babelfish `READ UNCOMMITTED` compared with SQL Server `READ UNCOMMITTED` isolation level
<a name="babelfish-transaction.examples.unc"></a>

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
<a name="babelfish-transaction.examples.com"></a>

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
<a name="babelfish-transaction.examples.snapshot"></a>

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
<a name="babelfish-transaction.examples.read"></a>

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\$1TX2'.  |  Row with id=1 has name='A\$1TX1'.  | 

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
<a name="babelfish-transaction.examples.serialize"></a>

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