ACID Properties in DBMS: Transactions Explained

A database is used by many people at once, and machines crash. A transaction is the tool that keeps data correct through both: a group of operations that the DBMS treats as one indivisible unit. Moving ₹1,000 from one account to another is two updates, and a world in which only one of them happened is a world where money vanished or appeared. This note defines transactions and their states, works each ACID property on that transfer, and explains how a DBMS actually delivers each one: logs, recovery, locking, and write-ahead logging.

What a transaction is

A transaction is a sequence of operations on the database that forms one logical unit of work. In theory courses it is written with two operations: read(X) copies item X from the database into a local variable, and write(X) copies the local variable back. The transfer T1 of ₹1,000 from account A (₹5,000) to account B (₹3,000) takes seven steps:

1. T1 moves ₹1,000 from A (₹5,000) to B (₹3,000). read copies an item from the database into T1's own variable, and write copies the variable back.
2. read(A) copies A = 5000 into T1's variable. Nothing in the database changes.
3. A := A − 1000 changes only T1's own copy, now A = 4000. The database still holds A = 5000.
4. write(A) puts 4000 into the database while B still holds 3000: the database now shows a total of 7000, ₹1,000 short, until write(B) runs.
5. read(B) copies B = 3000 into T1's variable. Nothing in the database changes.
6. B := B + 1000 changes only T1's own copy, now B = 4000. The database still holds B = 3000.
7. write(B) stores 4000. The database is consistent again, A + B = 8000, but T1 has not committed yet, so a crash now would still roll it back.
8. Commit: both writes are in, A = 4000 and B = 4000, and the total is 8000 again. Every ACID property exists so that nobody acts on the state between write(A) and write(B).

Every ACID property is about making sure nobody, including a crash, ever acts on the ₹7,000 state between write(A) and write(B).

Transaction states

1. A transaction runs in the active state. When its last statement has run it is partially committed, its changes possibly still only in memory, and it is committed once its commit record reaches stable storage.
2. Any error, deadlock or crash sends an active transaction to failed, and so can a failed log write after the last statement. Rolling back its changes makes it aborted; the system then restarts it or kills it.

After an abort the system either restarts the transaction (if the failure was not its own fault, such as a deadlock) or kills it (if its logic was wrong). Some textbooks add a final Terminated state reached after commit or abort.

Atomicity

All or nothing. Either every operation of the transaction is reflected in the database, or none is.

Suppose the server crashes just after write(A). Without atomicity the database would restart with A = 4000 and B = 3000, and ₹1,000 would be gone. With it, the recovery manager finds that T1 never committed and undoes its write, restoring A to 5000.

How: before changing a value, the DBMS logs the old value. Rollback and crash recovery replay these undo records backwards. MySQL's InnoDB keeps them in undo logs (which also serve its multi-version reads).

Consistency

Valid state to valid state. If the database satisfies its rules before the transaction, it satisfies them afterwards. For the transfer, the rule is that A + B is the same before and after (₹8,000), and that no balance goes negative.

How: the DBMS enforces declared constraints (primary and foreign keys, NOT NULL, CHECK) and rejects a statement that would break one. Rules it was never told, such as "transfers conserve money", are kept by the transaction's own logic. Consistency is therefore the one property shared with the application; atomicity and isolation make sure that correct logic stays correct despite crashes and concurrency.

Isolation

Concurrent transactions do not see each other's partial work. The result of running transactions concurrently must equal the result of running them one after another in some order.

Suppose T2 computes the bank's total, A + B, while T1 is half done. The DBMS keeps that total right with concurrency control: locking, or multi-version concurrency control (MVCC), which InnoDB and PostgreSQL use:

1. With no isolation, T2 reads A after T1's write(A) but B before write(B): 7000, a total that never existed in any committed state. This is what isolation forbids.
2. With multi-version concurrency control, T2 reads the committed versions as of its own start, A = 5000 and B = 3000, even though T1 has already written a newer A. The total is 8000, and nobody waited.
3. With locking, T1 holds an exclusive lock on A from write(A) until it commits, so T2's read(A) waits and T2 runs after the commit. It sees 8000, the state after the transfer, at the cost of waiting.

Full isolation (serializability) costs throughput, so SQL offers weaker isolation levels that allow some anomalies. The concurrency control note covers schedules, locks and isolation levels.

Durability

Committed means permanent. Once the user is told the transfer succeeded, it survives a power cut, a crash or a restart.

Suppose the server crashes a moment after T1 commits, before the changed pages have been written from memory to disk. On restart, the recovery manager finds T1's commit record in the log and redoes its writes, so A = 4000 and B = 4000.

How: the commit is acknowledged only after the transaction's log records are forced to stable storage. In InnoDB this is the redo log, flushed at every commit by default (innodb_flush_log_at_trx_commit = 1); the settings 0 and 2 trade up to about a second of committed transactions on a crash for speed. Durability on one machine does not protect against losing the disk itself; that needs replicas and backups.

COMMIT, ROLLBACK and SAVEPOINT

START TRANSACTION;
UPDATE accounts SET balance = balance - 1000 WHERE id = 'A';
SAVEPOINT after_debit;
UPDATE accounts SET balance = balance + 1000 WHERE id = 'B';
ROLLBACK TO SAVEPOINT after_debit;   -- undoes only the credit to B
COMMIT;                              -- A = 4000, B = 3000

COMMIT makes the changes permanent. ROLLBACK undoes everything since START TRANSACTION. SAVEPOINT name marks a point inside the transaction, and ROLLBACK TO SAVEPOINT name undoes only the work after it while the transaction stays open. The example deliberately commits half a transfer to show that a savepoint gives you partial rollback, and that keeping the result consistent is your job.

Three MySQL behaviours worth knowing:

• Autocommit is on by default, so each statement outside START TRANSACTION is a transaction of its own.
• A failing statement does not abort the transaction. If the second UPDATE breaks a CHECK constraint, InnoDB undoes that statement only; the first stays in place until you ROLLBACK or COMMIT. Applications must roll back on error themselves. A deadlock, by contrast, rolls back the whole transaction.
• DDL commits implicitly, so a CREATE TABLE or TRUNCATE inside a transaction commits what came before it.

Write-ahead logging and recovery

The log is a sequential file of records describing every change. T1 writes four: <T1 start>, then <T1, A, 5000, 4000> and <T1, B, 3000, 4000> (transaction, item, old value, new value), then <T1 commit>.

Write-ahead logging (WAL) has two rules:

1. Before a changed data page is written to disk, the log records for its changes must be on stable storage. This keeps undo possible.
2. A transaction is committed only when all its log records, up to and including the commit record, are on stable storage. This keeps redo possible.

Writing the log is cheap because it is sequential, so the DBMS can keep data pages in memory and write them back lazily. Real systems allow uncommitted changes to reach disk (called steal) and do not force pages to disk at commit (no-force), so recovery needs both undo and redo:

1. T1's log: a start record, one record per write holding the item's old and new value, and a commit record. Write-ahead logging puts each record on stable storage before the data page it describes.
2. Crash after the A record. The page holding A = 4000 had already reached disk, but there is no commit record, so recovery must undo T1, putting the old value 5000 back from the log.
3. Crash after the B record: both new values may be on disk, and still no commit record. Recovery undoes in reverse log order, B first and then A, restoring A = 5000 and B = 3000.
4. Crash after the commit record, before either data page was written. T1 committed, so recovery must redo it from the log's new values: A = 4000, B = 4000. That is durability.

Recovery redoes committed transactions and undoes uncommitted ones. To avoid scanning the whole log, the DBMS writes periodic checkpoints recording which transactions were active and which pages were dirty, and recovery starts from the last one. ARIES, published at IBM in 1992, is the standard recovery algorithm built on these ideas, with analysis, redo and undo passes. Older textbooks also describe deferred update (nothing reaches the database before commit, so recovery needs only redo) and immediate update (changes may reach it early, so recovery needs undo and redo).

Shadow paging

Shadow paging achieves atomicity and durability without a log of changes. The database is a set of pages found through a page table. When a transaction starts, the current page table is copied; the original becomes the shadow page table and is never modified. Each page the transaction writes is copied to a new location, and only the new table points to it.

1. While T runs, each page it writes is copied to a new place (P2', P4') and only the current table points at the copies. The db root still names the shadow table, which is never modified.
2. Commit: flush the new pages and the current table, then switch the db root to the current table in one atomic write. The old P2 and P4 are now garbage to collect.
3. A crash or abort before the switch: the db root still names the shadow table, which describes the old, consistent database. The copies are simply discarded; nothing needs undoing or redoing.

It makes recovery trivial, but it scatters related pages across the disk, leaves old pages to garbage-collect, makes every commit write a page table, and is hard to combine with concurrent transactions. That is why mainstream relational systems use WAL; the copy-on-write idea survives in some storage engines and file systems.

Common mistakes

• Saying the DBMS alone guarantees consistency: it enforces declared constraints; business invariants are the transaction's job.
• Assuming an error inside a MySQL transaction rolls it back: only the failing statement is undone.
• Equating "committed" with "written to the data files": it means the log is on stable storage.
• Thinking isolation means transactions run one at a time: they run concurrently, with results equivalent to some serial order.
• Treating durability as backup: it covers crashes, not a destroyed disk.

Interview questions

Explain ACID with an example. Use a transfer of ₹1,000 from A to B. Atomicity: both updates or neither. Consistency: the total is conserved and no balance goes negative. Isolation: a concurrent report never sees money in flight. Durability: once confirmed, the transfer survives a crash.

Which component of a DBMS provides each property? The recovery manager, through the log, provides atomicity and durability. The concurrency control manager provides isolation. Integrity constraints and the application's transaction logic provide consistency.

What are the states of a transaction? Active, partially committed, committed, failed and aborted, with terminated in some books. A transaction can fail from the active or partially committed state, and an aborted one is either restarted or killed.

What is a savepoint? A named point inside a transaction. ROLLBACK TO SAVEPOINT undoes the work done after it without ending the transaction, which is useful for retrying one step of a long transaction.

Why does a DBMS need both undo and redo during recovery? Because the buffer manager may write uncommitted changes to disk (so they must be undone) and need not write committed changes before acknowledging the commit (so they must be redone). Undo restores atomicity; redo restores durability.

What is a checkpoint? A point at which the DBMS records the active transactions and flushes or notes the dirty pages, so recovery can start from it rather than from the beginning of the log. It bounds restart time.

What is the difference between WAL and shadow paging? WAL updates pages in place and logs every change so it can undo and redo. Shadow paging never overwrites a page, writing copies and switching a page-table pointer at commit; it needs no undo or redo but fragments data and handles concurrency poorly.

Does autocommit mean every statement is durable? Yes, with the default log-flush setting: under autocommit each statement commits on its own, so its changes are durable once it returns. It also means a multi-statement change has no atomicity unless you wrap it in START TRANSACTION and COMMIT.

Next, what happens when many transactions run at once: Concurrency Control in DBMS. Transactions and isolation appear in the SQL (Intermediate) skill test.