MySQL COMMIT and ROLLBACK

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →

Moving money between 2 accounts takes 2 statements, and a failure between them leaves the money nowhere. A transaction groups statements so that either all of them count or none of them do. These examples use the sakila_practice database from INSERT. Run the setup block there again before you start, then open the client with mysql -u root -p sakila_practice.

How a transaction works

START TRANSACTION;
FIRST_STATEMENT;
SECOND_STATEMENT;
COMMIT;

Replace FIRST_STATEMENT and SECOND_STATEMENT with the statements that have to succeed together. COMMIT makes the work permanent and visible to everyone else. ROLLBACK throws it away and puts the rows back as they were. Until you type one of the 2, the changes are yours alone: another session still sees the old values. Every statement outside a transaction commits by itself, because autocommit is on.

SELECT @@autocommit AS autocommit;

+------------+
| autocommit |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

That is why the earlier lessons needed no COMMIT: each INSERT and UPDATE was a transaction of 1 statement. START TRANSACTION suspends that until you finish.

Examples

1) Undo the work with ROLLBACK

SELECT * FROM account;

+------------+------------------+---------+
| account_id | owner            | balance |
+------------+------------------+---------+
|          1 | Mary Smith       |  500.00 |
|          2 | Patricia Johnson |  250.00 |
+------------+------------------+---------+
2 rows in set (0.00 sec)

Move 100 from Mary to Patricia, look at the result, then change your mind.

START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE account_id = 1;
UPDATE account SET balance = balance + 100 WHERE account_id = 2;
SELECT * FROM account;
ROLLBACK;

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

+------------+------------------+---------+
| account_id | owner            | balance |
+------------+------------------+---------+
|          1 | Mary Smith       |  400.00 |
|          2 | Patricia Johnson |  350.00 |
+------------+------------------+---------+
2 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

The SELECT inside the transaction reports 400 and 350, because a session sees its own uncommitted work. The ROLLBACK then undoes both updates.

SELECT * FROM account;

+------------+------------------+---------+
| account_id | owner            | balance |
+------------+------------------+---------+
|          1 | Mary Smith       |  500.00 |
|          2 | Patricia Johnson |  250.00 |
+------------+------------------+---------+
2 rows in set (0.00 sec)

This is the only undo MySQL has. Once a statement outside a transaction has run, the old values are gone.

2) Keep the work with COMMIT

START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE account_id = 1;
UPDATE account SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

SELECT * FROM account;

+------------+------------------+---------+
| account_id | owner            | balance |
+------------+------------------+---------+
|          1 | Mary Smith       |  400.00 |
|          2 | Patricia Johnson |  350.00 |
+------------+------------------+---------+
2 rows in set (0.00 sec)

The 2 updates landed together. No other session ever saw 400 and 250 at the same time, which is the whole reason for writing it this way.

Some statements commit without being asked

A statement that changes the shape of the database cannot be rolled back. Worse, running one commits whatever the open transaction had done up to that point.

START TRANSACTION;
UPDATE account SET balance = 0 WHERE account_id = 1;
CREATE TABLE note (id INT);
ROLLBACK;

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Every line reports success, including the ROLLBACK.

SELECT * FROM account;

+------------+------------------+---------+
| account_id | owner            | balance |
+------------+------------------+---------+
|          1 | Mary Smith       |    0.00 |
|          2 | Patricia Johnson |  350.00 |
+------------+------------------+---------+
2 rows in set (0.00 sec)

The balance is 0. CREATE TABLE committed the update before it ran, so the ROLLBACK had nothing left to undo and still reported success. ALTER TABLE, DROP TABLE, TRUNCATE TABLE and RENAME TABLE behave the same way. Keep all 5 out of a transaction that is protecting data. Temporary tables are the exception. CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE commit nothing, so the same test with a temporary table in the middle leaves the balance at 500.00.

Summary

Write START TRANSACTION, then the statements, then COMMIT or ROLLBACK.
Expect every statement outside a transaction to commit by itself, because autocommit is on.
Expect your session to see its own uncommitted changes and no other session to see them.
Treat ROLLBACK as the only undo there is.
Keep CREATE TABLE, ALTER TABLE, DROP TABLE, TRUNCATE TABLE and RENAME TABLE out of a transaction, because each one commits it.
Expect the TEMPORARY forms of CREATE and DROP to commit nothing.

Getting Started

Server Development

Using Extensions

Creating Extensions

VillageSQL is a drop-in replacement for MySQL with extensions.

How a transaction works

Examples

1) Undo the work with ROLLBACK

2) Keep the work with COMMIT

Some statements commit without being asked

Summary

See also

VillageSQL is a drop-in replacement for MySQL with extensions.

​How a transaction works

​Examples

​1) Undo the work with ROLLBACK

​2) Keep the work with COMMIT

​Some statements commit without being asked

​Summary

​See also

How a transaction works

Examples

1) Undo the work with ROLLBACK

2) Keep the work with COMMIT

Some statements commit without being asked

Summary

See also