10.4.2 Concurrency control

• What is the purpose of concurrency control in database management?

  Ensure data integrity
• Concurrency control is necessary to prevent transactions from interfering with each other, which could lead to data errors.
• One common concurrency control technique is locking
• Match the lock type with its purpose:

  Exclusive lock ↔️ Allows only one write

  Shared lock ↔️ Allows multiple reads
• What does time-stamping involve in concurrency control?

  Assigning unique time-stamps
• Order the steps in applying locking for concurrency control:

  1️⃣ Transaction requests lock on data item

  2️⃣ Lock is granted if available

  3️⃣ Transaction modifies data

  4️⃣ Transaction releases lock
• Time-stamping avoids deadlocks but requires more complex implementation
• Lost updates occur when multiple transactions modify the same data concurrently, and one transaction's updates are overwritten by another.
• What is the primary cause of inconsistent data in concurrent database access?

  Reading partially updated data
• Deadlocks occur when two or more transactions are blocked indefinitely, each waiting for the other to release a resource
• Concurrency control mechanisms are essential to prevent deadlocks and ensure reliable database operations.
• Match the type of lock with its description:

  Exclusive lock ↔️ Allows only one transaction to write

  Shared lock ↔️ Allows multiple transactions to read
• What happens when Transaction 2 tries to read Table A while Transaction 1 holds an exclusive lock on it?

  Transaction 2 is blocked
• Two common concurrency control techniques are locking and time-stamping
• What are two common techniques for concurrency control in a database?

  Locking and time-stamping
• Locking involves placing locks on data items to prevent other transactions from accessing them.
• Exclusive locks allow multiple transactions to write simultaneously.

  False
• What type of lock allows multiple transactions to read a data item concurrently?

  Shared lock
• Time-stamping assigns unique time-stamps to each transaction to ensure consistency.
• Time-stamping avoids deadlocks in database concurrency control.
• A disadvantage of locking is that it can lead to deadlocks and longer transaction waits.
• Time-stamping has lower overhead than locking in managing concurrent transactions.

  False
• Order the challenges of concurrent database access from most to least severe:

  1️⃣ Deadlocks

  2️⃣ Inconsistent data

  3️⃣ Lost updates
• Concurrency control techniques are designed to prevent lost updates, inconsistent data, and deadlocks in databases.
• What are the primary challenges mitigated by concurrency control techniques?

  Lost updates, inconsistent data, deadlocks
• Two common concurrency control techniques are locking and time-stamping
• What is the purpose of exclusive locks in locking mechanisms?

  Allow only one write
• Shared locks allow multiple transactions to read data items concurrently.
• How does timestamping ensure data consistency in concurrent transactions?

  Validates reads and writes
• Timestamping avoids deadlocks but has the disadvantage of managing timestamps
• Match the concurrency control technique with its advantage:

  Locking ↔️ Ensures high data integrity

  Timestamping ↔️ Avoids deadlocks
• Concurrency control techniques aim to prevent lost updates and deadlocks in databases.
• What is the primary goal of concurrency control in database management?

  Maintain data integrity
• Concurrency control maintains data integrity by coordinating concurrent access
• Lost updates occur when multiple transactions modify the same data concurrently, and one update is overwritten.
• What is the cause of inconsistent data in concurrent database access?

  Reading partially updated data
• Deadlocks occur when two or more transactions are blocked indefinitely, waiting for each other to release a resource
• Match the concurrency challenge with its mitigation:

  Lost Updates ↔️ Locking

  Inconsistent Data ↔️ Locking, Timestamping

  Deadlocks ↔️ Locking, Deadlock Detection
• What are the two primary types of locks used in locking mechanisms?

  Exclusive and Shared
• Locking mechanisms prevent multiple transactions from simultaneously modifying the same data.