SQL Transactions and ACID Explained: COMMIT, ROLLBACK and Isolation Levels

Moving 300 rupees from Asha to Ben takes two updates: subtract from one account and add to the other. If the system fails between them, money vanishes. SQL transactions exist to make that impossible, by treating both steps as a single unit.

In this guide we run through commits, rollbacks and failures on a small accounts table. We ran those examples in SQLite, and the balances shown come from that run. The basic commands are similar across databases, but locking and isolation behaviour vary a lot, so we point out where to check your own database’s documentation.

What is a transaction?

A transaction is a sequence of one or more SQL statements that the database treats as a single logical operation. Here is our table:

CREATE TABLE accounts (
  id INT PRIMARY KEY,
  owner TEXT,
  balance INT CHECK (balance >= 0)
);

INSERT INTO accounts VALUES (1, 'Asha', 1000), (2, 'Ben', 200);

The CHECK constraint is a rule that a balance can never go below zero, which we will use later. Note that the syntax to begin a transaction varies: SQLite and PostgreSQL accept BEGIN, MySQL also has START TRANSACTION, and SQL Server uses BEGIN TRANSACTION.

Outside an explicit transaction, most databases use autocommit, where each statement is saved as soon as it finishes. Oracle is a notable exception, because a session there stays in a transaction until you commit.

COMMIT: make it permanent

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

-- Asha | 700
-- Ben  | 500

Until COMMIT runs, the changes are provisional. Once it returns successfully, the database guarantees that they are saved. The total across both accounts stayed at 1200, which is the whole point of the exercise.

ROLLBACK: cancel everything

ROLLBACK undoes everything since the transaction began:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
SELECT owner, balance FROM accounts;
-- Asha | 600   (visible inside this transaction)
-- Ben  | 500

ROLLBACK;
SELECT owner, balance FROM accounts;
-- Asha | 700   (back to where it was)
-- Ben  | 500

A rollback also happens automatically if the connection drops or the server crashes before you commit, so a half-finished transaction does not survive.

When a statement fails

Here is a more realistic case. Asha tries to send 5000, which the account does not hold. The credit to Ben runs first, and then the debit breaks the CHECK rule:

BEGIN;
UPDATE accounts SET balance = balance + 5000 WHERE id = 2;
UPDATE accounts SET balance = balance - 5000 WHERE id = 1;
-- error: CHECK constraint failed: balance >= 0

ROLLBACK;

-- Asha | 700
-- Ben  | 500

Without the transaction, Ben would have kept the 5000 and the books would be wrong. Be aware of one important detail: a failing statement does not always cancel the transaction by itself. In SQLite and MySQL, for example, earlier statements can remain pending, so your code has to issue the rollback. PostgreSQL instead marks the whole transaction as aborted and refuses further statements until you roll back.

In application code, always pair the transaction with error handling. Here is the idea in Python, and our guide to Python exception handling explains the syntax:

try:
    conn.execute("BEGIN")
    conn.execute(debit_sql, values)
    conn.execute(credit_sql, values)
    conn.execute("COMMIT")
except Exception:
    conn.execute("ROLLBACK")
    raise

Savepoints

A savepoint marks a position inside a transaction, so you can undo only part of the work:

BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
SAVEPOINT bonus;
UPDATE accounts SET balance = balance + 999 WHERE id = 2;
ROLLBACK TO bonus;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
COMMIT;

-- Asha | 650
-- Ben  | 550

The 999 bonus was undone, while the rest of the transaction carried on and committed. This is useful for optional steps in a long process.

ACID properties explained

Consistency is commonly misunderstood. The database enforces only the rules you declare, such as constraints and foreign keys. It will not stop your code from transferring the wrong amount. Durability is usually achieved with a write-ahead log, where changes are recorded safely on disk before the commit is confirmed.

What happens with concurrent transactions?

We opened two connections to one SQLite file. The first started a transaction and updated a row from 1 to 2, without committing yet. Here is what the second connection saw:

Connection B reads:   1                     -- no uncommitted data
Connection B writes:  database is locked    -- it has to wait
(A commits)
Connection B reads:   2                     -- now it sees the change

SQLite allows only one writer at a time, which is simpler than server databases, where many transactions can write different rows simultaneously. But the principle is the same: other sessions should not see uncommitted changes under normal settings, and conflicting writes have to wait. In our test, we set the second connection to give up immediately rather than wait, so the error appeared at once.

Read anomalies

When transactions overlap, certain problems can occur. The SQL standard defines these in terms of what a transaction may observe:

Another problem is the lost update. Two sessions read a balance of 100, each adds 10 and each writes 110. One update has effectively vanished, when the answer should be 120.

SQL isolation levels

Isolation levels let you trade strictness for concurrency. Here is what the SQL standard allows at each level:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

The exact syntax and behaviour depend on your database. Defaults also differ: PostgreSQL and SQL Server use read committed, while MySQL’s InnoDB uses repeatable read. Some databases give stronger guarantees than the standard requires, or treat one level as another. In PostgreSQL, for example, read uncommitted behaves like read committed. Always read your database’s documentation before relying on a specific behaviour.

Deadlocks

A deadlock happens when two transactions each hold a lock the other one needs. Transaction A locks row 1 and wants row 2, while transaction B locks row 2 and wants row 1. Neither can continue.

Databases such as PostgreSQL, MySQL and SQL Server detect this and cancel one transaction with an error. That is normal behaviour, and your application should be prepared for it. To reduce deadlocks, always update rows in a consistent order, such as lowest account ID first, keep transactions short, and retry when one is chosen as the victim.

Best practices

• Keep transactions short. Long ones hold locks and block other users.
• Never wait for a user or a slow API inside a transaction. Do that work before you begin, or afterwards.
• Always handle failure. Every path should end in a commit or a rollback.
• Add constraints. They give the database real rules to enforce. Good table design helps too.
• Use good indexes. Faster lookups mean that locks are held for less time.
• Be careful with retries. Retry on deadlocks, but make sure that an operation cannot apply twice.
• Test concurrency on purpose. Many of these bugs appear only under load, so basic integration tests alone may not reveal them.

Final thoughts

Transactions turn several risky steps into one safe operation, and ACID describes the guarantees that make this trustworthy. Whenever a business action touches more than one row or table, ask yourself what would happen if it stopped halfway. If the answer worries you, wrap it in a transaction. To practise, open two sessions in a local database and try to produce the anomalies yourself.

Frequently asked questions

Keep learning