CS4432: Database Systems II

1
CS4432 Database Systems II

• Concurrency Control
• Enforcing Serializability Locking

2
Enforcing Serializable Schedules

• DBMSs use a Scheduler that schedules the
  actions of transactions
• Transactions send their requests (R or W) to
  Scheduler
• The scheduler prevents the formation of cycles
• It grants permission to R or W only if no cycle
  will be formed

3
Locking Protocol

• Scheduler uses a locking protocol to enforce
  serializability
• Two New actions
• Lock (exclusive) li(A) ? Transaction Ti
  locks item A
• Unlock Ui(A) ? Transaction Ti
  unlocks (releases) item A

lock table
4
Rule 1 Well-Formed Transactions

• Ti li(A) pi(A) ui(A) ...

Any action (R/W) must be after the lock (l) and
before the unlock (u)
Rule 1 is at the level of each transaction
independent of the others
5
Rule 2 Legal Scheduler

• S .. li(A) ... ui(A) ...

no lj(A)
No transaction Tj can lock item A that is already
locked by another transaction Ti (Transaction Tj
must wait until Ti releases its lock)
Rule 2 is at the level of the complete schedule
(Set of interleaving transactions)
6
Schedule F Lets Add Some Locking!
7
Still Something is Missing
Still by applying the locks.results is not
consistent !!!
8
Rule 3 Two Phase Locking (2PL)

• Ti . li(A) ... ui(A) ...

No locks
No unlocks

• A transaction never start unlocks until it takes
  all its locks.
• In other words Take all locks before the first
  unlock

Rule 3 is for each transaction
9
2 Phase Locking Protocol
of locks held by a transaction
Collect locks
Release locks
Growing Phase
Shrinking Phase
10
Schedule F Does it follow 2PL ?
11
Example 2PL Schedule G
12
Example 2PL Schedule G
13
Scheduler Forced Schedule G to be Serializable
14
Theorem

• Any legal schedule S following the 2PL
• ? Is a Conflict Serializable schedule

Most DBMSs use two-phase locking (2PL) to enforce
concurrency
15
Improvement 1 Handling Deadlocks

• 2PL protocol does not prevent deadlocks
• Transactions waiting for each other indefinitely

16
Handling Deadlocks

• Detection
• Wait-for graph
• Prevention
• Resource ordering
• Timeout
• Wait-die
• Wound-wait

17
Deadlock Detection

• Build Wait-For graph
• Ti ? Tj means Ti is waiting for Tj
• Use lock table structures
• Built incrementally by the scheduler
• When cycle found, rollback victim

18
Deadlock Prevention Timeout

• If transaction waits more than L sec., roll it
  back!
• Pros Simple scheme
• Cons Hard to select L

Check the other techniques in Textbook for your
knowledge
19
Improvement 2 Avoiding Cascade Rollback

• 2PL protocol does not prevent dirty reads

To avoid this situation, we use Strict Two-Phase
Locking (S2PL)
Now T2 is based on deleted transaction
20
Strict 2PL (S2PL)

• A transaction releases its lock only at commit or
  rollback time

Commit or rollback event
of locks held by a transaction
Collect locks
Release locks
Growing Phase
Shrinking Phase
21
S2PL Example
22
Summary of Concurrency Control

• How to manage multiple transactions at the same
  time
• ? and still ensure consistency
• Schedules (order of actions)
• Serial, Conflict Serializable, Legal
• Precedence Graphs for Conflict Serializable
• Locking Protocol
• 2PL , S2PL, Handling of deadlocks