SQL Transactions and ACID Properties

Explain database transaction ACID properties and isolation levels.

ACID

• Atomicity: All operations in a transaction either succeed entirely or roll back entirely.
• Consistency: The database remains in a valid state before and after a transaction.
• Isolation: Concurrent transactions are isolated from each other, as if executed sequentially.
• Durability: Committed transactions persist even if the system crashes.

Isolation Levels (low to high)

• READ UNCOMMITTED: Dirty reads, non-repeatable reads, and phantom reads possible.
• READ COMMITTED: Prevents dirty reads; non-repeatable reads and phantoms possible.
• REPEATABLE READ: Prevents dirty and non-repeatable reads; phantoms possible.
• SERIALIZABLE: Full isolation, no read anomalies; lowest concurrency.

Higher levels are safer but reduce concurrency. PostgreSQL defaults to READ COMMITTED; MySQL InnoDB defaults to REPEATABLE READ.

Common Read Anomalies

• Dirty read: Reading uncommitted data from another transaction.
• Non-repeatable read: Same row returns different values within the same transaction.
• Phantom read: Second query in the same transaction returns new rows.