TRANSACTION / COMMIT / ROLLBACK

Starts a transaction (MySQL).

START TRANSACTION;

Starts a transaction (standard SQL / PostgreSQL).

BEGIN;

Commits the changes.

COMMIT;

Rolls back all changes.

ROLLBACK;

Sets a savepoint.

SAVEPOINT savepoint_name;

Rolls back to a savepoint.

ROLLBACK TO SAVEPOINT savepoint_name;

The following examples use the accounts table.

Money transfer: moving 10,000 from account A to account B. The following shows execution line by line in interactive mode.

START TRANSACTION;
UPDATE accounts SET balance = balance - 10000 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 10000 WHERE account_id = 2;
COMMIT;

Check the committed result. user_a's balance decreased by 10,000 and user_c's balance increased by 10,000.

SELECT * FROM accounts;
+------------+--------+---------+
| account_id | name   | balance |
+------------+--------+---------+
|          1 | user_a |   40000 |
|          2 | user_c |   30000 |
|          3 | user_e |   75000 |
+------------+--------+---------+
3 rows in set (0.00 sec)

In practice, transactions are usually controlled from application code. When written in a .sql file, it looks like this.

sample_transfer.sql

START TRANSACTION;

UPDATE accounts SET balance = balance - 10000 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 10000 WHERE account_id = 2;

COMMIT;

Example using a savepoint.

sample_transaction.sql

START TRANSACTION;

-- Increase user_e's balance.
UPDATE accounts SET balance = balance + 5000 WHERE account_id = 3;
SAVEPOINT after_update;

-- Accidentally decrease user_a's balance — this will be rolled back.
UPDATE accounts SET balance = balance - 5000 WHERE account_id = 1;

-- Roll back only the change to user_a, keeping user_e's update.
ROLLBACK TO SAVEPOINT after_update;

COMMIT;
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)

sample_transaction.sql

SELECT * FROM accounts;
+------------+--------+---------+
| account_id | name   | balance |
+------------+--------+---------+
|          1 | user_a |   50000 |
|          2 | user_c |   20000 |
|          3 | user_e |   80000 |
+------------+--------+---------+
3 rows in set

In MySQL, use START TRANSACTION or BEGIN to start a transaction.

START TRANSACTION;
UPDATE accounts SET balance = balance - 10000 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 10000 WHERE account_id = 2;
COMMIT;

In PostgreSQL, use BEGIN to start a transaction. START TRANSACTION is also supported.

BEGIN;
UPDATE accounts SET balance = balance - 10000 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 10000 WHERE account_id = 2;
COMMIT;

In SQLite, use BEGIN TRANSACTION to start a transaction. The default mode is auto-commit.

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 10000 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 10000 WHERE account_id = 2;
COMMIT;

Transactions guarantee four properties known as ACID: Atomicity, Consistency, Isolation, and Durability. Always use transactions for operations that span multiple tables — such as bank transfers or inventory deductions combined with order registration — to ensure no partial state is left behind if a failure occurs.

MySQL defaults to auto-commit mode (AUTO_COMMIT=1). Issuing START TRANSACTION temporarily disables auto-commit for that transaction. You can permanently disable auto-commit with SET AUTOCOMMIT = 0, but be careful not to forget to issue COMMIT.

For managing privileges, see GRANT / REVOKE.