Transactions and ACID Properties

Modern applications rarely interact with a database through a single, isolated SQL statement. Transferring money between bank accounts, placing an online order, or registering a new user typically require several related changes to the database that must either all succeed or all fail together. A database transaction is the mechanism that makes this possible. It groups one or more SQL statements into a single logical unit of work, treating them as an indivisible whole. The database guarantees that the combined effect of those statements is handled safely, correctly, and permanently — even in the presence of concurrent users, application bugs, or hardware failures. The formal framework that defines what "safely and correctly" means is known as ACID, an acronym standing for Atomicity, Consistency, Isolation, and Durability. Together these four properties are the bedrock of reliable data management in relational database systems.

A transaction begins either explicitly, when the application issues a statement such as BEGIN or START TRANSACTION, or implicitly, when the database automatically wraps a single statement in its own transaction if no explicit transaction is open. Once open, every subsequent SQL statement becomes part of the transaction until one of two outcomes occurs. A COMMIT instruction signals that all changes made during the transaction should be made permanent and visible to the rest of the system. A ROLLBACK instruction signals that something has gone wrong — or that the application has deliberately decided to abort — and every change made during the transaction should be undone, returning the database to exactly the state it was in before the transaction started. The following example illustrates the basic structure:

-- Begin an explicit transaction
BEGIN;

UPDATE accounts SET balance = balance - 500 WHERE account_id = 101;
UPDATE accounts SET balance = balance + 500 WHERE account_id = 202;

-- If both updates succeed, make the changes permanent
COMMIT;

-- If an error occurred instead, undo everything
-- ROLLBACK;

In this bank-transfer scenario, deducting funds from one account and crediting another must both succeed or neither should take effect. If the system crashes or an error occurs between the two UPDATE statements, the transaction framework ensures money is not simply destroyed. The ACID properties define exactly how the database delivers this guarantee.

Atomicity: All or Nothing

Atomicity is the property that makes a transaction indivisible. The word comes from the Greek atomos, meaning "uncuttable." No matter how many statements a transaction contains, the database treats the entire group as a single atomic operation: either every statement succeeds and all changes are committed, or — if any single statement fails for any reason — the entire transaction is automatically rolled back and the database is left exactly as it was before the transaction began.

Consider what happens without atomicity. In the bank-transfer example, if the debit from account 101 succeeds but a network error prevents the credit to account 202, the database would be left in a state where $500 has simply vanished. The customer loses money, the bank's books no longer balance, and there is no clean way to know from the data alone what went wrong. Atomicity prevents this class of partial update corruption entirely.

Database engines implement atomicity using a transaction log (also called a write-ahead log or redo/undo log). Before any data page is actually modified on disk, the intended change is recorded in the log. If the transaction is rolled back — whether by application request or by a crash — the database engine reads the log entries in reverse and applies their undo records to reverse every modification. The data pages are restored to their pre-transaction state as if the transaction never ran.

BEGIN;

INSERT INTO orders (order_id, customer_id, total) VALUES (9001, 42, 250.00);
INSERT INTO order_items (order_id, product_id, qty) VALUES (9001, 7, 2);

-- Simulate a constraint violation on the second insert
-- The engine rolls back both inserts; order 9001 does not exist in either table
ROLLBACK;

Because of atomicity, the orders row inserted in the first statement is also removed when the rollback fires. There is no orphaned order record with no items, and no orphaned item record referencing a non-existent order.

Consistency: Preserving Valid Data States

Consistency is the property that ensures every transaction takes the database from one valid state to another valid state. "Valid" is defined by the set of rules — constraints, triggers, cascades, and application-level invariants — that have been declared on the database schema. Before a transaction begins the data satisfies all those rules. After a successful commit the data must still satisfy all those rules. A transaction that would violate any rule is rejected; its changes are never stored.

Typical examples of the rules that consistency must preserve include:

• Referential integrity: A foreign key in order_items referencing orders means you can never insert an item for an order that does not exist, and you can never delete an order that still has items (unless a cascade is defined).
• Check constraints: A constraint such as CHECK (balance >= 0) prevents an account balance from going negative.
• Unique constraints: A UNIQUE index on an email column ensures two users cannot share the same address.
• Not-null constraints: Required columns cannot be left empty.
• Application invariants: Business rules enforced in stored procedures or application code, such as "total order value must equal the sum of its line items."

-- Attempting to insert an order_item for a non-existent order
BEGIN;
INSERT INTO order_items (order_id, product_id, qty) VALUES (9999, 7, 1);
-- ERROR: insert or update on table "order_items" violates foreign key constraint
-- The transaction is automatically rolled back; no row is inserted
COMMIT;

It is important to understand that consistency is a shared responsibility. The database engine enforces the constraints declared in the schema, but the application developer must correctly identify and declare those constraints in the first place, and must also implement any business-logic rules that the database engine cannot express natively. A database can only protect the invariants it knows about.

Isolation: Shielding Concurrent Transactions

In any real system dozens, hundreds, or thousands of transactions may be running at the same time. Without isolation, those concurrent transactions could interfere with each other in ways that produce wrong answers even when every individual transaction is internally correct. Isolation is the property that makes concurrent transactions appear, from each transaction's point of view, as if they are the only transaction running — as though they execute one at a time, in some serial order.

The classic anomalies that arise from insufficient isolation are:

• Dirty read: Transaction A reads data that Transaction B has modified but not yet committed. If B then rolls back, A has acted on data that never officially existed.
• Non-repeatable read: Transaction A reads a row, Transaction B commits a change to that row, and then Transaction A reads the same row again within the same transaction and gets a different value.
• Phantom read: Transaction A executes a range query (e.g., SELECT ... WHERE age > 30), Transaction B inserts or deletes rows that fall in that range and commits, and then Transaction A re-executes the same query and gets a different set of rows.
• Lost update: Two transactions both read a value, compute a new value based on it, and write back — the second write overwrites the first, and one update is silently discarded.

Database systems prevent these anomalies through two main mechanisms. Lock-based concurrency control has transactions acquire shared or exclusive locks on the data they access, making other transactions wait until the lock is released. Multi-Version Concurrency Control (MVCC), used by PostgreSQL, MySQL/InnoDB, and Oracle, maintains multiple timestamped versions of each row so that readers can see a consistent snapshot of the data as it existed at the start of their transaction without blocking writers, and writers do not block readers.

Because full serial isolation carries a performance cost, the SQL standard defines four isolation levels that let designers trade off between strict correctness and throughput:

Read Committed is the default in many systems (PostgreSQL, Oracle, SQL Server) and prevents dirty reads while permitting non-repeatable and phantom reads. Serializable is the strictest level and provides the full illusion of serial execution, at the cost of more blocking or more transaction retries. The appropriate level depends on the application's tolerance for anomalies versus its need for concurrency and speed.

-- Set isolation level before starting a transaction (PostgreSQL syntax)
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT balance FROM accounts WHERE account_id = 101;
-- ... perform calculations ...
SELECT balance FROM accounts WHERE account_id = 101;
-- Guaranteed to return the same value even if another session changed it
COMMIT;

Durability: Surviving System Failures

Durability is the property that guarantees once a transaction has been committed, its effects are permanent. Even if the database server loses power one millisecond after issuing the commit acknowledgment, the data will still be there when the system restarts. This guarantee is what allows applications and users to trust a "success" response without worrying that the data is actually sitting in a volatile memory buffer that will evaporate in a power cut.

Durability is implemented primarily through the write-ahead log (WAL). The principle of the WAL is simple but powerful: every change is first written to the durable, append-only transaction log on disk before the actual data pages are modified. When a COMMIT is issued, the engine ensures the log record for that commit has been flushed to non-volatile storage (a physical disk write, not just an OS buffer). Only after that flush does the commit acknowledgment go back to the client.

If the server crashes after the flush but before the data pages themselves are updated, the recovery process on restart reads the WAL and replays (redoes) the committed log records, applying the changes to the data pages. Because the log is on durable storage, no committed data is ever lost. Uncommitted transactions whose log records did not include a commit marker are rolled back using the undo portions of their log entries.

• In PostgreSQL, the WAL directory stores log segments that are also used for replication and point-in-time recovery.
• In MySQL/InnoDB, the redo log and doublewrite buffer together guarantee durability.
• In high-availability setups, log shipping or synchronous replication to a standby server extends durability beyond a single machine.

Durability does carry a performance cost — the requirement to flush to disk on every commit is slower than simply writing to memory. Database administrators can sometimes tune this behavior (for example, with PostgreSQL's synchronous_commit setting), but relaxing durability guarantees must be a deliberate, well-understood decision because it introduces the possibility of losing recently committed data.

How ACID Properties Work Together

The four ACID properties are not independent features bolted onto a database — they form an interlocking system in which each property supports the others:

• Atomicity and Consistency are natural partners. Atomicity guarantees that either all of a transaction's changes are applied or none are. Consistency guarantees that whichever outcome occurs, the database remains in a rule-abiding state. Without atomicity, partial changes could violate consistency rules. Without consistency rules, atomicity would faithfully commit corrupt data.
• Isolation protects in-flight consistency. While a transaction is in progress it may temporarily put the database into an intermediate state that violates an invariant — for example, money has been debited from one account but not yet credited to another. Isolation ensures no other transaction can observe that intermediate state. Other transactions see either the complete before-picture or the complete after-picture, never a half-finished one.
• Durability seals the guarantee. Once Atomicity, Consistency, and Isolation have ensured that a completed transaction represents a valid, fully applied set of changes, Durability ensures those changes survive. The work done by the other three properties is not wasted by a system failure at the last moment.

From a practical standpoint, ACID compliance is a critical criterion when selecting a database system. Traditional relational databases — PostgreSQL, MySQL with InnoDB, Oracle, Microsoft SQL Server — are fully ACID-compliant by design. Some NoSQL systems, built for extreme horizontal scale, relax one or more ACID properties (trading consistency or durability for availability and speed), a trade-off formalized in the CAP theorem and BASE model. Understanding ACID allows architects and developers to reason clearly about which guarantees a system provides, what risks exist if those guarantees are weakened, and whether a given database engine is appropriate for the reliability requirements of the application being built.