SET TRANSACTION

Syntax

SET [GLOBAL | SESSION] TRANSACTION
    transaction_property [, transaction_property] ...

transaction_property:
    ISOLATION LEVEL level
  | READ WRITE
  | READ ONLY

level:
     REPEATABLE READ
   | READ COMMITTED
   | READ UNCOMMITTED
   | SERIALIZABLE

Overview

This statement sets the transaction isolation level or the transaction access mode globally, for the current session, or for the next transaction:

• With the GLOBAL keyword, the statement sets the default transaction level globally for all subsequent sessions. Existing sessions are unaffected.

• With the SESSION keyword, the statement sets the default transaction level for all subsequent transactions performed within the current session.

• Without any SESSION or GLOBAL keyword, the statement sets the isolation level for only the next (not started) transaction performed within the current session. After that it reverts to using the session value.

A change to the global default isolation level requires the SUPER privilege. Any session is free to change its session isolation level (even in the middle of a transaction), or the isolation level for its next transaction.

Isolation Level

To set the global default isolation level at server startup, use the --transaction-isolation=level option on the command line or in an option file. Values of level for this option use dashes rather than spaces, so the allowable values are READ_UNCOMMITTED,READ-COMMITTED, REPEATABLE-READ, or SERIALIZABLE. For example, to set the default isolation level to REPEATABLE READ, use these lines in the [mariadb] section of an option file:

InnoDB supports each of the translation isolation levels described here using different locking strategies. The default level isREPEATABLE READ. For additional information about InnoDB record-level locks and how it uses them to execute various types of statements, see InnoDB Lock Modes, and innodb-locks-set.html.

Isolation Levels

The following sections describe how MariaDB supports the different transaction levels.

READ UNCOMMITTED

SELECT statements are performed in a non-locking fashion, but a possible earlier version of a row might be used. Thus, using this isolation level, such reads are not consistent. This is also called a "dirty read". Otherwise, this isolation level works likeREAD COMMITTED.

READ COMMITTED

A somewhat Oracle-like isolation level with respect to consistent (non-locking) reads: Each consistent read, even within the same transaction, sets and reads its own fresh snapshot. See innodb-consistent-read.html.

Gap Locking at READ COMMITTED

InnoDB takes no gap locks at this isolation level. For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), and for UPDATE and DELETE statements, InnoDB locks only the index records it examines, not the gaps before them, and thus allows the free insertion of new records next to locked records. This applies to range-type search conditions (such as WHERE id > 100) as well as to unique searches (such as WHERE id = 100).

Duplicate-key checking is the exception: when InnoDB checks a unique index for a duplicate value, it takes a next-key (gap plus index-record) lock regardless of the isolation level. Foreign key constraint checks, by contrast, do not take gap locks at READ COMMITTED.

Gap locks are what block phantom rows, so without them InnoDB cannot be logged safely one statement at a time. With binary logging enabled and binlog_format set to STATEMENT, InnoDB rejects any statement that would write rows:

The default binlog_format of MIXED is unaffected, as is ROW.

Semi-Consistent Reads

In a semi-consistent read, an UPDATE statement skips a row that another transaction has locked, provided the latest committed version of that row does not match the WHERE condition. The statement proceeds instead of waiting for the lock, which means you might see only a partially consistent read.

Semi-consistent reads are deliberately limited to UPDATE: the optimization was never implemented for DELETE, which waits for the lock. They also require the statement to scan the clustered index with a non-unique search condition. An UPDATE that matches every column of a unique index exactly, such as WHERE id = 100, waits for the lock, as does one that scans a secondary index.

Releasing a lock on a non-matching row is a separate mechanism, with a different scope. It applies to a DELETE as much as to an UPDATE, it applies to unique searches, and it is unaffected by innodb_snapshot_isolation: if InnoDB locks a record at READ COMMITTED or READ UNCOMMITTED and then finds that the record does not match the WHERE condition, it releases that record lock — unless the transaction has itself modified the row.

It does share one restriction with semi-consistent reads: the statement must be scanning the clustered index. A record lock taken while scanning a secondary index is held until the transaction ends, even though the row failed the WHERE condition.

REPEATABLE READ

This is the default isolation level for InnoDB. For consistent reads, there is an important difference from the READ COMMITTED isolation level: All consistent reads within the same transaction read the snapshot established by the first read. This convention means that if you issue several plain (non-locking) SELECT statements within the same transaction, these SELECT statements are consistent also with respect to each other. See innodb-consistent-read.html.

For other search conditions, InnoDB locks the index range scanned, using gap locks or next-key (gap plus index-record) locks to block insertions by other sessions into the gaps covered by the range.

This is the minimum isolation level for non-distributed XA transactions.

Snapshot Isolation and DML Operations

While innodb_snapshot_isolation is enabled, MariaDB enforces REPEATABLE READ more strictly for UPDATE and DELETE statements:

• Conflict Detection: If an UPDATE or DELETE attempts to modify a row that has been changed by a concurrent transaction since your snapshot was established, the operation is rejected.

• ER_CHECKREAD (1020): This rejection triggers error ER_CHECKREAD. The revised error message suggests that the user should try restarting the transaction.

• Automatic Rollback: Unlike a simple statement error, ER_CHECKREAD is treated similarly to a deadlock: the entire transaction is rolled back.

• Purpose: This prevents the transaction from switching to "current-read" mode for that row, which would otherwise allow the transaction to observe concurrent changes it did not make, violating the pure repeatable read invariant.

Traditional Locking Behavior

If innodb_snapshot_isolation is disabled (set to OFF), InnoDB follows traditional behavior where locking reads (SELECT ... FOR UPDATE), UPDATE, and DELETE statements read the latest committed version of rows. In this mode, subsequent non-locking SELECT statements for those same rows also return the current version rather than the snapshot version, which can lead to non-repeatable read anomalies.

SERIALIZABLE

This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... LOCK IN SHARE MODE if autocommit is disabled. If autocommit is enabled, the SELECT is its own transaction. It therefore is known to be read only and can be serialized if performed as a consistent (non-locking) read and need not block for other transactions. (This means that to force a plain SELECT to block if other transactions have modified the selected rows, you should disable autocommit.)

Distributed XA transactions should always use this isolation level.

innodb_snapshot_isolation

If the innodb_snapshot_isolation system variable is not set to ON, strictly speaking anything other than READ UNCOMMITTED is not clearly defined. While it is ON, an attempt to acquire a lock on a record that does not exist in the current read view raises an error and rolls the transaction back.

Prefer ON, because it is what makes REPEATABLE READ behave as its name claims. The reason to choose OFF is application compatibility, not performance. With ON, an UPDATE or DELETE that touches a row changed since the transaction's snapshot fails with ER_CHECKREAD (1020) and the whole transaction is rolled back, exactly as for a deadlock; the server treats the two errors as the same class, and replication retries both. An application that already retries on ER_LOCK_DEADLOCK needs no change. A legacy application that does not handle ER_CHECKREAD the same way loses those transactions instead of retrying them, and innodb_snapshot_isolation=OFF gives it the MySQL-compatible — though not well-defined — behavior until it can be fixed.

Access Mode

It is not permitted to specify both READ WRITE and READ ONLY in the same statement.

READ WRITE and READ ONLY can also be specified in the START TRANSACTION statement, in which case the specified mode is only valid for one transaction.

Choosing an Isolation Level

Stay on the default REPEATABLE READ unless the application needs the semantics of a different level. READ COMMITTED is not a throughput setting in either direction: which level costs more depends on the shape of the workload, because the two differ in when a read view is opened.

At READ COMMITTED, every statement opens its own read view. At REPEATABLE READ, all statements share the read view taken when the transaction started. That single difference cuts both ways:

• Many short statements in a recently started transaction are more expensive at READ COMMITTED. Opening a read view means collecting the identifiers of the read-write transactions that are currently active, and under high concurrency that collection is measurable. This is a common shape for web applications. MDEV-21423 reduced the cost from MariaDB 11.8.9 and 12.3.3 without removing it.

• Complex statements in a long-running transaction can be cheaper at READ COMMITTED. Each statement is allowed to see more recent data, so rebuilding the row versions it needs walks less history. At REPEATABLE READ the read view stays fixed at the start of the transaction, and the older it gets, the further back through the undo history InnoDB has to walk to reconstruct the snapshot that a statement is entitled to see.

So measure the workload rather than assume a direction.

The reasons to choose READ COMMITTED are about behavior rather than speed:

• Every statement sees the most recently committed data. This suits an application written against a database whose default behaves that way, such as SQL Server. See MariaDB Transactions and Isolation Levels for SQL Server Users.

• InnoDB takes no gap locks, so an insert into a range that another transaction has locked is not blocked. See Gap Locking at READ COMMITTED. On its own this is not a reason to expect better throughput, for the read view reason above.

What you give up in exchange:

• Reads are no longer repeatable. Two identical SELECT statements in the same transaction can return different rows.

• Statement-based binary logging is not available, as described under Gap Locking at READ COMMITTED.

innodb_snapshot_isolation is a separate decision from the isolation level, and one to make explicitly rather than inherit, because its default is not the same in every supported release. See innodb_snapshot_isolation above.

Examples

Attempting to set the isolation level within an existing transaction without specifying GLOBAL or SESSION.

_{This page is licensed: GPLv2, originally from fill_help_tables.sql}