Modules/Database/ Transaction Lab

Transactions and ACID

Databases & SQLBy Ashish JangraUpdated 26 October 2026

A transfer is two writes that must succeed or fail together. Step it through, with a second session watching, and fail it on purpose.

Session A: Transfer $30

Fail after the second UPDATE

Press Step to BEGIN the transaction.

Session B's Isolation

what B is allowed to see while A is mid-transaction

Two Sessions, One Table

—

Session A sees

Session B sees

Session A's SQL, run so far

ACID, As It Happens

Overview

Atomicity means a transaction's changes are committed together or rolled back together. Consistency means committed changes preserve the database rules and application invariants that are actually enforced. Isolation defines how concurrent transactions interact. Durability means committed changes survive failures according to the database's configuration and storage guarantees.

What ACID promises

ACID does not automatically encode every business rule. Constraints, transaction boundaries, isolation, and application logic all contribute. See constraints and isolation levels.

A transfer example

A transfer needs to debit one account and credit another as one unit:

query.sqlSQLite

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

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

Result

If either update fails or an invariant is violated, the application should roll back rather than commit a partial transfer. A production implementation also checks affected-row counts, prevents overdrafts with a predicate or constraint, and handles concurrency conflicts.

Commit, rollback, and durability

Use the transaction API for the target database and ensure every error path commits or rolls back deliberately. Durability can depend on configuration, replication mode, and storage guarantees; “COMMIT returned” should be interpreted using the engine's documented settings.

Transactions should be short. Long transactions can hold locks or snapshots and interfere with other work. A deadlock or serialization failure may abort the transaction; retry the complete operation when it is safe, not just the final statement. See deadlocks.

Check invariants inside the transaction

A transfer should validate that the debit actually updated one row and that the balance cannot go below the allowed limit. Make those conditions part of the UPDATE predicate or a database constraint, then inspect the affected-row count before committing. If the credit fails, roll back the debit. Isolation matters when two withdrawals race for the same balance. An atomic conditional update or suitable lock can protect the invariant; a read followed by an unconditional write can lose updates.

Make retries idempotent

A client may lose its connection after the database commits but before it receives confirmation. Retrying the request can then apply the action twice unless it is idempotent. Use a request or transaction identifier with a uniqueness constraint to recognize duplicates, or design the operation so repeating it has the same final effect. This is especially important for payments, inventory reservations, and external message delivery. ACID protects database transactions; it does not make a network request exactly-once across systems.