Transactions, ACID & isolation levels

Transferring ₹500 from Asha to Rohan takes two updates: subtract from one account, add to the other. If the server crashes between them, money vanishes. A transaction groups statements into a single unit that either fully happens or doesn't happen at all. Transactions also control what concurrent users can see of each other's unfinished work.

Sample data#

CREATE TABLE accounts (
    id      INT PRIMARY KEY,
    owner   VARCHAR(20) NOT NULL,
    balance DECIMAL(10,2) NOT NULL CHECK (balance >= 0)
) ENGINE = InnoDB;

INSERT INTO accounts VALUES (1, 'Asha', 1000.00), (2, 'Rohan', 200.00);

Transactions need a transactional storage engine. InnoDB, the default, is one. MyISAM isn't, and silently ignores ROLLBACK.

COMMIT and ROLLBACK#

START TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;

SELECT * FROM accounts;

+----+-------+---------+
| id | owner | balance |
+----+-------+---------+
|  1 | Asha  |  500.00 |
|  2 | Rohan |  700.00 |
+----+-------+---------+

• START TRANSACTION (or BEGIN) opens a transaction.
• COMMIT makes every change permanent and visible to others.
• ROLLBACK throws all of the changes away:

START TRANSACTION;
UPDATE accounts SET balance = 0 WHERE id = 2;
SELECT balance FROM accounts WHERE id = 2;   -- we see our own uncommitted change
ROLLBACK;
SELECT balance FROM accounts WHERE id = 2;   -- back to 700.00

+---------+
| balance |
+---------+
|    0.00 |
+---------+
+---------+
| balance |
+---------+
|  700.00 |
+---------+

In application code, the pattern is always: begin, run the statements, commit if everything succeeded, roll back on any error.

# Python (mysql-connector) sketch
try:
    conn.start_transaction()
    cur.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s", (500, 1))
    cur.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s", (500, 2))
    conn.commit()
except Exception:
    conn.rollback()
    raise

Autocommit#

By default MySQL runs in autocommit mode: every statement outside an explicit transaction is its own transaction and commits immediately. That's why a single UPDATE sticks even without COMMIT.

SELECT @@autocommit;

+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+

SET autocommit = 0; changes this for your session, so that changes stay pending until you COMMIT. Be careful: forgetting to commit leaves locks held and work invisible to everyone else. Prefer explicit START TRANSACTION blocks.

Savepoints#

A savepoint marks a point inside a transaction that you can roll back to without abandoning the whole thing:

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
SAVEPOINT after_fee;
UPDATE accounts SET balance = balance - 9999 WHERE id = 1;   -- violates the CHECK
ROLLBACK TO SAVEPOINT after_fee;
COMMIT;
SELECT * FROM accounts;

ERROR 3819 (HY000): Check constraint 'accounts_chk_1' is violated.
+----+-------+---------+
| id | owner | balance |
+----+-------+---------+
|  1 | Asha  |  400.00 |
|  2 | Rohan |  700.00 |
+----+-------+---------+

The failing statement was rejected by the CHECK constraint. A failed statement is rolled back on its own, but the transaction stays open, so your code decides what to do next. Here we kept the 100 fee and committed.

Implicit commits#

Some statements commit the current transaction automatically, and can't be rolled back themselves: DDL (CREATE, ALTER, DROP, TRUNCATE, RENAME), user management (CREATE USER, GRANT), LOCK TABLES and a few others. Never mix schema changes into a data transaction and expect ROLLBACK to undo them.

ACID#

Isolation levels#

When transactions run at the same time, three classic anomalies can occur:

• Dirty read: seeing another transaction's uncommitted change (which may later be rolled back).
• Non-repeatable read: reading the same row twice in one transaction and getting different values, because someone committed an update in between.
• Phantom read: running the same range query twice and getting new rows, because someone inserted in between.

SQL defines four isolation levels:

*InnoDB's REPEATABLE READ uses a consistent snapshot for ordinary SELECTs, so they don't see phantoms. Locking reads (SELECT ... FOR UPDATE) and writes see the latest data, and InnoDB uses gap locks to block phantoms there.

SELECT @@transaction_isolation;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT @@transaction_isolation;

+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED          |
+-------------------------+

Seeing REPEATABLE READ in action

Open two mysql sessions side by side and run these steps in order:

-- Session A                                   -- Session B
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 2;
--  → 700.00
                                               UPDATE accounts SET balance = 50 WHERE id = 2;
                                               -- (autocommit: committed immediately)
SELECT balance FROM accounts WHERE id = 2;
--  → still 700.00 under REPEATABLE READ
--    (it would be 50.00 under READ COMMITTED)
COMMIT;
SELECT balance FROM accounts WHERE id = 2;
--  → 50.00 (a new snapshot)

InnoDB achieves this with MVCC (multi-version concurrency control). Instead of blocking readers, it keeps older row versions in the undo log and shows each transaction the version that matches its snapshot. That's why, in InnoDB, readers don't block writers and writers don't block readers.

Which level should you use?

• REPEATABLE READ (default) is a good general choice and gives stable reports within a transaction.
• READ COMMITTED is popular for high-concurrency OLTP apps (and is PostgreSQL's and Oracle's default). It takes fewer gap locks, so there are fewer deadlocks, and each statement sees the latest committed data.
• SERIALIZABLE turns plain SELECTs into locking reads. It's the safest and the slowest.
• READ UNCOMMITTED is almost never appropriate.

Isolation levels don't protect a read-then-write pattern on their own. "Read the balance in the app, then write balance − 500" can still lose updates under concurrency. Use an atomic UPDATE ... SET balance = balance - 500 WHERE balance >= 500, or lock the row with SELECT ... FOR UPDATE (next lesson).

Best practices#

• Keep transactions short. Never wait for user input or call slow external APIs inside one, because locks and old row versions pile up.
• Always handle errors with a rollback, and close transactions in a finally block or with your framework's transaction helper.
• Make retries safe. Deadlocks and lock-wait timeouts happen under load, so be ready to retry the whole transaction.
• Do big batch jobs in chunks (for example 1,000 rows per transaction) rather than one huge transaction.

What's next#

Isolation levels describe what you see. Locks decide who waits for whom. Next: row locks, FOR UPDATE, SKIP LOCKED and deadlocks.