Explain the ACID properties.

Detailed explanation

ACID describes four guarantees that make database transactions reliable: Atomicity, Consistency, Isolation, and Durability. A transaction groups database operations into one logical unit of work. In the example, transferring ₹500 requires debiting account 1 and crediting account 2. Treating these updates as independent operations could leave money missing if execution stops between them. Interviewers expect you to explain each property using this scenario and distinguish database guarantees from rules that application code must enforce.

Atomicity means the transaction’s changes are applied completely or not at all. If the transfer cannot finish, a rollback must undo the debit rather than leave a partial transfer. Database engines support this through mechanisms such as undo information or transaction visibility rules. However, an UPDATE that matches zero rows is usually not an SQL error, so the application must verify that both accounts were updated and roll back when necessary. Consistency means a correctly written transaction preserves defined invariants, moving the database from one valid state to another. Constraints such as primary keys, foreign keys, and CHECK constraints enforce some rules; application logic must enforce others, such as preserving the combined balance during this transfer.

Isolation governs how concurrent transactions interact. If two transfers access the same account, the database coordinates their reads and writes using mechanisms such as locks or multiversion concurrency control (MVCC). The exact protection depends on the isolation level: weaker levels may allow anomalies such as non-repeatable reads, while serializable isolation aims for an outcome equivalent to some serial execution. Isolation therefore does not mean every transaction literally runs alone, and concurrency can still produce waits, deadlocks, or retryable failures.

Durability means that once a commit is successfully acknowledged, its changes survive a crash under the database’s configured guarantees. Many engines use write-ahead logging: recovery information reaches durable storage before commit is acknowledged, allowing committed changes to be replayed after restart even when data pages were not yet written. Durability is not a replacement for backups or protection against every possible storage failure.

Key points to remember

• Atomicity makes the transaction an all-or-nothing unit, including during crash recovery.
• Consistency preserves defined constraints and business invariants through correctly written transactions.
• Isolation controls concurrent visibility and interference; guarantees depend on the isolation level.
• Durability preserves acknowledged commits across crashes within the configured storage guarantees.
• For the transfer, validate affected rows and business rules before committing; otherwise roll back.

Common mistakes candidates make

• Assuming consistency means the database automatically understands and enforces every business rule.
• Claiming isolation prevents every concurrency anomaly at every isolation level.
• Assuming any unsuccessful operation automatically rolls back the entire transaction; error behaviour varies, and updating zero rows may not raise an error.

Likely follow-up questions