ACID Properties and Transaction Isolation Levels

• Atomicity – all statements in a transaction succeed together or none do; implemented with an undo log that can revert partial changes.
• Consistency – every committed transaction leaves the database satisfying all defined rules (constraints, foreign keys, checks).
• Isolation – concurrent transactions produce a result as if they ran in some serial order; tuned by isolation levels.
• Durability – once COMMIT returns, the changes are permanently recorded and survive crashes, achieved by flushing the WAL before acknowledging.
• Isolation levels – Read Uncommitted, Read Committed, Repeatable Read, Serializable – each prevents progressively more concurrency anomalies at the cost of reduced concurrency.

What is Atomicity?

Atomicity means a transaction is an indivisible unit. The database either applies every change (commit) or, if anything fails, reverts every change (rollback) so the database looks as if the transaction never ran. This is implemented with an undo log or rollback segment.

BEGIN;
UPDATE accounts SET balance = balance - 50000 WHERE account_id = 'SAV2001';
-- If this next statement fails, the first UPDATE is also rolled back
UPDATE accounts SET balance = balance + 50000 WHERE account_id = 'CUR3001';
COMMIT;

For a transfer, if the credit fails after the debit, atomicity rolls back the debit so money is never lost.

What is Consistency?

Consistency means every committed transaction leaves the database satisfying all defined rules – constraints, foreign keys, checks, triggers. If a transaction would violate a rule, it is rejected and rolled back.

-- CHECK constraint prevents negative balance
ALTER TABLE accounts ADD CONSTRAINT chk_balance CHECK (balance >= 0);

-- This transfer will fail if it makes Rajesh's balance negative
BEGIN;
UPDATE accounts SET balance = balance - 1000000 WHERE account_id = 'SAV2001';
-- ERROR: new row violates check constraint "chk_balance"
ROLLBACK;

Consistency in ACID is about integrity constraints, which is subtly different from the "C" in CAP (which is about all nodes agreeing).

What is Isolation?

Isolation means concurrent transactions produce a result as if they had run in some serial order. Perfect isolation is expensive, so databases offer tunable isolation levels defined by which concurrency anomalies they prevent.

• The classic anomalies are:
• Dirty read – reading another transaction's uncommitted change.
• Non-repeatable read – re-reading a row and getting a different committed value because another transaction changed it.
• Phantom read – re-running a range query and getting new rows another transaction inserted.

What are the four isolation levels?

| Level | Dirty Read | Non-Repeatable Read | Phantom Read | Default In |
|-------|-----------|--------------------|--------------|-----------|
| Read Uncommitted | Possible | Possible | Possible | Rarely used |
| Read Committed | Prevented | Possible | Possible | PostgreSQL, Oracle |
| Repeatable Read | Prevented | Prevented | Possible* | MySQL InnoDB |
| Serializable | Prevented | Prevented | Prevented | Strictest |

*MySQL InnoDB's Repeatable Read also blocks many phantoms via next-key locking.

-- Set the isolation level for a transaction (PostgreSQL)
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT balance FROM accounts WHERE account_id = 'SAV2001';
UPDATE accounts SET balance = balance - 50000 WHERE account_id = 'SAV2001';
COMMIT;  -- may fail with a serialization error; application retries

What is Durability?

Durability means once COMMIT returns, the changes are permanently recorded and survive a crash or power loss. Databases achieve this with a write-ahead log (WAL) flushed to durable storage before acknowledging the commit.

Transaction Flow:
  App → BEGIN → UPDATE → COMMIT
                           │
                           ▼
                    WAL flushed to disk
                           │
                           ▼
                    "COMMIT OK" sent to app
                           │
                    (crash here? data safe!)

How do you choose the right isolation level?

Pick the lowest level that still protects your invariant. Higher isolation means more locking and lower concurrency.

package com.bank.service;

import java.math.BigDecimal;
import java.sql.*;

public class TransferService {

    private final Connection connection;

    public TransferService(Connection connection) {
        this.connection = connection;
    }

    public void transfer(String from, String to, BigDecimal amount) throws SQLException {
        connection.setAutoCommit(false);
        // Read Committed + explicit row locks is often better than Serializable
        connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
        try {
            // Lock the rows to prevent concurrent modifications
            try (PreparedStatement lock = connection.prepareStatement(
                    "SELECT balance FROM accounts WHERE account_id IN (?, ?) FOR UPDATE")) {
                lock.setString(1, from);
                lock.setString(2, to);
                lock.executeQuery();
            }
            try (PreparedStatement debit = connection.prepareStatement(
                    "UPDATE accounts SET balance = balance - ? WHERE account_id = ?")) {
                debit.setBigDecimal(1, amount);
                debit.setString(2, from);
                debit.executeUpdate();
            }
            try (PreparedStatement credit = connection.prepareStatement(
                    "UPDATE accounts SET balance = balance + ? WHERE account_id = ?")) {
                credit.setBigDecimal(1, amount);
                credit.setString(2, to);
                credit.executeUpdate();
            }
            connection.commit();
        } catch (SQLException e) {
            connection.rollback();
            throw e;
        } finally {
            connection.setAutoCommit(true);
        }
    }
}

What is MVCC and how does it help?

MVCC (multi-version concurrency control) gives each transaction a consistent snapshot of the data. Readers see the version of the data that was committed when their transaction started. This means readers do not block writers and writers do not block readers.

Transaction T1 (read):  sees snapshot at time T=10
Transaction T2 (write): modifies data at time T=11

T1 still sees the T=10 version – no blocking, no dirty read.

What happens with Serializable failures?

A Serializable transaction can abort with a serialization failure even when it did nothing wrong. The database detects that the concurrent execution would produce a result different from any serial order, and forces one transaction to retry.

// Application must be prepared to retry
int retries = 3;
while (retries-- > 0) {
    try {
        transferService.transfer("SAV2001", "CUR3001", new BigDecimal("50000"));
        break;
    } catch (SQLException e) {
        if ("40001".equals(e.getSQLState())) {
            // Serialization failure – retry
            continue;
        }
        throw e;
    }
}

What are common mistakes?

The most common mistake is running at Read Uncommitted "for speed" in a financial system. Dirty reads in a banking system can show money that does not exist.

-- Wrong: dirty read in a banking context
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT balance FROM accounts WHERE account_id = 'SAV2001';
-- Might see a balance from an uncommitted transfer that will be rolled back!

Another mistake is using Serializable everywhere. The locking overhead is high and most queries do not need it. Use Read Committed with targeted SELECT ... FOR UPDATE instead.

What are common interview follow-up questions?

• "What is a lost update?" Two transactions read the same value, compute a new value, and both write, so one update is overwritten. Prevented by SELECT ... FOR UPDATE or higher isolation.
• "What is the difference between MVCC and locking?" MVCC avoids read-write conflicts by keeping versions; locking serialises conflicting access.
• "Can you have ACID without a relational database?" Yes. MongoDB 4.0+ supports multi-document ACID transactions.