tx_isolation

Overview

In 23.08 ES, 23.07 ES, 10.6 ES, 10.6 CS, 10.5 ES, 10.5 CS, 10.4 ES, 10.4 CS, 10.3 ES, 10.3 CS, 10.2 ES, 10.2 CS:

Sets the default transaction isolation level.

In 23.08 ES, 23.07 ES, 10.6 ES, 10.5 ES, 10.4 ES:

Sets the default transaction isolation level. SkySQL interfaces refer to this system variable as transaction_isolation.

See also: System Variables for MariaDB Enterprise Server 23.08, in 23.07 ES, in 10.6 ES, in 10.5 ES, in 10.4 ES, in 10.3 ES, in 10.2 ES, in 10.6 CS, in 10.5 CS, in 10.4 CS, in 10.3 CS, and in 10.2 CS

See also: System Variables for MariaDB Enterprise Server 23.08, in 23.07 ES, in 10.6 ES, in 10.5 ES, and in 10.4 ES

USAGE

SkySQL interfaces refer to the tx_isolation system variable as transaction_isolation. The system variable can be configured in Configuration ManagerConfiguration Manager for some topologies using that name.

The session value of the tx_isolation system variable can also be set dynamically at runtime by executing SET SESSION:

SET SESSION tx_isolation='READ-COMMITTED';

The session value of this system variable can also be set indirectly by executing SET [SESSION] TRANSACTION.

The tx_isolation system variable can be set indirectly by setting the transaction_isolation option in a configuration file :

[mariadb]
transaction_isolation=READ-COMMITTED

The global value of the tx_isolation system variable can also be set dynamically at runtime by executing SET GLOBAL:

SET GLOBAL tx_isolation='READ-COMMITTED';

The session value of the tx_isolation system variable can also be set dynamically at runtime by executing SET SESSION:

SET SESSION tx_isolation='READ-COMMITTED';

When the tx_isolation system variable is set dynamically at runtime, its value will be reset the next time the server restarts. To make the value persist on restart, set the transaction_isolation option in a configuration file too.

The global and session values of this system variable can also be set indirectly by executing SET GLOBAL TRANSACTION and SET SESSION TRANSACTION.

Note

Support for Transaction Isolation is storage engine dependent. That is, it is available for InnoDB, but may not be supported or fully supported by other storage engine (such as, MariaDB ColumnStore).

DETAILS

The tx_isolation system variable displays the current transaction isolation in different contexts:

  • The global value displays the transaction isolation for new connections.

  • The session value displays the transaction isolation for the current connection.

  • The value can also refer to the transaction isolation of the next transaction in a special context. After the next transaction completes, the transaction isolation is reset to the session value.

SYNONYMS

SCHEMA

PARAMETERS

Command-line

--transaction_isolation={READ-UNCOMMITTED|READ-COMMITTED|REPEATABLE-READ|SERIALIZABLE}

Configuration file

Supported via transaction_isolation

Dynamic

Yes

Scope

Global, Session

Data Type

ENUM (READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE)

Product Default Value

REPEATABLE-READ

SKYSQL

Cloud

SkySQL Topology

ES Ver

Service Default

AWS

Multi-Node Analytics

10.6

REPEATABLE-READ (choices: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE)

Single Node Analytics

10.6

REPEATABLE-READ (choices: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE)

Replicated Transactions

10.4

REPEATABLE-READ (choices: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE)

10.5

REPEATABLE-READ (choices: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE)

10.6

REPEATABLE-READ (choices: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE)

Single Node Transactions

10.4

REPEATABLE-READ (choices: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE)

10.5

REPEATABLE-READ (choices: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE)

10.6

REPEATABLE-READ (choices: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE)

GCP

Multi-Node Analytics

10.6

REPEATABLE-READ (choices: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE)

Single Node Analytics

10.6

REPEATABLE-READ (choices: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE)

Replicated Transactions

10.4

REPEATABLE-READ (choices: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE)

10.5

REPEATABLE-READ (choices: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE)

10.6

REPEATABLE-READ (choices: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE)

Single Node Transactions

10.4

REPEATABLE-READ (choices: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE)

10.5

REPEATABLE-READ (choices: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE)

10.6

REPEATABLE-READ (choices: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE)

Cloud

SkySQL Topology

ES Ver

Service Default

AWS

ColumnStore Data Warehouse

10.6

REPEATABLE-READ

Enterprise Server With Replica(s)

10.4

REPEATABLE-READ

10.5

REPEATABLE-READ

10.6

REPEATABLE-READ

Enterprise Server Single Node

10.4

REPEATABLE-READ

10.5

REPEATABLE-READ

10.6

REPEATABLE-READ

GCP

ColumnStore Data Warehouse

10.6

REPEATABLE-READ

Enterprise Server With Replica(s)

10.4

REPEATABLE-READ

10.5

REPEATABLE-READ

10.6

REPEATABLE-READ

Enterprise Server Single Node

10.4

REPEATABLE-READ

10.5

REPEATABLE-READ

10.6

REPEATABLE-READ

PRIVILEGES

The tx_isolation system variable requires the SUPER privilege to dynamically set its global value at runtime.

The tx_isolation system variable does not require any special privilege to dynamically set its session value at runtime.

EXAMPLES

Dynamically Setting the Global Transaction Isolation

The global value of the tx_isolation system variable can be dynamically set in two ways.

Firstly, it can be set by executing SET GLOBAL:

SHOW GLOBAL VARIABLES LIKE 'tx_isolation';

+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+

SET GLOBAL tx_isolation='READ-COMMITTED';

SHOW GLOBAL VARIABLES LIKE 'tx_isolation';

+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+

Secondly, it can be set by executing SET GLOBAL TRANSACTION:

SHOW GLOBAL VARIABLES LIKE 'tx_isolation';

+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

SHOW GLOBAL VARIABLES LIKE 'tx_isolation';

+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+

Dynamically Setting the Session Transaction Isolation

The session value of the tx_isolation system variable can be dynamically set in two ways.

Firstly, it can be set by executing SET SESSION:

SHOW SESSION VARIABLES LIKE 'tx_isolation';

+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+

SET SESSION tx_isolation='READ-COMMITTED';

SHOW SESSION VARIABLES LIKE 'tx_isolation';

+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+

Secondly, it can be set by executing SET SESSION TRANSACTION:

SHOW SESSION VARIABLES LIKE 'tx_isolation';

+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

SHOW SESSION VARIABLES LIKE 'tx_isolation';

+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+

Dynamically Setting the Next Transaction's Isolation

The tx_isolation system variable can refer to the transaction isolation of the next transaction. After the next transaction completes, the transaction isolation is reset to the session value. The transaction isolation of the next transaction can be dynamically set in two ways.

Firstly, it can be set by executing SET @@tx_isolation, which evokes some non-standard behavior compared to other system variables:

SHOW SESSION VARIABLES LIKE 'tx_isolation';

+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+

SET @@tx_isolation='READ-COMMITTED';

BEGIN;

INSERT INTO test.contacts VALUES
    (1, 'William', 'Longfellow', 'w.longfellow@example.edu');

SELECT TRX_STATE, TRX_ISOLATION_LEVEL
FROM information_schema.INNODB_TRX
WHERE TRX_MYSQL_THREAD_ID=CONNECTION_ID();

+-----------+---------------------+
| TRX_STATE | TRX_ISOLATION_LEVEL |
+-----------+---------------------+
| RUNNING   | READ COMMITTED      |
+-----------+---------------------+

COMMIT;

Secondly, it can be set by executing SET TRANSACTION with no GLOBAL or SESSION keyword:

SHOW SESSION VARIABLES LIKE 'tx_isolation';

+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN;

INSERT INTO test.contacts VALUES
    (1, 'William', 'Longfellow', 'w.longfellow@example.edu');

SELECT TRX_STATE, TRX_ISOLATION_LEVEL
FROM information_schema.INNODB_TRX
WHERE TRX_MYSQL_THREAD_ID=CONNECTION_ID();

+-----------+---------------------+
| TRX_STATE | TRX_ISOLATION_LEVEL |
+-----------+---------------------+
| RUNNING   | READ COMMITTED      |
+-----------+---------------------+

COMMIT;

If either of these options are executed while already in a transaction, then an error with the ER_CANT_CHANGE_TX_CHARACTERISTICS error code will be raised:

BEGIN;

SET @@tx_isolation='READ-COMMITTED';

ERROR 1568 (25001): Transaction characteristics can't be changed while a transaction is in progress

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

ERROR 1568 (25001): Transaction characteristics can't be changed while a transaction is in progress

Dynamically Resetting Global Value to Default

The global value of the tx_isolation system variable can be dynamically reset to the default value by setting it to DEFAULT:

SET GLOBAL tx_isolation='READ-COMMITTED';

SHOW GLOBAL VARIABLES LIKE 'tx_isolation';

+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+

SET GLOBAL tx_isolation=DEFAULT;

SHOW GLOBAL VARIABLES LIKE 'tx_isolation';

+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+

Resetting Session Value to Global Value

The session value of the tx_isolation system variable can be reset to the global value by setting it to DEFAULT:

SET SESSION tx_isolation='READ-COMMITTED';

SHOW GLOBAL VARIABLES LIKE 'tx_isolation';

+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+

SHOW SESSION VARIABLES LIKE 'tx_isolation';

+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+

SET SESSION tx_isolation=DEFAULT;

SHOW SESSION VARIABLES LIKE 'tx_isolation';

+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+

Invalid Value for Variable

If a user tries to set the tx_isolation system variable to an invalid value, then the operation will fail with the ER_WRONG_VALUE_FOR_VAR error code:

SET GLOBAL tx_isolation=-1;

ERROR 1231 (42000): Variable 'tx_isolation' can't be set to the value of '-1'

Invalid Type for Variable

If a user tries to set the tx_isolation system variable to an invalid argument, then the operation will fail with the ER_WRONG_TYPE_FOR_VAR error code:

SET GLOBAL tx_isolation=1.0;

ERROR 1232 (42000): Incorrect argument type to variable 'tx_isolation'

ERROR HANDLING

FEATURE INTERACTION

RESPONSES

DIAGNOSIS

ISO 9075:2016

CHANGE HISTORY

Release Series

History

23.08 Enterprise

  • Present starting in MariaDB Enterprise Server 23.08.0.

23.07 Enterprise

  • Present starting in MariaDB Enterprise Server 23.07.0.

10.6 Enterprise

  • Present starting in MariaDB Enterprise Server 10.6.4-1.

10.6 Community

  • Present starting in MariaDB Community Server 10.6.0.

10.5 Enterprise

  • Present starting in MariaDB Enterprise Server 10.5.3-1.

10.5 Community

  • Present starting in MariaDB Community Server 10.5.0.

10.4 Enterprise

  • Present starting in MariaDB Enterprise Server 10.4.6-1.

10.4 Community

  • Present starting in MariaDB Community Server 10.4.0.

10.3 Enterprise

  • Present starting in MariaDB Enterprise Server 10.3.16-1.

10.3 Community

  • Present starting in MariaDB Community Server 10.3.0.

10.2 Enterprise

  • Present starting in MariaDB Enterprise Server 10.2.25-1.

10.2 Community

  • Present starting in MariaDB Community Server 10.2.0.

Release Series

History

23.08 Enterprise

  • Present starting in MariaDB Enterprise Server 23.08.0.

23.07 Enterprise

  • Present starting in MariaDB Enterprise Server 23.07.0.

10.6 Enterprise

  • Present starting in MariaDB Enterprise Server 10.6.4-1.

10.5 Enterprise

  • Present starting in MariaDB Enterprise Server 10.5.3-1.

10.4 Enterprise

  • Present starting in MariaDB Enterprise Server 10.4.6-1.

EXTERNAL REFERENCES