Syntax

Description

The lock_type can be one of:

MariaDB enables client sessions to acquire table locks explicitly for the purpose of cooperating with other sessions for access to tables, or to prevent other sessions from modifying tables during periods when a session requires exclusive access to them. A session can acquire or release locks only for itself. One session cannot acquire locks for another session or release locks held by another session.

Locks may be used to emulate transactions or to get more speed when updating tables.

LOCK TABLES explicitly acquires table locks for the current client session. Table locks can be acquired for base tables or views. To use LOCK TABLES, you must have the LOCK TABLES privilege, and the SELECT privilege for each object to be locked. See .

For view locking, LOCK TABLES adds all base tables used in the view to the set of tables to be locked and locks them automatically. If you lock a table explicitly with LOCK TABLES, any tables used in triggers are also locked implicitly, as described in .

explicitly releases any table locks held by the current session.

Aliases

Aliases need to correspond to the aliases used in prior SQL statements in the session. For example:

WAIT/NOWAIT

Set the lock wait timeout. See .

Limitations

• LOCK TABLES . You may experience crashes or locks when used with Galera.

• LOCK TABLES works on XtraDB/InnoDB tables only if the system variable is set to 1 (the default) and is set to 0 (1 is default). Please note that no error message will be returned on LOCK TABLES with innodb_table_locks = 0.

• LOCK TABLES

• While a connection holds an explicit lock on a table, it cannot access a non-locked table. If you try, the following error will be produced:

• While a connection holds an explicit lock on a table, it cannot issue the following: INSERT DELAYED, CREATE TABLE, CREATE TABLE ... LIKE, and DDL statements involving stored programs and views (except for triggers). If you try, the following error will be produced:

• LOCK TABLES can not be used in stored routines - if you try, the following error will be produced on creation:

See Also

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

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}

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}

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 for details.

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

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}

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 for details.

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

MariaDB has always had the wait_timeout and interactive_timeout 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.

These accept a time in seconds to time out, by closing the connection, transactions that are idle for longer than this period. By default all are set to zero, or no timeout.

affects all transactions, affects write transactions only and affects read transactions only. The latter two variables work independently. However, if either is set along with , the settings for or will take precedence.

Examples

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

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).

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. The AND NO CHAIN option just tells your DBMS to end the transaction — that is, these four SQL statements are equivalent:

All of them end a transaction without saving any transaction characteristics. The only other options, the equivalent statements ...

... both tell your DBMS to end a transaction, but to save that transaction's characteristics for the next transaction.

ROLLBACK is much simpler than COMMIT: it may involve no more than a few deletions (of Cursors, locks, prepared SQL statements and log-file entries). It's usually assumed that ROLLBACK can't fail, although such a thing is conceivable (for example, an encompassing transaction might reject an attempt to ROLLBACK because it's lining up for a COMMIT).

ROLLBACK cancels all effects of a transaction. It does not cancel effects on objects outside the DBMS's control (for example the values in host program variables or the settings made by some SQL/CLI function calls). But in general, it is a convenient statement for those situations when you say "oops, this isn't working" or when you simply don't care whether your temporary work becomes permanent or not.

Here is a moot question. If all you've been doing is SELECTs, so that there have been no data changes, should you end the transaction with ROLLBACK or COMMIT? It shouldn't really matter because both ROLLBACK and COMMIT do the same transaction-terminating job. However, the popular conception is that ROLLBACK implies failure, so after a successful series of SELECT statements the convention is to end the transaction with COMMIT rather than ROLLBACK.

MariaDB (and most other DBMSs) supports rollback of SQL-data change statements, but not of SQL-Schema statements. This means that if you use any of CREATE, ALTER, DROP, GRANT, REVOKE, you are implicitly committing at execution time.

The result will be that both the INSERT and the DROP will go through as separate transactions so the ROLLBACK will have no effect.

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

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}

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

Syntax

Description

The lock wait timeout can be explicitly set in the statement by using either WAIT n (to set the wait in seconds) or NOWAIT, in which case the statement will immediately fail if the lock cannot be obtained. WAIT 0 is equivalent to NOWAIT.

See Also

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

Syntax

Description

UNLOCK TABLES

Syntax

Description

The START TRANSACTION or BEGIN statement begins a new transaction. commits the current transaction, making its changes permanent. rolls back the current transaction, canceling its changes. The statement disables or enables the default autocommit mode for the current session.

START TRANSACTION and SET autocommit = 1 implicitly commit the current transaction, if there is one.

The optional WORK keyword is supported forCOMMIT and ROLLBACK, as are theCHAIN and RELEASE clauses.CHAIN and RELEASE can be used for additional control over transaction completion. The value of the system variable determines the default completion behavior.

The AND CHAIN clause causes a new transaction to begin as soon as the current one ends, and the new transaction has the same isolation level as the just-terminated transaction. The RELEASE clause causes the server to disconnect the current client session after terminating the current transaction. Including the NO keyword suppressesCHAIN or RELEASE completion, which can be useful if the system variable is set to cause chaining or release completion by default.

Access Mode

It is not permitted to specify both READ WRITE and READ ONLY in the same statement.

READ WRITE and READ ONLY can also be specified in the statement, in which case the specified mode is valid for all sessions, or for all subsequent transaction used by the current session.

autocommit

By default, MariaDB runs with mode enabled. This means that as soon as you execute a statement that updates (modifies) a table, MariaDB stores the update on disk to make it permanent. To disable autocommit mode, use the following statement:

After disabling autocommit mode by setting the autocommit variable to zero, changes to transaction-safe tables (such as those for InnoDB orNDBCLUSTER) are not made permanent immediately. You must use COMMIT to store your changes to disk or ROLLBACK to ignore the changes.

To disable autocommit mode for a single series of statements, use the START TRANSACTION statement.

DDL Statements

DDL statements (CREATE, ALTER, DROP) and administrative statements (FLUSH, RESET, OPTIMIZE, ANALYZE, CHECK, REPAIR, CACHE INDEX), transaction management statements (BEGIN, START TRANSACTION) and LOAD DATA INFILE, cause an implicit

Transactions cannot be used in Stored Functions or Triggers. In Stored Procedures and Events BEGIN is not allowed, so you should use START TRANSACTION instead.

A transaction acquires a on every table it accesses to prevent other connections from altering their structure. The lock is released at the end of the transaction. This happens even with non-transactional storage engines (like or ), so it makes sense to use transactions with non-transactional tables.

in_transaction

The system variable is a session-only, read-only variable that returns 1 inside a transaction, and 0 if not in a transaction.

WITH CONSISTENT SNAPSHOT

The WITH CONSISTENT SNAPSHOT option starts a consistent read for storage engines such as that can do so, the same as if a START TRANSACTION followed by a SELECT from any InnoDB table was issued.

See .

Examples

See Also

_{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 SHUTDOWN) produce a 1400 error (SQLSTATE '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.

If you are not sure whether a statement has implicitly committed the current transaction, you can query the server system variable.

Note that when a transaction starts (not in autocommit mode), all locks acquired with are released. And acquiring such locks always commits the current transaction. To preserve the data integrity between transactional and non-transactional tables, the function can be used.

Exceptions

These statements do not cause an implicit commit in the following cases:

• and , when the TEMPORARY keyword is used.

  • However, causes an implicit commit even when used on a temporary table.

• and , when used to create a UDF (instead of a stored function). However, and cause commits even when used with temporary tables.

_{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 for details.

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

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}