13.4. MySQL Transactional and Locking Statements

MySQL 5.0

13.4. MySQL Transactional and Locking Statements

MySQL supports local transactions (within a given client connection) through statements such as , , , and . See Section 13.4.1, “, , and Syntax”. Beginning with MySQL 5.0, XA transaction support is available, which enables MySQL to participate in distributed transactions as well. See Section 13.4.7, “XA Transactions”.

13.4.1. START TRANSACTION, COMMIT, and ROLLBACK Syntax

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

The and statement begin 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 connection.

Beginning with MySQL 5.0.3, the optional keyword is supported for and , as are the and clauses. and can be used for additional control over transaction completion. The value of the system variable determines the default completion behavior. See Section 5.2.2, “Server System Variables”.

The 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 clause causes the server to disconnect the current client connection after terminating the current transaction. Including the keyword suppresses or completion, which can be useful if the system variable is set to cause chaining or release completion by default.

By default, MySQL runs with autocommit mode enabled. This means that as soon as you execute a statement that updates (modifies) a table, MySQL stores the update on disk.

If you are using a transaction-safe storage engine (such as , , or ), you can disable autocommit mode with the following statement:

SET AUTOCOMMIT=0;

After disabling autocommit mode by setting the variable to zero, you must use to store your changes to disk or if you want to ignore the changes you have made since the beginning of your transaction.

To disable autocommit mode for a single series of statements, use the statement:

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

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

and are supported as aliases of for initiating a transaction. is standard SQL syntax and is the recommended way to start an ad-hoc transaction.

The statement differs from the use of the keyword that starts a compound statement. The latter does not begin a transaction. See Section 17.2.5, “ Compound Statement Syntax”.

You can also begin a transaction like this:

START TRANSACTION WITH CONSISTENT SNAPSHOT;

The clause starts a consistent read for storage engines that are capable of it. Currently, this applies only to . The effect is the same as issuing a followed by a from any table. See Section 14.2.10.4, “Consistent Non-Locking Read”.

The clause does not change the current transaction isolation level, so it provides a consistent snapshot only if the current isolation level is one that allows consistent read ( or ).

Beginning a transaction causes an implicit to be performed.

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

  • If you use tables from more than one transaction-safe storage engine (such as and ), and the transaction isolation level is not , it is possible that when one transaction commits, another ongoing transaction that uses the same tables will see only some of the changes made by the first transaction. That is, the atomicity of transactions is not guaranteed with mixed engines and inconsistencies can result. (If mixed-engine transactions are infrequent, you can use to set the isolation level to on a per-transaction basis as necessary.)

  • If you use non-transaction-safe tables within a transaction, any changes to those tables are stored at once, regardless of the status of autocommit mode.

    If you issue a statement after updating a non-transactional table within a transaction, an warning occurs. Changes to transaction-safe tables are rolled back, but not changes to non-transaction-safe tables.

Each transaction is stored in the binary log in one chunk, upon . Transactions that are rolled back are not logged. (Exception: Modifications to non-transactional tables cannot be rolled back. If a transaction that is rolled back includes modifications to non-transactional tables, the entire transaction is logged with a statement at the end to ensure that the modifications to those tables are replicated.) See Section 5.12.3, “The Binary Log”.

You can change the isolation level for transactions with . See Section 13.4.6, “ Syntax”.

Rolling back can be a slow operation that may occur without the user having explicitly asked for it (for example, when an error occurs). Because of this, displays in the column for the connection during implicit and explicit ( SQL statement) rollbacks.

13.4.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 statement.

13.4.3. Statements That Cause an Implicit Commit

Each of the following statements (and any synonyms for them) implicitly end a transaction, as if you had done a before executing the statement:

  • , , , , , , , , , , , , , , , , , , , , , .

  • commits a transaction only if any tables currently are locked.

  • The , , and statements cause an implicit commit beginning with MySQL 5.0.8. The , , , , , and statements cause an implicit commit beginning with MySQL 5.0.13.

  • The statement in is processed as a single transaction. This means that a from the user does not undo statements the user made during that transaction.

Transactions cannot be nested. This is a consequence of the implicit performed for any current transaction when you issue a statement or one of its synonyms.

Statements that cause implicit cannot be used in an XA transaction while the transaction is in an state.

13.4.4. SAVEPOINT and ROLLBACK TO SAVEPOINT Syntax

SAVEPOINT 
ROLLBACK [WORK] TO SAVEPOINT 
RELEASE SAVEPOINT 

supports the SQL statements and . Starting from MySQL 5.0.3, and the optional keyword for are supported as well.

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

The statement rolls back a transaction to the named savepoint. Modifications that the current transaction made to rows after the savepoint was set are undone in the rollback, but does not release the row locks that were stored in memory after the savepoint. (Note that 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 statement returns the following error, it means that no savepoint with the specified name exists:

ERROR 1181: Got error 153 during ROLLBACK

The 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 , or a that does not name a savepoint.

Beginning with MySQL 5.0.17, 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.

13.4.5. LOCK TABLES and UNLOCK TABLES Syntax

LOCK TABLES
     [AS ] {READ [LOCAL] | [LOW_PRIORITY] WRITE}
    [,  [AS ] {READ [LOCAL] | [LOW_PRIORITY] WRITE}] ...
UNLOCK TABLES

locks base tables (but not views) for the current thread. If any of the tables are locked by other threads, it blocks until all locks can be acquired. releases any locks held by the current thread. All tables that are locked by the current thread are implicitly unlocked when the thread issues another , or when the connection to the server is closed.

A table lock protects only against inappropriate reads or writes by other clients. The client holding the lock, even a read lock, can perform table-level operations such as .

Note the following regarding the use of with transactional tables:

  • is not transaction-safe and implicitly commits any active transactions before attempting to lock the tables. Also, beginning a transaction (for example, with ) implicitly performs an . (See Section 13.4.3, “Statements That Cause an Implicit Commit”.)

  • The correct way to use with transactional tables, such as tables, is to set and not to call until you commit the transaction explicitly. When you call , internally takes its own table lock, and MySQL takes its own table lock. releases its table lock at the next commit, but for MySQL to release its table lock, you have to call . You should not have , because then releases its table lock immediately after the call of , and deadlocks can very easily happen. Note that we do not acquire the table lock at all if , to help old applications avoid unnecessary deadlocks.

  • does not release MySQL's non-transactional table locks.

To use , you must have the privilege and the privilege for the involved tables.

The main reasons to use are to emulate transactions or to get more speed when updating tables. This is explained in more detail later.

If a thread obtains a lock on a table, that thread (and all other threads) can only read from the table. If a thread obtains a lock on a table, only the thread holding the lock can write to the table. Other threads are blocked from reading or writing the table until the lock has been released.

The difference between and is that allows non-conflicting statements (concurrent inserts) to execute while the lock is held. However, this cannot be used if you are going to manipulate the database files outside MySQL while you hold the lock. For tables, is the same as as of MySQL 5.0.13. (Before that, essentially does nothing: It does not lock the table at all, so for tables, the use of is deprecated because a plain consistent-read does the same thing, and no locks are needed.)

When you use , you must lock all tables that you are going to use in your queries. Because will not lock views, if the operation that you are performing uses any views, you must also lock all of the base tables on which those views depend. While the locks obtained with a statement are in effect, you cannot access any tables that were not locked by the statement. Also, you cannot use a locked table multiple times in a single query. Use aliases instead, in which case you must obtain a lock for each alias separately.

mysql> 
mysql> 
ERROR 1100: Table 't' was not locked with LOCK TABLES
mysql> 

If your queries 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> 
mysql> 
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 queries using that alias:

mysql> 
mysql> 
ERROR 1100: Table 't' was not locked with LOCK TABLES
mysql> 

locks normally have higher priority than locks to ensure that updates are processed as soon as possible. This means that if one thread obtains a lock and then another thread requests a lock, subsequent lock requests wait until the thread has gotten the lock and released it. You can use locks to allow other threads to obtain locks while the thread is waiting for the lock. You should use locks only if you are sure that eventually there will be a time when no threads have a lock.

works as follows:

  1. Sort all tables to be locked in an internally defined order. From the user standpoint, this order is undefined.

  2. If a table is locked with a read and a write lock, put the write lock before the read lock.

  3. Lock one table at a time until the thread gets all locks.

This policy ensures that table locking is deadlock free. There are, however, other things you need to be aware of about this policy: If you are using a lock for a table, it means only that MySQL waits for this particular lock until there are no threads that want a lock. When the thread has gotten the lock and is waiting to get the lock for the next table in the lock table list, all other threads wait for the lock to be released. If this becomes a serious problem with your application, you should consider converting some of your tables to transaction-safe tables.

You can safely use to terminate a thread that is waiting for a table lock. See Section 13.5.5.3, “ Syntax”.

Note that you should not lock any tables that you are using with because in that case the is performed by a separate thread.

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

  • If you are going to run many operations on a set of tables, it is much faster to lock the tables you are going to use. Locking tables speeds up inserting, updating, or deleting on them. The downside is that no thread can update a -locked table (including the one holding the lock) and no thread can access a -locked table other than the one holding the lock.

    The reason some operations are faster under is that MySQL does not flush the key cache for the locked tables until is called. Normally, the key cache is flushed after each SQL statement.

  • If you are using a storage engine in MySQL that does not support transactions, you must use if you want to ensure that no other thread comes between a and an . The example shown here requires to execute safely:

    LOCK TABLES trans READ, customer WRITE;
    SELECT SUM(value) FROM trans WHERE customer_id=;
    UPDATE customer
      SET total_value=
      WHERE customer_id=;
    UNLOCK TABLES;
    

    Without , it is possible that another thread might insert a new row in the table between execution of the and statements.

You can avoid using in many cases by using relative updates (=+) or the function. See Section 1.9.5.3, “Transactions and Atomic Operations”.

You can also avoid locking tables in some cases by using the user-level advisory lock functions and . These locks are saved in a hash table in the server and implemented with and for high speed. See Section 12.9.4, “Miscellaneous Functions”.

See Section 7.3.1, “Locking Methods”, for more information on locking policy.

You can lock all tables in all databases with read locks with the statement. See Section 13.5.5.2, “ Syntax”. This is a very convenient way to get backups if you have a filesystem such as Veritas that can take snapshots in time.

Note: If you use on a locked table, it may become unlocked. See Section A.7.1, “Problems with .

13.4.6. SET TRANSACTION Syntax

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
{ READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

This statement sets the transaction isolation level for the next transaction, globally, or for the current session.

The default behavior of is to set the isolation level for the next (not yet started) transaction. If you use the keyword, the statement sets the default transaction level globally for all new connections created from that point on. Existing connections are unaffected. You need the privilege to do this. Using the keyword sets the default transaction level for all future transactions performed on the current connection.

For descriptions of each transaction isolation level, see Section 14.2.10.3, “ and . supports each of these levels in MySQL 5.0. The default level is .

To set the initial default global isolation level for mysqld, use the option. See Section 5.2.1, “mysqld Command Options”.

13.4.7. XA Transactions

MySQL 5.0.3 and up provides server-side support for XA transactions. Currently, this support is available for the 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 Section I.5, “Restrictions on XA Transactions”.

On the client side, there are no special requirements. The XA interface to a MySQL server consists of SQL statements that begin with the 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 will work.

Currently, among the MySQL Connectors, MySQL Connector/J 5.0.0 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 allow 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. (However, for a distributed transaction, you must use the isolation level to achieve ACID properties. It is enough to use for a non-distributed transaction, but not for a distributed transaction.)

Some examples of distributed transactions:

  • An application may act as an integration tool that combines a messaging service with an RDBMS. The application makes sure that transactions dealing with message sending, retrieval, and processing that also involve a transactional database all happen in a global transaction. You can think of this as “transactional email.

  • An application performs actions that involve different database servers, such as a MySQL server and an Oracle server (or multiple MySQL servers), where actions that involve multiple servers must happen as part of a global transaction, rather than as separate transactions local to each server.

  • A bank keeps account information in an RDBMS and distributes and receives money via automated teller machines (ATMs). It is necessary to ensure that ATM actions are correctly reflected in the accounts, but this cannot be done with the RDBMS alone. A global transaction manager integrates the ATM and database resources to ensure overall consistency of financial transactions.

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

  • A Resource Manager (RM) provides access to transactional resources. A database server is one kind of resource manager. It must be possible to either commit or roll back transactions managed by the RM.

  • A Transaction Manager (TM) coordinates the transactions that are part of a global transaction. It communicates with the RMs that handle each of these transactions. The individual transactions within a global transaction are “branches” of the global transaction. Global transactions and their branches are identified by a naming scheme described later.

The MySQL implementation of XA MySQL 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 musts 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 will be able to commit, all branches are told to commit. If any branch indicated when it was prepared that it will not be able to 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.

13.4.7.1. XA Transaction SQL Syntax

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

XA {START|BEGIN}  [JOIN|RESUME]

XA END  [SUSPEND [FOR MIGRATE]]

XA PREPARE 

XA COMMIT  [ONE PHASE]

XA ROLLBACK 

XA RECOVER

For , the and clauses are not supported.

For the clause is not supported.

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

:  [,  [,  ]]

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

and must be string literals, each up to 64 bytes (not characters) long. and can be specified in several ways. You can use a quoted string (), hex string (, ), or bit value (').

is an unsigned integer.

The and 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 and as hex strings.

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 values in a list of values returned by the statement.

starts an XA transaction with the given value. Each XA transaction must have a unique value, so the value must not currently be used by another XA transaction. Uniqueness is assessed using the and values. All following XA statements for the XA transaction must be specified using the same value as that given in the statement. If you use any of those statements but specify an 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 value in the value. For this reason, values must be globally unique so that there is no ambiguity about which global transaction a given XA transaction is part of. The part of the value must be different for each XA transaction within a global transaction. (The requirement that values be different is a limitation of the current MySQL XA implementation. It is not part of the XA specification.)

The statement returns information for those XA transactions on the MySQL server that are in the state. (See Section 13.4.7.2, “XA Transaction States”.) The output includes a row for each such XA transaction on the server, regardless of which client started it.

output rows look like this (for an example value consisting of the parts , , and ):

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

The output columns have the following meanings:

  • is the part of the transaction

  • is the length in bytes of the part of the

  • is the length in bytes of the part of the

  • is the concatenation of the and parts of the

13.4.7.2. XA Transaction States

An XA transaction progresses through the following states:

  1. Use to start an XA transaction and put it in the state.

  2. For an XA transaction, issue the SQL statements that make up the transaction, and then issue an statement. puts the transaction in the state.

  3. For an XA transaction, you can issue either an statement or an statement:

    • puts the transaction in the state. An statement at this point will include the transaction's value in its output, because lists all XA transactions that are in the state.

    • prepares and commits the transaction. The value will not be listed by because the transaction terminates.

  4. For a XA transaction, you can issue an statement to commit and terminate the transaction, or 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> 
Query OK, 0 rows affected (0.00 sec)

mysql> 
Query OK, 1 row affected (0.04 sec)

mysql> 
Query OK, 0 rows affected (0.00 sec)

mysql> 
Query OK, 0 rows affected (0.00 sec)

mysql> 
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 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 , no XA statements can be used until the transaction has been committed or rolled back.

Note that if an XA transaction is in the 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. You will receive the following error 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 Section 13.4.3, “Statements That Cause an Implicit Commit”.