Transaction Isolation Levels for MariaDB Xpand

Overview

MariaDB Xpand uses transaction isolation levels and Multi-Version Concurrency Control (MVCC) to determine which data is visible for a given transaction.

Supported Transaction Isolation Levels

MariaDB Xpand supports the following transaction isolation levels to user transactions:

  • Repeatable Read

Transaction Visibility

Each transaction isolation level has different transaction visibility rules. The rules depend on the Transaction, Invocation, and Commit IDs generated by Xpand.

MariaDB Xpand does not support the Read Uncommitted isolation level, so an uncommitted transaction is never visible to other transactions. Once the transaction commits, it generates a commit identifier (cid). The commit identifier (cid) is used to determine whether the transaction's modifications should be visible to another transaction.

The following table describes the transaction visibility rules for each transaction isolation level:

Isolation Level

Snapshot Anchor

Comment

Repeatable Read (default)

transaction

  • Xpand's implementation of Repeatable Read is more strict than the ANSI standard's definition.

  • It allows a per-transaction consistent snapshot read.

  • Each subsequent statement in a transaction sees the database at the start of transaction.

  • Transactions may also observe changes to the database made within the transaction.

  • Rows are visible for a transaction when the transaction's xid > the row version's cid.

Read Committed

statement

  • Xpand's implementation of Read Committed is more strict than the ANSI standard's definition.

  • It allows a per-statement consistent snapshot read.

  • Each subsequent statement in a transaction gets a new iid, so every new statement can see rows committed before statement started executing (but never during).

  • Rows are visible for a statement when the statement's iid > the row version's cid.

Serializable

transaction

  • Note: The Serializable transaction isolation level is not currently available to end user transactions.

  • Strict ANSI isolation level.

  • Xpand uses Serializable internally to perform data moves within the cluster.

  • Rows are visible for a transaction when the transaction's xid > the row version's cid.

  • Xpand returns an error when the MVCC scheduler cannot guarantee transaction serializability.

The following diagram shows how transaction visibility works at different isolation levels.

Xpand Isolation Levels

Checking the Transaction Isolation Level

MariaDB Xpand only supports the Repeatable Read transaction isolation level for end user transactions. To confirm that your session is using a supported transaction isolation level, check the session value of the tx_isolation system variable:

SHOW SESSION VARIABLES LIKE 'tx_isolation';

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

Setting the Transaction Isolation Level

There are two methods to set your session's transaction isolation level.

The tx_isolation system variable can be directly set by executing SET SESSION:

SET SESSION tx_isolation='REPEATABLE-READ';

It can also be set by executing SET SESSION TRANSACTION:

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;