ACID Properties in Relational Databases

ACID is an acronym for Atomicity, Consistency, Isolation, and Durability. These properties are a set of guarantees that database management systems (DBMS) provide to ensure that transactions—logical units of work—are processed reliably. A transaction is a sequence of one or more operations (e.g., SQL statements) that should be treated as a single, indivisible unit. The need for ACID arises from real-world scenarios where multiple users concurrently access and modify data, and where system failures (crashes, power outages) can occur at any moment. Without ACID, databases could end up in inconsistent states, losing or corrupting data.

Atomicity is implemented through a mechanism called 'write-ahead logging' (WAL) or 'undo logging'. Before any changes are written to the actual data pages, the DBMS writes a record of the intended changes to a transaction log on durable storage. If a transaction fails or the system crashes before completion, the DBMS uses the log to 'undo' any partial changes, restoring the database to its previous state. For example, in SQL Server, the transaction log records 'before' and 'after' images. If a transaction aborts, SQL Server uses the 'before' image to roll back changes. The default behavior is that every transaction is atomic unless explicitly broken into multiple transactions.

Consistency is enforced through database constraints, such as PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK, and NOT NULL. These constraints are defined in the database schema and are checked automatically at the end of each transaction. If a transaction would violate any constraint, the entire transaction is rolled back. Consistency also includes application-level rules, but the DBMS only guarantees that constraints are not violated. For example, if a CHECK constraint ensures a salary column is > 0, any transaction that tries to set a negative salary will fail. Importantly, the DBMS does not enforce business rules that are not expressed as constraints; those must be handled by application code.

Isolation controls how concurrent transactions interact. The SQL standard defines four isolation levels: Read Uncommitted, Read Committed, Repeatable Read, and Serializable. Each level prevents certain phenomena: dirty reads, non-repeatable reads, and phantom reads. The default isolation level in most relational databases (e.g., SQL Server, PostgreSQL) is Read Committed, which prevents dirty reads but allows non-repeatable reads and phantoms. Isolation is implemented using locks (shared, exclusive, update) or multiversion concurrency control (MVCC). MVCC, used by PostgreSQL and Oracle, creates snapshots of data so that readers never block writers and vice versa. For example, in PostgreSQL, when a transaction updates a row, it creates a new version of the row; other transactions see the old version until the update commits. This allows high concurrency without dirty reads.

Durability ensures that once a transaction commits, its changes persist even if the system crashes immediately after. The primary mechanism is writing the transaction log to non-volatile storage (disk) before acknowledging the commit. This is often called 'write-ahead logging' (WAL) or 'commit log'. The DBMS flushes the log buffer to disk using fsync or similar OS calls. The default is that every commit forces a log flush, which can impact performance. Some databases allow asynchronous commits (e.g., SQL Server's DELAYED_DURABILITY) that trade durability for speed, but the default is full durability. In Azure SQL Database, durability is always guaranteed at the storage level, as data is replicated to multiple nodes.

- SQL Server: READ COMMITTED (with row versioning optionally enabled) - PostgreSQL: READ COMMITTED - MySQL (InnoDB): REPEATABLE READ - Oracle: READ COMMITTED (with MVCC) - Lock Timeouts: Most databases have a default lock timeout, e.g., SQL Server has @@LOCK_TIMEOUT defaulting to -1 (no timeout). However, applications can set a timeout to avoid indefinite waits. - Deadlock Detection: Databases automatically detect deadlocks and choose a victim transaction to abort. In SQL Server, deadlock detection occurs every 5 seconds by default, and the transaction with the least cost is rolled back. - Log Flush Interval: By default, SQL Server flushes the log at commit time. However, for bulk operations, you can use BULK_LOGGED recovery model to minimize logging.

To check the current isolation level in SQL Server:

DBCC USEROPTIONS;

Look for 'isolation level' in the output.

To set isolation level in SQL Server:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

To view transaction log size in SQL Server:

DBCC SQLPERF(LOGSPACE);

To check if a database is using read committed snapshot isolation (RCSI):

SELECT name, is_read_committed_snapshot_on FROM sys.databases;

In PostgreSQL, to check current isolation level:

SHOW transaction_isolation;

To set isolation level:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

ACID properties are central to relational databases but are often relaxed in NoSQL databases (e.g., MongoDB, Cassandra) to achieve higher scalability and availability. NoSQL databases typically offer 'eventual consistency' and may not support multi-document transactions. However, some NoSQL systems have added ACID support for single-document operations. In Azure, Cosmos DB offers multiple consistency levels, including strong consistency which provides ACID-like guarantees within a single partition. For DP-900, understand that relational databases are chosen when ACID is mandatory, while NoSQL is chosen when flexibility and scale are more important.

ACID also interacts with replication and backup. For example, in Always On Availability Groups, transactions are committed on the primary replica and then replicated to secondary replicas. Durability on the secondary depends on the synchronization mode (synchronous vs asynchronous). Synchronous commit ensures the secondary has the log written before acknowledging the primary, providing zero data loss but impacting performance.

When faced with a question about ACID, first identify which property is being described. For example, if the question talks about 'all-or-nothing', it's Atomicity. If it mentions 'concurrent transactions not interfering', it's Isolation. If it's about 'data surviving a crash', it's Durability. Then, recall the specific mechanism: Atomicity uses undo logs; Consistency uses constraints; Isolation uses locks or MVCC; Durability uses write-ahead logs. Use this to eliminate options that describe a different property or a mechanism that doesn't match. For instance, if an option says 'Atomicity is achieved by using foreign key constraints', that is wrong because foreign keys enforce Consistency, not Atomicity.

A bank processes millions of transactions daily, including fund transfers, bill payments, and account updates. ACID properties are non-negotiable. For example, when a customer transfers $500 from savings to checking, the system must debit savings and credit checking atomically. If the server crashes after the debit, the system must roll back the debit to prevent money loss. The bank uses a relational database like SQL Server with full ACID compliance. They set the isolation level to READ COMMITTED to prevent dirty reads while allowing reasonable concurrency. Durability is ensured by synchronous commit and geo-replication across data centers. In production, the transaction log is sized appropriately (e.g., 500 GB) to handle peak loads, and log backups are taken every 5 minutes to prevent log growth. Misconfiguration, such as setting the isolation level to READ UNCOMMITTED, could cause dirty reads and lead to incorrect balance displays, potentially causing customer disputes.

An e-commerce platform handles order placement, inventory deduction, and payment processing. A single order transaction might involve updating the inventory table, inserting an order record, and charging the customer's credit card. ACID ensures that if the payment fails, the inventory is not deducted and the order is not created. The database uses SERIALIZABLE isolation to prevent phantom reads, ensuring that inventory counts remain accurate even under high concurrency. For example, two customers trying to buy the last item in stock: the second transaction will be blocked until the first completes, preventing overselling. In production, the database is tuned to minimize deadlocks by accessing tables in a consistent order. A common misconfiguration is using too low an isolation level (e.g., READ COMMITTED) which could allow non-repeatable reads, causing the inventory check to see an outdated count and potentially oversell. The system also uses distributed transactions (MSDTC) when involving separate databases for orders and payments.

A hospital's electronic health record (EHR) system requires strict consistency and durability. Patient data must never be lost or corrupted. When a doctor updates a patient's medication list, the transaction must be atomic: either all changes are saved or none. The database uses REPEATABLE READ isolation to ensure that during a transaction, subsequent reads of the same data return the same values, preventing lost updates. Durability is critical: after the doctor saves changes, they must persist even if the server crashes. The system uses write-ahead logging and regular backups. In production, the transaction log is stored on high-availability storage (e.g., Azure Premium SSD with geo-redundancy). Misconfiguration, such as disabling log flushing for performance (e.g., DELAYED_DURABILITY), could result in data loss on a crash, violating regulatory requirements. The system also implements row-level security to ensure that only authorized users can access certain data, but this is separate from ACID.