Database Concurrency Control

Databases Concurrency

• Arises because multiple users access the database simultaneously.
• Can lead to conflicts and compromise data validity when multiple users argue for the same data element.
• Occurs when multiple transactions access the same resource concurrently, potentially compromising data integrity.

Need for Concurrency Control

• Concurrent transactions can interfere with each other, which causes problems such as:
  • Lost Updates.
  • Uncommitted Update.
  • Inconsistent analysis.
• Is a process to manage simultaneous database operations without interference.

Lost Update

• Occurs when concurrent transactions overwrite each other's updates.
• In transaction T1, X is read, decremented by 5 and written, and is then lost.
• In transaction T2, X is read, incremented by 5, and written, succeeding in the update.

Uncommitted Update ("Dirty Read")

• Occurs when one transaction reads the uncommitted changes of another transaction.
• Transaction T2 reads committed value of X that should not have been seen because transaction 1 rolls back.

Inconsistent Analysis

• Transaction T2 reads X and Y for summation while transaction T1 is updating them.
• Occurs when one transaction reads data while another transaction is in the process of updating it.

Binary Lock

• When a transaction requests data item (X), the data item (X) is closed to prevent other transactions from operating on that (X) until the requesting transaction finishes its process with item (X) and releases data item (X).
• Other transactions must wait until the item (X) is released before one of them can close it.
• Implemented using lock_item and unlock_item algorithms.

lock_item(X) Algorithm

• If lock(X) = 0 (item is unlocked), then lock(X) is set to 1 (lock the item).
• If lock(X) != 0 i.e. the item is locked, wait until lock(X) = 0 and the lock manager wakes up the transaction, then repeat from the beginning.

unlock_item(X) Algorithm

• lock(X) is set to 0 (unlock the item).
• If any transactions are waiting, wake up one of them.

Shared Exclusive Lock

• Transactions can share reading access; multiple transactions can read the same data item simultaneously.
• The writing process is exclusive to only one transaction until the item is released.
• Implemented using three algorithms: read_lock, write_lock, and unlock.

read_lock(X) Algorithm

• If lock(X) = "unlocked", then lock(X) is set to "read-locked" and the number of reads i.e. no_of_reads(X) is set to 1.
• Else if lock(X) = "read-locked", then no_of_reads(X) is incremented by 1 (i.e. no_of_reads(X) + 1).
• Is the lock(X) isn't unlocked, or read_locked, wait until lock(X) = "unlocked" and the lock manager wakes up the transaction.

write_lock(X) Algorithm

• If lock(X) = "unlocked", then lock(X) is set to "write-locked".
• Else wait until lock(X) = "unlocked" and the lock manager wakes up the transaction.

unlock(X) Algorithm

• If lock(X) = "write-locked", then lock(X) is set to "unlocked" and one of the waiting transactions (if any) is woken up.
• Else if lock(X) = "read-locked", then no_of_reads(X) is decremented by 1 i.e. no_of_reads(X) – 1. If no_of_reads(X) = 0, then lock(X) is set to "unlocked" and one of the waiting transactions (if any) is woken up.