Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Learn about transactions in MariaDB Server. This section covers SQL statements for managing atomic operations (START TRANSACTION, COMMIT, ROLLBACK), ensuring data integrity and consistency.
Commit the current transaction. This statement permanently saves all changes made during the current transaction to the database.
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.
- 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
Set the transaction isolation level to SERIALIZABLE. This highest level ensures total isolation by converting plain SELECTs to locking reads.
SERIALIZABLE is one of the transaction isolation levels. Similar to REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... LOCK IN SHARE MODE if autocommit is disabled.
See Isolation Levels for details.
This page is licensed: CC BY-SA / Gnu FDL
Set the transaction isolation level to READ COMMITTED. In this mode, each query within a transaction sees only data committed before the query began.
READ COMMITTED is one of the transaction isolation levels. Each consistent read, even within the same transaction, sets and reads its own fresh snapshot.
See Isolation Levels for details.
This page is licensed: CC BY-SA / Gnu FDL
Release explicit table locks. This statement releases all locks acquired by the current session with LOCK TABLES.
UNLOCK TABLESUNLOCK TABLES explicitly releases any table locks held by the current session. See for more information.
In addition to releasing table locks acquired by the statement, the UNLOCK TABLES statement also releases the global read lock acquired by the FLUSH TABLES WITH READ LOCK statement. The FLUSH TABLES WITH READ LOCK statement is very useful for performing backups. See for more information about FLUSH TABLES WITH READ LOCK.
This page is licensed: CC BY-SA / Gnu FDL
Set the transaction isolation level to REPEATABLE READ. This default InnoDB level ensures consistent results for repeated reads within the same transaction.
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 Isolation Levels for details.
This page is licensed: CC BY-SA / Gnu FDL
Set the transaction isolation level to READ UNCOMMITTED. This lowest isolation level allows dirty reads, where a transaction can see uncommitted changes.
READ UNCOMMITTED is one of the transaction isolation levels. SELECT statements are performed in a non-locking fashion, but a possible earlier version of a row might be used.
See Isolation Levels for details.
This page is licensed: CC BY-SA / Gnu FDL
Understand how MariaDB manages concurrency. Metadata locks protect the structure of database objects from being modified while they are in use.
MariaDB supports metadata locking. This means that when a transaction (including ) 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 ) 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.
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 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 plugin is installed, the table stores information about existing metadata locks.
The table contains metadata lock information.
Example
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]Let's use the following MEMORY (non-transactional) table:
Connection 1 starts a transaction, and INSERTs a row into t:
t's metadata is now locked by connection 1. Connection 2 tries to alter t, but has to wait:
Connection 2's prompt is blocked now.
Now connection 1 ends the transaction:
...and connection 2 finally gets the output of its command:
The Performance Schema metadata_locks table does not contain metadata lock information.
This page is licensed: CC BY-SA / Gnu FDL
CREATE TABLE t (a INT) ENGINE = MEMORY;START TRANSACTION;
INSERT INTO t SET a=1;ALTER TABLE t ADD COLUMN b INT;COMMIT;Query OK, 1 row affected (35.23 sec)
Records: 1 Duplicates: 0 Warnings: 0Understand how timeouts affect transactions. This section explains system variables that control wait times for locks and transaction duration.
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 variables help handle this situation:
idle_transaction_timeout (all transactions)
idle_write_transaction_timeout (write transactions)
(read transactions)
There is no variables for more granular control.
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.
This page is licensed: CC BY-SA / Gnu FDL
Undo changes in the current transaction. This statement reverts the database to its state before the transaction started or to a specific savepoint.
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
Identify statements that force a commit. Certain commands, like DDL statements, implicitly commit the current transaction before executing.
Some SQL statements cause an implicit commit. As a rule of thumb, such statements are DDL statements. The same statements (except for ) produce a 1400 error ( 'XAE09') if a XA transaction is in effect.
Here is the list:
SET autocommit = 1 causes an implicit commit if the value was 0.
All these statements cause an implicit commit before execution. This means that, even if the statement fails with an error, the transaction is committed. Some of them, like CREATE TABLE ... SELECT, also cause a commit immediatly after execution. Such statements couldn't be rollbacked in any case.
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
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionAND CHAINAND NO CHAINAll 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
ROLLBACK [ WORK ] [ AND [ NO ] CHAIN ]
[ TO [ SAVEPOINT ] {<savepoint name> | <simple target specification>} ]These statements do not cause an implicit commit in the following cases:
CREATE TABLE and DROP TABLE, when the TEMPORARY keyword is used.
However, TRUNCATE TABLE causes an implicit commit even when used on a temporary table.
CREATE FUNCTION and DROP FUNCTION, when used to create a UDF (instead of a stored function). However, CREATE INDEX and DROP INDEX cause commits even when used with temporary tables.
causes a commit only if a was used on non-transactional tables.
This page is licensed: CC BY-SA / Gnu FDL
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.This page is licensed: CC BY-SA / Gnu FDL
Explicitly lock one or more tables. This statement acquires read or write locks on tables, preventing other sessions from accessing them.
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 need to correspond to the aliases used in prior SQL statements in the session. For example:
Set the lock wait timeout. See .
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 the active transaction, if any. Also, starting a transaction always releases all table locks acquired with LOCK TABLES. This means that there is no way to have table locks and an active transaction at the same time. The only exceptions are the transactions in
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:
This page is licensed: GPLv2, originally from
Begin a new transaction. This statement initiates a transaction, optionally setting characteristics like consistent snapshots or read-only mode.
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.
The access mode specifies whether the transaction is allowed to write data or not. By default, transactions are in READ WRITE mode (see the system variable). READ ONLY mode allows the storage engine to apply optimizations that cannot be used for transactions which write data. Note that, unlike the global mode, the privilege doesn't allow writes and DDL statements on temporary tables are not allowed either.
The access mode specifies whether the transaction is allowed to write data or not. By default, transactions are in READ WRITE mode (see the system variable). READ ONLY mode allows the storage engine to apply optimizations that cannot be used for transactions which write data. Note that, unlike the global mode, the privilege doesn't allow writes and DDL statements on temporary tables are not allowed either.
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.
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 (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 COMMIT
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.
The system variable is a session-only, read-only variable that returns 1 inside a transaction, and 0 if not in a transaction.
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 .
This page is licensed: GPLv2, originally from
ROLLBACK;
ROLLBACK WORK;
ROLLBACK AND NO CHAIN;
ROLLBACK WORK AND NO CHAIN;ROLLBACK AND CHAIN;
ROLLBACK WORK AND CHAIN;INSERT INTO Table_2 VALUES(5);
DROP TABLE Table_3 CASCADE;
ROLLBACK;ALTER DATABASE ... UPGRADE DATA DIRECTORY NAME
ALTER EVENT
ALTER FUNCTION
ALTER PROCEDURE
ALTER SEQUENCE
ALTER SERVER
ALTER TABLE
ALTER VIEW
ANALYZE TABLE
BEGIN
CACHE INDEX
CHANGE MASTER TO
CHECK TABLE
CREATE DATABASE
CREATE EVENT
CREATE FUNCTION
CREATE INDEX
CREATE PROCEDURE
CREATE ROLE
CREATE SEQUENCE
CREATE SERVER
CREATE TABLE
CREATE TRIGGER
CREATE USER
CREATE VIEW
DROP DATABASE
DROP EVENT
DROP FUNCTION
DROP INDEX
DROP PROCEDURE
DROP ROLE
DROP SEQUENCE
DROP SERVER
DROP TABLE
DROP TRIGGER
DROP USER
DROP VIEW
FLUSH
GRANT
LOAD INDEX INTO CACHE
LOCK TABLES
OPTIMIZE TABLE
RENAME TABLE
RENAME USER
REPAIR TABLE
RESET
REVOKE
SET PASSWORD
SHUTDOWN
START SLAVE
START TRANSACTION
STOP SLAVE
TRUNCATE TABLEALTER TABLE tbl_name [WAIT n|NOWAIT] ...
CREATE ... INDEX ON tbl_name (index_col_name, ...) [WAIT n|NOWAIT] ...
DROP INDEX ... [WAIT n|NOWAIT]
DROP TABLE tbl_name [WAIT n|NOWAIT] ...
LOCK TABLE ... [WAIT n|NOWAIT]
OPTIMIZE TABLE tbl_name [WAIT n|NOWAIT]
RENAME TABLE tbl_name [WAIT n|NOWAIT] ...
SELECT ... FOR UPDATE [WAIT n|NOWAIT]
SELECT ... LOCK IN SHARE MODE [WAIT n|NOWAIT]
TRUNCATE TABLE tbl_name [WAIT n|NOWAIT]SET SESSION idle_transaction_timeout=2;
BEGIN;
SELECT * FROM t;
Empty set (0.000 sec)
## wait 3 seconds
SELECT * FROM t;
ERROR 2006 (HY000): MySQL server has gone awaySET SESSION idle_write_transaction_timeout=2;
BEGIN;
SELECT * FROM t;
Empty set (0.000 sec)
## wait 3 seconds
SELECT * FROM t;
Empty set (0.000 sec)
INSERT INTO t VALUES(1);
## wait 3 seconds
SELECT * FROM t;
ERROR 2006 (HY000): MySQL server has gone awaySET SESSION idle_transaction_timeout=2, SESSION idle_readonly_transaction_timeout=10;
BEGIN;
SELECT * FROM t;
Empty set (0.000 sec)
## wait 3 seconds
SELECT * FROM t;
Empty set (0.000 sec)
## wait 11 seconds
SELECT * FROM t;
ERROR 2006 (HY000): MySQL server has gone awayLOCK TABLE[S]
tbl_name [[AS] alias] lock_type
[, tbl_name [[AS] alias] lock_type] ...
[WAIT n|NOWAIT]
lock_type:
READ [LOCAL]
| [LOW_PRIORITY] WRITE
| WRITE CONCURRENT
UNLOCK TABLESSTART TRANSACTION [transaction_property [, transaction_property] ...] | BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET autocommit = {0 | 1}
transaction_property:
WITH CONSISTENT SNAPSHOT
| READ WRITE
| READ ONLYCREATEALTERDROPCOMMITROLLBACKSET autocommit=0;START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;When using LOCK TABLES on a TEMPORARY table, it will always be locked with a WRITE lock.
While a connection holds an explicit read lock on a table, it cannot modify it. If you try, the following error will be produced:
READ
Read lock, no writes allowed
READ LOCAL
Read lock, but allow concurrent inserts
WRITE
Exclusive write lock. No other connections can read or write to this table
LOW_PRIORITY WRITE
Exclusive write lock, but allow new read locks on the table until we get the write lock.
WRITE CONCURRENT
Exclusive write lock, but allow READ LOCAL locks to the table.
LOCK TABLE t1 AS t1_alias1 READ;
SELECT * FROM t1;
ERROR 1100 (HY000): Table 't1' was not locked with LOCK TABLES
SELECT * FROM t1 AS t1_alias2;
ERROR 1100 (HY000): Table 't1_alias2' was not locked with LOCK TABLES
SELECT * FROM t1 AS t1_alias1;ERROR 1099 (HY000): Table 'tab_name' was locked with a READ lock and can't be updatedERROR 1100 (HY000): Table 'tab_name' was not locked with LOCK TABLESERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transactionERROR 1314 (0A000): LOCK is not allowed in stored proceduresManage distributed transactions. This section covers XA statements for coordinating two-phase commits across multiple resources.
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.
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.
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.
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 (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 declares that the specified ACTIVE transaction is finished and it changes its state to IDLE. SUSPEND [FOR MIGRATE] has no effect.
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.
Prepared transactions were automatically rolled back on client disconnect, but were not rolled back if the server was crashed or killed. This violated XA guarantees and could have caused inconsistent data, if the transaction in question was already irrevocably committed in another XA participant.
Prepared transactions are automatically rolled back on client disconnect, but are not rolled back if the server was crashed or killed. This violated XA guarantees and can cause inconsistent data, if the transaction in question was already irrevocably committed in another XA participant.
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 rolls back and terminates an IDLE or PREPARED transaction.
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.
2-phases commit:
1-phase commit:
Human-readable:
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.
From MariaDB 10.5, XA PREPARE persists the XA transaction following the XA Specification. If an existing application relies on the previous behavior, upgrading to 10.5 or later can leave XA transactions in the PREPAREd state indefinitely after disconnect, causing such applications to no longer function correctly.
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
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 [FORMAT=['RAW'|'SQL']]
xid: gtrid [, bqual [, formatID ]]XA COMMIT 'test' ONE PHASE;
ERROR 1399 (XAE07): XAER_RMFAIL: The command cannot be executed when global transaction is in the ACTIVE state
XA COMMIT 'test2';
ERROR 1399 (XAE07): XAER_RMFAIL: The command cannot be executed when global transaction is in the NON-EXISTING stateXA {START|BEGIN} xid [JOIN|RESUME]XA START 'test' RESUME;
ERROR 1398 (XAE05): XAER_INVAL: Invalid arguments (or unsupported command)
XA START 'test' JOIN;
ERROR 1398 (XAE05): XAER_INVAL: Invalid arguments (or unsupported command)XA START 'test';
INSERT INTO t VALUES (1,2);
XA END 'test';
XA START 'test' RESUME; -- This reverts the XA END and continues the transaction
INSERT INTO t VALUES (3,4);
XA END 'test';
XA PREPARE 'test';
XA COMMIT 'test';XA END xid [SUSPEND [FOR MIGRATE]]XA PREPARE xidXA COMMIT xid [ONE PHASE]XA ROLLBACK xidXA RECOVER [FORMAT=['RAW'|'SQL']]XA RECOVER;
+----------+--------------+--------------+------+
| formatID | gtrid_length | bqual_length | data |
+----------+--------------+--------------+------+
| 1 | 4 | 0 | test |
+----------+--------------+--------------+------+XA START 'test';
INSERT INTO t VALUES (1,2);
XA END 'test';
XA PREPARE 'test';
XA COMMIT 'test';XA START 'test';
INSERT INTO t VALUES (1,2);
XA END 'test';
XA COMMIT 'test' ONE PHASE;xa start '12\r34\t67\v78', 'abc\ndef', 3;
INSERT t1 values (40);
xa end '12\r34\t67\v78', 'abc\ndef', 3;
xa prepare '12\r34\t67\v78', 'abc\ndef', 3;
xa recover format='RAW';
+----------+--------------+--------------+--------------------+
| formatID | gtrid_length | bqual_length | data |
+----------+--------------+--------------+--------------------+
34 67v78abc 11 | 7 | 12
def |
+----------+--------------+--------------+--------------------+
xa recover format='SQL';
+----------+--------------+--------------+-----------------------------------------------+
| formatID | gtrid_length | bqual_length | data |
+----------+--------------+--------------+-----------------------------------------------+
| 3 | 11 | 7 | X'31320d3334093637763738',X'6162630a646566',3 |
+----------+--------------+--------------+-----------------------------------------------+
xa rollback X'31320d3334093637763738',X'6162630a646566',3;Create a named marker within a transaction. Savepoints allow you to roll back part of a transaction without canceling the entire operation.
SAVEPOINT identifier
ROLLBACK [WORK] TO [SAVEPOINT] identifier
RELEASE SAVEPOINT identifierInnoDB 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.
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
ERROR 1305 (42000): SAVEPOINT svp_name does not exist