Title: Chapter 7 Isolation Concepts
1Chapter 7 Isolation Concepts
2Isolation Concepts (1)
- The system state consists of objects related in
certain ways. These relationships are best
thought of as invariants about the
objects.Examples - Account balances must be positive.
- All managers must be employees and so must appear
in the EMPLOYEE table. - The system state is said to be consistent if it
satisfies all these invariants.
3Isolation Concepts (2)
- Often, the state must be made temporarily
inconsistent while it is being transformed (by a
transaction) to a new, consistent state. - Concurrent transaction execution must be
controlled so that correct programs do not
malfunction. - Systems try to control concurrency by providing
the appearance of isolation (e.g., using locking).
4The Dependency Model of Isolation
- Ii ? the set of objects read by transaction Ti
(its input)Oi ? the set of objects written by
Ti (its output)Ti ? the set of transactions. - The set of transactions Ti can run in parallel
with no concurrency anomalies if their outputs
are disjoint from one anothers inputs and
outputsOi ? (Ij ? Oj) ? for all i ? j - The ACID property assumes that the transaction
knows what it is doing to its datagt If a
transaction runs in isolation, it will
correctly transform the system state.
5Static Allocation
- The transaction scheduler compares the new
transactions needs to all the running
transactions. - If there is a conflict, initiation of the new
transaction is delayed until the conflicting
transactions has completed. - Problem It is difficult to determine the inputs
and outputs of a transaction before it runs. - Example Since a banking transaction potentially
updates any record of the Account table, static
allocation would probably reserve the entire
table.
6Dynamic allocation
- Each transaction is viewed as a sequence of
actions (rather than as an input-output set.) - When an action accesses a particular object, the
object is dynamically allocated to that
transaction. - Advantage more concurrency
7Transaction Dependencies (1)
- Transactions are sequences of actions operating
on objects. Objects go through a sequence of
versions as they are written by these actions - Nothing ever changes rather, new versions of
objects are created. - Dependencies
- If a transaction reads an object, the transaction
depends on that object version. - If the transaction writes an object, the
resulting object version depends on the writing
transaction.
8Transaction Dependencies (2)
-
- Note Only write actions create versions and
dependencies.
a dependency graph
9Isolation Theorems (1)
- The main result of isolation theorems is that any
dependency graph without cycles implies an
isolated execution of the transactions. -
The transactions can be
topologically sorted to
make an equivalent
execution history in which
each
transaction ran
serially, one completing
before the next began.
T1
T1
T2
T2
T3
T4
T3
T4
10Isolation Theorems (2)
-
There is no topological
order
for this set of transactions.
gt concurrency anomalies.
T1
T2
T3
11Three Transaction Dependencies (1)
- The various ways isolation is violated are
characterized by the related dependency cycles.
Cycles take one of three generic forms. - Lost update caused by WRITE-gtWRITE
dependenciesT2 READ lt o,1 gt
T1s writeT1 WRITE lt o,2 gt
is ignored byT2
WRITE lt o,3 gt
T2 - Dirty read caused by WRITE-gtREAD dependencies
T2 WRITE lt o,2 gtT1
READ lt o,2 gtT2 WRITE lt o,3 gtT1 reads an
object previously written by T2, then T2 makes
further changes to the object.
12Three Transaction Dependencies (2)
- Unrepeatable read caused by READ-gtWRITE
dependencies -
T1 READ lt o,1 gtT2 WRITE lt o,2 gtT1
READ lt o,2 gt - T1 reads an object twice, once before T2 updates
it and once after committed T2 has updated it
(the first version is not repeatable) - Note Preventing such anomalies gives the effect
of running transactions in isolation.
13Isolation (1)
- Definition The outputs of a transaction are said
to be uncommitted, or dirty, if the transaction
has not yet issued COMMIT WORK. - Transaction T is isolated from other transactions
if - T does not overwrite dirty data of other
transactions. - Ts writes are neither read nor written by other
transactions until COMMIT WORK - T does not read dirty data from other
transactions. - Other transactions do not write any data read by
T before T completes.
14Isolation (2)
- Effects
- (1) (2) preclude lost updates
- (3) isolates the transaction from dirty reads
- (4) prevents unrepeatable reads.
- Properties
- Each transaction reads consistent input state.
- Any execution of the system is equivalent to some
serial execution (no concurrency anomalies) - None of the updates of committed transactions can
be lost, and aborted transactions can be rerun.
15Notations
- S lt a, b, c gt a sequenceS S?
concatenation of two sequencesSi the ith
element of sequence S - lt Si predicate (Si) gt a subsequence of S
- The system state, S, consists of an infinite set
of named objects, each with a value. S is denoted
lt name, value gt - The system supports the following actions on
these objects.
READ WRITE XLOCK (exclusive lock) SLOCK (share
lock) UNLOCK
BEGIN COMMIT ROLLBACK
16Simple Transactions (1)
- To simplify the transaction model, BEGIN, COMMIT,
and ROLLBACK are defined in terms of the other
actions, so that only READ, WRITE, LOCK and
UNLOCK actions remain. - Examples
17Simple Transactions (2)
- The simple transactions equivalent to T and
T? - Idea The commit action simply releases
locks, while the ROLLBACK action must first undo
all changes to the objects the transaction wrote
and then issue the unlock statements.
18Well-Formed and 2-Phase Transactions (1)
- A transaction is said to be well-formed if
- All its READ, WRITE, and UNLOCK actions are
covered by locks (preceded by locking of the
corresponding object), and - Each lock action is eventually followed by a
corresponding UNLOCK action. - - READ
and UNLOCK actions
on o3 are
not covered by an
SLOCK or XLOCK. - -
WRITE action on o1 is not
covered by an XLOCK
19Well-Formed and 2-Phase Transactions (2)
- A transaction is defined as 2-phase if all its
lock actions precede all its UNLOCK actions. - Growing phase acquiring locks
- Shrinking Phase releasing locks
- The previous example is a 2-phase.
20Transaction Histories
- Any sequence-preserving merge of the actions of a
set of transactions into a single sequence is
called a history for the set of transactions. - A history lists the order in which actions were
successfully completed. - The simplest histories first run all the actions
of one transaction, then run all the actions of
another to completion, and so on. Such
one-transaction-at-a-time histories are called
serial histories. - Note serial histories have no concurrency-induced
inconsistency and no transaction sees dirty
data. - An initial state and a history completely define
the systems behavior.
21Legal Histories (1)
- Locking constrains the set of allowed histories.
- Histories that obey the locking constraints are
called Legal.
22Legal Histories (2)
-
- Note Histories are not constructed, they are a
byproduct of the system behavior. For example,
locking systems only produce legal behavior.
23Dependency Graph (1)
- lt t, a, o gt a step of the history. It denotes
an action a by transaction t
on object o. - V(o, k) The version of an object o at step k of
a history.At step k of
history H, object o has a version equal to the
number of writes of that object before this
step.V(o, k) lt t, aj, o gt?H jltk and aj
WRITE
24Dependency Graph (2)
- Each history H for a set of transactions Ti
defines a ternary dependency relation DEP(H) lt
T, lt o, V(o,j) gt, T? gt ? DEP(H)if a1 is a WRITE
and a2 is a WRITE, or a1 is a WRITE and a2 is
a READ, or a1 is a READ and a2 is a
WRITE.where a1 and a2 are actions performed on
the object o by T and T?, respectively.Note
This definition captures the WRITE ?WRITE,
WRITE?READ, and READ?WRITE dependencies. - The dependency relation for a history defines a
directed dependency graph transactions are the
nodes, and object versions label the edges.lt T,
lt o, j gt, T?gt ? DEP(H) ? the graph has an edge
from node T to node T? labeled by lt o, j gt
25Equivalent and Isolated Histories
- Two histories for the same set of transactions
are equivalent if they have the same dependency
relation (DEP(H) DEP(H?)). - A history is said to be isolated if it is
equivalent to a serial history. - The dependencies of a history define a time order
of the transactions. This ordering is denoted by
the symbol ltltlt. - Tltltlt T? if there is a path in the dependency
graph from transaction T to transaction T?TltltltH
T? if lt T, o, T? gt ? DEP(H), or
TltltltHT? and lt T?, o, T? gt ? DEP(H) - All equivalent histories define the same
ordering. - BEFORE(T) T? T?ltltltTAFTER(T) T?
TltltltT?T? is called a wormhole transaction if
T?? BEFORE(T) ? AFTER(T)
26Wormholes Are Not Isolated (1)
- TltltltT?ltltltT indicates a cycle in the dependency
graph. - Since a dependency graph with cycles is not
equivalent to any serial history, a history with
wormholes is not isolated.
27Wormholes Are Not Isolated (2)
-
- No wormhole
T3,T4,T5,T6 transaction
are wormhole
transaction ?No cycles!
?Cycles!Note Two transactions not
related by ltltlt maybe arbitrarily
scheduled with respect to each
other.Example T2 and T4
28Theorems
- Wormhole theorem A history is isolated if, and
only if, it has no wormhole transactions. - Locking theorem If all transactions are
well-formed and two-phase, then any legal history
will be isolated. - Locking theorem (converse) If a transaction is
not well-formed or is not two-phase, then it is
possible to write another transaction such that
the resulting pair is a wormhole. - Rollback theorem An update transaction that does
an UNLOCK and then a ROLLBACK is not two-phase.
29Wormhole Theorem Proof (1)
- A history is isolated if, and only if, it has no
wormhole transactions. - Proof1. Isolation ? no wormholes This proof is
by contradiction. - Suppose H is an isolated history of Ti i
1,.., n ? H is equivalent to some serial
history SH T1 T2 Tn - Suppose that H has a wormholeTltltlt T?ltltlt
TjltltltHTi ltltlt ltltltT???ltltltT
? Let i be the minimum transaction
index in this wormhole. - By the minimality of i, Tj comes completely AFTER
Ti in SH. ? TjltltltSH Ti is impossible. - Since ltltltH and ltltltSH are equivalent, Tj ltltltH Ti
is not possible either ! ? H has no wormholes.
30Wormhole Theorem Proof (2)
- 2. No wormholes ? isolated This proof is by
induction on the number of transactions, n, that
appear in history H. - (1) If n lt 2, then any history is a serial
history ? any history is isolated. - (2) Suppose the induction hypothesis is true for
n-1 transactions - (3) Consider some history H of n transactions
that has no wormholes.
31Wormhole Theorem Proof (3)
- (a) Pick a transaction T and construct the
sequence S lt T, T?, gt such that TltltltH
T? ltltltH Since H has no wormhole, S must
be finite (otherwise, some T?? must appear
in S more than once)(b) Let T be the last
transaction in S. Consider the history
H? lt lt ti, ai, oi gt ? H ti ? T gt ?
DEP(H?) lt T, lto, i gt, T? gt ? DEP(H) T? ? T
(c) H has no wormholes ? H? has no wormholes
? H? is isolated (due to (2)) ? H?
has an equivalent serial history SH?
T1 T2 Tn-1 (d) DEP(SH) DEP(SH?) ?
lt T?, lto, igt, T gt ? DEP(H)
DEP(H?) ? lt T?, lto, igt, T gt ? DEP(H)
lt T, lt o, i gt, T? gt ?
DEP(H) T? ? T ? lt T?, lt o, i gt,
T gt ? DEP(H) DEP(H). Thus, the
induction step is proven.
32Serializability Theorem (1)
- The Wormhole Theorem is the basic result from
which all the others follow. - The Wormhole Theorem can be stated in many
different ways. - Serializability Theorem A history H is isolated
(also called a serializable schedule or a
consistent schedule) if, and only if, ltltltH
implies a partial order of the transactions
(i.e., no cycles).
33Serializability Theorem (2)
- Note If there had been a cycle, it would have
produced dependency graph arrows pointing up in
the history (back in time).
34Locking Theorem Lemma
- SHRINK(T) ? the index of the first unlock step of
T is the history H. - Lemma Let T and T? be two well-formed and
2-phase transactions of a legal history H.
TltltltH T? ? SHRINK(T) lt SHRINK(T?) - Proof
- TltltltH T? ? there are some steps i lt j of H such
that - Hi lt T, a, o gt, Hj lt T?, a?, o gt, and
- a and a? are conflicting actions.
- H is legal ? H does not grant conflicting locks
to two different
transactions at the same time. ?
there must be k1 and k2 such that i lt k1 lt k2 lt
j, Hk1 lt T, UNLOCK, o gt,
and Hk2 lt T, LOCK, o
gt. - T and T? are 2-phase ? SHRINK(T) ? k1 lt k2 lt
SHRINK(T?)
35Locking Theorem Proof
- Wormhole Theorem A history is isolated if, and
only if, it has no wormhole transactions. - Lemma TltltltHT? ? SHRINK(T) lt SHRINK(T?)
- Locking Theorem If all transactions are
well-formed and two-phase, then any legal history
will be isolated (locking correctly done provides
the isolation). - Proof This proof is by contradiction.
- Suppose that H is not isolated.Wormhole Theorem
? there must be a sequence lt T1,, Tn gt such
that T1 ltltlt T2 ltltlt ltltlt Tn ltltlt T1 - Lemma ? SHRINK(T1) lt SHRINK(T2) lt lt SHRINK(T1)
is a contradiction ? H cannot have any wormholes.
36Degenerate Transactions
- A transaction is degenerate if it does any of the
following - Useless lock Locks something that it never reads
or writes - Orphan unlock Unlocks something that it has not
locked. - Orphan lock Ends without unlocking some of its
locks (impossible if it ends with COMMIT or
ROLLBACK).
37Locking Theorem (Converse) Proof
- Locking Theorem (converse) If a transaction is
not well-formed or not two-phase, then it is
possible to write another transaction such that
the resulting pair has a legal but not isolated
history (unless the transaction is degenerate). - Proof1. Not well-formed ? legal but not
isolated - Suppose that transaction T is not well formed and
not degenerate T lt lt T,a1, c gt, , ltT, ak, o
gt, gt ak is a READ or WRITE action that is not
covered by a lock. The READ case is proved here
the WRITE case is similar.
38Locking Theorem (Converse) Proof (2)
- We can write a transaction T? T? lt lt T?,
XLOCK, o gt, lt T?, WRITE, o gt, lt T?, WRITE, o gt,
lt T?, UNLOCK, o gt gt ? T? is 2-phase and
well-formed. - Consider the historyH ltTi iltk gt lt
T?1, T?2, Tk, T?3, T?4 gt lt Ti i
gt k gt ? H is legal since no conflicting locks are
granted on object o at any point of the history. - H implies that lt T?, lt o, j gt, T gt and lt T, lt o,
j gt, T? gt must be in DEP(H) for some j.? Tltltlt
T?ltltltT ? T is a wormhole.? H is not isolated
(wormhole theorem).
39Locking Theorem (Converse) Proof (3)
- 2. Not two-phase ? legal but not isolated
- Suppose flat transaction T is not two-phaseT
lt lt T, a1, o gt, .., lt T, aj, o1 gt, .., lt T, ak,
o2 gt, ..gt
? ?
UNLOCK SLOCK or XLOCK - We can write a transaction T? T? lt lt T?,
XLOCK, o1 gt, lt T?, XLOCK, o2 gt, lt T?, WRITE, o1
gt, lt T?, WRITE, o2 gt, lt T?, UNLOCK, o1 gt, lt T?,
UNLOCK, o2 gt? T? is two-phase and well-formed.
40Locking Theorem (Converse) Proof (4)
- Consider the historyH lt Ti i ? j gt T?
lt Ti i gt j gt? H is legal - Since T is not degenerate, it- must read or
write o1 before the UNLOCK at step j, - - must read or write o2 after the LOCK at step
k. ? lt T, lt o1, m gt, T? gt and lt T?, lt o2, n gt, T
gt must be in DEP(H).? TltltltH T?ltltltHT? T
is a wormhole in H.? H is not an isolated
history (according to the wormhole theorem).
41Locking Theorem
- It would be nice if the Locking Theorem were
stronger and readA history is isolated if, and
only if, every transaction is well-formed and
two-phase.Example A serial history is isolated
even if its transactions are not well-formed or
two-phase. (In fact, we dont even need locking
in this case). - The best the Locking Theorem can say is,If you
break the rules, then someone else may come along
next week and get bad info from you or give you
bad information. - The lack of the only-if part created a cottage
industry that searched for interesting
refinements of the theorem.
42Rollback Theorem
- Rollback Theorem A transaction that does an
UNLOCK of an XLOCK and then does a ROLLBACK is
not well-formed and, consequently, is a potential
wormhole (unless the transaction is degenerate). - Proof
- T acquires XLOCK on object o and is not
degenerate ? T writes o - The ROLLBACK action has to WRITE (UNDO) object o
again. - The second WRITE is not covered by a lock ? the
transaction is not well-formed? the transaction
is a potential wormhole. - It might be argued that the ROLLBACK should
reacquire XLOCK on all such unlocked
objects.However, that would violate the 2-phase
restriction and, in doing do, introduce another
potential wormhole.
43Summary of the Isolation Theorems
- The implication of these theorems is that a
transaction should1. Be well-formed it should
cover all actions with locks2. Set XLOCK on any
data it writes.3. Be 2-phase it should not
release locks until it knows it needs no more
locks.4. Hold XLOCKs until COMMIT or ROLLBACK. - If these rules are followed, the execution
history will give each transaction the illusion
it is running in isolation. - On the other hand, if any of the above rules are
violated, then the transactions may have
inconsistent inputs or outputs.
44Isolation In Practice
- Most systems do not provide true isolation.
Implementers make a compromise between
correctness and performance.1. Typical system
default is to ignore READ ? WRITE
dependencies. ? cursor stability in most SQL
systems. ? users must request true isolation
as an option.2. Many systems also allow the
option to ignore WRITE ? READ isolation.
? browse access option allows queries to scan the
database without acquiring locks and
without delaying other transactions.
45Degrees of Isolation (1)
- Degree 3 This is true isolation.
- Lock protocol two-phase and well-formed.
- Degree 2 A 2? isolated transaction has no lost
updates and no dirty reads. - Lock protocol two-phase with respect to XLOCKs
and well-formed. - Common name cursor stability / ignore READ ?
WRITE /
46Degrees of Isolation (2)
- Degree 1 A 1? isolated transaction has no lost
updates. - Lock protocol two-phase with respect to XLOCKs
and well-formed with
respect to writes - Common name browse mode / ignore READ ? WRITE
WRITE ? READ / - Degree 0 A 0? isolated transaction does not
overwrite another transactions dirty data if the
other transaction is 1? or greater. - Lock protocol well-formed with respect to writes
- Common name anarchy / ignore all dependencies /
47Mixing Isolation Levels
- A 1? transactions inputs are 1? isolated? the
transaction may get dirty reads.? such data may
not satisfy the system consistency
constraints.? If the transaction uses dirty
reads to update the database, then other
transactions will see inconsistent values
and may malfunction.? such transaction systems
assume that the 1? transaction knows what
it is doing. - Most systems reserve 1? isolation (browse mode)
for read-only transactions.
48SQL systems (1)
- The lower degrees of isolation allow transactions
to acquire fewer locks or to hold the locks for a
shorter time. - SQL systems implement a slightly better form of
isolation than pure 2?. - Cursor stability To prevent lost updates, most
SQL systems keep a shared lock on the record
currently addressed by a cursor.
49SQL systems (2)
50Phantoms (1)
- Select from EMPwhere eyes blue and
hair red - Case1 locks all the individual records that
satisfy the where clause (predicate) - Problem What is to prevent someone else from
inserting a new, blue-eyed, red-haired EMP during
the execution of the above query.
51Phantoms (2)
-
---- first match
- Just
inserted?
-
current -
match - The record corresponding to Casey is a phantom.
- Case 2 locks the whole EMP table
- Problem significantly reduces concurrency.
- This solution is unacceptable
Name Eyes Hair
Andrew Blue Red
Casey Blue Red
Jane Blue Red
Kathleen Blue Red
52Predicate Locks (1)
- Rather than locking an individual object, a
predicate lock can specify a subset of the
database to which the lock applies. - Example 1 lt t, slock, emp.eyes blue ?
emp.hair red gt
transaction t requests
slocks for all the
emp tuples that satisfy this predicate - lt t, mode, p gt and lt t?, mode?, p? gt are
compatible if t t?, or mode mode?
slock, or ( p AND p? ) FALSE / t and t?
access a different
subset of the database /
53Predicate Locks (2)
- Example 2lt t, slock, emp.eyes blue ?
emp.hair red gt
- lt t, xlock, emp.eyes blue gt
- lt t?, slock, emp.eyes blue gt
- lt t?, xlock, emp.eyes blue gt
- Predicate lock is an elegant solution for
phantoms.
compatible
compatible
incompatible
54Predicate Locks Scheduling (1)
- A predicate lock system can schedule transactions
dynamically as follows - Each time a transaction requests a predicate
lock, the system compares the lock request with
the other granted and waiting predicate locks. - If the lock request is compatible with all other
requests, it is added to the granted set and
granted immediately.
55Predicate Locks Scheduling (2)
- Otherwise, it is marked as waiting and added to
the waiting list. - Waiting predicates are considered in turn. A
waiting predicate is granted the lock if it
becomes compatible with all the current granted
and waiting predicates. - This scheme is very expensive ? not used in
practice.
56Granular Locks (1)
- Predicate locks (predicate comparisons) are too
expensive. Simplified predicate locks, called
granular locks, are typically used in practice. - In granular locking, the system picks a fixed set
of predicates (in essence, to pre-compute the
predicate locks) - Each predicate is represented by a name, and
- The predicates are organized into a lock
hierarchy.
57Granular Locks (2)
- Example
- A finest granularity lockSites Ripa ? Files
Phone ? Records Giovanna - To lock all the data in Ripa (coarse grain)Sites
Ripa.
58Intent Lock Mode (1)
- When a transaction sets a lock on a granule at a
given level of the granularity hierarchy, it is
implicitly locking in the same mode all its
descendants. - Example when a lock is set on a relation, all
tuples of this relation are
locked in the same way. - It is necessary to guarantee that after setting a
basic lock (SLOCK, XLOCK) on a given vertex of
the granularity hierarchy by a transaction T no
other transaction T? can obtain an incompatible
lock on any of its predecessors.
59Intent Lock Mode (2)
- There must be a way to prevent T? from
setting Xlock on Ripa. - A solution to the above problem Intention locks.
- Setting an intention lock at a given level
implies basic locking at all lower levels. - The modes of intention locks depend directly on
the modes of the basic lock.
Transaction T locks the file phone.
Sometime later, T? XLOCKs Ripa
60Intention Locking
-
- Setting a record-granularity SLOCK on Giovanna
consists of the following sequence of lock
requests. - LOCK IN DATABASE IN INTENT MODELOCK
NODE RIPA IN INTENT MODELOCK FILE
PHONE IN INTENT MODELOCK NAME
GIOVANNA IN SHARED MODE - Intent mode represents the intention to set locks
at a finer granularity.
61Intention Locks (1)
- IX Intent to set shared or exclusive locks at
finer granularity. - IS Intent to set shared locks at finer
granularity. - SIX A shared lock on a given granule with intent
to set finer-granularity shared or
exclusive locks.
62Intention Locks (2)
Granted ModeRequest Mode None IS IX S SIX Update X
IS - -
IX - - - -
S - - - -
SIX - - - - -
Update - - - - -
X - - - - - -
- Update lock will be discussed next.
63Update Locks (1)
- A typical construct in programs is to read a
record and rewrite some of its fields. - If two different transactions both read an object
at nearly the same time, then they both request
it in exclusive mode. They creates a deadlock
cycle. - A study of system R showed that virtually all
deadlocks in that system were of this form.
64Update Locks (2)
- A solution use UPDATE lock mode.
- When an SQL cursor is declared with FOR
UPDATE,record locks acquired by the cursor are
acquired in UPDATE mode. - If the cursor updates the datathen the update
mode lock is converted to an XLOCK,
which is held to transaction commit.else the
update mode lock is downgraded to shared
mode
65Update Locks Compatibility (1)
Granted ModeRequest Mode IS IX S SIX Update X
IS -
IX -
S -
SIX -
Update - - - - -
X -
66Update Locks Compatibility (2)
- The compatibility matrix for update mode locks is
asymmetric update is compatible with share, but
share is not compatible with update.? This
allows the updater to read but delays other
transaction reader and updaters, since the
transaction is about to update.
67Granular Locking Protocol (1)
- Acquire locks from root to leaf
- Release locks from leaf to root.
- To acquire an S mode or IS mode lock on a
non-root node, its predecessor must be held in
IS mode or higher (one of IS, IX, S, SIX, U, X) - To acquire an X, U, SIX, or IX mode lock on a
non-root node, its predecessor must be held in IX
mode or higher (one of IX, SIX, U, X)
68Granular Locking Protocol (2)
- Example
- Transaction T3 wants to lock the entire database
in shared mode. It is waiting for T1 and T2 to
complete. - Transaction T2 has record-granularity locks on
File-2, and is waiting for T1 to release an XLOCK
on one of these records.
69Key-Range Locking
- A key range is denoted by the first key in the
range and by the first key after the range. - Example R, S) ? all k, such that R ? k lt S.
- A fixed number of key ranges is predetermined
- Example A, N), N, X), X, ?)
- Locking protocol
- A transaction acquires a lock on the key range
before it accesses records in the key range. - Since key ranges are granules within files,
intent mode locks on the file (and on any
coarser-granularity objects) must first be
acquired in accordance with the granular lock
protocol. - Why does this work?
- If the key ranges are thought of as predicates,
they are simply predicate locks covering the
records.? key-range locking also prevents
phantoms.
70Dynamic Key-Range Locks (1)
- The key-range locking described in the last page
is not adaptive because there are a fixed number
of key ranges. - Dynamic key-range locking assigns a key range to
every record in the file.
71Dynamic Key-Range Locks (2)
- Example The file has three records W, Y, Z.
- Current key rangesA, W), W, Y), Y, Z), Z,?)
1 - After the insertion of X, 1 becomesA, W),
W, X), X, Y), Y, Z), Z, ?) - After the deletion of Y, 1 becomesA,
W), W, Z), Z, ?) - Dynamic key ranges appear and disappear. This
makes the locking protocol more complex than
static key-range locking.
72Dynamic Key-Range Locking Protocol (1)
- Given a sorted file of three records W, Y, Z.?
key ranges A, W), W, Y), Y, Z), Z, ?) - There are four operations of interest1. Read
unique read a unique record (say X),
given its key. Protocol The
transaction T must SLOCK W, Y)
preventing anyone else from updating
that key range. - Example If the record is not found, the lock
prevents a phantom insertion of X
by another transaction prior to
Ts commit.
73Dynamic Key-Range Locking Protocol (2)
- 2. Read next read the next record,Y, after the
record W. - protocol The transaction T must hold a lock on
W,Y). (This prevents any phantoms from appearing
in this key range). All T needs, then, is to
request a SLOCK on key Y (This implicitly locks
Y,Z)). - 3. Insert insert record X between W and Y.
- protocol First, the old key range W,Y) (seen to
be W,X)) must be locked in exclusive mode. Then,
the new key range X,Y) should be locked in
exclusive mode. - 4. Delete delete record Y.
- protocol First, lock Y,Z in exclusive mode,
then lockW,Y) is exclusive mode. When these two
locks are granted, perform the delete.
74Dynamic Key-Range Locks Problem (1)
- Problem If a key-range lock has to wait to be
granted, then when it is granted,
the key range may have
disappeared. - Example The file has three records W, Y,
Z.current key ranges A, W), W, Y), Y, Z),
Z, ?) - 1. locks W, Y) in XLOCK 2. locksX, Y)
in XLOCK -
3. Waits for X, Y) - 4. T1 aborts, the key range returns to
W, Y)
5. The lock on X, Y) is granted to T2,
but X, Y) disappears.
T2 reads the record previous to Y
T1 inserts X
T I M E
75Dynamic Key-Range Locks Problem (2)
- Solution If the key range has changed, the
transaction should release the lock and
request the lock for the new key
range (i.e., W, Y)).
76Lock Conversion (1)
- QuestionCan a transaction requests a look in
shared mode, and later converts it to exclusive
mode? - The transaction should not release the shared
lock, and then request the exclusive lock. - Reason 2P-locking is violated!
77Lock Conversion (2)
- If a lock is held in one mode and requested in a
second mode, the request is converted to the max
of the two modes. - Example If the lock is held in IX mode and
requested in U mode, then the request is
converted to a request for a X mode lock. - Note dirty reads might occur.
conversion
Requested mode
Current made
78Lock Escalation (1)
- If the system makes a mistake and uses fine
granularity when coarse granularity is
appropriate, the lock system may run out of
storage. - 1. Typical solution If the number of locks held
by a transaction reaches an escalation
threshold, the lock manager converts
fine-granularity locks to coarse locks.
79Lock Escalation (2)
- Example It looks for an IS lock with a large
number of children set in S mode,
converts the parent IS mode lock to
an S mode, and releases all the
S mode locks on the children.
IS
S
Page 101
Page 101
Lockescalation
S
S
record 112
record 113
record 134
record134
record113
record112
??
??
The records are implicitly locked in Shared mode.
80Lock Escalation (3)
- 2. Rdbs approach It defaults to
file-granularity locks. If there is any
contention on the file-granularity lock,
each holder of the lock de-escalates to
finer granularity on demand.
81Hotspots
- The virtue of page-granularity locking is that
- It is very easy to implement,
- It works well in almost all cases, and
- It can be used to give phantom protection (by
locking the page on which the phantom would have
to appear). - However, page-granularity locking can create
hotspots.Examples - 1. Directories are typically small, high-traffic
read-write databases, they can become hotspots. - 2. The debit/credit transaction demonstrates
main-memory hotspotproblems. All the branch
records (usually less than 100) typically fit in
a couple of pages. - 3. Inserting in a sequential file can give page
locking systems heartburn, since locking the
page containing the end of the file prevents any
other transaction from inserting at the end of
the file.
82Sequential Nested Transaction Locking
- The simplest generalization of flat transactions
is nested transactions without parallelism,
called sequential nested transactions, or
savepoints. - The effects of the transaction are made durable
and public only when the top-level transaction
invokes COMMIT work. - Implementation
- All subtransactions run under the same TRID.
- Locks are maintained as a stack. When the
transaction does a nested BEGIN work, that
establishes a savepoint in the stack. - If the transaction does a ROLLBACK to one of
these savepoints, all locks subsequent to the
lock in the savepoint are released.
83Parallel Nested Transaction Locking (1)
- The parallel subtransactions are each viewed as
atomic units (they have A, C, and I, but not D of
ACID) - Each subtransaction gets a unique TRID.
- The nested transactions are viewed as nodes of
the transaction family tree. - At commit, both inherited and acquired locks are
given back to the parent, but at ROLLBACK only
inherited locks are returned.
84Parallel Nested Transaction Locking (2)
- When a transaction requests a lock that is
already held by another transaction, the
following test is run - Is the other transaction in the same family?
- If yes, is it an ancestor?
- If yes, inherit 1
- If no, wait, then inherit. 2
- If no, wait. 3
-
-
acquired
1
3
2
Family F2
Family F1
85Deadlock Avoidance vs. Toleration (1)
- Deadlock A situation in which each transaction
of the deadlock set is waiting
for another member of the set.
These transactions will not
advance unless one transaction
stops waiting. In this situation, one of
the transactions must rollback.
86Deadlock Avoidance vs. Toleration (2)
- Solutions
- 1. Deadlock avoidance
- Linearly ordering resources and requesting them
only in that order (avoiding wait-for cycles). - Asking jobs to predeclare their maximum needs.
- 2. Deadlock detection
- Whenever a transaction waits more than a certain
time, declare that something is wrong (very
pessimistic) and rollback that transaction.Exampl
es IBMs CICS, Tandems Encompass, NonStop SQL
Systems. - After a transaction times out, the system runs a
deadlock detector that looks for cycles in the
wait-for graph.
87Wait-for Graph and Deadlock Detection (1)
- 1. Lock data structures
- Each lock L has two lists, the granted list and
the waiting list. - It convenient to have a quick way of finding all
the places a particular transaction is waiting.
Therefore, a transaction wait list, with an entry
for each active transaction, is maintained.
88Wait-for Graph and Deadlock Detection (2)
- 2. Wait-for graph The edge T ? T? should be
added to the wait-for graph if T in the waiting
list and T? in the waiting or granted list meet
two conditions - T? is in the granted list or is ahead of T is the
waiting list, and - Modes m and m? are incompatible.
l2
l1
granted list
waiting list
89Wait-for Graph and Deadlock Detection (3)
- 3. Deadlock detection
- The graph can be examined for cycles by
doing a depth-first search that runs in time
proportional to the number of edges.
90Distributed Deadlock (1)
- Problem The transaction wait list is
distributed. Typically, each
site maintains a local fragment
of the transaction wait list
containing all transaction waiting at that
node.
91Distributed Deadlock (2)
- Solution Path Pushing
- Each site with a wait-for edge pointing to a
transaction in a second site traces back from
that edge to source transactions. - It then sends (pushes) the resulting graph to the
target site. - The target site adds these edges and iterates the
logic. - If there is a cycle, one or more sites in the
cycle's path will find it.
92Distributed Deadlock (3)
- Note
- There is no standard representation of the
wait-for graph. - Not all RMs provide their part of the wait-for
graph.? Implementing a global deadlock detector
may not be possible.
93Phantom Deadlocks (1)
- Since the wait-for graph is constantly changing,
it is not possible to get a consistent picture of
the graph. - Phantom deadlocks cycles in the graph caused by
transient arcs.
94Phantom Deadlocks (2)
- Example
- At time t1 T T?
T T? Site 1
Site 2
Site3 - (T? is finishing (collecting
subgraphs) (serving T) writing the log) - At time t2
- Good news the deadlock detector will not miss
any real deadlocks.
Collecting subgraphs
log lock
T? request a service at site 3
Collecting subgraph
T T?
T T?
Site1
Site2
Site3
(completed the log operation for T?, currently
serving T)
(a phantom deadlockis detected)
(serving T)
95Deadlock Resolution
- Once a deadlock is found, it must be broken.?
The standard resolution technique is to
roll back the least expensive transactions
the ones with the shortest logs.
96Deadlock Summary
- Wait-for graphs have the following
characteristics - 1. Sparse (waits are very rare)
- 2. Most cycles are very short (two)
- 3. Cycles are very rare
- Most systems use the following approach
- 1. The system falls back on timeout, plus
- 2. A simple local deadlock detector that detects
local deadlocks.
97Probability of Deadlock (1)
- A very simple model
- The system consists of R records.
- There are n1 concurrent transactions.
- Each transaction performs r 1 actions. Each
action accesses a random record from the set of R
records. - nrltltR that is, most of the database is unlocked
most of the time.
98Probability of Deadlock (2)
- Analysis
- The probability that a single request waits isPW
( n r/2 ) / R n r / 2R - The probability that a particular transaction T,
waits in its life time isPW(T) 1 ( 1 PW
)r 1 1
Avg num of locks held by each transaction
The probability that a single request does not
have to wait.
nrltltR ? PWltlt1 ? these high order
terms can be dropped
PW(T) nr2 / 2R
99Probability of Deadlock (3)
- The probability that some transaction T is
deadlockPD(T) PW(T)2 / n ?1 PW(T)3 ?2
PW(T)4 ??
T1
T2
PW(T) prob for waiting for some transaction
In general, the probability of a cycle of length
I is proportional to PW(T)iSince we typically
have PW(T)lt0.1? These terms can be dropped.
1/n PW(T) prob for waiting for a specific
transaction (i.e., T1)
100Probability of Deadlock (4)
- The probability that any transaction
deadlocksPD PD(T1) PD(T2) ??
PD(Tn1)PD ? n PD(T) n2 r4 / 4R2
Multiprogramming level must be kept low
Fine-granularity locking, browse mode locking and
shared locks are important. They all tend to
reduce the transaction collision cross sections.
deadlocks are very rare
101Reducing the transactions collision cross
section (1)
- Transactions collision cross section is the
time-space product of the amount of data the
transaction locks and the lock duration. - Reducing the transactions collision cross
section? minimizes lock waits? increases
concurrency reduces deadlocks
102Reducing the transactions collision cross
section (2)
- T2 and T3 occur concurrently. However, they are
isolated because they access different data. - T3 and T4 access the same data. However, they are
isolated because they occur during time spans. - Since T2 collides with T1, isolation problems can
occur.
Collision Cross Section
Time
T2
T3
T1
T4
Locks
103Field Calls (1)
- Field calls have been the most successful
technique to minimize the collision cross
section. - Field call is an action on a specific hotspot
record. The action consists of two parts a
predicate a transform. - Example update inventory set
quantity-on-hand quantity-on-hand - delta
where item MS Windows
and quantity-on-hand ?
deltapredicate quantity-on-hand ?
deltatransform quantity-on-hand
quantity-on-hand - delta
104Field calls (2)
- 1. The record corresponding to MS Windows is
locked in short duration shared mode. - 2. The predicate is tested against the MS
Windows record, and the shared lock is
immediately released afterward. - 3. If the predicate is true, the system generates
a REDO log record to be performed at a
transaction commit. The REDO log record contains
the predicate and the transform. - 4. At commit (phase 1), all deferred REDO log
records are examined. - Shared locks are acquired on predicate only
records, - and exclusive locks are acquired on records
mentioned in transforms. - 5. Each predicate is again tested at commit phase
1. If the predicates are still true at phase 1,
then at commit phase 2, each transform is applied
and the locks are freed.
105Field calls Advantages
Checkout Clerk C1
Checkout Clerk C2
update inventory set qoh qoh - delta where
item milk AND qoh ? delta
Stocking Clerk S
-5
-10
300
C2 updates
S updates
C1 updates
Milk
1000
Milk
990
Milk
1290
Milk
1285
Original qoh
time
C1
S
C2
C2
S
C1
C2
S
C1
C1
S
C2
106Field calls Recovery (1)
- Field calls never have any transaction undo work.
If the transaction aborts, the field calls have
not made any changes to the database thus, they
are already undone. - Redo of field calls is a little more subtle.
- Since transformations are deferred to phase 2 of
commit, it is safest to delay inserting field
call transforms into the REDO log until the end
of phase 1 of commit. - Reason Since transactions are inserting field
call transforms will appear in the order that
they were applied (rather than the order in which
the field calls themselves were issued).
107Field calls Recovery (2)
hotspot record h
t
t?
transaction T
transaction T
Example
- time transaction T transaction T?
log 1. t(h)
, t(h) 2.
t?(h) , t(h),
t?(h) 3.
commit 4.
commit
redo order- Effect(commit order) t(t?(h))-
System crashed, the redo logic reconstructed the
value of h t?(t(h)).- Note commit order ?
redo order If t?(t(h)) ? t(t?(h)) ?
the wrong value is reconstructed.
108Escrow Locking
- Refinement of field callsIf we can preserve the
truth of the predicate between the time the
transaction first makes the field call and the
time the predicate is revaluated at phase 1 of
commit, then we can prevents transaction
predicate failure at commit, and the consequent
transaction aborts.
Phase1commit
Phase 2 commit
Field call
time
Re-evaluate predicate
predicate test
T1 ends
T1 begins
Try to preserve the predicate
109Escrow Locking (2)
- Idea
- Associated with each hotspot field is a range of
values. m, n - For each new field call, the predicate is tested
against both m and n.
The new value if all the pending field call
transforms are applied.
Note m n if there is no pending field call.
The current value (in the database)
110Escrow Locking (2)
- Note we need to test the predicates against both
end values because the operator could be a
(instead of - ).
111Optimistic Concurrency Control
- Conventional locking techniques are pessimistic
- They assume a high degree of contention among
concurrent transactions. - A certain degree of checking is done before a
database operation can be executed. - Optimistic concurrency control techniques do not
perform any locking at all. A transaction is
composed of three phases - Read Phase Updates are applied only to local
copies of the data items kept in the transaction
workspace. - Validation Phase Checking is performed to ensure
that serializability will not be violated if the
transaction updates are applied to the database. - Write Phase
- If the validation is successfulthen the updates
are applied to the databaseelse the updates are
discarded and the transaction is restarted.
112Optimistic Concurrency Control Validation
- The validation phase for Ti checks that, for each
such transaction Tj that is committed or is in
its validation phase, one of the following
conditions holds - 1.Transaction Tj completes its write phase before
Ti starts its read phase. - 2.Ti starts its write phase after Tj completes
its write phase, and the read-set of Ti has
no items in common with the write-set of Tj. - 3.Tj completes its read phase before Ti completes
its read phase, and both read-set and
write-set of Ti have no items in common with
the write-set of Tj. - Note If any one of these three conditions holds,
there is no interference and Ti is
validated successfully.
113Optimistic Concurrency Control Validation (1)
- The validation phase for Ti checks that for each
such transaction Tj that is committed or is in
its validation phase, one of the following
conditions holds - 1.Transaction Tj completes its write phase before
Ti starts its read phase. Tj
Ti - 2.Ti starts its write phase after Tj completes
its write phase, and the read-set of Ti has
no items in common with the write-set of Tj
(no Write ? Read dependencies) Tj
Ti
R
V
W
R
V
W
R
V
W
R
V
W
114Optimistic Concurrency Control Validation (2)
- 3. Both the read-set and the write-set of Ti have
no - items in common with the write-set of Tj
(no Read ? Write, Write ? Write
dependencies), and Tj completes its read
phase before Ti completes its read phase.
Tj Ti
R
V
W
R
V
W
115Optimistic Concurrency Control Problem
- Advantage No checks are made while the
transaction is running
? locking is not required. - Disadvantage
- When interference is significant, this approach
is worse than conventional locking - Conventional locking would serialize the
transactions, causing them to wait for one
another. - Optimistic techniques would let all the
concurrent transactions run and then abort all
but one of them. - Field calls plus escrow reads is better is
general.
116Ti