MENU


Transactions

By default, MySQL autocommits, meaning statements are executed immediately as they are issued. Starting a transaction with START TRANSACTION turns off autocommit. Changes made by statements are not permanent until they are committed explicitly with COMMIT. Statements that have not been committed can also be cancelled with ROLLBACK. A transaction ends with COMMIT or ROLLBACK, unless the AND CHAIN clause is used.

Syntax

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, COMMIT is required to make changes permanent, or ROLLBACK to discard them.

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 by COMMIT or ROLLBACK.

Beginning a transaction causes any pending transaction to be committed; transactions cannot be nested.

For best results, transactions should use only tables managed by a single transaction-safe storage engine such as InnoDB. Transactions that are rolled back are not logged.

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

Sets the transaction properties. Without SESSION or GLOBAL, the 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 behaves like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT...LOCK IN SHARE MODE if autocommit is disabled.

Exceptions

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.

Implicit Commit

Some statements cause an implicit commit:

PHP

Transactions can also be executed at the PHP level, using the mysqli extension's begin_transaction(), commit(), rollback() and autocommit() methods. See also PHP for general PHP/MySQL connectivity. 

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;

a
100

<!DOCTYPE html><html><head></head><body>

<?php
$S = new mysqli("localhost", "root", "password", "testDB");
$S->query("DROP TABLE IF EXISTS tbl");
$S->query("CREATE TABLE tbl (a INT)");
$S->begin_transaction();

   $S->query("INSERT INTO tbl VALUES(100)");
$S->rollback();

$S->autocommit(FALSE);
   $S->query("INSERT INTO tbl VALUES(200)");

// $S->commit();
$S->autocommit(TRUE); // also commits the transaction

tabulate($S->query("SELECT * FROM tbl"));

// A generic result printer
function tabulate($result) {

   $fInfo = $result->fetch_fields();
   echo "<table border='1'><tr>";
   foreach ($fInfo as $col) {

      echo "<td>" . $col->name . "</td>";
   }
   while ($row = $result->fetch_row()) {

      echo "</tr><tr>";
      foreach ($row as $val) {

         echo "<td>" . $val . "</td>";
      }
   }
   echo "</tr></table><br/>";
}
?>
</body>
</html>