by
SQL Tutorial › Modifying Data › Transactions & ACID

SQL Transactions and ACID

IntermediateLesson 22 of 6913 min read8 queries run on MySQL 8.4

A transaction groups several statements into one unit of work that either succeeds completely or leaves no trace. Moving money between two accounts is the textbook case: the debit and the credit must both happen, or neither. This lesson runs real transactions on MySQL 8.4 and explains the ACID guarantees behind them.

Advertisement
In this lesson
  • Start, commit and roll back a transaction
  • Undo part of a transaction with SAVEPOINT
  • What autocommit does
  • The four ACID properties and the isolation levels

The problem transactions solve

Transfer 2,000 from Asha’s account to Ravi’s account. That is two updates. If the server crashes, or the second update fails, after the first one, 2,000 disappears. A transaction makes the two updates atomic.

Every example in this lesson starts with this table:

MySQL
CREATE TABLE accounts (  account_id INT PRIMARY KEY,  holder     VARCHAR(30) NOT NULL,  balance    INT NOT NULL CHECK (balance >= 0));INSERT INTO accounts VALUES (1, 'Asha', 10000), (2, 'Ravi', 3000);

START TRANSACTION and COMMIT

MySQL
CREATE TABLE accounts (account_id INT PRIMARY KEY, holder VARCHAR(30) NOT NULL, balance INT NOT NULL CHECK (balance >= 0));INSERT INTO accounts VALUES (1, 'Asha', 10000), (2, 'Ravi', 3000); START TRANSACTION;UPDATE accounts SET balance = balance - 2000 WHERE account_id = 1;UPDATE accounts SET balance = balance + 2000 WHERE account_id = 2;COMMIT; SELECT * FROM accounts;
ResultMySQL 8.4
mysql> CREATE TABLE accounts (account_id INT PRIMARY KEY, holder VARCHAR(30) …
Query OK, 0 rows affected
mysql> INSERT INTO accounts VALUES (1, 'Asha', 10000), (2, 'Ravi', 3000)
Query OK, 2 rows affected
mysql> START TRANSACTION
Query OK, 0 rows affected
mysql> UPDATE accounts SET balance = balance – 2000 WHERE account_id = 1
Query OK, 1 row affected
mysql> UPDATE accounts SET balance = balance + 2000 WHERE account_id = 2
Query OK, 1 row affected
mysql> COMMIT
Query OK, 0 rows affected
mysql> SELECT * FROM accounts
account_idholderbalance
1Asha8000
2Ravi5000
2 rows in set
How this query works, step by step
  1. START TRANSACTION (or BEGIN) opens a transaction. From now on, changes are pending.
  2. The two UPDATEs move the money. Other sessions still see the old balances.
  3. COMMIT makes both changes permanent and visible to everyone at the same moment.
  4. The total money in the bank is still 13,000.
Advertisement

ROLLBACK

ROLLBACK throws away every change since START TRANSACTION:

MySQL
CREATE TABLE accounts (account_id INT PRIMARY KEY, holder VARCHAR(30) NOT NULL, balance INT NOT NULL CHECK (balance >= 0));INSERT INTO accounts VALUES (1, 'Asha', 10000), (2, 'Ravi', 3000); START TRANSACTION;UPDATE accounts SET balance = 0 WHERE account_id = 1;SELECT * FROM accounts;ROLLBACK;SELECT * FROM accounts;
ResultMySQL 8.4
mysql> CREATE TABLE accounts (account_id INT PRIMARY KEY, holder VARCHAR(30) …
Query OK, 0 rows affected
mysql> INSERT INTO accounts VALUES (1, 'Asha', 10000), (2, 'Ravi', 3000)
Query OK, 2 rows affected
mysql> START TRANSACTION
Query OK, 0 rows affected
mysql> UPDATE accounts SET balance = 0 WHERE account_id = 1
Query OK, 1 row affected
mysql> SELECT * FROM accounts
account_idholderbalance
1Asha0
2Ravi3000
2 rows in set
mysql> ROLLBACK
Query OK, 0 rows affected
mysql> SELECT * FROM accounts
account_idholderbalance
1Asha10000
2Ravi3000
2 rows in set
How this query works, step by step
  1. Inside the transaction Asha’s balance is set to 0, and this session sees the change.
  2. ROLLBACK undoes it.
  3. The final SELECT shows the original 10,000.

When a statement fails mid-transaction

Ravi tries to send 5,000 but has only 3,000. The credit to Asha runs first, then the debit breaks the CHECK (balance >= 0) rule:

MySQL
CREATE TABLE accounts (account_id INT PRIMARY KEY, holder VARCHAR(30) NOT NULL, balance INT NOT NULL CHECK (balance >= 0));INSERT INTO accounts VALUES (1, 'Asha', 10000), (2, 'Ravi', 3000); START TRANSACTION;UPDATE accounts SET balance = balance + 5000 WHERE account_id = 1;UPDATE accounts SET balance = balance - 5000 WHERE account_id = 2;
ResultMySQL 8.4
mysql> CREATE TABLE accounts (account_id INT PRIMARY KEY, holder VARCHAR(30) …
Query OK, 0 rows affected
mysql> INSERT INTO accounts VALUES (1, 'Asha', 10000), (2, 'Ravi', 3000)
Query OK, 2 rows affected
mysql> START TRANSACTION
Query OK, 0 rows affected
mysql> UPDATE accounts SET balance = balance + 5000 WHERE account_id = 1
Query OK, 1 row affected
mysql> UPDATE accounts SET balance = balance – 5000 WHERE account_id = 2
ERROR 3819 (HY000): Check constraint 'accounts_chk_1' is violated.

The failed statement changed nothing, but the transaction is still open, and the first update is still pending:

MySQL
SELECT * FROM accounts;ROLLBACK;SELECT * FROM accounts;
ResultMySQL 8.4
mysql> SELECT * FROM accounts
account_idholderbalance
1Asha15000
2Ravi3000
2 rows in set
mysql> ROLLBACK
Query OK, 0 rows affected
mysql> SELECT * FROM accounts
account_idholderbalance
1Asha10000
2Ravi3000
2 rows in set
How this query works, step by step
  1. The first SELECT shows Asha at 15,000: MySQL does not undo the whole transaction automatically when one statement fails.
  2. Your code must react to the error by calling ROLLBACK.
  3. After the rollback both balances are back to 10,000 and 3,000.
Watch out

In application code, always wrap transactions in try/except (or try/catch): on any error, ROLLBACK; only when every statement succeeded, COMMIT.

Advertisement

SAVEPOINT: partial rollback

A savepoint marks a point inside a transaction that you can roll back to without losing the earlier work:

MySQL
CREATE TABLE accounts (account_id INT PRIMARY KEY, holder VARCHAR(30) NOT NULL, balance INT NOT NULL CHECK (balance >= 0));INSERT INTO accounts VALUES (1, 'Asha', 10000), (2, 'Ravi', 3000); START TRANSACTION;UPDATE accounts SET balance = balance - 1000 WHERE account_id = 1;SAVEPOINT after_fee;UPDATE accounts SET balance = balance + 9999 WHERE account_id = 2;ROLLBACK TO SAVEPOINT after_fee;COMMIT; SELECT * FROM accounts;
ResultMySQL 8.4
mysql> CREATE TABLE accounts (account_id INT PRIMARY KEY, holder VARCHAR(30) …
Query OK, 0 rows affected
mysql> INSERT INTO accounts VALUES (1, 'Asha', 10000), (2, 'Ravi', 3000)
Query OK, 2 rows affected
mysql> START TRANSACTION
Query OK, 0 rows affected
mysql> UPDATE accounts SET balance = balance – 1000 WHERE account_id = 1
Query OK, 1 row affected
mysql> SAVEPOINT after_fee
Query OK, 0 rows affected
mysql> UPDATE accounts SET balance = balance + 9999 WHERE account_id = 2
Query OK, 1 row affected
mysql> ROLLBACK TO SAVEPOINT after_fee
Query OK, 0 rows affected
mysql> COMMIT
Query OK, 0 rows affected
mysql> SELECT * FROM accounts
account_idholderbalance
1Asha9000
2Ravi3000
2 rows in set
How this query works, step by step
  1. A fee of 1,000 is taken from Asha.
  2. SAVEPOINT after_fee remembers this point.
  3. A wrong credit to Ravi is made, then undone with ROLLBACK TO SAVEPOINT after_fee.
  4. COMMIT saves the fee only: Asha 9,000, Ravi unchanged.

Autocommit

By default MySQL runs in autocommit mode: every statement outside an explicit transaction is committed immediately.

MySQL
SELECT @@autocommit;
ResultMySQL 8.4
@@autocommit
1
1 row in set

SET autocommit = 0; keeps a transaction open until you COMMIT or ROLLBACK. Most applications leave autocommit on and use START TRANSACTION where they need several statements to succeed together.

Note

DDL statements such as CREATE, ALTER, DROP and TRUNCATE cause an implicit commit in MySQL: they commit any open transaction and cannot be rolled back.

ACID

PropertyMeansIn the transfer example
Atomicityall or nothingdebit and credit both happen, or neither
Consistencyrules always holdCHECK (balance >= 0) is never broken
Isolationconcurrent transactions do not see each other’s half-done worknobody sees the money “in flight”
Durabilitycommitted data survives a crashafter COMMIT, a power cut cannot undo the transfer

MySQL’s default storage engine, InnoDB, provides all four.

Advertisement

Isolation levels

MySQL
SELECT @@transaction_isolation;
ResultMySQL 8.4
@@transaction_isolation
REPEATABLE-READ
1 row in set
LevelDirty readNon-repeatable readPhantom read
READ UNCOMMITTEDpossiblepossiblepossible
READ COMMITTEDnopossiblepossible
REPEATABLE READ (MySQL default)nonomostly prevented in InnoDB
SERIALIZABLEnonono
  • Dirty read: seeing another transaction’s uncommitted change.
  • Non-repeatable read: reading the same row twice and getting different values.
  • Phantom read: running the same query twice and getting new rows.

Change it for the next transaction with SET TRANSACTION ISOLATION LEVEL READ COMMITTED;.

Locking reads

When you read a row in order to update it, lock it so nobody changes it in between:

MySQL
CREATE TABLE accounts (account_id INT PRIMARY KEY, holder VARCHAR(30) NOT NULL, balance INT NOT NULL CHECK (balance >= 0));INSERT INTO accounts VALUES (1, 'Asha', 10000), (2, 'Ravi', 3000); START TRANSACTION;SELECT balance FROM accounts WHERE account_id = 1 FOR UPDATE;UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;COMMIT;SELECT * FROM accounts;
ResultMySQL 8.4
mysql> CREATE TABLE accounts (account_id INT PRIMARY KEY, holder VARCHAR(30) …
Query OK, 0 rows affected
mysql> INSERT INTO accounts VALUES (1, 'Asha', 10000), (2, 'Ravi', 3000)
Query OK, 2 rows affected
mysql> START TRANSACTION
Query OK, 0 rows affected
mysql> SELECT balance FROM accounts WHERE account_id = 1 FOR UPDATE
balance
10000
1 row in set
mysql> UPDATE accounts SET balance = balance – 500 WHERE account_id = 1
Query OK, 1 row affected
mysql> COMMIT
Query OK, 0 rows affected
mysql> SELECT * FROM accounts
account_idholderbalance
1Asha9500
2Ravi3000
2 rows in set
How this query works, step by step
  1. SELECT ... FOR UPDATE reads Asha’s balance and locks her row until the transaction ends.
  2. Another session trying to update or lock the same row waits.
  3. After COMMIT the lock is released.

Quick quiz

1. What does ROLLBACK do?

2. A statement fails inside a MySQL transaction. What happens to the earlier statements?

3. Which ACID property guarantees that committed data survives a crash?

4. What is MySQL InnoDB’s default isolation level?

Advertisement

Transactions FAQ

What is a transaction in SQL?

A transaction is a group of SQL statements executed as one unit: either all of them are committed, or all are rolled back.

What is the difference between COMMIT and ROLLBACK?

COMMIT makes the changes of the current transaction permanent. ROLLBACK discards them.

What are ACID properties?

Atomicity (all or nothing), Consistency (constraints always hold), Isolation (concurrent transactions do not interfere) and Durability (committed data survives failures).

Can I roll back a TRUNCATE or DROP in MySQL?

No. DDL statements commit implicitly in MySQL, so they cannot be undone with ROLLBACK.