MENU


Transactions

By default, MySQL autocommits, which means that statements are executed immediately as they are issued. By starting a transaction with START TRANSACTION, you turn off autocommit. Changes made by statements are not permanent until you COMMIT them explicitly. You also have the choice to ROLLBACK statements that have not been committed, so that the changes are cancelled. A transaction ends with COMMIT or ROLLBACK unless the AND CHAIN clause is used.


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}

SAVEPOINT identifier

ROLLBACK [WORK] TO [SAVEPOINT] identifier

RELEASE SAVEPOINT identifier
WITH CONSISTENT SNAPSHOT starts a consistent read for InnoDB. The only isolation level that permits a consistent read is REPEATABLE READ.
READ ONLY allows optimizations to be made for InnoDB.
READ WRITE is the default.
After autocommit has been SET to zero, you must use COMMIT to make the changes permanent or ROLLBACK to ignore the changes.
BEGIN is the same as START TRANSACTION, but the latter permits modifiers.
WORK is ignored.
AND CHAIN starts a new transaction as soon as the current one ends.
RELEASE disconnects the current client session after terminating the current transaction.
ROLLBACK TO rolls back to the named savepoint without terminating the transaction.
RELEASE SAVEPOINT removes the named savepoint without performing any COMMIT or ROLLBACK. Savepoints are deleted with COMMIT or ROLLBACK.
Beginning a transaction causes any pending transaction to be committed. Thus, transactions cannot be nested.
Transactions should be performed using only tables managed by a single transaction-safe storage engine, for best results.
Transactions that are rolled back are not logged. 

DROP TABLE IF EXISTS tbl;
CREATE TABLE tbl (a INT);

START TRANSACTION;
   INSERT INTO tbl VALUES (100);
   SAVEPOINT sp;
   INSERT INTO tbl VALUES (200);
   ROLLBACK TO sp;
COMMIT;

SELECT * FROM tbl;

100
SET [GLOBAL | SESSION] TRANSACTION
transaction_characteristic [, transaction_characteristic] ...

transaction_characteristic:
ISOLATION LEVEL level
| READ WRITE
| READ ONLY

level:
REPEATABLE READ
| READ COMMITTED
| READ UNCOMMITTED
| SERIALIZABLE
This sets the transaction properties. Without SESSION or GLOBAL, this statement applies to the next transaction within the current session.
For REPEATABLE READ, the default isolation level for InnoDB, all consistent reads within the same transaction read the snapshot established by the first read.
For READ COMMITTED, each consistent read, even within the same transaction, sets and reads its own fresh snapshot.
For READ UNCOMMITTED, SELECT statements are performed in a nonblocking fashion, but a possible earlier version of a row might be used. Reads are not consistent.
SERIALIZABLE is like REPEATEABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT…LOCK IN SHARE MODE if autocommit is disabled.
DROP TABLE IF EXISTS tbl;
CREATE TABLE tbl (a INT);

START TRANSACTION;
INSERT INTO tbl VALUES (100);
SAVEPOINT sp;
INSERT INTO tbl VALUES (200);
ROLLBACK TO sp;
COMMIT;

SELECT * FROM tbl;
100

Some statements cannot be rolled back. These include data definition language (DDL) statements, such as those that create or drop databases, and those that create, drop, or alter tables or stored routines. A transaction cannot be fully rolled back if it contains such a statement.
Some statements cause an implicit commit: