Transactions for T-SQL

This topic provides reference information about transaction handling when migrating from Microsoft SQL Server 2019 to Amazon Aurora MySQL. You can gain insights into the key differences in transaction support, isolation levels, and syntax between these two database systems.

SQL Server Usage

A transaction is a unit of work performed against 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.

MySQL Usage

Amazon Aurora MySQL-Compatible Edition (Aurora MySQL) supports the four transaction isolation levels specified in the SQL:1992 standard: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE.

The simplified syntax for setting transaction boundaries in Aurora MySQL is shown following:

SET [SESSION] TRANSACTION ISOLATION LEVEL [READ WRITE | READ ONLY] | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED | SERIALIZABLE]

The default isolation level for Aurora MySQL is REPEATABLE READ.

To set the transaction isolation level, you will need to set the tx_isolation parameter when using Aurora MySQL. For more information, see Server Options.

Starting from MySQL 8, InnoDB supports NOWAIT and SKIP LOCKED options with SELECT …​ FOR SHARE and SELECT …​ FOR UPDATE locking read statements. NOWAIT causes the statement to return immediately if a requested row is locked by another transaction.

SKIP LOCKED removes locked rows from the result set. SELECT …​ FOR SHARE replaces SELECT …​ LOCK IN SHARE MODE but LOCK IN SHARE MODE remains available for backward compatibility. The statements are equivalent. However, FOR UPDATE and FOR SHARE support NOWAIT SKIP LOCKED and OF tbl_name options. For more information, see SELECT Statement in the MySQL documentation.

Syntax

Simplified syntax for setting transaction boundaries:

SET [SESSION] TRANSACTION ISOLATION LEVEL [READ WRITE | READ ONLY] | REPEATABLE READ |
READ COMMITTED | READ UNCOMMITTED | SERIALIZABLE]

In Aurora MySQL, you can optionally specify a transaction intent. Setting a transaction to READ ONLY turns off the transaction’s ability to modify or lock both transactional and non-transactional tables visible to other transactions, but the transaction can still modify or lock temporary tables. It also enables internal optimization to improve performance and concurrency. The default is READ WRITE.

Simplified syntax for the commands defining transaction boundaries:

START TRANSACTION WITH CONSISTENT SNAPSHOT | READ WRITE | READ ONLY

Or

BEGIN [WORK]

The WITH CONSISTENT SNAPSHOT option starts a consistent read transaction. The effect is the same as issuing a START TRANSACTION followed by a SELECT from any table. WITH CONSISTENT SNAPSHOT doesn’t change the transaction isolation level.

A consistent read uses snapshot information to make query results available based on a point in time regardless of modifications performed by concurrent transactions. If queried data has been changed by another transaction, the original data is reconstructed using the undo log. Consistent reads avoid locking issues that may reduce concurrency. With the REPEATABLE READ isolation level, the snapshot is based on the time the first read operation is performed. With the READ COMMITTED isolation level, the snapshot is reset to the time of each consistent read operation.

Use the following statement to commit work at the end of a transaction.

COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]

Use the following statement to rollback work at the end of a transaction.

ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]

One of the ROLLBACK options is ROLLBACK TO SAVEPOINT<logical_name>. This command will rollback all changes in current transaction up to the save point mentioned.

Create transaction save point during the transaction

SAVEPOINT <logical_name>

Aurora MySQL supports both auto commit and explicit commit modes. You can change mode using the autocommit system variable.

SET autocommit = {0 | 1}

Examples

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

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

Summary

The following table summarizes the key differences in transaction support and syntax when migrating from SQL Server to Aurora MySQL.

For more information, see Transaction Isolation Levels in the MySQL documentation.