Transcript Relational Query Optimization

Chapter 17
Concurrency Control
1
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
2
Conflict serialiable vs. serialiable
Conflict serializable -> serializable
 But not vice versa, e.g.

T1: R(A)
W(A)Commit
T2:
W(A)commit
T3:
W(A)Commit

Equivalent to T1 -> T2 -> T3, but not conflict
serializable
3
Example

T1:
T2:
A schedule that is not conflict serializable:
R(A), W(A),
R(A), W(A), R(B), W(B)
R(B), W(B)
A
T1

T2
precedence graph
B
The cycle in the graph reveals the problem.
The output of T1 depends on T2, and viceversa.
4
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 Tj’s actions
 Capturing all conflicts

Theorem: Schedule is conflict serializable if
and only if its precedence graph is acyclic
5
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
6
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.
7
More on 2PL
Relaxation of strict 2PL
 A growing phase: acquiring locks
 A shrinking phase: releases locks
 2PL -> conflict serializable

 Why?

An equivalent serial order of transactions is
given by the order in which transactions enter
their shrinking phase
8
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 -> 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?
9
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
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)
S is view serializable if it is view equivalent to some
serial schedule
10
View serializable vs. conflict serializable
Conflict serialiable -> 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)
11
Classes of schedules: Venn diagram
12
Deadlocks
Deadlock: Cycle of transactions waiting for
locks to be released by each other.
 Two ways of dealing with deadlocks:



Deadlock prevention
Deadlock detection
13
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
14
Deadlock Prevention (cont’d)
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

15
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
16
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
17
Multiple-Granularity Locking
Hard to decide what granularity to lock
(tuples vs. pages vs. tables).
 Locking overhead
 Data “containers” are nested:

Database
contains
Tables
Pages
Tuples
18
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.

--
IS IX S
X





IS 



IX 





--
S
X

19
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.

20
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
--
IS IX S
X





IS 



IX 





--
S
X

21
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.

22
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 there’s a
possible conflict, abort and restart
 WRITE: Make local copies of changes public.
 If
lots of conflicts, cost of repeatedly
restarting transactions hurts performance
23
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 < Tj
 One of the following 3 validation conditions
must hold

24
Validating Tj: Test 1

Ti completes before Tj begins.
Ti
R
V
Tj
W
R
V
W
25
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
R
V
W
Tj
26
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
R
W
V
W
Tj
27
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
28
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.

29
When Xact T wants to read Object O

If TS(T) < 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) > WTS(O):
 Allow T to read O.
 Reset RTS(O) to max(RTS(O), TS(T))
30
When Xact T wants to Write Object O

If TS(T) < 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) < 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))?
31
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
T1
R(A)
T2
W(A)
Commit
W(A)
Commit

T1
R(A)
W(A)
Commit
T2
W(A)
Commit
The Thomas Write Rule relies on the fact that T1's write on
object A is never seen by any transaction
 T1’s write is ignored
32
Timestamp CC and Recoverability
Unfortunately, unrecoverable schedules are
allowed
 Timestamp CC can be modified
to allow only recoverable schedules

T1
W(A)
T2
R(A)
W(B)
Commit
33
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.

34
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.
35
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.
36