Interview Questions

Database transactions: ACID, isolation levels, locking and deadlocks

Himanshu Sharma · 12 September 2023 · 11 min read

You transfer 300 rupees from Alice to Bob. In the database that is two changes: Alice's balance goes down, Bob's goes up. If the server crashes between the two, 300 rupees vanish. If two transfers hit the same account at the same moment, one can overwrite the other. Transactions exist to make these situations safe. This post explains what a transaction is, the four ACID guarantees, how to use COMMIT, ROLLBACK and savepoints, what the four isolation levels actually do (shown with two real sessions running side by side), how locks and deadlocks work, and how to use transactions from PHP. Everything was run on MySQL 8.4 with InnoDB, and the PHP code on PHP 8.4.

The sample tables

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

CREATE TABLE transfers (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  from_id    INT, to_id INT, amount DECIMAL(10,2),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO accounts VALUES (1, 'Alice', 1000), (2, 'Bob', 500);

Note the CHECK (balance >= 0): an account can never go negative, which gives us a way to make a statement fail on purpose. Also note ENGINE=InnoDB; transactions need a transactional storage engine (see the end of this post).

What a transaction is

A transaction is a group of statements that the database treats as one unit of work: either all of them take effect, or none do. You mark the unit with START TRANSACTION and end it with COMMIT (keep the changes) or ROLLBACK (undo them).

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

Until COMMIT, other sessions do not see your changes (by default), and if the connection drops, the database undoes them. By default MySQL runs in autocommit mode (SELECT @@autocommit returns 1): every statement outside a transaction is committed immediately, as its own tiny transaction. START TRANSACTION temporarily switches that off until you commit or roll back.

Rollback in action

Let's make the second statement fail. Alice has 1,000; taking 5,000 breaks the CHECK:

START TRANSACTION;
UPDATE accounts SET balance = balance + 300 WHERE id = 2;     -- works
UPDATE accounts SET balance = balance - 5000 WHERE id = 1;    -- fails

ERROR 3819 (HY000): Check constraint 'accounts_chk_1' is violated.

After the error, the first update is still pending inside the open transaction, so Bob briefly has 800 in that session. Ending the session (or running ROLLBACK) discards it, and a fresh query showed Bob back at 500 and Alice at 1,000. The application's job is to catch the error and issue ROLLBACK explicitly (see the PHP section).

Savepoints: partial rollback

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

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
SAVEPOINT s1;
UPDATE accounts SET balance = balance - 200 WHERE id = 1;
SELECT balance FROM accounts WHERE id = 1;          -- 700.00
ROLLBACK TO SAVEPOINT s1;
SELECT balance FROM accounts WHERE id = 1;          -- 900.00
COMMIT;

The first SELECT showed 700.00; after ROLLBACK TO SAVEPOINT s1 it showed 900.00 (the first 100 was kept), and COMMIT made that permanent. Savepoints are useful in long batch jobs where one bad item should not undo everything.

ACID

The four guarantees a real transaction system gives you.

Letter	Property	Meaning	In the bank example
A	Atomicity	all statements succeed or none do	both balance changes happen, or neither
C	Consistency	the database moves from one valid state to another; constraints always hold	no account ever goes negative; total money stays constant
I	Isolation	concurrent transactions don't see each other's unfinished work in harmful ways	two simultaneous transfers don't corrupt a balance
D	Durability	once committed, data survives crashes and power loss	after `COMMIT`, a reboot does not lose the transfer

How MySQL achieves them in InnoDB: undo logs record how to reverse changes (atomicity), constraints and foreign keys enforce rules (consistency), locks and multi-version concurrency control (MVCC) manage concurrent access (isolation), and the redo log is written to disk before a commit is acknowledged (durability).

A transfer as a stored procedure

To keep the whole operation on the server and make errors roll back automatically, wrap it in a procedure with an exit handler:

DELIMITER //
CREATE PROCEDURE transfer(IN from_id INT, IN to_id INT, IN amt DECIMAL(10,2))
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;              -- pass the error on to the caller
  END;
  START TRANSACTION;
  UPDATE accounts SET balance = balance - amt WHERE id = from_id;
  UPDATE accounts SET balance = balance + amt WHERE id = to_id;
  INSERT INTO transfers (from_id, to_id, amount) VALUES (from_id, to_id, amt);
  COMMIT;
END //
DELIMITER ;

CALL transfer(1, 2, 300); moved the money (Alice 700, Bob 800) and logged one transfer row. CALL transfer(1, 2, 5000); failed with the check-constraint error, and afterwards the balances were unchanged and the log still had exactly one row. Details of procedures are in the stored procedures post.

Isolation levels

Perfect isolation (transactions run one after another) is safe but slow, so databases offer levels that trade safety for concurrency. The standard describes three unwanted behaviours:

Dirty read: you read data that another transaction has changed but not yet committed (and might roll back).
Non-repeatable read: you read the same row twice in one transaction and get different values, because someone committed a change in between.
Phantom read: you run the same query twice and the second time extra rows appear (or vanish), because someone inserted or deleted matching rows.

Level	Dirty read	Non-repeatable read	Phantom read
`READ UNCOMMITTED`	possible	possible	possible
`READ COMMITTED`	no	possible	possible
`REPEATABLE READ` (MySQL default)	no	no	no for plain reads in InnoDB (possible after a write)
`SERIALIZABLE`	no	no	no

Reading about it is one thing; here is what actually happened with two sessions. Session A opens a transaction, reads Alice's balance, waits three seconds, and reads again. In between, session B updates Alice's balance to 1,111 and inserts a new account, committing each immediately.

Isolation level REPEATABLE READ:

A first read     1000.00
A second read    1000.00     <- same value: A keeps its snapshot
A phantom count  2           <- the new account is not visible

Isolation level READ COMMITTED:

A first read     1000.00
A second read    1111.00     <- B's committed change is visible: non-repeatable read
A phantom count  3           <- and the new row appears: phantom read

Isolation level READ UNCOMMITTED: session B updated the balance to 9,999 but did not commit, and session A (in this mode) read 9999.00. Then B rolled back, and the balance was 1,000.00 again. A had acted on a value that never officially existed: a dirty read.

Set the level for the next transaction or the session:

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

MySQL's InnoDB defaults to REPEATABLE READ (the query above shows REPEATABLE-READ); PostgreSQL, SQL Server and Oracle default to READ COMMITTED. Choose the lowest level that is correct for the work: READ COMMITTED suits many web applications; reports that need a stable view use REPEATABLE READ; SERIALIZABLE suits rare cases where concurrency anomalies cannot be tolerated at all. Never use READ UNCOMMITTED for anything that matters.

Locks

Isolation levels are enforced with locks and snapshots. The tool you use most is the explicit row lock, SELECT ... FOR UPDATE, which reads a row and locks it until the transaction ends. It prevents the classic "lost update", where two transactions read the same balance, both subtract, and one overwrites the other.

I tested it with two sessions. Session A ran SELECT ... WHERE id = 1 FOR UPDATE, slept for three seconds, then subtracted 100. Session B started one second later with the same FOR UPDATE statement:

B waited about two seconds until A committed, then read the new balance of 899.00 (not the old 999.00). B never saw stale data.
With FOR UPDATE NOWAIT, B instead failed at once with ERROR 3572: Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set. SKIP LOCKED is a related option, useful for job queues.

If B had waited too long, MySQL would fail it after innodb_lock_wait_timeout (50 seconds by default). Keep transactions short and do not wait for user input or call external services inside one: every second you hold a lock is a second others wait.

The pattern for a safe update is:

START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;   -- lock the row
-- decide in application code, or just:
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

For simple arithmetic like this, the single statement SET balance = balance - 100 is already atomic. The explicit lock is needed when you must read, then decide, then write.

Deadlocks

A deadlock happens when two transactions each hold a lock the other needs. Neither can continue. In my test, session A updated account 1 and then wanted account 2; session B updated account 2 and then wanted account 1:

-- Session A                       -- Session B
UPDATE ... WHERE id = 1;           UPDATE ... WHERE id = 2;
UPDATE ... WHERE id = 2;  (waits)  UPDATE ... WHERE id = 1;  (waits for A)

InnoDB detected the cycle at once, let A finish, and rolled B back with:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Deadlocks are normal in busy systems, and the correct response is to retry the whole transaction. To make them rarer:

Touch rows in a consistent order. If both transfers always lock the lower account id first, the cycle cannot form.
Keep transactions short and small.
Index the columns used in WHERE, so fewer rows get locked (see indexes).
Use the lowest isolation level that is correct.

SHOW ENGINE INNODB STATUS prints details of the latest deadlock.

Transactions in PHP with PDO

In application code, the pattern is beginTransaction, commit, and rollBack in a catch:

function transfer(PDO $pdo, int $from, int $to, string $amount): void {
    try {
        $pdo->beginTransaction();
        $pdo->prepare('UPDATE accounts SET balance = balance - ? WHERE id = ?')->execute([$amount, $from]);
        $pdo->prepare('UPDATE accounts SET balance = balance + ? WHERE id = ?')->execute([$amount, $to]);
        $pdo->commit();
        echo "transfer of $amount committed\n";
    } catch (Throwable $e) {
        if ($pdo->inTransaction()) { $pdo->rollBack(); }
        echo "rolled back: ", $e->getMessage(), "\n";
    }
}

The connection must use PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, or failed statements won't throw. With Bob at 800 and Alice at 700, transfer($pdo, 2, 1, '100.00') printed transfer of 100.00 committed, and transfer($pdo, 2, 1, '99999.00') printed rolled back: SQLSTATE[HY000]: General error: 3819 Check constraint 'accounts_chk_1' is violated., leaving the balances at 800 (Alice) and 700 (Bob). Always use prepared statements, as above, and DECIMAL (passed as strings) for money, never floating point. In Laravel the same idea is DB::transaction(function () { ... }), which commits on success, rolls back on any exception, and can retry deadlocks: DB::transaction($callback, attempts: 3).

Transactions need the right storage engine

MySQL's older MyISAM engine has no transactions. In a test, I inserted a row into a MyISAM table inside START TRANSACTION ... ROLLBACK, and the row was still there afterwards. Use InnoDB, the default, for any data that matters. Also remember that some statements cause an implicit commit in MySQL (all DDL such as CREATE TABLE, ALTER TABLE, DROP), so don't expect to roll back schema changes the way you roll back data.

Common interview questions

What is a transaction? A group of statements executed as one all-or-nothing unit.
Explain ACID. Atomicity, consistency, isolation, durability (table above).
What is the default isolation level in MySQL? REPEATABLE READ.
What are dirty, non-repeatable and phantom reads? See the examples above.
What is a deadlock and how do you prevent it? A lock cycle; consistent lock order, short transactions, retries.
What is the difference between COMMIT and SAVEPOINT? COMMIT ends the transaction permanently; a savepoint is a marker inside it that you can roll back to.
Does ROLLBACK undo CREATE TABLE? No, DDL commits implicitly in MySQL.

Common mistakes

Using a non-transactional table (MyISAM).
Forgetting to roll back after an error and leaving the connection with an open transaction.
Holding a transaction open while waiting for a user or an API.
Reading a value, deciding in code, then writing it without a lock.
Using floats for money.
Not retrying after a deadlock error.

Next steps

Transactions are part of the senior-level topics in the top 20 SQL topics. Continue with views, stored procedures and functions, joins, and practise on a real shop database in the free MySQL course.