Transaction Management and Concurrency Control

TL;DR

Transaction management ensures your database operations are reliable and consistent, even if things go wrong. Concurrency control allows multiple users to work on the database simultaneously without corrupting data. Together, they make sure your database is always accurate and available.

1. The Mental Model

Imagine your database as a busy bank vault. Transactions are like individual customers performing operations (depositing, withdrawing). Concurrency control is the security system that lets many customers use the vault at once without accidentally stepping on each other's toes or messing up the balances.

2. The Core Material

When you interact with a database, you're often performing a series of actions that should either all succeed or all fail together. This group of actions is called a transaction. For example, transferring money from account A to account B isn't just one step; it's deducting from A and adding to B. If only one part happens, your bank's books are wrong!

ACID Properties

Photo by Pixabay on Pexels

Transactions are guided by the ACID properties:

• Atomicity: All or nothing. If any part of a transaction fails, the entire transaction is rolled back, leaving the database unchanged.
• Consistency: A transaction brings the database from one valid state to another valid state. It must obey all defined rules (e.g., integrity constraints).
• Isolation: Concurrent transactions don't interfere with each other. Each transaction appears to execute in isolation, as if it were the only one running.
• Durability: Once a transaction is committed, its changes are permanent and survive system failures (like a power outage).

Concurrency Control

Photo by panumas nikhomkhai on Pexels

When multiple users try to access and modify the same data at the same time, you need concurrency control to prevent problems. Without it, you could face issues like:

• Lost Updates: One transaction overwrites another's changes without realizing it.
• Dirty Reads (Uncommitted Dependency): One transaction reads data written by another transaction that hasn't committed yet, and might later be rolled back.
• Non-Repeatable Reads: A transaction reads the same data twice and gets different results because another committed transaction changed it in between.
• Phantom Reads: A transaction reruns a query and finds new rows that weren't there before, because another committed transaction inserted them.

To avoid these, concurrency control mechanisms are used, primarily through locking.

Locking Mechanisms

• Shared Locks (Read Locks): Allow multiple transactions to read the same data concurrently. No transaction can write to data with a shared lock.
• Exclusive Locks (Write Locks): Only one transaction can hold an exclusive lock on data at a time. No other transaction can read or write that data.

Two-Phase Locking (2PL)

2PL is a common concurrency control protocol. It ensures serializability (transactions behave as if they ran one after another). It has two phases:

1. Growing Phase: A transaction can acquire locks but cannot release any.
2. Shrinking Phase: A transaction can release locks but cannot acquire any new ones.

Once a transaction releases a lock, it can't acquire any more. This protocol helps prevent lost updates and dirty reads.

graph TD
    A["Start Transaction"] --> B{"Growing Phase"}
    B -- "Acquire Locks" --> C["Hold Locks"]
    C -- "Can't release" --> B
    C -- "Ready to Commit/Rollback" --> D{"Shrinking Phase"}
    D -- "Release Locks" --> E["End Transaction"]
    D -- "Can't acquire new locks" --> D

3. Worked Example

Let's say you have two bank accounts, Account_A with $100 and Account_B with $50. You want to transfer $20 from Account_A to Account_B.

Transaction T1: Transfer $20 from Account_A to Account_B

1. START TRANSACTION;
2. SELECT balance FROM Account_A WHERE account_id = 'A'; (Balance: $100)
3. UPDATE Account_A SET balance = balance - 20 WHERE account_id = 'A'; (New balance: $80)
4. SELECT balance FROM Account_B WHERE account_id = 'B'; (Balance: $50)
5. UPDATE Account_B SET balance = balance + 20 WHERE account_id = 'B'; (New balance: $70)
6. COMMIT;

Now imagine another transaction, T2, trying to read Account_A's balance after step 3 but before step 6 of T1.

If there's no isolation:

• T1 updates Account_A to $80.
• T2 reads Account_A as $80. (This is a dirty read if T1 later rolls back!)
• If T1 then crashes before committing, Account_A should revert to $100. But T2 based its information on $80, which was never final.

With concurrency control (e.g., using exclusive locks on rows):

1. T1: START TRANSACTION;
2. T1: Acquires exclusive lock on Account_A. SELECT balance FROM Account_A WHERE account_id = 'A'; (Balance: $100)
3. T1: UPDATE Account_A SET balance = balance - 20 WHERE account_id = 'A'; (New balance: $80)
4. T2: Tries to SELECT balance FROM Account_A. It waits because Account_A is locked by T1.
5. T1: Acquires exclusive lock on Account_B. SELECT balance FROM Account_B WHERE account_id = 'B'; (Balance: $50)
6. T1: UPDATE Account_B SET balance = balance + 20 WHERE account_id = 'B'; (New balance: $70)
7. T1: COMMIT; (Releases locks on Account_A and Account_B).
8. T2: Now that the lock is released, T2 can read Account_A's balance, which is now the committed value of $80.

This ensures T2 only sees committed, consistent data.

4. Key Takeaways

• Transactions group related database operations into an all-or-nothing unit.
• ACID properties (Atomicity, Consistency, Isolation, Durability) define how reliable transactions are.
• Concurrency control manages simultaneous access to data, preventing inconsistencies.
• Locking is a primary mechanism for concurrency control, using shared (read) and exclusive (write) locks.
• Two-Phase Locking (2PL) ensures that transactions behave as if they executed sequentially, preventing common concurrency issues.
• Isolation levels determine the degree to which transactions are protected from one another's uncommitted changes.
• Without proper transaction management, data can become corrupted and unreliable.

Common mistakes you should avoid:
- Forgetting to COMMIT or ROLLBACK transactions, which can leave locks open or data in an inconsistent state.
- Holding locks for too long, leading to reduced concurrency and potential deadlocks.
- Not understanding your database's default isolation level, which might allow dirty reads or non-repeatable reads by default.
- Assuming simple SELECT statements don't need to be part of a transaction in highly concurrent environments (e.g., for repeatable reads).

5. Now Try It

Think about an online shopping cart. Design a transaction that adds an item to a user's cart and simultaneously decreases the item's available stock. What steps are involved, and where would locks (shared or exclusive) be needed to prevent other users from buying an item that's just sold out, or seeing an incorrect stock count? Describe what would happen if your transaction failed halfway through due to a network error, focusing on the ACID properties.