tx_isolation

USAGE

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.

PARAMETERS

Command-line

Not Supported

Configuration file

Not Supported

Dynamic

Yes

Scope

Global, Session

Data Type

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

Default Value

REPEATABLE-READ

SKYSQL

Cloud

SkySQL Topology

ES Ver

Default

AWS

HA (Primary/Replica)

10.4

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

AWS

HA (Primary/Replica)

10.5

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

AWS

Transactional Standalone

10.4

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

AWS

Transactional Standalone

10.5

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

AWS

Distributed Transactions

10.5

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

GCP

ColumnStore

10.5

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

GCP

ColumnStore Multi

10.5

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

GCP

Galera

10.4

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

GCP

Galera

10.5

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

GCP

HA (Primary/Replica)

10.4

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

GCP

HA (Primary/Replica)

10.5

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

GCP

HTAP

10.5

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

GCP

Transactional Standalone

10.4

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

GCP

Transactional Standalone

10.5

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

GCP

Distributed Transactions

10.5

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

See MariaDB SkySQL documentation to learn more about the MariaDB SkySQL database as a service.

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'

CHANGE HISTORY

Release Series

History

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.

EXTERNAL REFERENCES