Transaction Management and Concurrency Control

1
Transaction Management and Concurrency Control

• Concurrency Control

2
Example 4

• Indicate whether the following schedules can
  produce anomalies

3
Example 5
Is the schedule serializable?
4
Locking Mechanism

• Locks
• Binary lock
• Shared lock on object A - Si(A)
• Exclusive lock on object A - Xi(A)
• Locking mechanism
• To read an object a transaction must receive a
  shared lock on the object
• To write an object a transaction must receive an
  exclusive lock on the object

5
Locking Mechanism (Cont)

• A transaction must first lock an object to get an
  access.
• If the object is not locked yet, a shared or an
  exclusive lock is granted
• If the object is locked by a shared lock
  belonging to another transaction, another shared
  lock may be granted or the transaction must wait
  until the existing lock is released.
• If the object is locked by an exclusive lock
  belonging to another transaction, the transaction
  must wait until the existing lock is released

6
Lock Granularity

• Database-level
• Table-level
• Page-level
• Row-level
• Field-level

7
Example 6
8
Strict Two-Phase Locking Protocol

• First rule
• If a T reads an object, it first requests a
  shared lock on the object.
• If a T writes an object, it first requests an
  exclusive lock on the object
• Second rule
• All locks held by a transaction are released
  when transaction is completed.

9
Example 7
10
Two-Phase Locking Protocol

• To read an object, T requests a shared lock. To
  write an object, T requests an exclusive lock
• A transaction cannot request additional locks
  once it releases any lock.
• No data are affected until all locks are
  obtained.
• A transaction is divided into 2 phases
• Growing phase
• Shrinking phase

11
Two-Phase Locking Protocol
12
Lock Management

• Lock manager
• Lock table holds
• The number of transactions holding a lock on the
  object
• The lock type
• A pointer to a queue of lock requests
• Transaction table holds
• list of locks held by a transaction
• Atomicity of locking

13
Deadlocks (example 8)
A deadlock occurs when two or more transactions
are each waiting for locks to be released by the
other.
14
Deadlock Prevention

• Assigning priorities
• Timestamp
• Wait-die strategy
• T is allowed to wait for only if it has a higher
  priority, otherwise T is aborted
• Wound-wait strategy
• If T has a higher priority than the transaction
  holding the lock, the latter is aborted. If T is
  younger, T is allowed to wait.
• Priority for aborted transaction

15
Example 9

• Consider the schedule

• What are the actions according to wait-die
  strategy?
• What are the actions according to wound-wait
  strategy?

16
Deadlock Detection

• Waits-for graph
• Each node corresponds to an active transaction
• There is an arc from Tk to Tl currently holding a
  lock if and only if Tk is waiting for Tl to
  release the lock.
• If a waits-for graph is cyclic, then the schedule
  contains a deadlock
• Another approach if T is waiting for a lock
  longer than allowed, T is aborted and restarted

17
Example 9
Does the schedule contain a deadlock?
18
Frequency of Deadlock Detection

• Assign initial time step ?t, ?tmin, and ?tmax
• If during ?t no deadlock is detected
• ?t 2 ?t
• If during ?t a deadlock is detected
• ?t ?t/2

19
Recovery after Deadlock Detection

• Choice of a victim
• A transaction that has just started
• A transaction that has a few locks
• A transactions that have a few changes left.

20
Database Performance

• Deadlock prevention
• Aborted transactions
• Suspended transactions
• Too strong conditions
• Deadlock detection
• Analysis of schedules

21
Concurrency Control with Time Stamping Methods

• Assigns global unique time stamp to each
  transaction
• Produces order for transaction submission
• Properties
• Uniqueness
• Monotonicity
• Conflicting operations can be executed only in
  time stamp order

22
Disadvantages of Time Stamping Methods

• Each value requires two additional time stamps
  fields
• Last time field read
• Last update
• Requires additional system resources to stop,
  re-schedule and re-stamp transactions

23
Concurrency Control with Optimistic Methods

• Assume most database operations do not conflict
• A transaction is executed without restrictions
  until it commits
• Phases
• Read Phase
• Validation Phase
• Write Phase

24
Recovery Management

• Restore a database to the most recent consistent
  state
• Reasons
• System fails
• Atomicity and durability of transactions
• Basic unit - a transaction
• Database buffer

25
The Log

• History of actions executed by the DBMS
• Stable storage
• Log tail
• Actions are recorded in chronological order

26
Log Sequence Number

• Monotonically increasing order
• Every page (object) contains pageLSN
• PageLSN - the most recent LSN from the log

27
The Write-Ahead Log Protocol

• All Update log records must be written to stable
  storage first
• LSNs are determined by pageLSN
• Log records
• Transaction records
• Systems records

28
Actions recorded to the Log

• Updating an object
• Update record is appended to the log tail
• pageLSN is set to the current LSN
• Commit
• Commit record is appended to the log
• Log tail is written to stable storage

29
Actions recorded to the Log (cont)

• End
• Abort
• Abort log record is appended to the log
• Undo is initiated
• Undoing updates
• After the action described in update log record
  is undone, a compensation log record (CRL) is
  appended to the log.

30
Checkpoints

• A point of synchronization between database and
  transaction log
• All buffers are force-written to secondary
  storage
• Checkpoints in AREIS
• Begin_checkpoint record
• End_checkpoint record
• Master record with LSN of the begin_checkpoint
  record is written to stable storage

31
ARIES

• Algorithm for Recovery and Isolation Exploiting
  Semantics
• Three main phases
• Analysis
• Redo
• Undo

32
Basic Principles

• Write-ahead logging
• Repeating history during Redo phase
• Logging changes during Undo

33
Analysis Phase

• Scan log from the checkpoint to the end
• To identifies active transactions at the time of
  the crash (must be undone)
• To determine the transactions that were committed
  after the checkpoint and must be redone.

34
Redo Phase

• Repeating history paradigm
• Redo(Redo(A)) Redo(A)
• Redo actions are reflected to the log
• The result
• The database is brought to the state as before
  the crash

35
Undo Phase

• Scans backward from the end of the log
• Undo(Undo(A)) Undo(A)
• For every Undo action the CLR is added to the log
  
• Removes all changes caused by incomplete
  transactions