13.3 Transactional and Locking Statements

MySQL supports local transactions (within a given client session) through statements such as note 'SET autocommit': commit, note 'START TRANSACTION': commit, note 'COMMIT': commit, and note 'ROLLBACK': commit. See note commit::. XA transaction support enables MySQL to participate in distributed transactions as well. See note xa::.

 File: manual.info.tmp, Node: commit, Next: cannot-roll-back, Prev: sql-transactional-statements, Up: sql-transactional-statements

13.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements

 START TRANSACTION
     [TRANSACTION_CHARACTERISTIC [, TRANSACTION_CHARACTERISTIC] ...]

 TRANSACTION_CHARACTERISTIC: {
     WITH CONSISTENT SNAPSHOT
   | READ WRITE
   | READ ONLY
 }

 BEGIN [WORK]
 COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
 ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
 SET autocommit = {0 | 1}

These statements provide control over use of transactions:

By default, MySQL runs with autocommit mode enabled. This means that, when not otherwise inside a transaction, each statement is atomic, as if it were surrounded by 'START TRANSACTION' and 'COMMIT'. You cannot use 'ROLLBACK' to undo the effect; however, if an error occurs during statement execution, the statement is rolled back.

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

 START TRANSACTION;
 SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
 UPDATE table2 SET summary=@A WHERE type=1;
 COMMIT;

With 'START TRANSACTION', autocommit remains disabled until you end the transaction with 'COMMIT' or 'ROLLBACK'. The autocommit mode then reverts to its previous state.

'START TRANSACTION' permits several modifiers that control transaction characteristics. To specify multiple modifiers, separate them by commas.

Important:

Many APIs used for writing MySQL client applications (such as JDBC) provide their own methods for starting transactions that can (and sometimes should) be used instead of sending a 'START TRANSACTION' statement from the client. See *note connectors-apis::, or the documentation for your API, for more information.

To disable autocommit mode explicitly, use the following statement:

 SET autocommit=0;

After disabling autocommit mode by setting the 'autocommit' variable to zero, changes to transaction-safe tables (such as those for note 'InnoDB': innodb-storage-engine. or note 'NDB': mysql-cluster.) are not made permanent immediately. You must use *note 'COMMIT': commit. to store your changes to disk or 'ROLLBACK' to ignore the changes.

'autocommit' is a session variable and must be set for each session. To disable autocommit mode for each new connection, see the description of the 'autocommit' system variable at *note server-system-variables::.

'BEGIN' and 'BEGIN WORK' are supported as aliases of 'START TRANSACTION' for initiating a transaction. 'START TRANSACTION' is standard SQL syntax, is the recommended way to start an ad-hoc transaction, and permits modifiers that 'BEGIN' does not.

The 'BEGIN' statement differs from the use of the 'BEGIN' keyword that starts a note 'BEGIN ... END': begin-end. compound statement. The latter does not begin a transaction. See note begin-end::.

Note:

Within all stored programs (stored procedures and functions, triggers, and events), the parser treats 'BEGIN [WORK]' as the beginning of a note 'BEGIN ... END': begin-end. block. Begin a transaction in this context with note 'START TRANSACTION': commit. instead.

The optional 'WORK' keyword is supported for 'COMMIT' and 'ROLLBACK', as are the 'CHAIN' and 'RELEASE' clauses. 'CHAIN' and 'RELEASE' can be used for additional control over transaction completion. The value of the 'completion_type' system variable determines the default completion behavior. See *note server-system-variables::.

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 new transaction also uses the same access mode ('READ WRITE' or 'READ ONLY') 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 suppresses 'CHAIN' or 'RELEASE' completion, which can be useful if the 'completion_type' system variable is set to cause chaining or release completion by default.

Beginning a transaction causes any pending transaction to be committed. See *note implicit-commit::, for more information.

Beginning a transaction also causes table locks acquired with note 'LOCK TABLES': lock-tables. to be released, as though you had executed note 'UNLOCK TABLES': lock-tables. Beginning a transaction does not release a global read lock acquired with 'FLUSH TABLES WITH READ LOCK'.

For best results, transactions should be performed using only tables managed by a single transaction-safe storage engine. Otherwise, the following problems can occur:

Each transaction is stored in the binary log in one chunk, upon note 'COMMIT': commit. Transactions that are rolled back are not logged. (Exception: Modifications to nontransactional tables cannot be rolled back. If a transaction that is rolled back includes modifications to nontransactional tables, the entire transaction is logged with a note 'ROLLBACK': commit. statement at the end to ensure that modifications to the nontransactional tables are replicated.) See *note binary-log::.

You can change the isolation level or access mode for transactions with the note 'SET TRANSACTION': set-transaction. statement. See note set-transaction::.

Rolling back can be a slow operation that may occur implicitly without the user having explicitly asked for it (for example, when an error occurs). Because of this, note 'SHOW PROCESSLIST': show-processlist. displays 'Rolling back' in the 'State' column for the session, not only for explicit rollbacks performed with the note 'ROLLBACK': commit. statement but also for implicit rollbacks.

Note:

In MySQL 5.7, 'BEGIN', 'COMMIT', and 'ROLLBACK' are not affected by '--replicate-do-db' or '--replicate-ignore-db' rules.

When 'InnoDB' performs a complete rollback of a transaction, all locks set by the transaction are released. If a single SQL statement within a transaction rolls back as a result of an error, such as a duplicate key error, locks set by the statement are preserved while the transaction remains active. This happens because 'InnoDB' stores row locks in a format such that it cannot know afterward which lock was set by which statement.

If a note 'SELECT': select. statement within a transaction calls a stored function, and a statement within the stored function fails, that statement rolls back. If note 'ROLLBACK': commit. is executed for the transaction subsequently, the entire transaction rolls back.

 File: manual.info.tmp, Node: cannot-roll-back, Next: implicit-commit, Prev: commit, Up: sql-transactional-statements

13.3.2 Statements That Cannot Be Rolled Back

Some statements cannot be rolled back. In general, these include data definition language (DDL) statements, such as those that create or drop databases, those that create, drop, or alter tables or stored routines.

You should design your transactions not to include such statements. If you issue a statement early in a transaction that cannot be rolled back, and then another statement later fails, the full effect of the transaction cannot be rolled back in such cases by issuing a *note 'ROLLBACK': commit. statement.

 File: manual.info.tmp, Node: implicit-commit, Next: savepoint, Prev: cannot-roll-back, Up: sql-transactional-statements

13.3.3 Statements That Cause an Implicit Commit

The statements listed in this section (and any synonyms for them) implicitly end any transaction active in the current session, as if you had done a *note 'COMMIT': commit. before executing the statement.

Most of these statements also cause an implicit commit after executing. The intent is to handle each such statement in its own special transaction because it cannot be rolled back anyway. Transaction-control and locking statements are exceptions: If an implicit commit occurs before execution, another does not occur after.

 File: manual.info.tmp, Node: savepoint, Next: lock-tables, Prev: implicit-commit, Up: sql-transactional-statements

13.3.4 SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT Statements

 SAVEPOINT IDENTIFIER
 ROLLBACK [WORK] TO [SAVEPOINT] IDENTIFIER
 RELEASE SAVEPOINT IDENTIFIER

'InnoDB' supports the SQL statements note 'SAVEPOINT': savepoint, note 'ROLLBACK TO SAVEPOINT': savepoint, note 'RELEASE SAVEPOINT': savepoint. and the optional 'WORK' keyword for note 'ROLLBACK': commit.

The *note 'SAVEPOINT': savepoint. statement sets a named transaction savepoint with a name of IDENTIFIER. If the current transaction has a savepoint with the same name, the old savepoint is deleted and a new one is set.

The *note 'ROLLBACK TO SAVEPOINT': savepoint. statement rolls back a transaction to the named savepoint without terminating the transaction. Modifications that the current transaction made to rows after the savepoint was set are undone in the rollback, but 'InnoDB' does not release the row locks that were stored in memory after the savepoint. (For a new inserted row, the lock information is carried by the transaction ID stored in the row; the lock is not separately stored in memory. In this case, the row lock is released in the undo.) Savepoints that were set at a later time than the named savepoint are deleted.

If the *note 'ROLLBACK TO SAVEPOINT': savepoint. statement returns the following error, it means that no savepoint with the specified name exists:

 ERROR 1305 (42000): SAVEPOINT IDENTIFIER does not exist

The *note 'RELEASE SAVEPOINT': savepoint. statement removes the named savepoint from the set of savepoints of the current transaction. No commit or rollback occurs. It is an error if the savepoint does not exist.

All savepoints of the current transaction are deleted if you execute a note 'COMMIT': commit, or a note 'ROLLBACK': commit. that does not name a savepoint.

A new savepoint level is created when a stored function is invoked or a trigger is activated. The savepoints on previous levels become unavailable and thus do not conflict with savepoints on the new level. When the function or trigger terminates, any savepoints it created are released and the previous savepoint level is restored.

 File: manual.info.tmp, Node: lock-tables, Next: set-transaction, Prev: savepoint, Up: sql-transactional-statements

13.3.5 LOCK TABLES and UNLOCK TABLES Statements

 LOCK {TABLE | TABLES}
     TBL_NAME [[AS] ALIAS] LOCK_TYPE
     [, TBL_NAME [[AS] ALIAS] LOCK_TYPE] ...

 LOCK_TYPE: {
     READ [LOCAL]
   | [LOW_PRIORITY] WRITE
 }

 UNLOCK {TABLE | TABLES}

MySQL 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. This is explained in more detail in *note lock-tables-restrictions::.

*note 'LOCK TABLES': lock-tables. explicitly acquires table locks for the current client session. Table locks can be acquired for base tables or views. You must have the 'LOCK TABLES' privilege, and the 'SELECT' privilege for each object to be locked.

For view locking, note 'LOCK TABLES': lock-tables. adds all base tables used in the view to the set of tables to be locked and locks them automatically. As of MySQL 5.7.32, note 'LOCK TABLES': lock-tables. checks that the view definer has the proper privileges on the tables underlying the view.

If you lock a table explicitly with note 'LOCK TABLES': lock-tables, any tables used in triggers are also locked implicitly, as described in note lock-tables-and-triggers::.

note 'UNLOCK TABLES': lock-tables. explicitly releases any table locks held by the current session. note 'LOCK TABLES': lock-tables. implicitly releases any table locks held by the current session before acquiring new locks.

Another use for note 'UNLOCK TABLES': lock-tables. is to release the global read lock acquired with the 'FLUSH TABLES WITH READ LOCK' statement, which enables you to lock all tables in all databases. See note flush::. (This is a very convenient way to get backups if you have a file system such as Veritas that can take snapshots in time.)

'LOCK TABLE' is a synonym for 'LOCK TABLES'; 'UNLOCK TABLE' is a synonym for 'UNLOCK TABLES'.

A table lock protects only against inappropriate reads or writes by other sessions. A session holding a 'WRITE' lock can perform table-level operations such as note 'DROP TABLE': drop-table. or note 'TRUNCATE TABLE': truncate-table. For sessions holding a 'READ' lock, note 'DROP TABLE': drop-table. and note 'TRUNCATE TABLE': truncate-table. operations are not permitted.

The following discussion applies only to non-'TEMPORARY' tables. *note 'LOCK TABLES': lock-tables. is permitted (but ignored) for a 'TEMPORARY' table. The table can be accessed freely by the session within which it was created, regardless of what other locking may be in effect. No lock is necessary because no other session can see the table.

Table Lock Acquisition

To acquire table locks within the current session, use the note 'LOCK TABLES': lock-tables. statement, which acquires metadata locks (see note metadata-locking::).

The following lock types are available:

'READ [LOCAL]' lock:

'[LOW_PRIORITY] WRITE' lock:

'WRITE' locks normally have higher priority than 'READ' locks to ensure that updates are processed as soon as possible. This means that if one session obtains a 'READ' lock and then another session requests a 'WRITE' lock, subsequent 'READ' lock requests wait until the session that requested the 'WRITE' lock has obtained the lock and released it. (An exception to this policy can occur for small values of the 'max_write_lock_count' system variable; see *note metadata-locking::.)

If the *note 'LOCK TABLES': lock-tables. statement must wait due to locks held by other sessions on any of the tables, it blocks until all locks can be acquired.

A session that requires locks must acquire all the locks that it needs in a single note 'LOCK TABLES': lock-tables. statement. While the locks thus obtained are held, the session can access only the locked tables. For example, in the following sequence of statements, an error occurs for the attempt to access 't2' because it was not locked in the note 'LOCK TABLES': lock-tables. statement:

 mysql> LOCK TABLES t1 READ;
 mysql> SELECT COUNT(*) FROM t1;
 +----------+
 | COUNT(*) |
 +----------+
 |        3 |
 +----------+
 mysql> SELECT COUNT(*) FROM t2;
 ERROR 1100 (HY000): Table 't2' was not locked with LOCK TABLES

Tables in the 'INFORMATION_SCHEMA' database are an exception. They can be accessed without being locked explicitly even while a session holds table locks obtained with *note 'LOCK TABLES': lock-tables.

You cannot refer to a locked table multiple times in a single query using the same name. Use aliases instead, and obtain a separate lock for the table and each alias:

 mysql> LOCK TABLE t WRITE, t AS t1 READ;
 mysql> INSERT INTO t SELECT * FROM t;
 ERROR 1100: Table 't' was not locked with LOCK TABLES
 mysql> INSERT INTO t SELECT * FROM t AS t1;

The error occurs for the first note 'INSERT': insert. because there are two references to the same name for a locked table. The second note 'INSERT': insert. succeeds because the references to the table use different names.

If your statements refer to a table by means of an alias, you must lock the table using that same alias. It does not work to lock the table without specifying the alias:

 mysql> LOCK TABLE t READ;
 mysql> SELECT * FROM t AS myalias;
 ERROR 1100: Table 'myalias' was not locked with LOCK TABLES

Conversely, if you lock a table using an alias, you must refer to it in your statements using that alias:

 mysql> LOCK TABLE t AS myalias READ;
 mysql> SELECT * FROM t;
 ERROR 1100: Table 't' was not locked with LOCK TABLES
 mysql> SELECT * FROM t AS myalias;

Note:

'LOCK TABLES' or 'UNLOCK TABLES', when applied to a partitioned table, always locks or unlocks the entire table; these statements do not support partition lock pruning. See *note partitioning-limitations-locking::.

Table Lock Release

When the table locks held by a session are released, they are all released at the same time. A session can release its locks explicitly, or locks may be released implicitly under certain conditions.

If the connection for a client session terminates, whether normally or abnormally, the server implicitly releases all table locks held by the session (transactional and nontransactional). If the client reconnects, the locks are longer in effect. In addition, if the client had an active transaction, the server rolls back the transaction upon disconnect, and if reconnect occurs, the new session begins with autocommit enabled. For this reason, clients may wish to disable auto-reconnect. With auto-reconnect in effect, the client is not notified if reconnect occurs but any table locks or current transactions are lost. With auto-reconnect disabled, if the connection drops, an error occurs for the next statement issued. The client can detect the error and take appropriate action such as reacquiring the locks or redoing the transaction. See Automatic Reconnection Control (https://dev.mysql.com/doc/c-api/5.7/en/c-api-auto-reconnect.html).

Note:

If you use note 'ALTER TABLE': alter-table. on a locked table, it may become unlocked. For example, if you attempt a second note 'ALTER TABLE': alter-table. operation, the result may be an error 'Table 'TBL_NAME' was not locked with LOCK TABLES'. To handle this, lock the table again prior to the second alteration. See also *note alter-table-problems::.

Interaction of Table Locking and Transactions

note 'LOCK TABLES': lock-tables. and note 'UNLOCK TABLES': lock-tables. interact with the use of transactions as follows:

LOCK TABLES and Triggers

If you lock a table explicitly with *note 'LOCK TABLES': lock-tables, any tables used in triggers are also locked implicitly:

Suppose that you lock two tables, 't1' and 't2', using this statement:

 LOCK TABLES t1 WRITE, t2 READ;

If 't1' or 't2' have any triggers, tables used within the triggers are also locked. Suppose that 't1' has a trigger defined like this:

 CREATE TRIGGER t1_a_ins AFTER INSERT ON t1 FOR EACH ROW
 BEGIN
   UPDATE t4 SET count = count+1
       WHERE id = NEW.id AND EXISTS (SELECT a FROM t3);
   INSERT INTO t2 VALUES(1, 2);
 END;

The result of the *note 'LOCK TABLES': lock-tables. statement is that 't1' and 't2' are locked because they appear in the statement, and 't3' and 't4' are locked because they are used within the trigger:

Table-Locking Restrictions and Conditions

You can safely use note 'KILL': kill. to terminate a session that is waiting for a table lock. See note kill::.

note 'LOCK TABLES': lock-tables. and note 'UNLOCK TABLES': lock-tables. cannot be used within stored programs.

Tables in the 'performance_schema' database cannot be locked with *note 'LOCK TABLES': lock-tables, except the 'setup_XXX' tables.

The scope of a lock generated by 'LOCK TABLES' is a single MySQL server. It is not compatible with NDB Cluster, which has no way of enforcing an SQL-level lock across multiple instances of note 'mysqld': mysqld. You can enforce locking in an API application instead. See note mysql-cluster-limitations-multiple-nodes::, for more information.

The following statements are prohibited while a note 'LOCK TABLES': lock-tables. statement is in effect: note 'CREATE TABLE': create-table, note 'CREATE TABLE ... LIKE': create-table, note 'CREATE VIEW': create-view, *note 'DROP VIEW': drop-view, and DDL statements on stored functions and procedures and events.

For some operations, system tables in the 'mysql' database must be accessed. For example, the *note 'HELP': help. statement requires the contents of the server-side help tables, and 'CONVERT_TZ()' might need to read the time zone tables. The server implicitly locks the system tables for reading as necessary so that you need not lock them explicitly. These tables are treated as just described:

 mysql.help_category
 mysql.help_keyword
 mysql.help_relation
 mysql.help_topic
 mysql.proc
 mysql.time_zone
 mysql.time_zone_leap_second
 mysql.time_zone_name
 mysql.time_zone_transition
 mysql.time_zone_transition_type

If you want to explicitly place a 'WRITE' lock on any of those tables with a *note 'LOCK TABLES': lock-tables. statement, the table must be the only one locked; no other table can be locked with the same statement.

Normally, you do not need to lock tables, because all single *note 'UPDATE': update. statements are atomic; no other session can interfere with any other currently executing SQL statement. However, there are a few cases when locking tables may provide an advantage:

You can avoid using *note 'LOCK TABLES': lock-tables. in many cases by using relative updates ('UPDATE customer SET VALUE=VALUE+NEW_VALUE') or the 'LAST_INSERT_ID()' function.

You can also avoid locking tables in some cases by using the user-level advisory lock functions 'GET_LOCK()' and 'RELEASE_LOCK()'. These locks are saved in a hash table in the server and implemented with 'pthread_mutex_lock()' and 'pthread_mutex_unlock()' for high speed. See *note locking-functions::.

See *note internal-locking::, for more information on locking policy.

 File: manual.info.tmp, Node: set-transaction, Next: xa, Prev: lock-tables, Up: sql-transactional-statements

13.3.6 SET TRANSACTION Statement

 SET [GLOBAL | SESSION] TRANSACTION
     TRANSACTION_CHARACTERISTIC [, TRANSACTION_CHARACTERISTIC] ...

 TRANSACTION_CHARACTERISTIC: {
     ISOLATION LEVEL LEVEL
   | ACCESS_MODE
 }

 LEVEL: {
      REPEATABLE READ
    | READ COMMITTED
    | READ UNCOMMITTED
    | SERIALIZABLE
 }

 ACCESS_MODE: {
      READ WRITE
    | READ ONLY
 }

This statement specifies transaction characteristics. It takes a list of one or more characteristic values separated by commas. Each characteristic value sets the transaction isolation level or access mode. The isolation level is used for operations on *note 'InnoDB': innodb-storage-engine. tables. The access mode specifies whether transactions operate in read/write or read-only mode.

In addition, *note 'SET TRANSACTION': set-transaction. can include an optional 'GLOBAL' or 'SESSION' keyword to indicate the scope of the statement.

Transaction Isolation Levels

To set the transaction isolation level, use an 'ISOLATION LEVEL LEVEL' clause. It is not permitted to specify multiple 'ISOLATION LEVEL' clauses in the same *note 'SET TRANSACTION': set-transaction. statement.

The default isolation level is 'REPEATABLE READ'. Other permitted values are 'READ COMMITTED', 'READ UNCOMMITTED', and 'SERIALIZABLE'. For information about these isolation levels, see *note innodb-transaction-isolation-levels::.

Transaction Access Mode

To set the transaction access mode, use a 'READ WRITE' or 'READ ONLY' clause. It is not permitted to specify multiple access-mode clauses in the same *note 'SET TRANSACTION': set-transaction. statement.

By default, a transaction takes place in read/write mode, with both reads and writes permitted to tables used in the transaction. This mode may be specified explicitly using *note 'SET TRANSACTION': set-transaction. with an access mode of 'READ WRITE'.

If the transaction access mode is set to 'READ ONLY', changes to tables are prohibited. This may enable storage engines to make performance improvements that are possible when writes are not permitted.

In read-only mode, it remains possible to change tables created with the 'TEMPORARY' keyword using DML statements. Changes made with DDL statements are not permitted, just as with permanent tables.

The 'READ WRITE' and 'READ ONLY' access modes also may be specified for an individual transaction using the *note 'START TRANSACTION': commit. statement.

Transaction Characteristic Scope

You can set transaction characteristics globally, for the current session, or for the next transaction only:

A change to global transaction characteristics requires the 'SUPER' privilege. Any session is free to change its session characteristics (even in the middle of a transaction), or the characteristics for its next transaction (prior to the start of that transaction).

To set the global isolation level at server startup, use the '--transaction-isolation=LEVEL' option on the command line or in an option file. Values of LEVEL for this option use dashes rather than spaces, so the permissible values are 'READ-UNCOMMITTED', 'READ-COMMITTED', 'REPEATABLE-READ', or 'SERIALIZABLE'.

Similarly, to set the global transaction access mode at server startup, use the '--transaction-read-only' option. The default is 'OFF' (read/write mode) but the value can be set to 'ON' for a mode of read only.

For example, to set the isolation level to 'REPEATABLE READ' and the access mode to 'READ WRITE', use these lines in the '[mysqld]' section of an option file:

 [mysqld]
 transaction-isolation = REPEATABLE-READ
 transaction-read-only = OFF

At runtime, characteristics at the global, session, and next-transaction scope levels can be set indirectly using the note 'SET TRANSACTION': set-transaction. statement, as described previously. They can also be set directly using the note 'SET': set-variable. statement to assign values to the 'transaction_isolation' and 'transaction_read_only' system variables:

The following tables show the characteristic scope level set by each *note 'SET TRANSACTION': set-transaction. and variable-assignment syntax.

SET TRANSACTION Syntax for Transaction Characteristics

Syntax Affected Characteristic Scope

'SET GLOBAL TRANSACTION Global TRANSACTION_CHARACTERISTIC'

'SET SESSION TRANSACTION Session TRANSACTION_CHARACTERISTIC'

'SET TRANSACTION Next transaction only TRANSACTION_CHARACTERISTIC'

SET Syntax for Transaction Characteristics

Syntax Affected Characteristic Scope

'SET GLOBAL VAR_NAME = VALUE' Global

'SET @@GLOBAL.VAR_NAME = VALUE' Global

'SET SESSION VAR_NAME = VALUE' Session

'SET @@SESSION.VAR_NAME = VALUE' Session

'SET VAR_NAME = VALUE' Session

'SET @@VAR_NAME = VALUE' Next transaction only

It is possible to check the global and session values of transaction characteristics at runtime:

 SELECT @@GLOBAL.transaction_isolation, @@GLOBAL.transaction_read_only;
 SELECT @@SESSION.transaction_isolation, @@SESSION.transaction_read_only;

Prior to MySQL 5.7.20, use 'tx_isolation' and 'tx_read_only' rather than 'transaction_isolation' and 'transaction_read_only'.

 File: manual.info.tmp, Node: xa, Prev: set-transaction, Up: sql-transactional-statements

13.3.7 XA Transactions

Support for XA transactions is available for the note 'InnoDB': innodb-storage-engine. storage engine. The MySQL 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 http://www.opengroup.org/public/pubs/catalog/c193.htm. Limitations of the current XA implementation are described in note xa-restrictions::.

On the client side, there are no special requirements. The XA interface to a MySQL server consists of SQL statements that begin with the 'XA' keyword. MySQL client programs must be able to send SQL statements and to understand the semantics of the XA statement interface. They do not need be linked against a recent client library. Older client libraries also work.

Among the MySQL Connectors, MySQL Connector/J 5.0.0 and higher supports XA directly, by means of a class interface that handles the XA SQL statement interface for you.

XA supports distributed transactions, that is, the ability to permit multiple separate transactional resources to participate in a global transaction. Transactional resources often are RDBMSs but may be other kinds of resources.

A global transaction involves several actions that are transactional in themselves, but that all must either complete successfully as a group, or all be rolled back as a group. In essence, this extends ACID properties 'up a level' so that multiple ACID transactions can be executed in concert as components of a global operation that also has ACID properties. (As with nondistributed transactions, 'SERIALIZABLE' may be preferred if your applications are sensitive to read phenomena. 'REPEATABLE READ' may not be sufficient for distributed transactions.)

Some examples of distributed transactions:

Applications that use global transactions involve one or more Resource Managers and a Transaction Manager:

The MySQL implementation of XA enables a MySQL server to act as a Resource Manager that handles XA transactions within a global transaction. A client program that connects to the MySQL server acts as the Transaction Manager.

To carry out a global transaction, it is necessary to know which components are involved, and bring each component to a point when it can be committed or rolled back. Depending on what each component reports about its ability to succeed, they must all commit or roll back as an atomic group. That is, either all components must commit, or all components must roll back. To manage a global transaction, it is necessary to take into account that any component or the connecting network might fail.

The process for executing a global transaction uses two-phase commit (2PC). This takes place after the actions performed by the branches of the global transaction have been executed.

  1. In the first phase, all branches are prepared. That is, they are told by the TM to get ready to commit. Typically, this means each RM that manages a branch records the actions for the branch in stable storage. The branches indicate whether they are able to do this, and these results are used for the second phase.

  2. In the second phase, the TM tells the RMs whether to commit or roll back. If all branches indicated when they were prepared that they can commit, all branches are told to commit. If any branch indicated when it was prepared that it could not commit, all branches are told to roll back.

In some cases, a global transaction might use one-phase commit (1PC). For example, when a Transaction Manager finds that a global transaction consists of only one transactional resource (that is, a single branch), that resource can be told to prepare and commit at the same time.

 File: manual.info.tmp, Node: xa-statements, Next: xa-states, Prev: xa, Up: xa

13.3.7.1 XA Transaction SQL Statements ......................................

To perform XA transactions in MySQL, use the following statements:

 XA {START|BEGIN} XID [JOIN|RESUME]

 XA END XID [SUSPEND [FOR MIGRATE]]

 XA PREPARE XID

 XA COMMIT XID [ONE PHASE]

 XA ROLLBACK XID

 XA RECOVER [CONVERT XID]

For *note 'XA START': xa-statements, the 'JOIN' and 'RESUME' clauses are recognized but have no effect.

For *note 'XA END': xa-statements. the 'SUSPEND [FOR MIGRATE]' clause is recognized but has no effect.

Each XA statement begins with the 'XA' keyword, and most of them require an XID value. An XID is an XA transaction identifier. It indicates which transaction the statement applies to. XID values are supplied by the client, or generated by the MySQL server. An XID value has from one to three parts:

 XID: GTRID [, BQUAL [, FORMATID ]]

GTRID is a global transaction identifier, BQUAL is a branch qualifier, and FORMATID is a number that identifies the format used by the GTRID and BQUAL values. As indicated by the syntax, BQUAL and FORMATID are optional. The default BQUAL value is '''' if not given. The default FORMATID value is 1 if not given.

GTRID and BQUAL must be string literals, each up to 64 bytes (not characters) long. GTRID and BQUAL can be specified in several ways. You can use a quoted string (''ab''), hex string ('X'6162'', '0x6162'), or bit value ('b'NNNN'').

FORMATID is an unsigned integer.

The GTRID and BQUAL values are interpreted in bytes by the MySQL server's underlying XA support routines. However, while an SQL statement containing an XA statement is being parsed, the server works with some specific character set. To be safe, write GTRID and BQUAL as hex strings.

XID values typically are generated by the Transaction Manager. Values generated by one TM must be different from values generated by other TMs. A given TM must be able to recognize its own XID values in a list of values returned by the *note 'XA RECOVER': xa-statements. statement.

note 'XA START XID': xa-statements. starts an XA transaction with the given XID value. Each XA transaction must have a unique XID value, so the value must not currently be used by another XA transaction. Uniqueness is assessed using the GTRID and BQUAL values. All following XA statements for the XA transaction must be specified using the same XID value as that given in the note 'XA START': xa-statements. statement. If you use any of those statements but specify an XID value that does not correspond to some existing XA transaction, an error occurs.

One or more XA transactions can be part of the same global transaction. All XA transactions within a given global transaction must use the same GTRID value in the XID value. For this reason, GTRID values must be globally unique so that there is no ambiguity about which global transaction a given XA transaction is part of. The BQUAL part of the XID value must be different for each XA transaction within a global transaction. (The requirement that BQUAL values be different is a limitation of the current MySQL XA implementation. It is not part of the XA specification.)

The note 'XA RECOVER': xa-statements. statement returns information for those XA transactions on the MySQL server that are in the 'PREPARED' state. (See note xa-states::.) The output includes a row for each such XA transaction on the server, regardless of which client started it.

*note 'XA RECOVER': xa-statements. output rows look like this (for an example XID value consisting of the parts ''abc'', ''def'', and '7'):

 mysql> XA RECOVER;
 +----------+--------------+--------------+--------+
 | formatID | gtrid_length | bqual_length | data   |
 +----------+--------------+--------------+--------+
 |        7 |            3 |            3 | abcdef |
 +----------+--------------+--------------+--------+

The output columns have the following meanings:

XID values may contain nonprintable characters. As of MySQL 5.7.5, *note 'XA RECOVER': xa-statements. permits an optional 'CONVERT XID' clause so that clients can request XID values in hexadecimal.

 File: manual.info.tmp, Node: xa-states, Next: xa-restrictions, Prev: xa-statements, Up: xa

13.3.7.2 XA Transaction States ..............................

An XA transaction progresses through the following states:

  1. Use *note 'XA START': xa-statements. to start an XA transaction and put it in the 'ACTIVE' state.

  2. For an 'ACTIVE' XA transaction, issue the SQL statements that make up the transaction, and then issue an note 'XA END': xa-statements. statement. note 'XA END': xa-statements. puts the transaction in the 'IDLE' state.

  3. For an 'IDLE' XA transaction, you can issue either an *note 'XA PREPARE': xa-statements. statement or an 'XA COMMIT ... ONE PHASE' statement:

    * *note 'XA PREPARE': xa-statements. puts the transaction in the
  'PREPARED' state.  An *note 'XA RECOVER': xa-statements.
  statement at this point includes the transaction's XID value
  in its output, because *note 'XA RECOVER': xa-statements.
  lists all XA transactions that are in the 'PREPARED' state.

* 'XA COMMIT ... ONE PHASE' prepares and commits the
  transaction.  The XID value is not listed by *note 'XA
  RECOVER': xa-statements. because the transaction terminates.

  4. For a 'PREPARED' XA transaction, you can issue an note 'XA COMMIT': xa-statements. statement to commit and terminate the transaction, or note 'XA ROLLBACK': xa-statements. to roll back and terminate the transaction.

Here is a simple XA transaction that inserts a row into a table as part of a global transaction:

 mysql> XA START 'xatest';
 Query OK, 0 rows affected (0.00 sec)

 mysql> INSERT INTO mytable (i) VALUES(10);
 Query OK, 1 row affected (0.04 sec)

 mysql> XA END 'xatest';
 Query OK, 0 rows affected (0.00 sec)

 mysql> XA PREPARE 'xatest';
 Query OK, 0 rows affected (0.00 sec)

 mysql> XA COMMIT 'xatest';
 Query OK, 0 rows affected (0.00 sec)

Within the context of a given client connection, XA transactions and local (non-XA) transactions are mutually exclusive. For example, if note 'XA START': xa-statements. has been issued to begin an XA transaction, a local transaction cannot be started until the XA transaction has been committed or rolled back. Conversely, if a local transaction has been started with note 'START TRANSACTION': commit, no XA statements can be used until the transaction has been committed or rolled back.

If an XA transaction is in the 'ACTIVE' state, you cannot issue any statements that cause an implicit commit. That would violate the XA contract because you could not roll back the XA transaction. The following error is raised if you try to execute such a statement:

 ERROR 1399 (XAE07): XAER_RMFAIL: The command cannot be executed
 when global transaction is in the ACTIVE state

Statements to which the preceding remark applies are listed at *note implicit-commit::.

 File: manual.info.tmp, Node: xa-restrictions, Prev: xa-states, Up: xa

13.3.7.3 Restrictions on XA Transactions ........................................

XA transaction support is limited to the 'InnoDB' storage engine.

For 'external XA,' a MySQL server acts as a Resource Manager and client programs act as Transaction Managers. For 'Internal XA', storage engines within a MySQL server act as RMs, and the server itself acts as a TM. Internal XA support is limited by the capabilities of individual storage engines. Internal XA is required for handling XA transactions that involve more than one storage engine. The implementation of internal XA requires that a storage engine support two-phase commit at the table handler level, and currently this is true only for 'InnoDB'.

For *note 'XA START': xa-statements, the 'JOIN' and 'RESUME' clauses are recognized but have no effect.

For *note 'XA END': xa-statements. the 'SUSPEND [FOR MIGRATE]' clause is recognized but has no effect.

The requirement that the BQUAL part of the XID value be different for each XA transaction within a global transaction is a limitation of the current MySQL XA implementation. It is not part of the XA specification.

Prior to MySQL 5.7.7, XA transactions were not compatible with replication. This was because an XA transaction that was in 'PREPARED' state would be rolled back on clean server shutdown or client disconnect. Similarly, an XA transaction that was in 'PREPARED' state would still exist in 'PREPARED' state in case the server was shutdown abnormally and then started again, but the contents of the transaction could not be written to the binary log. In both of these situations the XA transaction could not be replicated correctly.

In MySQL 5.7.7 and later, there is a change in behavior and an XA transaction is written to the binary log in two parts. When 'XA PREPARE ' is issued, the first part of the transaction up to 'XA PREPARE' is written using an initial GTID. A 'XA_prepare_log_event' is used to identify such transactions in the binary log. When 'XA COMMIT' or 'XA ROLLBACK' is issued, a second part of the transaction containing only the 'XA COMMIT' or 'XA ROLLBACK' statement is written using a second GTID. Note that the initial part of the transaction, identified by 'XA_prepare_log_event', is not necessarily followed by its 'XA COMMIT' or 'XA ROLLBACK', which can cause interleaved binary logging of any two XA transactions. The two parts of the XA transaction can even appear in different binary log files. This means that an XA transaction in 'PREPARED' state is now persistent until an explicit 'XA COMMIT' or 'XA ROLLBACK' statement is issued, ensuring that XA transactions are compatible with replication.

On a replica, immediately after the XA transaction is prepared, it is detached from the replica applier thread, and can be committed or rolled back by any thread on the replica. This means that the same XA transaction can appear in the note 'events_transactions_current': performance-schema-events-transactions-current-table. table with different states on different threads. The note 'events_transactions_current': performance-schema-events-transactions-current-table. table displays the current status of the most recent monitored transaction event on the thread, and does not update this status when the thread is idle. So the XA transaction can still be displayed in the 'PREPARED' state for the original applier thread, after it has been processed by another thread. To positively identify XA transactions that are still in the 'PREPARED' state and need to be recovered, use the *note 'XA RECOVER': xa-statements. statement rather than the Performance Schema transaction tables.

The following restrictions exist for using XA transactions in MySQL 5.7.7 and later:

 File: manual.info.tmp, Node: sql-replication-statements, Next: sql-prepared-statements, Prev: sql-transactional-statements, Up: sql-statements