Concurrency Control

1
Concurrency Control

• Chapters 22.2, 22.3, 22.5 and 7.5

2
Concurrency Control Techniques

• Pessimistic (conservative) - transactions are
  delayed or aborted to avoid conflicts
• Locking methods
• Time stamping
• Optimistic
• Conflicts are rare
• Conflicts are determined at the end of concurrent
  execution of transactions

3
Locking Methods

• Locking
• Shared lock on object A - Si(A)
• Exclusive lock on object A - Xi(A)
• Locking mechanism
• A transaction must first lock an object
  requesting appropriate access.
• If the object is not locked yet, the lock is
  granted
• Otherwise, the shared lock may be granted or the
  transaction must wait until the existing lock is
  released.

4
Lock Granularity

• Database-level
• Table-level
• Page-level
• Record-level
• Field-level

5
Example 5
6
Rigorous 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.

7
Example 6
8
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.
• Unlock operation is required
• A transaction is divided into 2 phases
• Growing phase
• Shrinking phase

9
Cascading Rollback (Example 7)
10
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

11
Lock Properties

• Atomicity of locking
• Lock upgrade
• Lock downgrade
• Convoy situation

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

• Timeout
• Assigning priorities
• Timestamp
• Wait-die strategy T is allowed to wait for only
  if it has a higher priority
• Wound-wait strategy T is allowed to wait for
  only if it has a lower priority
• Priority for aborted transaction

14
Example 9

• Consider the schedule

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

15
Deadlock Detection

• Waits-for graph
• Each node corresponds to an active transaction
• There is an arc (edge) 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

16
Example 9
Does the schedule contain a deadlock?
17
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

18
Recovery after Deadlock Detection

• Choice of a victim
• A transaction that has just started
• How far to roll a transaction back
• Starvation problem

19
Database Performance

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

20
Concurrency Control with Timestamping Methods

• Timestamp unique identifier indicates starting
  time of the transaction
• Each data item has read and write timestamps
  timestamps of last transactions operating with
  the data item
• Methods
• Basic timestamp ordering
• Thomass write rule

21
Basic Timestamp Ordering - Read Rule

• Let T1 and T2 transactions with timestamps
  ts(T1) lt ts(T2)
• T1 issues a R(A) and A is modified by T2
• T1 is aborted and restarted with a new timestamp
• T2 issues a R(B) and B is modified by T1
• T2 reads B

22
Basic Timestamp Ordering Write Rule

• T1 issues a W(B) and B is read by T2
• T1 is aborted and restarted with a new timestamp
• T1 issues a W(C) and C is modified by T2
• T1 is aborted and restarted with a new timestamp
• T2 issues a W(D) and D is read or modified by T1
• T2 modifies D

23
Thomass Write Rule

• Let T1 and T2 transactions with timestamps
  ts(T1) lt ts(T2)
• T1 issues a W(B) and B is read by T2
• T1 is aborted and restarted with a new timestamp
• T1 issues a W(C) and C is modified by T2
• W1(C) is ignored
• T2 issues a W(D) and D is modified by T1
• T2 modifies D

24
Multiversion Schemes

• Each W(O) from the schedule creates a new version
  of O
• When a transaction issues R(O), the system
  selects the version that ensures serializability
• Versions that are no longer needed are deleted

25
Multiversion Timestamping Method

• Each transaction receives a static timestamp
  ts(Ti)
• For each data item used the system maintains
  versions O1, O2,,Om
• Each version Ok consists of
• Content
• W-timestamp
• R-timestamp

26
Multiversion Timestamp Ordering

• Possible version for any transaction T
• Om - version with maximal W-timestamp which is
  less or equal to ts(T)
• If T requests R(O), the content of Om is returned
  and
• R-ts(Om) ts(T), if ts(T)R-ts(Om)
• R-ts(Om) stays the same, ts(T)ltR-ts(Om)
• If T requests W(O)
• If ts(T)ltR-ts(Om), T is aborted
• If ts(T)gtR-ts(Om), a new version Om1 is created
• If ts(T)R-ts(Om), the contents Om of is updated

27
Implementation Issues

• Control for schedule recoverability
• Version withdrawal
• Determine the timestamp of the oldest active
  transaction ts(T)
• Find all versions with W-timestamp less than
  ts(T)
• Delete all versions except the youngest one

28
Concurrency Control with Optimistic Methods

• Most database operations do not conflict
• Transaction is executed without restrictions
  until commits
• Phases
• Read Phase
• Validation Phase
• Write Phase

29
Transaction Phases

• Read-only transaction
• Read from the start until the commit point
• Validation possible conflicts are checked
• Update transaction
• Read
• Validation
• Write updates made to the local copy are stored
  to the database

30
Validation

• Each transaction is assigned three timestamps
  start, validation, and finish
• Test for transaction T
• For all S such that start(S)ltstart(T),
  finish(S)ltstart(T)
• For any S such that start(S)ltstart(T), but
  finish(S)start(T)
• T doesnt read data modified by S
• start(T)ltfinish(S)ltvalidation(T)

31
Rollback with Optimistic Method

• If the schedule contains a conflict, T is aborted
  and restarted
• Rollback affects only local copy
• No cascading rollbacks

32
Phantom Problem

• Two transactions are concurrently executed and
  one of them implements INSERT or DELETE statement
• Phantom the possibility that a transaction
  retrieves a set of objects twice and receives
  different sets

33
Transaction Support in SQL

• The beginning of a transaction
• Ending of the transaction
• Explicit
• COMMIT
• ROLLBACK
• Implicit
• Successful end of the program
• Abnormal program termination (Transaction is
  aborted).

34
Transaction Characteristics

• Access mode
• READ ONLY
• READ WRITE
• Isolation level
• Diagnostics size

35
Isolation Levels

• Serializable
• T reads only committed data
• No value read or written by T is modified by T
  until T is complete
• Phantom is not allowed
• Repeatable read
• T reads only committed data
• No value read or written by T is modified by T
  until T is complete
• Phantom is allowed

36
Isolation Levels (cont)

• Read committed
• T reads only committed data
• No value written by T is modified by T until T
  is complete
• Phantom is allowed
• Read uncommitted
• T reads changes of active transactions, values
  read can be further modified
• Is allowed for READ ONLY access mode

37
Transaction Configuration

• SET TRANSACTION
• READ ONLY l READ WRITE
• ISOLATION LEVEL
• READ UNCOMMITTED l READ COMMITTED l REPEATABLE
  READ l
• SERIALIZABLE

38
Transaction Support in Oracle

• SET TRANSACTION READ ONLY
• SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
• SET TRANSACTION ISOLATION LEVEL READ COMMITTED