ACID Properties

ACID Properties in DBMS

A transaction is a single logical unit of work which accesses and possibly modifies the contents of a database. Transactions access data by performing read and write operations. To maintain the correctness of the database, each transaction must preserve certain properties before and after its execution. These are collectively known as the ACID properties.

Overview of ACID

• Atomicity - either all operations of the transaction are performed, or none are.
• Consistency - a transaction takes the database from one valid state to another, preserving integrity constraints.
• Isolation - concurrently executing transactions do not interfere with each other; the combined effect is equivalent to some serial execution.
• Durability - once a transaction commits, its effects are permanent and survive subsequent failures.

1. Atomicity

Atomicity means a transaction is indivisible: it either completes fully (commits) or has no effect at all (aborts and rolls back). There is no partial completion visible to other transactions or to the database.

• Abort: If a transaction aborts, all changes it made are undone and are not visible to others.
• Commit: If a transaction commits, all its changes become visible and permanent (subject to durability mechanisms).

Atomicity is often described as the "all-or-nothing" rule.

Example (simple money transfer): Consider a transaction T that transfers 100 from account X to account Y. If T is split into two operations T1: debit X and T2: credit Y, and the system fails after T1 but before T2 (for example, after write(X) but before write(Y)), the database will be left inconsistent unless atomicity is enforced. Either both T1 and T2 must appear to have happened, or none.

Common implementation techniques that enforce atomicity:

• Write-Ahead Logging (WAL): record changes in a log before applying them to the database so they can be undone or redone.
• Undo/Redo mechanisms: rollback uncommitted changes on abort (undo), and reapply committed changes after a crash (redo).
• Transaction states: active → partially committed → committed or aborted; recovery procedures ensure atomic behaviour across failures.

2. Consistency

Consistency requires that a transaction, when executed in isolation on a consistent database, will produce another consistent database by preserving all declared integrity constraints (primary key, foreign key, domain constraints, and application-specific rules).

Consistency is a property of the combined DBMS and transaction logic: the DBMS provides mechanisms (constraints, triggers) while the transaction's code must be correct to maintain invariants.

Example (from the transfer): Suppose before the transfer the balances are X = 500 and Y = 200, so total = 700. After a correct transfer of 100 from X to Y, balances should be X = 400 and Y = 300, total = 700. If a failure causes only the debit to be written, total will no longer match the invariant, producing an inconsistent state.

• Integrity constraints are checked before and/or after transactions to enforce consistency.
• The responsibility for keeping application-level invariants lies with transaction logic; the DBMS helps enforce schema-level constraints.

3. Isolation

Isolation requires that concurrent transactions do not interfere and that the final state is the same as some serial execution of those transactions. Isolation prevents anomalies produced by interleaved operations.

Common concurrency anomalies:

• Dirty read: a transaction reads uncommitted changes made by another transaction.
• Non-repeatable read: a transaction reads the same item twice and gets different values because another transaction updated it in between.
• Phantom read: a transaction re-executes a query and finds additional rows inserted by another transaction.

Example (interleaving causing inconsistency): Let X = 50,000 and Y = 500. Transaction T transfers 50 from X to Y. If T performs write(X) (debit) and then is paused, and another transaction T'' reads both X and Y, it may see the updated X but the old Y. If T'' computes the sum X+Y it will get a value 50 less than the correct total, causing an inconsistency visible to the user.

Concurrency control mechanisms that provide isolation:

• Locking: shared (S) locks for reads and exclusive (X) locks for writes; transactions acquire and release locks to protect data items.
• Two-Phase Locking (2PL): a protocol where a transaction first acquires all needed locks (growing phase) and then releases them (shrinking phase); strict 2PL delays releasing write locks until commit, preventing dirty reads and ensuring recoverability.
• Timestamp ordering: assigns timestamps to transactions and orders conflicting operations by timestamp to achieve serialisability.
• Optimistic concurrency control: transactions execute without restrictions and are validated at commit time.

Serialisability is the correctness criterion: a concurrent schedule is serialisable if its outcome is equivalent to some serial execution of the same transactions. Techniques to test or achieve serialisability include conflict-serialisability (conflict graphs) and view-serialisability.

Isolation Levels (practical settings)

Commercial DBMSs offer several isolation levels trading off strictness and performance. Typical levels are:

• Read Uncommitted: allows dirty reads; weakest isolation.
• Read Committed: prevents dirty reads but allows non-repeatable reads and phantoms.
• Repeatable Read: prevents dirty and non-repeatable reads but may allow phantoms (implementation dependent).
• Serializable: strongest level; guarantees serialisability.

Choosing an isolation level is an engineering trade-off between throughput and correctness guarantees required by the application.

4. Durability

Durability ensures that once a transaction has committed, its effects persist permanently, even in case of system crashes, power failures, or other faults. Committed updates are stored in non-volatile storage and will be available after recovery.

• Log-based recovery: WAL requires that log records describing modifications reach stable storage before the corresponding data pages are written to disk.
• Checkpoints: periodically write a snapshot and log position to reduce recovery time after a crash.
• Shadow paging and stable storage: alternative approaches that ensure durability without redo/undo logs (used less commonly in large systems).

Durability together with atomicity allows the system to recover to a state in which the effects of committed transactions are present and effects of uncommitted transactions are absent.

Recoverability and Related Concepts

Recoverability concerns whether a schedule allows recovery without violating atomicity or consistency. Important definitions:

• Recoverable schedule: if whenever a transaction Tj reads a value previously written by Ti, then the commit of Ti occurs before the commit of Tj. This prevents Tj from committing based on Ti's uncommitted data.
• Avoiding Cascading Aborts (ACA): schedules that prevent transactions from reading uncommitted data, so aborting one transaction does not force many rollbacks.
• Strict schedule: a stronger requirement where transactions cannot read or write an item modified by an uncommitted transaction; strict schedules simplify recovery.

Distributed Transactions and Atomic Commit

When a transaction spans multiple nodes or databases, atomicity is ensured by an atomic commit protocol, the common one being the Two-Phase Commit (2PC). 2PC has a coordinator and participants:

• Phase 1 (prepare): the coordinator asks participants to prepare to commit; each participant replies yes/no.
• Phase 2 (commit/abort): if all participants vote yes, the coordinator instructs commit; otherwise it instructs abort. Participants follow the instruction and acknowledge.

2PC ensures atomicity across distributed resources but can block if the coordinator fails; optimisations and non-blocking variants exist for production systems.

Summary and Practical Notes

• ACID provides the foundation for correct transaction processing: Atomicity, Consistency, Isolation, Durability.
• Implementation relies on logs, locking protocols, timestamps, validation, checkpoints and recovery algorithms.
• Understanding anomalies, serialisability, and recovery properties (recoverable schedules, strict schedules) is essential for designing correct concurrent systems.
• In practice, DBMSs allow tuning (isolation levels, concurrency control modes) to balance correctness and performance depending on application needs.