Concurrency Control

1
Concurrency Control

• Chapter 17

2
Conflict Serializable Schedules

• Two schedules are conflict equivalent if
• Involve the same actions of the same transactions
• Every pair of conflicting actions is ordered the
  same way
• Schedule S is conflict serializable if S is
  conflict equivalent to some serial schedule

3
Conflict serialiable vs. serialiable

• Conflict serializable -gt serializable
• But not vice versa, e.g.
• T1 R(A) W(A)Commit
• T2 W(A)commit
• T3
  W(A)Commit
• Equivalent to T1 -gt T2 -gt T3, but not conflict
  serializable

4
Example

• A schedule that is not conflict serializable
• The cycle in the graph reveals the problem. The
  output of T1 depends on T2, and vice-versa.

T1 R(A), W(A), R(B), W(B) T2
R(A), W(A), R(B), W(B)
A
T1
T2
precedence graph
B
5
Precedence Graph

• Precedence graph
• One node per Xact
• An arc from Ti to Tj if an action of Ti precedes
  and conflicts with one of Tjs actions
• Capturing all conflicts
• Theorem Schedule is conflict serializable if and
  only if its precedence graph is acyclic

6
Review Strict 2PL

• Strict Two-phase Locking (Strict 2PL) Protocol
• Each Xact must obtain a S (shared) lock on object
  before reading, and an X (exclusive) lock on
  object before writing.
• All locks held by a transaction are released when
  the transaction completes
• If an Xact holds an X lock on an object, no
  other Xact can get a lock (S or X) on that
  object.
• If an Xact holds a lock (S or X) on an object,
  no other Xact can get an X lock on that object.
• Strict 2PL allows only schedules whose precedence
  graph is acyclic

7
Two-Phase Locking (2PL)

• Two-Phase Locking Protocol
• Each Xact must obtain a S (shared) lock on object
  before reading, and an X (exclusive) lock on
  object before writing.
• A transaction can not request additional locks
  once it releases any locks.
• If an Xact holds an X lock on an object, no
  other Xact can get a lock (S or X) on that
  object.
• If an Xact holds a lock (S or X) on an object,
  no other Xact can get an X lock on that object.

8
More on 2PL

• Relaxation of strict 2PL
• A growing phase acquiring locks
• A shrinking phase releases locks
• 2PL -gt conflict serializable
• Why?
• An equivalent serial order of transactions is
  given by the order in which transactions enter
  their shrinking phase

9
Strict 2PL vs. 2PL

• A schedule is said to be strict if a value
  written by a transaction T is not read or
  overwritten by other transactions until T either
  aborts or commits.
• Strict schedules are recoverable, ACA
• Strict 2PL -gt strict 2PL
• ACA conflict serialiable
• Strict 2PL most popular, 2PL no practical
  importance
• Strict 2PL is actually only one phase
• New terminology strict 2PL (S2PL) for strictness
  2PL
• strong strict 2PL
  (SS2PL) for our strict 2PL
• An example schedule allowed by S2PL but not
  SS2PL?

10
View Serializability

• Schedules S1 and S2 are view equivalent if
• If Ti reads initial value of A in S1, then Ti
  also reads initial value of A in S2
• If Ti reads value of A written by Tj in S1, then
  Ti also reads value of A written by Tj in S2
• If Ti writes final value of A in S1, then Ti also
  writes final value of A in S2
• S is view serializable if it is view equivalent
  to some serial schedule

T1 R(A) W(A) T2 W(A) T3 R(A)
W(A)
T1 R(A)W(A) T2 W(A) T3
R(A)W(A)
11
View serializable vs. conflict serializable

• Conflict serialiable -gt view serialiable
• So, more general condition
• The reverse is not true

T1 R(A) W(A) T2 W(A) T3 W(A)
T1 R(A),W(A) T2 W(A) T3
W(A)
12
Classes of schedules Venn diagram
13
Deadlocks

• Deadlock Cycle of transactions waiting for locks
  to be released by each other.
• Two ways of dealing with deadlocks
• Deadlock prevention
• Deadlock detection

14
Deadlock Prevention

• Assign priorities based on timestamps.
• The oldest transaction has the highest priority
• Assume Ti wants a lock that Tj holds. Two
  policies are possible
• Wait-Die It Ti has higher priority, Ti waits for
  Tj otherwise Ti aborts
• Wound-wait If Ti has higher priority, Tj aborts
  otherwise Ti waits

15
Deadlock Prevention (contd)

• In wait-die, lower priority transactions never
  wait for higher priority ones
• In wound-wait, higher priority transactions never
  wait for lower priority ones
• Either case, no deadlock cycle
• In both schemes, higher priority transaction is
  never aborted
• If a transaction re-starts, make sure it has its
  original timestamp

16
Deadlock Detection

• Create a waits-for graph
• Nodes are transactions
• There is an edge from Ti to Tj if Ti is waiting
  for Tj to release a lock
• Periodically check for cycles in the waits-for
  graph

17
Deadlock Detection (Continued)

• Example
• T1 S(A), R(A), S(B)
• T2 X(B),W(B) X(C)
• T3 S(C), R(C) X(A)
• T4 X(B)

T1
T2
T1
T2
T4
T3
T4
T3
18
Multiple-Granularity Locking

• Hard to decide what granularity to lock (tuples
  vs. pages vs. tables).
• Locking overhead
• Data containers are nested

contains
19
Solution New Lock Modes, Protocol

• Allow Xacts to lock at each level, but with a
  special protocol using new intention locks

• Before locking an item, Xact must set intention
  locks on all its ancestors.
• For unlock, go from specific to general (i.e.,
  bottom-up).
• SIX mode S IX at the same time.

20
Multiple Granularity Lock Protocol

• Each Xact starts from the root of the hierarchy.
• To get S or X lock on a node, must hold IS or IX
  on parent node.
• To get IX or SIX on a node, must hold IX or SIX
  on parent node.
• Must release locks in bottom-up order.

21
Examples

• T1 scans R, and updates a few tuples
• T1 gets an SIX lock on R, then repeatedly gets an
  S lock on tuples of R, and occasionally upgrades
  to X on the tuples.
• T2 uses an index to read only part of R
• T2 gets an IS lock on R, and repeatedly gets an S
  lock on tuples of R.
• T3 reads all of R
• T3 gets an S lock on R.
• Or, T3 could behave like T2 can use lock
• escalation to decide which.
• Lock escalation dynamically asks for
• coarser-grained locks when too many
• low level locks acquired

22
Optimistic CC

• Locking is a conservative/pessimistic approach in
  which conflicts are prevented.
• Disadvantages
• Lock management overhead.
• Deadlock detection/resolution.
• Lock contention for heavily used objects.
• If conflicts are rare, we might be able to gain
  concurrency by not locking, and instead checking
  for conflicts before Xacts commit.

23
Kung-Robinson Model

• Xacts have three phases
• READ Xacts read from the database, but make
  changes to private copies of objects.
• VALIDATE Check for conflicts. If theres a
  possible conflict, abort and restart
• WRITE Make local copies of changes public.
• If lots of conflicts, cost of repeatedly
  restarting transactions hurts performance

24
Validation

• Test conditions that are sufficient to ensure
  that no conflict occurred.
• Each Xact is assigned a numeric id.
• Just use a timestamp.
• To validate Tj, need to check all committed Ti
  with Ti lt Tj
• One of the following 3 validation conditions must
  hold

25
Validating Tj Test 1

• Ti completes before Tj begins.

Ti
Tj
R
V
W
R
V
W
26
Validating Tj Test 2

• Ti completes before Tj begins its Write phase
• Ti does not write any db object read by Tj

Ti
R
V
W
Tj
R
V
W
27
Validating Tj Test 3

• Ti completes Read phase before Tj does
• Ti does not write any db object read by Tj
• Ti does not write any db object written by Tj

Ti
R
V
W
Tj
R
V
W
28
Timestamp CC

• Idea Determine an equivalent serial order of
  Xacts in advance, e.g., by submission time,
  called the timestamp order
• At execution time, ensure that every pair of
  conflicting actions follows the timestamp
  ordering.
• If this is violated by the next action from Xact
  T, T is aborted and restarted with a new, larger
  timestamp.
• If restarted with same TS, T will fail again!
  Contrast use of timestamps in 2PL for ddlk
  prevention

29
Timestamp CC

• TS(T) the timestamp assigned to Xact T when
  starts (enters the system)
• RTS(O) the read timestamp for object O, set to
  the largest time-stamp of any transaction that
  has executed read(O) successfully.
• WTS(O) the write timestamp for object O, set to
  the largest time-stamp of any transaction that
  has executed write(O) successfully.

30
When Xact T wants to read Object O

• If TS(T) lt WTS(O)
• read(O) of T comes too late, someone with larger
  TS already wrote O
• this is a WR conflict violating the predefined
  timestamp order
• So, abort T and restart it with a new, larger TS
• If TS(T) gt WTS(O)
• Allow T to read O.
• Reset RTS(O) to max(RTS(O), TS(T))

31
When Xact T wants to Write Object O

• If TS(T) lt RTS(O)
• write(O) of T comes too late, someone with larger
  TS already read O
• this is a RW conflict violating the predefined
  timestamp order
• abort and restart T.
• If TS(T) lt WTS(O)
• write(O) of T comes too late, someone with larger
  TS already write O
• this is a WW conflict violating the predefined
  timestamp order
• Naïve approach abort T
• However, write(O) of T should be overwritten
  anyway, so
• Thomas Write Rule We can safely ignore such
  outdated writes need not restart T!
• Else, allow T to write O and set WTS(O) to TS(T)
• why not max(WTS(O), TS(T))?

32
Thomas write rule (watch out text)

• Naïve allows only conflict serializable
  schedules
• Thomas write rule some schedules are allowed,
  which are seriliable but not conflict
  serializable
• The Thomas Write Rule relies on the fact that
  T1's write on object A is never seen by any
  transaction
• T1s write is ignored

T1 T2 R(A) W(A)
Commit W(A) Commit
T1 T2 R(A) W(A) Commit
W(A) Commit
33
Timestamp CC and Recoverability

• Unfortunately, unrecoverable schedules are
  allowed
• Timestamp CC can be modified
  to allow only recoverable schedules

T1 T2 W(A) R(A) W(B)
Commit
34
Summary

• There are several lock-based concurrency control
  schemes (Strict 2PL, 2PL). Conflicts between
  transactions can be detected in the dependency
  graph
• The lock manager keeps track of the locks issued.
  Deadlocks can either be prevented or detected.

35
Summary (Contd.)

• Multiple granularity locking reduces the overhead
  involved in setting locks for nested collections
  of objects (e.g., a file of pages)
• Optimistic CC aims to minimize CC overheads in an
  optimistic environment where reads are common
  and writes are rare.
• Optimistic CC has its own overheads however most
  real systems use locking.

36
Summary (Contd.)

• Timestamp CC is another alternative to 2PL
  allows some serializable schedules that 2PL does
  not
• converse is also true
• Ensuring recoverability with Timestamp CC
  requires ability to block Xacts, which is similar
  to locking.