Transactions for T-SQL

This topic provides reference information about transaction handling in Microsoft SQL Server and Amazon Aurora PostgreSQL, focusing on their similarities and differences. It explores the fundamental principles of database transactions, including ACID properties and isolation levels, and how they are implemented in both database systems. The topic compares the default behaviors, syntax variations, and supported features for managing transactions in SQL Server and PostgreSQL.

SQL Server Usage

A transaction is a unit of work performed on a database and typically represents a change in the database. Transactions serve the following purposes:

Transactions are an all-or-nothing unit of work. Each transactional unit of work must either complete, or it must rollback all data changes. Also, transactions must be isolated from other transactions. The results of the view of data for each transaction must conform to the defined database isolation level.

Database transactions must comply with ACID properties.

Syntax

The following examples show the simplified syntax for the commands defining transaction boundaries.

Define the beginning of a transaction.

BEGIN TRAN | TRANSACTION [<transaction name>]

Commit work and the end of a transaction.

COMMIT WORK | [ TRAN | TRANSACTION [<transaction name>]]

Rollback work at the end of a transaction.

ROLLBACK WORK | [ TRAN | TRANSACTION [<transaction name>]]

SQL Server supports the standard ANSI isolation levels defined by the ANSI/ISO SQL standard (SQL92).

Each level provides a different approach for managing the concurrent run of transactions. The main purpose of a transaction isolation level is to manage the visibility of changed data as seen by other running transactions. Additionally, when concurrent transactions access the same data, the level of transaction isolation affects the way they interact with each other.

The main difference between isolation levels is the phenomena they prevent from appearing. The three preventable phenomena are:

The following table summarizes the four ANSI/ISO SQL standard (SQL92) isolation levels and indicates which phenomena are allowed or disallowed.

There are two common implementations for transaction isolation:

SQL Server implements both mechanisms. You can use them concurrently.

For optimistic isolation, SQL Server introduced two additional isolation levels: read-committed snapshot and snapshot.

Set the transaction isolation level using SET command. It affects the current run scope only.

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

Examples

The following example runs two DML statements within a serializable transaction.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
INSERT INTO Table1
VALUES (1, 'A');
UPDATE Table2
  SET Column1 = 'Done'
WHERE KeyColumn = 1;
COMMIT TRANSACTION;

For more information, see Transaction Isolation Levels (ODBC) and SET TRANSACTION ISOLATION LEVEL (Transact-SQL) in the SQL Server documentation.

PostgreSQL Usage

As with SQL Server, the same ANSI/ISO SQL (SQL92) isolation levels apply to PostgreSQL, but with several similarities and some differences.

PostgreSQL technically supports the use of any of the four transaction isolation levels, but only three can practically be used. The Read-Uncommitted isolation level serves as read-committed.

The way the repeatable-read isolation-level is implemented doesn’t allow for phantom reads, which is similar to the Serializable isolation-level. The primary difference between repeatable-read and serializable is that serializable guarantees that the result of concurrent transactions are precisely the same as if they were run serially, which isn’t always true for repeatable-reads.

Starting with PostgreSQL 12, you can add the AND CHAIN option to COMMIT or ROLLBACK commands to immediately start another transaction with the same parameters as preceding transaction.

Multiversion Concurrency Control

In PostgreSQL, the multiversion concurrency control (MVCC) mechanism allows transactions to work with a consistent snapshot of data ignoring changes made by other transactions that have not yet committed or rolled back. Each transaction sees a snapshot of accessed data accurate to its run start time regardless of what other transactions are doing concurrently.

Isolation Levels

PostgreSQL supports the read-committed, repeatable reads, and serializable isolation levels. Read-committed is the default isolation level.

Setting Isolation Levels in Aurora PostgreSQL

You can configure isolation levels at several levels.

Syntax

SET TRANSACTION transaction_mode [...]
SET TRANSACTION SNAPSHOT snapshot_id
SET SESSION CHARACTERISTICS AS TRANSACTION transaction_mode [...]

where transaction_mode is one of:

ISOLATION LEVEL {
  SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED
}
READ WRITE | READ ONLY [ NOT ] DEFERRABLE

Examples

The following example configures the isolation level for a specific transaction.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

The following example configures the isolation level for a specific session.

SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Use the following example to view the current isolation level.

SELECT CURRENT_SETTING('TRANSACTION_ISOLATION'); -- Session
SHOW DEFAULT_TRANSACTION_ISOLATION; -- Instance

You can use parameter groups to modify instance-level parameters for Aurora PostgreSQL. For example, you can alter the default_transaction_isolation parameter using the AWS Console or the AWS CLI. For more information, see Working with parameter groups.

Comparison table of relevant database features related to transactions

Read-Committed Isolation Level

SELECT employee_id, salary
FROM EMPLOYEES
WHERE employee_id=100;

employee_id  salary
100          24000.00

select employee_id, salary from
EMPLOYEES
where employee_id=100;

employee_id  salary
100          24000.00

begin;
UPDATE employees
SET salary=27000
WHERE employee_id=100;

begin;
set transaction isolation level
read committed;

SELECT employee_id, salary
FROM EMPLOYEES
WHERE employee_id=100;

employee_id  salary
100          27000.00

SELECT employee_id, salary
FROM EMPLOYEES
WHERE employee_id=100;

employee_id  salary
100          24000.00

UPDATE employees
SET salary=29000
WHERE employee_id=100;

Commit;

Commit;

SELECT employee_id, salary
FROM EMPLOYEES
WHERE employee_id=100;

employee_id  salary
100          29000.00

SELECT employee_id, salary
FROM EMPLOYEES
WHERE employee_id=100;

employee_id  salary
100          29000.00

Serializable Isolation Level

SELECT employee_id, salary
FROM EMPLOYEES
WHERE employee_id=100;

employee_id  salary
100          24000.00

SELECT employee_id, salary
FROM EMPLOYEES
WHERE employee_id=100;

employee_id  salary
100          24000.00

begin;
UPDATE employees
SET salary=27000
WHERE employee_id=100;

begin;
set transaction isolation level serializable;

SELECT employee_id, salary
FROM EMPLOYEES
WHERE employee_id=100;

employee_id  salary
100          27000.00

SELECT employee_id, salary
FROM EMPLOYEES
WHERE employee_id=100;

employee_id  salary
100          24000.00

update employees
set salary=29000
where employee_id=100;

Commit;

Commit;
ROLLBACK

SELECT employee_id, salary
FROM EMPLOYEES
WHERE employee_id=100;

employee_id  salary
100          27000.00

SELECT employee_id, salary
FROM EMPLOYEES
WHERE employee_id=100;

employee_id  salary
100          27000.00

Summary

COMMIT
[WORK|TRAN|TRANSACTION]

COMMIT
[ WORK | TRANSACTION ]

ROLLBACK [WORK |[ TRAN | TRANSACTION]

ROLLBACK [ WORK | TRANSACTION ]

SET IMPLICIT_TRANSACTIONS OFF | ON

SET AUTOCOMMIT { = | TO } { ON | OFF }

REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED | SERIALIZABLE

REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED | SERIALIZABLE

SNAPSHOT and READ
COMMITTED SNAPSHOT

READ COMMITTED SNAPSHOT

For more information, see Transactions, Transaction Isolation, and SET TRANSACTION in the PostgreSQL documentation.