Concurrency

1
Concurrency
2
Busy, busy, busy...

• In production environments, it is unlikely that
  we can limit our system to just one user at a
  time.
• Consequently, it is possible for multiple queries
  or transactions to be submitted at approximately
  the same time.
• If all of the queries were very small (i.e., in
  terms of time), we could probably just execute
  them on a first-come-first-served basis.
• However, many queries are both complex and time
  consuming.
• Executing these queries would make other queries
  wait a long time for a chance to execute.
• So, in practice, the DBMS may be running many
  different transactions at about the same time.

3
Concurrent Transactions

• Even when there is no failure, several
  transactions can interact to turn a
• consistent state
• into an
• inconsistent state.

4
Example

• Assume A B is a constraint required for
  consistency.

T1 T2
READ(A, t) READ(A, s)
t t 100 s s 2
WRITE(A, t) WRITE(A, s)
READ(B, t) READ(B, s)
T t 100 S s 2
WRITE(B, t) WRITE(B, s)

• Note that we omit OUTPUT steps for succinctness
  they always come at the end. We deal only with
  Reads and Writes in the main memory buffers.
• T1 and T2 individually preserve DB consistency.

5
An Acceptable Schedule S1

• Assume initially A B 25. Here is one way to
  execute (S1 T1 T2) so they do not interfere.

T1 T2 A B
READ(A, t) 25 25
t t 100
WRITE(A, t) 125
READ(B, t)
T t 100
WRITE(B, t) 125
READ(A, s)
s s 2
WRITE(A, s) 250
READ(B, s)
S s 2
WRITE(B, s) 250
6
Another Acceptable Schedule S2

• Here, transactions are executed as (S2T2 T1).
  The result is different, but consistency is
  maintained.

T1 T2 A B
READ(A, s) 25 25
s s 2
WRITE(A, s) 50
READ(B, s)
S s 2
WRITE(B, s) 50
READ(A, t)
t t 100
WRITE(A, t) 150
READ(B, t)
T t 100
WRITE(B, t) 150
7
Interleaving Doesn't Necessarily Hurt (S3)
T1 T2 A B
READ(A, t) 25 25
t t 100
WRITE(A, t) 125
READ(A, s)
s s 2
WRITE(A, s) 250
READ(B, t)
T t 100
WRITE(B, t) 125
READ(B, s)
S s 2
WRITE(B, s) 250
8
But Then Again, It Might!
T1 T2 A B
READ(A, t) 25 25
t t 100
WRITE(A, t) 125
READ(A, s)
s s 2
WRITE(A, s) 250
READ(B, s)
S s 2
WRITE(B, s) 50
READ(B, t)
T t 100
WRITE(B, t) 150
9
Semantics of transactions is also important.
10
We Need a Simpler Model

• Coincidence never happens
• Focus on reads and writes only.
• rT(X) denotes T reads X
• wT(X) denotes T writes X
• Transaction is a sequence of r and w actions on
  database elements.
• If transactions are T1,,Tk, then we use ri and
  wi, instead of rTi and wTi
• Schedule is a sequence of r and w actions
  performed by a collection of transactions.
• Serial Schedule All actions for each transaction
  are consecutive.
• r1(A) w1(A) r1(B) w1(B) r2(A) w2(A)
  r2(B) w2(B)
• Serializable Schedule A schedule whose effect
  is equivalent to that of some serial schedule.

11
Conflicts

• Suppose for fixed DB elements X and Y,
• ri(X) rj(Y) is part of a schedule, and we flip
  the order of these operations. Then ri(X) rj(Y)
  rj(Y) ri(X)
• This holds always (even when XY)
• We can flip ri(X) wj(Y) as long as X?Y
• However, ri(X) wj (X) ? wj(X) ri (X)
• In the RHS, Ti reads the value of X written by
  Tj, whereas it is not so in the LHS.
• We can flip wi(X) wj(Y) provided X?Y
• However, wi(X) wj(X) ? wj(X) wi(X)
• The final value of X may be different depending
  on which write occurs last.

12
Conflicts (Contd)

• There is a conflict if one of these two
  conditions hold.
• A read and a write of the same X, or
• Two writes of the same X
• Such actions conflict in general and may not be
  swapped in order.
• All other events (reads/writes) may be swapped
  without changing the effect of the schedule (on
  the DB).
• Definitions
• Two scheduless are conflict-equivalent if they
  can be converted into the other by a series of
  non-conflicting swaps of adjacent elements
• A schedule is conflict-serializable if it can be
  converted into a serializable schedule in the
  same way

13
Example
r1(A) w1(A) r2(A) w2(A) r1(B) w1(B) r2(B)
w2(B)
r1(A) w1(A) r2(A) w2(A) r1(B) w1(B) r2(B)
w2(B) r1(A) w1(A) r2(A) r1(B) w2(A) w1(B)
r2(B) w2(B) r1(A) w1(A) r1(B) r2(A) w2(A)
w1(B) r2(B) w2(B) r1(A) w1(A) r1(B) r2(A)
w1(B) w2(A) r2(B) w2(B) r1(A) w1(A) r1(B)
w1(B) r2(A)w2(A) r2(B) w2(B)
14
Conflict-serializability

• Sufficient condition for serializability but not
  necessary.
• Example
• S1 w1(Y) w1(X) w2(Y) w2(X) w3(X) -- This is
  serial
• S2 w1(Y) w2(Y) w2(X) w1(X) w3(X)
• S2 isnt conflict serializable, but it is
  serializable. It has the same effect as S1.
• Intuitively, the values of X written by T1 and T2
  have no effect, since T3 overwrites them.

15
Serializability/precedence Graphs

• Non-swappable pairs of actions represent
  potential conflicts between transactions.
• The existence of non-swappable actions enforces
  an ordering on the transactions that house these
  actions.
• Nodes transactions T1,,Tk
• Arcs There is an arc from Ti to Tj if they have
  conflict access to the same database element X
  and Ti is first in written Ti ltS Tj.

16
Precedence graphs
r2(A) r1(B) w2(A) r3(A) w1(B) w3(A) r2(B)
w2(B)

• Note the following
• w1(B) ltS r2(B)
• r2(A) ltS w3(A)
• These are conflicts since they contain a
  read/write on the same element
• They cannot be swapped. Therefore T1 lt T2 lt T3

r2(A) r1(B) w2(A) r2(B) r3(A) w1(B) w3(A)
w2(B)

• Note the following
• r1(B) ltS w2(B)
• w2(A) ltS w3(A)
• r2(B) ltS w1(B)
• Here, we have T1 lt T2 lt T3, but we also have T2 lt
  T1

16
17

• If there is a cycle in the graph
• Then, there is no serial schedule which is
  conflictequivalent to S.
• Each arc represents a requirement on the order of
  transactions in a conflict equivalent serial
  schedule.
• A cycle puts too many requirements on any linear
  order of transactions.
• If there is no cycle in the graph
• Then any topological order of the graph suggests
  a conflictequivalent schedule.

18
Why the Precedence-Graph Test Works?

• Idea if the precedence graph is acyclic, then we
  can swap actions to form a serial schedule.
• Proof By induction on n, number of
  transactions.
• Basis n 1. That is, ST1 then S is already
  serial.
• Induction ST1,T2,,Tn. Given that the
  precedence graph is acyclic, there exists Ti in S
  such that there is no Tj in S that Ti depends on.
  
• We swap all actions of Ti to the front (of S).
• (Actions of Ti)(Actions of the other n-1
  transactions)
• The tail is a precedence graph that is the same
  as the original without Ti, i.e. it has n-1
  nodes.
• ? By the induction hypothesis, we can reorder the
  actions of the other transactions to turn it into
  a serial schedule

19
Schedulers

• A scheduler takes requests from transactions for
  reads and writes, and decides if it is OK to
  allow them to operate on DB or defer them until
  it is safe to do so.
• Ideal a scheduler forwards a request iff it
  cannot result in a violation of serializability.
• Too hard to decide this in real time.
• Real a scheduler forwards a request if it cannot
  result in a violation of conflictserializability.

20
Lock Actions

• Before reading or writing an element X, a
  transaction Ti requests a lock on X from the
  scheduler.
• The scheduler can either grant the lock to Ti or
  make Ti wait for the lock.
• If granted, Ti should eventually unlock (release)
  the lock on X.
• Shorthands
• li(X) transaction Ti requests a lock on X
• ui(X) Ti unlocks/releases the lock on X

21
Validity of Locks

• The use of locks must be proper in 2 senses
• Consistency of Transactions
• Read or write X only when hold a lock on X.
• ri(X) or wi(X) must be preceded by some li(X)
  with no intervening ui(X).
• If Ti locks X, Ti must eventually unlock X.
• Every li(X) must be followed by ui(X).
• Legality of Schedules
• Two transactions may not have locked the same
  element X without one having first released the
  lock.
• A schedule with li(X) cannot have another lj(X)
  until ui(X) appears in between.

22
Legal Schedule Doesnt Mean Serializable
T1 T2 A B
25 25
l1(A) r1(A)
A A 100
w1(A)u1(A) 125
l2(A)r2(A)
A A 2
w2(A)u2(A) 250
l2(B)r2(B)
B B 2
w2(B)u2(B) 50
l1(B)r1(B)
B B 100
w1(B)u1(B) 150
Consistency constraint required for this example
AB
23
Two Phase Locking
There is a simple condition, which guarantees
conflict-serializability In every transaction,
all lock requests (phase 1) precede all unlock
requests (phase 2).
T1 T2 A B
25 25
l1(A) r1(A)
A A 100
w1(A) l1(B)u1(A) 125
l2(A)r2(A)
A A 2
w2(A) 250
l2(B) Denied
r1(B)
B B 100 125
w1(B)u1(B)
l2(B)u2(A)r2(B)
B B 2
w2(B)u2(B) 250
24
Why 2PL Works?

• Precisely a legal schedule S of 2PL transactions
  is conflictserializable.
• Proof is an induction on n, the number of
  transactions.
• Remember, conflicts involve only read/write
  actions, not locks, but the legality of the
  transaction requires that the r/w's be consistent
  with the l/u's.

25
Why 2PL Works (Contd)

• Basis if n1, then ST1, and hence S is
  conflict-serializable.
• Induction ST1,,Tn. Find the first
  transaction, say Ti, to perform an unlock action,
  say ui(X).
• We show that the r/w actions of Ti can be moved
  to the front of the other transactions without
  conflict.
• Consider some action such as wi(Y). Can it be
  preceded by some conflicting action wj(Y) or
  rj(Y)? In such a case we cannot swap them.
• If so, then uj(Y) and li(Y) must intervene, as
• wj(Y)...uj(Y)...li(Y)...wi(Y).
• Since Ti is the first to unlock, ui(X) appears
  before uj(Y).
• But then li(Y) appears after ui(X), contradicting
  2PL.
• Conclusion wi(Y) can slide forward in the
  schedule without conflict similar argument for a
  ri(Y) action.