SQL Transactions: COMMIT, ROLLBACK and ACID Explained

Transferring 500 from account 1 to account 2 takes two UPDATE statements. If the server fails after the first one, account 1 has lost 500 and account 2 never received it. A transaction removes that risk:

BEGIN TRANSACTION;

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

COMMIT;

Until COMMIT, the changes are provisional. If anything goes wrong, a ROLLBACK (or a crash) returns both accounts to where they were. Other sessions never see the half-finished state with the money missing.

You can see the two possible end states with a SELECT on sample balances. This runs in the Playground; the three accounts are written into the query:

WITH accounts(account_id, owner, balance) AS (
  VALUES (1, 'Neha', 2000), (2, 'Vikram', 1500), (3, 'Aditi', 800)
)
SELECT account_id, owner,
       balance AS before,
       balance + CASE account_id WHEN 1 THEN -500 WHEN 2 THEN 500 ELSE 0 END AS after_commit,
       balance AS after_rollback
FROM accounts;

▶ Run this query in the SQL Playground

The total across the accounts is the same before and after the commit. Keeping rules like that true, even when something fails halfway, is what transactions are for.

In auto-commit mode every single statement is committed as soon as it finishes. That is why a two-statement transfer needs an explicit BEGIN.

ROLLBACK on error

BEGIN;

UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;

-- the application checks the result
-- (e.g. the balance went negative, or the second account does not exist)

ROLLBACK;   -- account 1 is back to its original balance

BEGIN TRY
    BEGIN TRANSACTION;
    UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
    UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;
    COMMIT;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;
    THROW;
END CATCH;

A savepoint lets you undo part of a transaction. Here an order is created, the optional gift wrap step fails, and only that step is undone:

BEGIN;

INSERT INTO orders (id, user_id, total, status) VALUES (501, 4, 120.00, 'pending');
INSERT INTO order_items (order_id, product_id, quantity, unit_price, subtotal)
     VALUES (501, 17, 2, 60.00, 120.00);

SAVEPOINT before_gift_wrap;
UPDATE orders SET total = total + 5 WHERE id = 501;   -- gift wrap fee
-- the gift wrap service is unavailable:
ROLLBACK TO SAVEPOINT before_gift_wrap;

COMMIT;   -- the order and its item are saved, without the fee

Savepoints work in SQLite, PostgreSQL, MySQL and Oracle. SQL Server writes SAVE TRANSACTION name and ROLLBACK TRANSACTION name.

Durability is usually built on a write-ahead log: changes are written to a log file before the data files. After a crash the database replays committed work from the log and discards what was not committed.

Consistency rules a transaction protects

Many rules span several rows, which is exactly why multi-statement changes need a transaction. One such rule in the Playground: each order line's subtotal equals its quantity times its unit price. Any code that changes a quantity must change the subtotal in the same transaction. Checking it:

SELECT COUNT(*) AS order_lines,
       SUM(ABS(subtotal - quantity * unit_price) < 0.01) AS lines_consistent
FROM order_items;

▶ Run this query in the SQL Playground

All 496 lines pass. A query like this is a useful regular check on real data: if it ever finds a mismatch, some code updated one column without the other.

Full isolation is expensive, so databases let you choose how much two concurrent transactions may affect each other. Weaker levels allow these anomalies:

Defaults differ: READ COMMITTED in PostgreSQL, SQL Server and Oracle; REPEATABLE READ in MySQL InnoDB; SERIALIZABLE in SQLite, which allows only one writer at a time. Set the level per transaction:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;   -- PostgreSQL (inside the transaction), SQL Server, MySQL (before it)
BEGIN;
...
COMMIT;

PostgreSQL, Oracle and MySQL implement isolation with MVCC (multi-version concurrency control): writers create new row versions, and readers see a consistent snapshot without waiting for locks.

Preventing a lost update

Check-then-update logic, such as "is there enough balance? then debit", is unsafe if another transaction can change the balance between the two steps. Lock the row when you read it, or let the UPDATE itself check the condition:

-- PostgreSQL, MySQL, Oracle
BEGIN;
SELECT balance FROM accounts WHERE account_id = 1 FOR UPDATE;  -- row is locked
-- application checks the balance
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
COMMIT;

-- or in one statement: only debits if the money is there
UPDATE accounts
SET balance = balance - 500
WHERE account_id = 1 AND balance >= 500;   -- check the affected row count

Deadlocks

A deadlock happens when transaction A holds a lock B needs, while B holds a lock A needs. Neither can continue. The database detects the cycle and rolls one of them back (the "victim"), which then gets an error and should be retried.

-- Transaction A                          -- Transaction B
UPDATE accounts ... WHERE account_id = 1;  UPDATE accounts ... WHERE account_id = 2;
UPDATE accounts ... WHERE account_id = 2;  UPDATE accounts ... WHERE account_id = 1;
-- A waits for B's lock on 2,             -- B waits for A's lock on 1: deadlock

To avoid them: touch rows and tables in the same order in every transaction (for example lowest account_id first), keep transactions short, and index the columns in WHERE so fewer rows are locked.

When one unit of work spans two databases, for example an order database and a separate billing database, a single COMMIT is not enough. Two-phase commit (2PC) coordinates it:

2PC is slow and blocks if the coordinator fails at the wrong moment, so many modern systems avoid it. Instead they use a sequence of local transactions with compensating transactions to undo earlier steps if a later one fails, for example issuing a refund rather than un-charging a card.

Six errors and how to avoid them.

UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
-- crash here: the debit is already committed
UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;

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

BEGIN;
UPDATE orders SET status = 'shipped' WHERE id = 42;
-- session left open

BEGIN;
UPDATE orders SET status = 'shipped' WHERE id = 42;
COMMIT;

BEGIN;
SELECT ... FOR UPDATE;
-- wait for the user to confirm on screen
UPDATE ...;
COMMIT;

-- collect the confirmation first
BEGIN;
UPDATE ... WHERE ... AND version = 7;  -- detect concurrent changes
COMMIT;

BEGIN;
INSERT INTO orders ...;
-- application sends "order confirmed" email
UPDATE stock ...;   -- fails, ROLLBACK

BEGIN;
INSERT INTO orders ...;
UPDATE stock ...;
COMMIT;
-- now send the email

-- one code path updates account 2 then 1,
-- another updates 1 then 2

-- always lock the lower account_id first

SELECT balance FROM accounts WHERE account_id = 1;  -- 600
-- another session withdraws 400 here
UPDATE accounts SET balance = 600 - 500 WHERE account_id = 1;

UPDATE accounts
SET balance = balance - 500
WHERE account_id = 1 AND balance >= 500;

4 exercises on the real Playground tables.

Write each query yourself first. Every solution has been run against the Playground database.

1. 1Easy Find any order lines whose subtotal does not equal quantity times unit price (the rule a transaction should protect). How many are there? Table: order_items

   Show solution

   SQLite · runs in the Playground

   SELECT COUNT(*) AS inconsistent_lines
   FROM order_items
   WHERE ABS(subtotal - quantity * unit_price) >= 0.01;

   ▶ Run this query in the SQL Playground

   The answer is 0, which is what you want from such a check. ABS with a small tolerance avoids false alarms from rounding.

2. 2Medium Using the sample accounts, show which accounts could afford a 900 transfer out, using the same condition a safe UPDATE would use. Table: accounts (sample)

   Show solution

   SQLite · runs in the Playground

   WITH accounts(account_id, owner, balance) AS (
     VALUES (1, 'Neha', 2000), (2, 'Vikram', 1500), (3, 'Aditi', 800)
   )
   SELECT account_id, owner, balance,
          CASE WHEN balance >= 900 THEN 'debit allowed' ELSE 'rejected' END AS outcome
   FROM accounts;

   ▶ Run this query in the SQL Playground

   UPDATE ... WHERE account_id = ? AND balance >= 900 would change one row for Neha and Vikram and zero rows for Aditi.

3. 3Medium Check that every order has at least one order line, which an order-creation transaction should guarantee. Table: orders, order_items

   Show solution

   SQLite · runs in the Playground

   SELECT COUNT(*) AS orders_without_lines
   FROM orders o
   WHERE NOT EXISTS (SELECT 1
                     FROM order_items oi
                     WHERE oi.order_id = o.id);

   ▶ Run this query in the SQL Playground

   Creating the order and its lines in one transaction means an order can never exist without lines.

4. 4Medium Using the sample accounts, confirm that a 500 transfer from account 1 to 2 keeps the total balance unchanged. Table: accounts (sample)

   Show solution

   SQLite · runs in the Playground

   WITH accounts(account_id, balance) AS (
     VALUES (1, 2000), (2, 1500), (3, 800)
   )
   SELECT SUM(balance) AS total_before,
          SUM(balance + CASE account_id WHEN 1 THEN -500 WHEN 2 THEN 500 ELSE 0 END) AS total_after
   FROM accounts;

   ▶ Run this query in the SQL Playground

   Atomicity is what keeps this invariant true: either both changes happen or neither.