tx_isolation
This page is part of MariaDB's Enterprise Documentation.
The parent of this page is: System Variables for MariaDB Enterprise Server
Topics on this page:
Overview
Default transaction isolation level
See also: System Variables for MariaDB Enterprise Server 10.6, in 10.6 CS, in 10.5 ES, in 10.5 CS, in 10.4 ES, in 10.4 CS, in 10.3 ES, in 10.3 CS, in 10.2 ES, and in 10.2 CS
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 |
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 |
|
10.6 Community |
|
10.5 Enterprise |
|
10.5 Community |
|
10.4 Enterprise |
|
10.4 Community |
|
10.3 Enterprise |
|
10.3 Community |
|
10.2 Enterprise |
|
10.2 Community |
|
EXTERNAL REFERENCES
Information specific to MariaDB SkySQL can be found on the tx_isolation page in the SkySQL Documentation.