Database Transactions and Concurrency Control

Fundamental Principles of Database Transactions

A database transaction represents a single unit of work that is characterized by an all-or-nothing completion principle. To maintain system integrity, databases adhere to four core properties known by the acronym ACID:

  • Atomicity: This property guarantees that a transaction is never partially completed. If any part of the unit of work fails, the entire transaction is rolled back, ensuring the database remains in its previous state.
  • Consistency: This property ensures that a transaction brings the database from one valid state to another, maintaining all predefined rules and constraints.
  • Isolation: This property governs how transactions interact with one another when running concurrently. It ensures that the intermediate state of a transaction is invisible to other transactions.
  • Durability: This property ensures that once a transaction has been committed, its results are permanent and will survive any subsequent system crash or failure.
    • Implementation of Durability: Durability is typically achieved through the use of a write-ahead log. The system writes changes to this log before modifying the actual data pages. Because the log is sequential, it is computationally cheap to flush to the disk compared to random data page modifications.

Transaction Isolation Levels and Concepts

Isolation is a frequently misunderstood property because full isolation, known as serializability, is computationally expensive. As a result, most databases use weaker default isolation levels to balance performance and consistency.

  • Serializable: The strictest level of isolation. Concurrent transactions are guaranteed to produce the same results as if they had been executed sequentially (one after another).
  • Read Committed: This is the default isolation level for PostgreSQL. It prevents transactions from seeing data that has not yet been committed by others.
  • Repeatable Read: This is the default isolation level for MySQL's InnoDB engine. It ensures that if a transaction reads a row once, it will see the same data upon subsequent reads within that same transaction.

Concurrent Transaction Anomalies

When databases operate at isolation levels weaker than Serializable, specific data anomalies can occur:

  • Dirty Read: This occurs when a transaction is permitted to read data that has been modified by another transaction but not yet committed. If that other transaction rolls back, the first transaction has effectively read "garbage" data that never officially existed.
  • Non-Repeatable Read: This occurs when a transaction reads the same row twice and receives two different values. This happens because another transaction committed a change to that row between the first and second reads.
  • Phantom Read: This occurs when a transaction re-runs a query that returns a set of rows (such as a range query) and finds that the set has changed because a different transaction inserted or deleted rows in the interim.

Mitigation Strategies for Anomalies

Different isolation levels and locking mechanisms are used to defend against specific anomalies:

  • Defending Against Non-Repeatable Reads: Since this anomaly involves a row that already exists, it can be prevented by placing a lock on that specific row.
  • Defending Against Phantom Reads: Because phantoms involve rows that do not exist yet, row-level locking is insufficient. Protection requires range locking or "gap locking," where the database locks the space or gap where a future row might be inserted.
    • InnoDB Exception: While Repeatable Read classically permits phantom reads, MySQL's InnoDB engine prevents them in most cases through the use of next-key locking.
  • Read Committed vs. Repeatable Read Summary:
    • Read Committed: Prevents dirty reads; permits non-repeatable and phantom reads.
    • Repeatable Read: Prevents dirty and non-repeatable reads; classically permits phantom reads.

Deadlock Detection and Resolution

A deadlock occurs in a database environment when two transactions are stuck in a circular dependency, each holding a lock that the other transaction requires to proceed.

  • Database Resolution: Databases resolve deadlocks by maintaining a "wait-for graph." The system constantly searches this graph for cycles. When a cycle (deadlock) is detected, the database selects one transaction as the "victim" and forcibly aborts it to break the cycle.
  • Application-Side Requirements: Because the database—not the application—chooses which transaction to abort, application code must be designed to handle deadlock aborts. A frequent point of failure in production is treating a deadlock abort as a fatal error instead of a signal to retry the transaction.

Optimistic Concurrency Control (OCC)

Optimistic Concurrency Control serves as an alternative to traditional locking mechanisms (Pessimistic Concurrency Control).

  • Mechanism: OCC allows transactions to proceed without acquiring locks. Instead of preventing conflicts upfront, the system performs a check at the time of commit to see if any data the transaction read has been modified by another party. If a change is detected, the transaction is aborted.
  • Performance Dynamics:
    • Low Contention: OCC performs better than locking because it avoids the overhead of managing locks.
    • High Contention: OCC performs significantly worse than locking. Under high contention, most transactions will complete their full workload only to have it discarded at the commit phase, leading to massive wasted computational effort.