Transactions for MariaDB Xpand

Overview

MariaDB Xpand is a distributed SQL database that provides strong consistency, data integrity, and ACID compliant transactional processing:

  • Xpand enables autocommit by default

  • Xpand performs implicit commits for some operations

  • BEGIN, START TRANSACTION, COMMIT, and ROLLBACK are supported

autocommit

The autocommit system variable is enabled (1) by default.

To disable autocommit globally for the whole cluster, execute SET GLOBAL:

SET GLOBAL autocommit=0;

To disable autocommit for a single session, execute SET or SET SESSION in that session:

SET autocommit=0;

When autocommit is enabled for a given session and the session has not started an explicit transaction with BEGIN or START TRANSACTION, Xpand automatically performs a COMMIT after every statement executed by the session. In contrast, when the session has started an explicit transaction with BEGIN or START TRANSACTION in this scenario, Xpand only commits the transaction when an explicit COMMIT is executed or some other operation causes an implicit commit.

The autocommit system variable can be disabled by setting it to 0. When autocommit is disabled for a given session, Xpand only commits in-progress transactions when an explicit COMMIT is executed or some other operation causes an implicit commit.

Implicit Commits

With Xpand, some operations can cause in-progress transactions to be implicitly committed:

  • When a session has an in-progress transaction and the same session executes ALTER TABLE, the implicit commit behavior depends on whether the table being altered is a regular table or a temporary table:

    • When a regular table is being altered, the in-progress transaction is implicitly committed.

    • When a temporary table is being altered, the in-progress transaction is not implicitly committed.

  • When a session has an in-progress transaction and the same session executes one of the following statements, the in-progress transaction is not implicitly committed:

    • ANALYZE TABLE

    • FLUSH PRIVILEGES

    • FLUSH TABLES

    • REPAIR TABLE

    • UNLOCK TABLES

  • When any session has in-progress transactions and the cluster is restarted with clx dbrestart, all in-progress transactions are implicitly committed.

  • When a session executes statements from a file using SOURCE FILE_NAME, the statements from the file are not implicitly committed.

  • When a session has an in-progress transaction and the session encounters one of the following errors, the in-progress transaction is implicitly committed:

    • Unrecognized type name: Unknown type

    • Unknown Session

Explicit Transactions

An explicit transaction can be started with BEGIN or START TRANSACTION.

To start an explicit transaction, execute BEGIN:

BEGIN;

Or execute START TRANSACTION:

START TRANSACTION;

Xpand does not support START TRANSACTION WITH CONSISTENT SNAPSHOT.

When an explicit transaction is started and autocommit is enabled, autocommit behavior is disabled until an explicit COMMIT or ROLLBACK is executed or some other operation causes an implicit commit

Feature Interactions

Temporary Tables

  • When a session has one or more temporary tables and the session executes ROLLBACK, the temporary tables are automatically dropped.

In-memory Tables

  • When an in-progress transaction is rolled back and the transaction has made changes to in-memory tables, the changes to in-memory tables are rolled back.

System Variables

  • When a system variable's global or session value is changed with SET, the operation does not require an explicit COMMIT.

  • Xpand does not support the in_transaction system variable.