Eventually Bitwise

Transactions & Concurrency Control

Overview

Transactions and concurrency control ensure that a database remains in a consistent state even when multiple users are modifying data simultaneously. This is critical for preventing data corruption and ensuring reliability.

Core Concepts

  • Transaction: A logical unit of work that must be completed entirely or not at all (Atomicity).
  • Concurrency Problems:
    • Dirty Read: Reading data that has been modified by another transaction but not yet committed.
    • Non-repeatable Read: Reading the same row twice and getting different results because another transaction modified it.
    • Phantom Read: Reading a set of rows and finding that new rows have appeared because another transaction inserted them.
  • Isolation Levels:
    • Read Uncommitted: Lowest isolation, allows dirty reads.
    • Read Committed: Prevents dirty reads. (Default for many DBs).
    • Repeatable Read: Prevents dirty reads and non-repeatable reads.
    • Serializable: Highest isolation, prevents all three (behaves as if transactions ran one after another).
  • Locking Mechanisms:
    • Shared Lock (S): Allows reading but not writing.
    • Exclusive Lock (X): Prevents others from reading or writing.
    • Optimistic Concurrency Control: Assumes conflicts are rare; checks for changes at commit time (often using a version number).
    • Pessimistic Concurrency Control: Locks the data immediately upon access.

Code Examples

-- Start a transaction
BEGIN TRANSACTION;

-- Perform operations
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;

-- Commit if everything is OK
COMMIT;
-- Or rollback if an error occurs
ROLLBACK;

Use Cases

  • Banking Systems: Transferring money between accounts must be a single atomic transaction.
  • E-commerce: Ensuring a product is not sold twice (inventory management).
  • Booking Systems: Preventing two people from booking the same hotel room.

Gotchas

  • Deadlocks: Two transactions waiting for each other to release locks, causing a freeze.
  • Performance Trade-off: Higher isolation levels provide more safety but significantly reduce concurrency and throughput.

Related Notes

, ,