What is Concurrency Control?

Concurrency control is a mechanism used in databases to ensure the correct execution of transactions when multiple users or processes are accessing and modifying the database simultaneously. It ensures:

1. Data Integrity: Prevents inconsistencies caused by concurrent access to the same data.
2. Isolation: Ensures that each transaction operates as if it is the only one running.
3. Performance: Maximizes the system’s ability to handle multiple transactions efficiently.

Why is Concurrency Control Important?

Without proper concurrency control, the following issues can arise:

1. Dirty Reads:

   • Reading uncommitted changes made by another transaction.
   • Example: A transaction reads a value that is later rolled back, leading to inconsistencies.

2. Lost Updates:

   • Multiple transactions overwrite each other's changes.
   • Example: Two users update the same bank account balance, and one update is lost.

3. Nonrepeatable Reads:

   • A transaction reads the same data twice and gets different results because another transaction modified it in between.
   • Example: Checking inventory count before and after another user updates it.

4. Phantom Reads:

   • A transaction reads a set of rows, and another transaction inserts or deletes rows, causing the first transaction to see a different result.
   • Example: A query for available meeting rooms shows different results after another user books a room.

Concurrency Control Techniques

Databases use various techniques to manage concurrency and prevent the issues mentioned above:

1. Optimistic Concurrency Control

• Assumes conflicts are rare and allows transactions to execute without locks.

• Before committing, the database checks for conflicts:

  • If no conflicts are found, the transaction commits.
  • If conflicts are detected, the transaction is rolled back and retried.

• Example:

  • Two users edit the same document. If no overlapping changes are found, both edits are saved.

2. Locking Mechanisms

• Locks restrict access to data during a transaction, ensuring data consistency.
• Example:
  • Transaction A locks a row to update it.
  • Transaction B must wait until Transaction A releases the lock.

3. Timestamp Ordering

• Transactions are assigned timestamps when they begin.
• Transactions are executed in the order of their timestamps to avoid conflicts.
• Ensures serializability, making transactions appear as if they were executed one at a time.
• Example:
  • If Transaction A starts at 10:00 AM and Transaction B starts at 10:01 AM, A will execute first.

4. Multiversion Concurrency Control (MVCC)

• Maintains multiple versions of data for concurrent transactions.
• Each transaction works on its own version, ensuring isolation.
• Changes are visible only after the transaction commits.
• Commonly used in databases like PostgreSQL.
• Example:
  • Transaction A reads the old version of a row while Transaction B updates it. A will see the old value until B commits.

Deadlocks in Concurrency Control

A deadlock occurs when two or more transactions wait for each other’s locks, preventing progress.

Example:

1. Process A locks Resource 1 and needs Resource 2.
2. Process B locks Resource 2 and needs Resource 1.
3. Both transactions wait indefinitely.

Deadlock Resolution:

1. Timeouts: Automatically abort transactions that wait too long.
2. Deadlock Detection: The database identifies and aborts one of the conflicting transactions.

Real-World Applications of Concurrency Control

1. Banking Systems:

   • Prevents double withdrawals or inconsistent balance updates.
   • Example: Ensures that two users cannot withdraw the same amount simultaneously.

2. E-Commerce:

   • Manages inventory updates and prevents overselling.
   • Example: Locks a product row during purchase to update stock levels.

3. Collaborative Tools:

   • Ensures consistent edits in shared documents.
   • Example: Multiple users editing a Google Doc simultaneously.