Transactions

Feature compatibility	AWS SCT / AWS DMS automation level	AWS SCT action code index	Key differences
		Transaction Isolation	Default isolation level is set to REPEATABLE READ. Default mechanism CONSISTENT SNAPSHOT is similar to READ COMMITTED SNAPSHOT isolation in SQL Server. Syntax and option differences.

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:

• Provide units of work that enable recovery from logical or physical system failures while keeping the database in a consistent state.

• Provide units of work that enable recovery from failures while keeping a database in a consistent state when a logical or physical system failure occurs.

• Provide isolation between users and programs accessing a database concurrently.

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:

• Atomic — Transactions are all-or-nothing. If any part of the transaction fails, the entire transaction fails and the database remains unchanged.

  Note

  There are exceptions to this rule. For example, some constraint violations, for each ANSI definitions, shouldn’t cause a transaction rollback.

• Consistent — All transactions must bring the database from one valid state to another valid state. Data must be valid according to all defined rules, constraints, triggers, and so on.

• Isolation — Concurrent run of transactions must result in a system state that would occur if transactions were run sequentially.

  Note

  There are several exceptions to this rule based on the lenience of the required isolation level.

• Durable — After a transaction commits successfully and is acknowledged to the client, the engine must guarantee that its changes are persisted even in the event of power loss, system crashes, or any other errors.

  Note

  By default, SQL Server uses the auto commit or implicit transactions mode set to ON. Every statement is treated as a transaction on its own unless a transaction was explicitly defined. This behavior is different than other engines like Oracle where, by default, every DML requires an explicit COMMIT statement to be persisted.

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.

• Read uncommitted — A current transaction can see uncommitted data from other transactions. If a transaction performs a rollback, all data is restored to its previous state.

• Read committed — A transaction only sees data changes that were committed. Therefore, dirty reads aren’t possible. However, after issuing a commit, it would be visible to the current transaction while it’s still in a running state.

• Repeatable read — A transaction sees data changes made by the other transactions only after both transactions issue a commit or are rolled back.

• Serializable — This isolation level is the strictest because it doesn’t permit transaction overwrites of another transaction’s actions. Concurrent run of a set of serializable transactions is guaranteed to produce the same effect as running them sequentially in the same order.

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

• Dirty reads — A transaction can read data written by another transaction but not yet committed.

• Non-repeatable or fuzzy reads — When reading the same data several times, a transaction can find the data has been modified by another transaction that has just committed. The same query ran twice can return different values for the same rows.

• Phantom or ghost reads — Similar to a non-repeatable read, but it is related to new data created by another transaction. The same query ran twice can return different numbers of records.

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

Transaction isolation level	Dirty reads	Non-repeatable reads	Phantom reads
Read uncommitted	Allowed	Allowed	Allowed
Read committed	Disallowed	Allowed	Allowed
Repeatable read	Disallowed	Disallowed	Allowed
Serializable	Disallowed	Disallowed	Disallowed

There are two common implementations for transaction isolation:

• Pessimistic isolation or locking — Resources accessed by a transaction are locked for the duration of the transaction. Depending on the operation, resource, and transaction isolation level, other transactions can see changes made by the locking transaction, or they must wait for it to complete. With this mechanism, there is only one copy of the data for all transactions, which minimizes memory and disk resource consumption at the expense of transaction lock waits.

• Optimistic isolation (MVCC) — Every transaction owns a set of the versions of the resources (typically rows) that it accessed. In this mode, transactions don’t have to wait for one another at the expense of increased memory and disk utilization. In this isolation mechanism, there is a chance that conflicts will arise when transactions attempt to commit. In case of a conflict, the application needs to be able to handle the rollback, and attempt a retry.

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]

Note

Setting the GLOBAL isolation level isn’t supported in Aurora MySQL; only session scope can be changed. This behavior is similar to Oracle. Also, the default behavior of transactions is to use REPEATABLE READ and consistent reads. Applications designed to run with READ COMMITTED may need to be modified. Alternatively, explicitly change the default to READ COMMITTED.

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.

Note

Amazon Relational Database Service (Amazon RDS) for MySQL 8 supports a new innodb_deadlock_detect dynamic variable. You can use this variable to turn off the deadlock detection. On high concurrency systems deadlock detection can cause a slowdown when numerous threads wait for the same lock. At times it may be more efficient to turn off deadlock detection and rely on the innodb_lock_wait_timeout setting for transaction rollback when a deadlock occurs.

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]

Note

Setting a GLOBAL isolation level isn’t supported in Aurora MySQL. You can only change the session scope; similar to SQL Server SET scope. The default behavior of transactions is to use REPEATABLE READ and consistent reads. Applications designed to run with READ COMMITTED may need to be modified. Alternatively, they can explicitly change the default to READ COMMITTED.

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>

Note

If the current transaction has a save point with the same name, the old save point is deleted and a new one is set.

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.

Transaction property	SQL Server	Aurora MySQL	Comments
Default isolation level	READ COMMITTED	REPEATABLE READ	The Aurora MySQL default isolation level is stricter than SQL Server. Evaluate application needs and set appropriately.
Initialize transaction syntax	BEGIN TRAN or BEGIN TRANSACTION	START TRANSACTION	Code rewrite is required from BEGIN to START. If using the shorthand TRAN, rewrite to TRANSACTION.
Default isolation mechanism	Pessimistic lock based	Lock based for writes, consistent read for SELECT statements.	The Aurora MySQL default mode is similar to the READ COMMITTED SNAPSHOT isolation in SQL Server.
Commit transaction	COMMIT [WORK|TRAN|TRANSACTION]	COMMIT [WORK]	If you only use COMMIT or COMMIT WORK, no change is needed. Otherwise, rewrite TRAN and TRANSACTION to WORK.
Rollback transaction	ROLLBACK [WORK |[ TRAN | TRANSACTION]	ROLLBACK [WORK]	If you only use ROLLBACK or ROLLBACK WORK, no change is needed. Otherwise, rewrite TRAN and TRANSACTION to WORK.
Set autocommit off or on	SET IMPLICIT_TRANSACTIONS OFF | ON	SET autocommit = 0 | 1	For more information, see Session Options.
ANSI isolation	REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED | SERIALIZABLE	REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED | SERIALIZABLE	Compatible syntax.
MVCC	SNAPSHOT and READ COMMITTED SNAPSHOT	WITH CONSISTENT SNAPSHOT	Aurora MySQL consistent read in READ COMMITTED isolation is similar to READ COMMITTED SNAPSHOT in SQL Server.
Nested transactions	Supported, view level with @@trancount	Not supported	Starting a new transaction in Aurora MySQL while another transaction is active causes a COMMIT of the previous transaction.
Transaction chaining	Not supported	Causes a new transaction to open immediately upon transaction completion.
Transaction release	Not supported	Causes the client session to disconnect upon transaction completion.

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