Transaction Management

This section describes transaction management in Exasol.

Exasol ensures multi-user capability through the implementation of a transaction management system (TMS). This means that requests from different users can be processed in parallel. This section explains the basic concept of transactions and provides tips and recommendations for working with the Exasol TMS.

Concept

A transaction consists of several SQL statements. These are either confirmed with a COMMIT statement or undone with a ROLLBACK statement.

Example
-- Transaction 1
CREATE SCHEMA my_schema; 
COMMIT;

-- Transaction 2
CREATE TABLE t (i DECIMAL); 
SELECT * FROM t;
ROLLBACK;

-- Transaction 3
CREATE TABLE t (i VARCHAR(20)); 
COMMIT;

A transaction-based system ensures the maintenance of complete transaction security. Each transaction returns a correct result and leaves the database in a consistent state. To ensure this, the transaction must comply with ACID principles:

  • Atomicity: The transaction is either fully executed or not at all.
  • Consistency: The transaction is given the internal consistency of the database.
  • Isolation: The transaction is executed as if it is the only transaction in the system.
  • Durability: All changes to a completed transaction confirmed with COMMIT remain intact.

Exasol supports the Serializable transaction isolation level. This indicates that each transaction is carried out as if it was a part of a sequence even though transactions can run in parallel. Serialization helps ensure data consistency, but can also lead to some issues, such as: 

  • Transactions must wait for a commit from an earlier transaction before it can continue.
  • Transaction collisions for mixed read/write transactions, which results in a forced rollback of a transaction.

To ensure compliance with the ACID principles, every transaction is subject to an evaluation by the TMS. If necessary, the TMS intervenes and automatically rectifies conflicts through the enforcement of waiting times or by rolling back transactions in the event of a collision.

The information about occurring transaction conflicts is available in system tables EXA_USER_IMPERSONATION​_LAST_DAY and EXA_DBA_TRANSACTION​_CONFLICTS.

To keep the number of colliding transactions as low as possible, Exasol supports the "MultiCopy Format". This keeps multiple versions of every database object (temporarily). It allows the system throughput (number of fully executed transactions per unit of time) to be significantly increased compared to databases with "SingleCopy format".

The individual transactions are isolated from one another by the TMS by a lock procedure. The granularity of the lock procedure always surrounds an entire database object, for example, one schema or one table. This means that two transactions cannot simultaneously update different rows of a table.

Due to the TMS, for each transaction that is started, you will have one of the following scenarios:

  • The transaction runs till the end.
  • The transaction runs till the end, but waiting times occur due to a requirement to wait for other transactions to finish.
  • The transaction cannot be completed due to collisions with other transactions and is rolled back. In this case, you can repeat the transaction later.

Differences with Other Systems

Some of the other database systems have only partially implemented the transaction model and sometimes conceal transactions from the user. For example, the system may directly store schema statements (CREATE SCHEMA or CREATE TABLE) persistently in the database.

This reduces the risk of a collision when simultaneously executing transactions with schema statements, but has the disadvantage of a rollback. For example, it cannot undo the schema statement that has been executed. In contrast, the Exasol TMS does not conceal transactions from the user and does not persistently store statements in the database automatically.

Recommendations

To minimize the risk of transaction conflicts during parallel access by multiple users, the interfaces to Exasol (drivers) have the option AUTOCOMMIT=ON set by default. In AUTOCOMMIT mode, successfully completed SQL statements are automatically saved persistently.

However, for the parts that affect the performance of the system, it is better to not run a COMMIT after each SQL statement. This is true if intermediate tables, which are not intended to be saved persistently, are computed in scripts. Therefore, you can disable this option with the command SET AUTOCOMMIT OFF.

AUTOCOMMIT=OFF increases the risk of collisions and can affect other users negatively.

If the autocommit mode is disabled, the option -x in the EXAplus console is recommended. This causes the SQL script to be aborted if an error occurs during an automatic rollback after a transaction conflict. If batch scripts are started without this option, processing of the SQL statement sequence continues despite the error, it could lead to incorrect results and should be avoided.

For more information about transaction conflicts, see Transaction Conflicts for Mixed Read/Write Transactions.