> For the complete documentation index, see [llms.txt](https://mariadb.com/docs/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/set-commands/set-transaction.md).

# SET TRANSACTION

## Syntax

```bnf
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](/docs/server/reference/sql-statements/account-management-sql-statements/grant.md#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](/docs/server/server-management/variables-and-modes/server-system-variables.md#tx_isolation) 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-uncommitted),[READ-COMMITTED](#read-committed), [REPEATABLE-READ](#repeatable-read), or [SERIALIZABLE](#serializable). For example, to set the default isolation level to `REPEATABLE READ`, use these lines in the `[mariadb]` section of an option file:

```ini
[mariadb]
transaction-isolation = REPEATABLE-READ
```

{% tabs %}
{% tab title="Current" %}
To determine the global and session transaction isolation levels at runtime, check the value of the [transaction\_isolation](/docs/server/server-management/variables-and-modes/server-system-variables.md#transaction_isolation) variable.
{% endtab %}

{% tab title="< 11.1.1" %}
To determine the global and session transaction isolation levels at runtime, check the value of the [tx\_isolation](/docs/server/server-management/variables-and-modes/server-system-variables.md#tx_isolation) system variable.
{% endtab %}
{% endtabs %}

```sql
SELECT @@GLOBAL.transaction_isolation, @@tx_isolation;
```

InnoDB supports each of the translation isolation levels described here using different locking strategies. The default level is`REPEATABLE READ`. For additional information about InnoDB record-level locks and how it uses them to execute various types of statements, see [InnoDB Lock Modes](/docs/server/server-usage/storage-engines/innodb/innodb-lock-modes.md), and [innodb-locks-set.html](https://dev.mysql.com/doc/refman/en/innodb-locks-set.html).

## Isolation Levels

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

{% hint style="warning" %}
With both `READ UNCOMMITTED` and `READ COMMITTED` isolation levels, you can’t expect results to be deterministic between successive statements of the same transaction.
{% endhint %}

### 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 like`READ COMMITTED`.

{% hint style="warning" %}
With both `READ UNCOMMITTED` and `READ COMMITTED` isolation levels, you can’t expect results to be deterministic between successive statements of the same transaction.
{% endhint %}

### 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](https://dev.mysql.com/doc/refman/en/innodb-consistent-read.html).

#### Gap Locking at READ COMMITTED

InnoDB takes no [gap locks](/docs/server/server-usage/storage-engines/innodb/innodb-lock-modes.md#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](/docs/server/ha-and-performance/optimization-and-tuning/optimization-and-indexes/foreign-keys.md) 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](/docs/server/server-management/server-monitoring-logs/binary-log.md) enabled and [binlog\_format](/docs/server/ha-and-performance/standard-replication/replication-and-binary-log-system-variables.md#binlog_format) set to `STATEMENT`, InnoDB rejects any statement that would write rows:

```sql
SET SESSION binlog_format = STATEMENT;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
UPDATE t SET v = 9 WHERE id = 1;
ERROR 1665 (HY000): Cannot execute statement: impossible to write to binary log
since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine
limited to row-based logging. InnoDB is limited to row-logging when transaction
isolation level is READ COMMITTED or READ UNCOMMITTED.
```

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.

{% hint style="info" %}
At `READ COMMITTED`, semi-consistent reads apply only when [innodb\_snapshot\_isolation](/docs/server/server-usage/storage-engines/innodb/innodb-system-variables.md#innodb_snapshot_isolation) is disabled. That variable is enabled by default from MariaDB 11.6.2, and while it is enabled, `READ COMMITTED` performs an ordinary locking read and waits for the lock. Semi-consistent reads then apply to `READ UNCOMMITTED` only.
{% endhint %}

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](https://dev.mysql.com/doc/refman/en/innodb-consistent-read.html).

{% tabs %}
{% tab title="Current" %}
For locking reads (`SELECT` with `FOR UPDATE` or `LOCK IN SHARE MODE`), `UPDATE`, and `DELETE` statements, locking depends on whether the statement uses a unique index with a unique search condition, or a range-type search condition. MariaDB does not relax the gap locking for unique indexes.
{% endtab %}

{% tab title="< 11.0 / 10.11.2 / 10.10.3 / 10.9.5 / 10.8.7 / 10.7.8 / 10.6.12 / 10.5.19 / 10.4.28 / 10.3.38" %}
For locking reads (`SELECT` with `FOR UPDATE` or `LOCK IN SHARE MODE`), `UPDATE`, and `DELETE` statements, locking depends on whether the statement uses a unique index with a unique search condition, or a range-type search condition. For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it.
{% endtab %}
{% endtabs %}

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](/docs/server/reference/sql-statements/transactions/xa-transactions.md).

#### Snapshot Isolation and DML Operations

{% hint style="info" %}
[innodb\_snapshot\_isolation](/docs/server/server-usage/storage-engines/innodb/innodb-system-variables.md#innodb_snapshot_isolation) is enabled by default from MariaDB 11.6.2. It was added, defaulting to `OFF`, in MariaDB 10.6.18, 10.11.8, 11.0.6, 11.1.5, 11.2.4, and 11.4.2.
{% endhint %}

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**](/docs/server/reference/error-codes/mariadb-error-codes-1000-to-1099/e1020.md) **(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](/docs/server/reference/sql-statements/data-manipulation/selecting-data/select.md#lock-in-share-mode-for-update) if [autocommit](/docs/server/server-management/variables-and-modes/server-system-variables.md#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](/docs/server/reference/sql-statements/transactions/xa-transactions.md) should always use this isolation level.

### innodb\_snapshot\_isolation

If the [innodb\_snapshot\_isolation](/docs/server/server-usage/storage-engines/innodb/innodb-system-variables.md#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.

{% hint style="warning" %}
The default of `innodb_snapshot_isolation` is not the same across supported releases. It is `ON` from MariaDB 11.6.2, and `OFF` in the earlier series that introduced it, which includes the 10.11 and 11.4 long-term releases. Advice to keep the default is therefore ambiguous; set the variable explicitly.
{% endhint %}

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](/docs/server/reference/error-codes/mariadb-error-codes-1000-to-1099/e1020.md) (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

{% tabs %}
{% tab title="Current" %}
The access mode specifies whether the transaction is allowed to write data or not. By default, transactions are in `READ WRITE` mode (see the [tx\_read\_only](/docs/server/server-management/variables-and-modes/server-system-variables.md#tx_read_only) system variable). `READ ONLY` mode allows the storage engine to apply optimizations that cannot be used for transactions which write data. Note that, unlike the global [read\_only](/docs/server/server-management/variables-and-modes/server-system-variables.md#read_only) mode, the [READ\_ONLY ADMIN](/docs/server/reference/sql-statements/account-management-sql-statements/grant.md#read_only-admin) privilege doesn't allow writes, and DDL statements on temporary tables are not allowed either.
{% endtab %}

{% tab title="< 10.11.0" %}
The access mode specifies whether the transaction is allowed to write data or not. By default, transactions are in `READ WRITE` mode (see the [tx\_read\_only](/docs/server/server-management/variables-and-modes/server-system-variables.md#tx_read_only) system variable). `READ ONLY` mode allows the storage engine to apply optimizations that cannot be used for transactions which write data. Note that, unlike the global [read\_only](/docs/server/server-management/variables-and-modes/server-system-variables.md#read_only) mode, the [SUPER](/docs/server/reference/sql-statements/account-management-sql-statements/grant.md#super) privilege doesn't allow writes, and DDL statements on temporary tables are not allowed either.
{% endtab %}
{% endtabs %}

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](/docs/server/reference/sql-statements/transactions/start-transaction.md) 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](https://jira.mariadb.org/browse/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](/docs/server/server-management/install-and-upgrade-mariadb/migrating-to-mariadb/migrating-to-mariadb-from-sql-server/mariadb-transactions-and-isolation-levels-for-sql-server-users.md).
* 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](#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](#gap-locking-at-read-committed).

[innodb\_snapshot\_isolation](/docs/server/server-usage/storage-engines/innodb/innodb-system-variables.md#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](#innodb_snapshot_isolation) above.

## Examples

```sql
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
```

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

```sql
START TRANSACTION;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
ERROR 1568 (25001): Transaction characteristics can't be changed while a transaction is in progress
```

<sub>*This page is licensed: GPLv2, originally from*</sub> [<sub>*fill\_help\_tables.sql*</sub>](https://github.com/MariaDB/server/blob/main/scripts/fill_help_tables.sql)

{% @marketo/form formId="4316" %}