The COMMIT statement ends a transaction, saving any changes to the data so that they become visible to subsequent transactions. Also, unlocks metadata changed by current transaction. If autocommit is set to 1, an implicit commit is performed after each statement. Otherwise, all transactions which don't end with an explicit COMMIT are implicitly rollbacked and the changes are lost. The ROLLBACK statement can be used to do this explicitly.

The required syntax for the COMMIT statement is as follows:

COMMIT is the more important transaction terminator, as well as the more interesting one. The basic form of the COMMIT statement is simply the keyword COMMIT (the keyword WORK is simply noise and can be omitted without changing the effect).

The optional AND CHAIN clause is a convenience for initiating a new transaction as soon as the old transaction terminates. If AND CHAIN is specified, then there is effectively nothing between the old and new transactions, although they remain separate. The characteristics of the new transaction will be the same as the characteristics of the old one — that is, the new transaction will have the same access mode, isolation level and diagnostics area size (we'll discuss all of these shortly) as the transaction just terminated.

RELEASE tells the server to disconnect the client immediately after the current transaction.

There are NO RELEASE and AND NO CHAIN options. By default, commits do not RELEASE or CHAIN, but it's possible to change this default behavior with the server system variable. In this case, the AND NO CHAIN and NO RELEASE options override the server default.

See Also

• - server system variable that determines whether statements are automatically committed.

• - server system variable that determines whether COMMIT's are standard, COMMIT AND CHAIN or COMMIT RELEASE.

_{This page is licensed: CC BY-SA / Gnu FDL}

READ COMMITTED is one of the transaction isolation levels. Each consistent read, even within the same transaction, sets and reads its own fresh snapshot.

See Isolation Levels for details.

_{This page is licensed: CC BY-SA / Gnu FDL}

READ UNCOMMITTED is one of the transaction isolation levels. SELECT statements are performed in a non-locking fashion, but a possible earlier version of a row might be used.

See Isolation Levels for details.

_{This page is licensed: CC BY-SA / Gnu FDL}

MariaDB supports metadata locking. This means that when a transaction (including XA transactions) uses a table, it locks its metadata until the end of transaction. Non-transactional tables are also locked, as well as views and objects which are related to locked tables/views (stored functions, triggers, etc). When a connection tries to use a DDL statement (like an ALTER TABLE) which modifies a table that is locked, that connection is queued, and has to wait until it's unlocked. Using savepoints and performing a partial rollback does not release metadata locks.

LOCK TABLES ... WRITE are also queued. Some wrong statements which produce an error may not need to wait for the lock to be freed.

The metadata lock's timeout is determined by the value of the lock_wait_timeout server system variable (in seconds). However, note that its default value is 31536000 (1 year). If this timeout is exceeded, the following error is returned:

If the metadata_lock_info plugin is installed, the Information Schema metadata_lock_info table stores information about existing metadata locks.

_{This page is licensed: CC BY-SA / Gnu FDL}

SERIALIZABLE is one of the transaction isolation levels. Similar to REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... LOCK IN SHARE MODE if autocommit is disabled.

See Isolation Levels for details.

_{This page is licensed: CC BY-SA / Gnu FDL}

Syntax

Description

REPEATABLE READ is one of the transaction isolation levels. All consistent reads within the same transaction read the snapshot established by the first read.

See for details.

_{This page is licensed: CC BY-SA / Gnu FDL}

MariaDB has always had the and settings, which close connections after a certain period of inactivity.

However, these are by default set to a long wait period. In situations where transactions may be started, but not committed or rolled back, more granular control and a shorter timeout may be desirable so as to avoid locks being held for too long.

Syntax

Description

Syntax

Description

InnoDB supports the SQL statements SAVEPOINT,ROLLBACK TO SAVEPOINT, RELEASE SAVEPOINT and the optional WORK keyword forROLLBACK.

Each savepoint must have a legal . A savepoint is a named sub-transaction.

Normally undoes the changes performed by the whole transaction. When used with the TO clause, it undoes the changes performed after the specified savepoint, and erases all subsequent savepoints. However, all locks that have been acquired after the save point will survive. RELEASE SAVEPOINT does not rollback or commit any changes, but removes the specified savepoint.

When the execution of a trigger or a stored function begins, it is not possible to use statements which reference a savepoint which was defined from out of that stored program.

When a (including implicit commits) or a ROLLBACK statement (with no TO clause) is performed, they act on the whole transaction, and all savepoints are removed.

Errors

If COMMIT or ROLLBACK is issued and no transaction was started, no error is reported.

If SAVEPOINT is issued and no transaction was started, no error is reported but no savepoint is created. When ROLLBACK TO SAVEPOINT or RELEASE SAVEPOINT is called for a savepoint that does not exist, an error like this is issued:

_{This page is licensed: GPLv2, originally from}

Some SQL statements cause an implicit commit. As a rule of thumb, such statements are DDL statements. The same statements (except for ) produce a 1400 error ( 'XAE09') if a XA transaction is in effect.

Here is the list:

SET autocommit = 1 causes an implicit commit if the value was 0.

All these statements cause an implicit commit before execution. This means that, even if the statement fails with an error, the transaction is committed. Some of them, like CREATE TABLE ... SELECT, also cause a commit immediatly after execution. Such statements couldn't be rollbacked in any case.

The ROLLBACK statement rolls back (ends) a transaction, destroying any changes to SQL-data so that they never become visible to subsequent transactions. The required syntax for the ROLLBACK statement is as follows.

The ROLLBACK statement will either end a transaction, destroying all data changes that happened during any of the transaction, or it will just destroy any data changes that happened since you established a savepoint. The basic form of the ROLLBACK statement is just the keyword ROLLBACK (the keyword WORK is simply noise and can be omitted without changing the effect).

Extended syntax so that it is possible to set and for the following statements:

Syntax

Syntax

Description

Overview

The MariaDB XA implementation is based on the X/Open CAE document Distributed Transaction Processing: The XA Specification. This document is published by The Open Group and available at c193.htm.

XA transactions are designed to allow distributed transactions, where a transaction manager (the application) controls a transaction which involves multiple resources. Such resources are usually DBMSs, but could be resources of any type. The whole set of required transactional operations is called a global transaction. Each subset of operations which involve a single resource is called a local transaction. XA used a 2-phases commit (2PC). With the first commit, the transaction manager tells each resource to prepare an effective commit, and waits for a confirm message. The changes are not still made effective at this point. If any of the resources encountered an error, the transaction manager will rollback the global transaction. If all resources communicate that the first commit is successful, the transaction manager can require a second commit, which makes the changes effective.

In MariaDB, XA transactions can only be used with storage engines that support them. At least , , and support them. XA transactions are always supported.

Like regular transactions, XA transactions create on accessed tables.

XA transactions require as a minimum isolation level. However, distributed transactions should always use .

Trying to start more than one XA transaction at the same time produces a 1400 error ( 'XAE09'). The same error is produced when attempting to start an XA transaction while a regular transaction is in effect. Trying to start a regular transaction while an XA transaction is in effect produces a 1399 error ( 'XAE07').

The for regular transactions produce a 1400 error ( 'XAE09') if a XA transaction is in effect.

Internal XA vs External XA

XA transactions are an overloaded term in MariaDB. If a is XA-capable, it can mean one or both of these:

• It supports MariaDB's internal two-phase commit API. This is transparent to the user. Sometimes this is called "internal XA", since MariaDB's internal can handle coordinating these transactions.

• It supports XA transactions, with the XA START, XA PREPARE, XA COMMIT, etc. statements. Sometimes this is called "external XA", since it requires the use of an external transaction coordinator to use this feature properly.

Transaction Coordinator Log

If you have two or more XA-capable storage engines enabled, then a transaction coordinator log must be available.

There are currently two implementations of the transaction coordinator log:

• Binary log-based transaction coordinator log

• Memory-mapped file-based transaction coordinator log

If the is enabled on a server, then the server will use the binary log-based transaction coordinator log. Otherwise, it will use the memory-mapped file-based transaction coordinator log.

See for more information.

Syntax

The interface to XA transactions is a set of SQL statements starting with XA. Each statement changes a transaction's state, determining which actions it can perform. A transaction which does not exist is in the NON-EXISTING state.

When trying to execute an operation which is not allowed for the transaction's current state, an error is produced:

XA START

XA START (or BEGIN) starts a transaction and defines its xid (a transaction identifier). The new transaction will be in ACTIVE state.

The xid can have 3 components, though only the first one is mandatory. gtrid is a quoted string representing a global transaction identifier. bqual is a quoted string representing a local transaction identifier. formatID is an unsigned integer indicating the format used for the first two components; if not specified, defaults to 1. MariaDB does not interpret in any way these components, and only uses them to identify a transaction. xids of transactions in effect must be unique.

Using the JOIN or RESUME keywords will currently cause an error to be returned.

An exception to this is that XA START xid RESUME will resume the transaction if the xid is the same as the previous xid used in XA END xid. This simply undoes the XA END and moves it from the IDLE state back into ACTIVE.

XA END

XA END declares that the specified ACTIVE transaction is finished and it changes its state to IDLE. SUSPEND [FOR MIGRATE] has no effect.

XA PREPARE

XA PREPARE prepares an IDLE transaction for commit, changing its state to PREPARED. Prepared transactions are stored persistently and will survive disconnects and server crashes, and must be explicitly committed or rolled back.

XA COMMIT

XA COMMIT definitely commits and terminates a transaction which has already been PREPARED. If the ONE PHASE clause is specified, this statements performs a 1-phase commit on an IDLE transaction.

XA ROLLBACK

XA ROLLBACK rolls back and terminates an IDLE or PREPARED transaction.

XA RECOVER

The XA RECOVER statement shows information about all transactions which are in the PREPARED state. It does not matter which connection created the transaction: if it has been PREPARED, it appears. But this does not mean that a connection can commit or rollback a transaction which was started by another connection. Note that transactions using a 1-phase commit are never in the PREPARED state, so they cannot be shown by XA RECOVER.

XA RECOVER produces four columns:

You can use XA RECOVER FORMAT='SQL' to get the data in a human readable form that can be directly copy-pasted into XA COMMIT or XA ROLLBACK. This is particularly useful for binary xid generated by some transaction coordinators.

formatID is the formatID part of xid.

data are the gtrid and bqual parts of xid, concatenated.

gtrid_length and bqual_length are the lengths of gtrid and bqual, respectively.

Examples

2-phases commit:

1-phase commit:

Human-readable:

Known Issues

MariaDB Galera Cluster

does not support XA transactions.

However, builds include a built-in plugin called wsrep. Consequently, these builds have multiple XA-capable storage engines by default, even if the only "real" storage engine that supports external enabled on these builds by default is . Therefore, when using one these builds MariaDB would be forced to use a by default, which could have performance implications.

See for more information.

Incompatibility with XA behavior

As a work-around, the variable can be set to TRUE to re-enable the old behavior and roll back XA transactions in the PREPAREd state at disconnect. This is non-standard behaviour, and is not recommended for new applications. If rollback-at-disconnect is desired, it is better to use a normal (non-XA) transaction.

_{This page is licensed: CC BY-SA / Gnu FDL}