Grokking Database Fundamentals for Tech Interviews
Ask Author
Back to course home

0% completed

Vote For New Content
Understanding the Concurrency Control
Table of Contents

Contents are not accessible

Contents are not accessible

Contents are not accessible

Contents are not accessible

Contents are not accessible

What is Concurrency Control?

Image

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:

Image

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.

Image

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.

.....

.....

.....

Like the course? Get enrolled and start learning!

Table of Contents

Contents are not accessible

Contents are not accessible

Contents are not accessible

Contents are not accessible

Contents are not accessible