Concurrency Control

Concurrency Control

1. Problem Definition

   • Objectives
     • Throughput: number of transactions processed per unit time eg> 1 million transactions per second
     • Maximum transactions throughput(work perform) while preventing interference among multiple users
   • Constraint
     • No interference: result same as serial/sequential effect
     • Interference occurs on commonly manipulated data known as hot spot.

2. Concurrency Control Problems

   • Last Update Problem
     • Preventing Last Update Problem Using 2PL
   • Uncommitted Dependency Problem
     • Preventing Uncommited using 2PL

3. Concurrency Control Mechanisms

   • Locking
     • Obtain R/W Lock before accessing an item
     • Locking Conflicts
     • Locking Granularity
     • Wait if a conflicting lock is held in The Lock Manager
       • Shared Lock ( S lock): conflicts with exclusive locks
       • Exclusive Lock (X lock): conflicts with all other kinds of locks
     • Two-Phase Locking 2PL
       • Growing Phase: All files are getting locked.
       • Shrinking Phase: All locks are released
       • Strict 2PL
       • Deadlock
         • Deadlock(Mutual Waiting)
         • Deadlock Resolution
           • Detection
             • Overhead is reasonable for deadlocks among 2 or 3 transactions
           • TimeOut
             • Waiting Limit
             • Can abort transactions that are not deadlock
             • Timeout interval is difficult to determine
   • Optimistic Concurrency
     • Private Workspaces Each process is executing privately