notes 09: transaction processing
DESCRIPTION
Notes 09: Transaction Processing. Slides are modified from the CS 245 class slides of Hector Garcia-Molina. Reading. Chapter 10.1, 10.2 Chapter 11.1 – 11.3, 11.6. Transactions. A sequence of operations on one or more data items. - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/1.jpg)
1
Notes 09: Transaction Processing
Slides are modified from the CS 245 class slides of Hector
Garcia-Molina
![Page 2: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/2.jpg)
Reading
• Chapter 10.1, 10.2• Chapter 11.1 – 11.3, 11.6
2
![Page 3: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/3.jpg)
Transactions
• A sequence of operations on one or more data items.
• Fundamental assumption: a transaction when run on a consistent state and without interference with other transactions will leave the database in a consistent state.
3
![Page 4: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/4.jpg)
Termination of Transactions
• Commit: guarantee that all of the changes of the transaction are permanent
• Abort: guarantee that none of the effects of the transaction are permanent– Reason for abort: user, transaction,
system failure
4
![Page 5: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/5.jpg)
5
Chapter 9 Concurrency Control
T1 T2 … Tn
DB(consistencyconstraints)
![Page 6: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/6.jpg)
6
Example:
T1: Read(A) T2: Read(A)A A+100 A A2Write(A) Write(A)Read(B) Read(B)B B+100 B B2Write(B) Write(B)
Constraint: A=B
![Page 7: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/7.jpg)
7
Schedule A
T1 T2Read(A); A A+100Write(A);Read(B); B B+100;Write(B);
Read(A);A A2;
Write(A);
Read(B);B B2;
Write(B);
A B25 25
125
125
250
250250 250
![Page 8: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/8.jpg)
8
Schedule B
T1 T2
Read(A);A A2;
Write(A);
Read(B);B B2;
Write(B);Read(A); A A+100Write(A);Read(B); B B+100;Write(B);
A B25 25
50
50
150
150150 150
![Page 9: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/9.jpg)
9
Schedule C
T1 T2Read(A); A A+100Write(A);
Read(A);A A2;
Write(A);Read(B); B B+100;Write(B);
Read(B);B B2;
Write(B);
A B25 25
125
250
125
250250 250
![Page 10: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/10.jpg)
10
Schedule D
T1 T2Read(A); A A+100Write(A);
Read(A);A A2;
Write(A);
Read(B);B B2;
Write(B);Read(B); B B+100;Write(B);
A B25 25
125
250
50
150250 150
![Page 11: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/11.jpg)
11
Schedule E
T1 T2’Read(A); A A+100Write(A);
Read(A);A A1;
Write(A);
Read(B);B B1;
Write(B);Read(B); B B+100;Write(B);
A B25 25
125
125
25
125125 125
Same as Schedule Dbut with new T2’
![Page 12: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/12.jpg)
12
• Want schedules that are “good”,regardless of
– initial state and– transaction semantics
• Only look at order of read and writes
Example: Sc=r1(A)w1(A)r2(A)w2(A)r1(B)w1(B)r2(B)w
2(B)
![Page 13: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/13.jpg)
13
Sc’=r1(A)w1(A) r1(B)w1(B)r2(A)w2(A)r2(B)w2(B)
T1 T2
Example: Sc=r1(A)w1(A)r2(A)w2(A)r1(B)w1(B)r2(B)w
2(B)
![Page 14: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/14.jpg)
14
However, for Sd:Sd=r1(A)w1(A)r2(A)w2(A)
r2(B)w2(B)r1(B)w1(B)
• as a matter of fact, T2 must precede T1
in any equivalent schedule, i.e., T2 T1
![Page 15: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/15.jpg)
15
T1 T2 Sd cannot be rearrangedinto a serial
scheduleSd is not “equivalent” to
any serial scheduleSd is “bad”
• T2 T1
• Also, T1 T2
![Page 16: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/16.jpg)
16
Returning to Sc
Sc=r1(A)w1(A)r2(A)w2(A)r1(B)w1(B)r2(B)w2(B)
T1 T2 T1 T2
no cycles Sc is “equivalent” to aserial schedule(in this case T1,T2)
![Page 17: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/17.jpg)
17
Concepts
Transaction: sequence of ri(x), wi(x) actionsConflicting actions: r1(A) w2(A) w1(A)
w2(A) r1(A) w2(A)
Schedule: represents chronological orderin which actions are executed
Serial schedule: no interleaving of actions or transactions
![Page 18: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/18.jpg)
Transaction
• A transaction T is a partial order, denoted by <, where– T { r[x], w[x]:x is a data item}
{a, c}– aT iff cT– Suppose t is either a or c, than for any
other operation pT , p<t
18
![Page 19: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/19.jpg)
Complete History
• A complete history H over T={T1, …, Tn} is a partial order , <H, where
– H= (i=1, …, n) Ti
– <H (i=1, …, n) <I
– For any pair of conflicting operations p, q, either p <H q or q<Hp
19
![Page 20: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/20.jpg)
20
Conflict equivalent histories
• H1, H2 are conflict equivalent if– Both are over the same transactions and have
the same operations, and – They order the conflicting operations the same
way, i.e., if H1 can be transformed into H2 by a series of swaps on non-conflicting actions.
![Page 21: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/21.jpg)
21
Conflict Serializable History
A history is conflict serializable if it is conflict equivalent to some serial history.
![Page 22: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/22.jpg)
View Equivalence
• Let T1: x=x+1, T2: x=x*3• Let initially x=5• Serial histories:
– T1, T2, final value x=18– T2, T1, final value x=16
• No efficient algorithm to guarantee view serializability
22
![Page 23: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/23.jpg)
23
Nodes: transactions in SArcs: Ti Tj whenever
- pi(A), qj(A) are actions in S- pi(A) <S qj(A)
- at least one of pi, qj is a write
Precedence graph P(S) (S is schedule/
history)
![Page 24: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/24.jpg)
24
Exercise:
• What is P(S) forS = w3(A) w2(C) r1(A) w1(B) r1(C) w2(A) r4(A) w4(D)
• Is S serializable?
![Page 25: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/25.jpg)
25
Lemma
S1, S2 conflict equivalent P(S1)=P(S2)
Proof:Assume P(S1) P(S2) Ti: Ti Tj in S1 and not in S2
S1 = …pi(A)... qj(A)… pi, qj
S2 = …qj(A)…pi(A)... conflict
S1, S2 not conflict equivalent
![Page 26: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/26.jpg)
26
Note: P(S1)=P(S2) S1, S2 conflict equivalent
Counter example:
S1=w1(A) r2(A) w2(B) r1(B) S2=r2(A) w1(A) r1(B) w2(B)
![Page 27: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/27.jpg)
27
Theorem
P(S1) acyclic S1 conflict serializable
() Assume S1 is conflict serializable Ss: Ss, S1 conflict equivalent P(Ss) = P(S1)
P(S1) acyclic since P(Ss) is acyclic
![Page 28: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/28.jpg)
28
() Assume P(S1) is acyclicTransform S1 as follows:(1) Take T1 to be transaction with no incident arcs(2) Move all T1 actions to the front
S1 = ……. qj(A)…….p1(A)…..
(3) we now have S1 = < T1 actions ><... rest ...>(4) repeat above steps to serialize rest!
T1
T2 T3
T4
Theorem
P(S1) acyclic S1 conflict serializable
![Page 29: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/29.jpg)
29
How to enforce serializable schedules?
Option 1: run system, recording P(S); at end of day, check
for P(S) cycles and declare if execution was good
![Page 30: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/30.jpg)
30
Option 2: prevent P(S) cycles from occurring T1 T2 ….. Tn
Scheduler
DB
How to enforce serializable schedules?
![Page 31: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/31.jpg)
What to do with aborted transactions?
• If a transaction T aborts, the DBMS must:– Undo all values written by T– Abort all transactions that read any
value written by T (avoid dirty read)– Ensure recoverability
• Strict execution: a transaction can read or write only committed values.
31
![Page 32: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/32.jpg)
32
A locking protocol
Two new actions:lock (exclusive): li (A)
unlock: ui (A)
scheduler
T1 T2
locktable
![Page 33: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/33.jpg)
33
Rule #1: Well-formed transactionsTi: … li(A) … pi(A) … ui(A) ...
That is, if the transaction 1) locks an item in an appropriate mode before accessing it and 2) unlocks each item that it locks.
![Page 34: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/34.jpg)
34
Rule #2 Legal scheduler
S = …….. li(A) ………... ui(A) ……...
If the transaction does not lock any item if it is already locked in a conflicting mode.
no lj(A)
![Page 35: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/35.jpg)
35
• What schedules are legal?What transactions are well-formed?S1 = l1(A)l1(B)r1(A)w1(B)l2(B)u1(A)u1(B)r2(B)w2(B)u2(B)l3(B)r3(B)u3(B)
S2 = l1(A)r1(A)w1(B)u1(A)u1(B)l2(B)r2(B)w2(B)l3(B)r3(B)u3(B)
S3 = l1(A)r1(A)u1(A)l1(B)w1(B)u1(B)l2(B)r2(B)w2(B)u2(B)l3(B)r3(B)u3(B)
Exercise:
![Page 36: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/36.jpg)
36
• What schedules are legal?What transactions are well-formed?S1 = l1(A)l1(B)r1(A)w1(B)l2(B)u1(A)u1(B)r2(B)w2(B)u2(B)l3(B)r3(B)u3(B)
S2 = l1(A)r1(A)w1(B)u1(A)u1(B)l2(B)r2(B)w2(B)l3(B)r3(B)u3(B)
S3 = l1(A)r1(A)u1(A)l1(B)w1(B)u1(B)l2(B)r2(B)w2(B)u2(B)l3(B)r3(B)u3(B)
Exercise:
![Page 37: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/37.jpg)
37
Schedule F
T1 T2l1(A);Read(A)A A+100;Write(A);u1(A)
l2(A);Read(A)A Ax2;Write(A);u2(A)l2(B);Read(B)B Bx2;Write(B);u2(B)
l1(B);Read(B)B B+100;Write(B);u1(B)
![Page 38: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/38.jpg)
38
Schedule F
T1 T2 25 25
l1(A);Read(A)A A+100;Write(A);u1(A) 125
l2(A);Read(A)A Ax2;Write(A);u2(A) 250l2(B);Read(B)B Bx2;Write(B);u2(B)
50l1(B);Read(B)B B+100;Write(B);u1(B)
150 250
150
A B
![Page 39: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/39.jpg)
39
Rule #3 Two phase locking (2PL)
for transactions
Ti = ……. li(A) ………... ui(A) ……...
no unlocks no locks
![Page 40: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/40.jpg)
40
# locksheld byTi
Time Growing Shrinking Phase Phase
![Page 41: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/41.jpg)
41
Schedule G
T1 T2l1(A);Read(A)A A+100;Write(A)l1(B); u1(A)
l2(A);Read(A) A Ax2;Write(A);ll22(B)(B)
delayed
![Page 42: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/42.jpg)
42
Schedule G
T1 T2l1(A);Read(A)A A+100;Write(A)l1(B); u1(A)
l2(A);Read(A) A Ax2;Write(A);ll22(B)(B)
Read(B);B B+100Write(B); u1(B)
delayed
![Page 43: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/43.jpg)
43
Schedule G
T1 T2l1(A);Read(A)A A+100;Write(A)l1(B); u1(A)
l2(A);Read(A) A Ax2;Write(A);ll22(B)(B)
Read(B);B B+100Write(B); u1(B)
l2(B); u2(A);Read(B) B Bx2;Write(B);u2(B);
delayed
![Page 44: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/44.jpg)
44
Schedule H (T2 reversed)
T1 T2l1(A); Read(A) l2(B);Read(B)A A+100;Write(A) B Bx2;Write(B)
ll11(B)(B) l l22(A)(A)delayeddelayed
![Page 45: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/45.jpg)
45
• Assume deadlocked transactions are rolled back– They have no effect– They do not appear in schedule
E.g., Schedule H =This space intentionally
left blank!
![Page 46: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/46.jpg)
46
Next step:
Show that rules #1,2,3 conflict- serializable schedules
Advantage: Easy to guarantee these properties!
![Page 47: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/47.jpg)
47
Conflict rules for li(A), ui(A):
• li(A), lj(A) conflict • li(A), uj(A) conflict
Note: no conflict < ui(A), uj(A)>, < li(A), rj(A)>,...
![Page 48: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/48.jpg)
48
Theorem Rules #1,2,3 conflict (2PL) serializable
schedule
To help in proof:Definition Shrink(Ti) = SH(Ti) =
first unlock action of Ti
![Page 49: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/49.jpg)
49
LemmaTi Tj in S SH(Ti) <S SH(Tj)
Proof of lemma:Ti Tj means that
S = … pi(A) … qj(A) …; p,q conflictBy rules 1,2:
S = … pi(A) … ui(A) … lj(A) ... qj(A) …
By rule 3: SH(Ti) SH(Tj)
So, SH(Ti) <S SH(Tj)
![Page 50: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/50.jpg)
50
Proof:(1) Assume P(S) has cycle
T1 T2 …. Tn T1
(2) By lemma: SH(T1) < SH(T2) < ... <
SH(T1)
(3) Impossible, so P(S) acyclic(4) S is conflict serializable
Theorem Rules #1,2,3 conflict (2PL) serializable
schedule
![Page 51: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/51.jpg)
Problem with 2PL
• Deadlock• Deadlock detection, prevention,
avoidance
51
![Page 52: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/52.jpg)
52
Variations of 2PL
• Conservative 2PL:each transaction must acquire all its locks before any of the operations are submitted– Adv: no deadlocks– Disadv: need read and write sets in
advance– Starvation
![Page 53: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/53.jpg)
Variations of 2PL
• Strict 2PL: transactions release their locks after either commit or abort– Adv: transaction has all the locks
needed, recoverable histories, avoid cascading aborts
– Disadv: cannot avoid deadlocks, performance tradeoffs
53
![Page 54: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/54.jpg)
Deadlock Avoidance 1.
• Order database object in linear order and transactions must acquire locks in this order– Adv.: avoid deadlock– Disadv.: need order in advance and
need to know all the operations in advance
54
![Page 55: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/55.jpg)
Deadlock Avoidance 2.
• Time-out: if a transaction waits for a lock longer than a predefined time period then the transaction aborts– Adv.: no information about the
transactions is needed in advance– Disadv.: potentially cascading aborts
55
![Page 56: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/56.jpg)
Deadlock Avoidance 3.
• Wait-for-graph: keep a record on which transactions waits for which transaction to release a lock. If cycle then there is a deadlock.– Adv.: simplified administration– Disadv.: which transaction to abort?
56
![Page 57: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/57.jpg)
57
• Beyond the variations of 2PL protocol, it is all a matter of improving performance and allowing more concurrency….– Shared locks– Multiple granularity (database -> cell)– Inserts, deletes and phantoms– Other types of C.C. mechanisms
![Page 58: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/58.jpg)
58
Shared locks
So far:S = ...l1(A) r1(A) u1(A) … l2(A) r2(A) u2(A) …
Do not conflict
Instead:S=... ls1(A) r1(A) ls2(A) r2(A) …. us1(A)
us2(A)
![Page 59: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/59.jpg)
59
Lock actionsl-ti(A): lock A in t mode (t is S or X)u-ti(A): unlock t mode (t is S or X)
Shorthand:ui(A): unlock whatever modes
Ti has locked A
![Page 60: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/60.jpg)
60
Rule #1 Well formed transactionsTi =... l-S1(A) … r1(A) … u1 (A) …Ti =... l-X1(A) … w1(A) … u1 (A) …
![Page 61: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/61.jpg)
61
• What about transactions that read and write same object?
Option 1: Request exclusive lockTi = ...l-X1(A) … r1(A) ... w1(A) ... u(A)
…
![Page 62: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/62.jpg)
62
Option 2: Upgrade (E.g., need to read, but don’t know if will write…)
Ti=... l-S1(A) … r1(A) ... l-X1(A) …w1(A) ...u(A)…
Think of- Get 2nd lock on A, or- Drop S, get X lock
• What about transactions that read and write same object?
![Page 63: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/63.jpg)
63
Rule #2 Legal scheduler
S = ....l-Si(A) … … ui(A) …
no l-Xj(A) (but can have l-Sj(A) )
S = ... l-Xi(A) … … ui(A) …
no l-Xj(A) no l-Sj(A)
![Page 64: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/64.jpg)
64
A way to summarize Rule #2
Compatibility matrix
Comp S XS true falseX false false
![Page 65: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/65.jpg)
65
Rule # 3 2PL transactions
No change except for upgrades:(I) If upgrade gets more locks
(e.g., S {S, X}) then no change!
(II) If upgrade releases read (shared)lock (e.g., S X)- can be allowed in growing
phase
![Page 66: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/66.jpg)
66
Proof: similar to X locks case
Detail:l-ti(A), l-rj(A) do not conflict if comp(t,r)l-ti(A), u-rj(A) do not conflict if comp(t,r)
Theorem Rules 1,2,3 Conf.serializable
for S/X locks schedules
![Page 67: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/67.jpg)
67
Lock types beyond S/X
Examples:(1) increment/decrement lock(2) update lock
![Page 68: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/68.jpg)
68
Example (1): increment lock
• Atomic increment action: INi(A){Read(A); A A+k;
Write(A)}• INi(A), INj(A) do not conflict!
A=7A=5 A=17
A=15
INi(A)
+2
INj(A)
+10
+10
INj(A)
+2
INi(A)
![Page 69: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/69.jpg)
69
Comp S X ISXI
![Page 70: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/70.jpg)
70
Comp S X IS T F FX F F FI F F T
How about adding another column and row
for decrement operations?
![Page 71: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/71.jpg)
71
Update locks
A common deadlock problem with upgrades:
T1 T2l-S1(A)
l-S2(A)
l-Xl-X11(A)(A)
l-Xl-X22(A)(A)
--- Deadlock ---
![Page 72: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/72.jpg)
72
Solution
If Ti wants to read A and knows itmay later want to write A, it requestsupdate lock (not shared)
![Page 73: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/73.jpg)
73
Comp S X USXU
New request
Lock alreadyheld in
![Page 74: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/74.jpg)
74
Comp S X US T F TX F F FU TorF F F
-> symmetric table?
New request
Lock alreadyheld in
![Page 75: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/75.jpg)
75
Note: object A may be locked in different modes at the same time...
S1=...l-S1(A)…l-S2(A)…l-U3(A)… l-S4(A)…?
l-U4(A)…? • To grant a lock in mode t, mode t must be compatible with all currently held locks on object
![Page 76: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/76.jpg)
Locking Granularity
• Database• Relation• Attributes• Tuple
76
Database hierarchy (tree)
![Page 77: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/77.jpg)
Locking Granularity• Tree:
– Each node has a unique parent– Each node in the hierarchy can be locked– A requested node is locked in an explicit
mode, e.g., exclusive lock
• Implicit lock:– When a parent node is locked in an
exclusive lock, each descendent is locked in an implicit lock
77
![Page 78: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/78.jpg)
Intentional Lock
• Must be assigned top-down• Indicate intention (e.g., IS, IX)• Must tag both the ancestors and
the descendents of a node
78
![Page 79: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/79.jpg)
Locks
• IS: intentional share the requested node– Descendents: can be explicitly locked
in S or IS mode
• IX: intentional exclusive access to the requested node– Descendents: can be locked explicitly
in X, S, SIX, IS, and IX
79
![Page 80: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/80.jpg)
Locks• S: shared access to the requested node
– Descendents: implicit shared locks on all
• X: exclusive access to the requested node– Descendents: implicit x-locks on all
• SIX: shared and intentional exclusive access on the requested node– Descendents: implicit shared lock on all,
and explicit lock in X, SIX, or IX mode
80
![Page 81: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/81.jpg)
Compatibility
IS IX S SIX X
IS
IX
S
SIX
X
81
![Page 82: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/82.jpg)
82
How does locking work in practice?
• Every system is different(E.g., may not even provide CONFLICT-SERIALIZABLE schedules)
• But here is one (simplified) way ...
![Page 83: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/83.jpg)
83
(1) Don’t trust transactions torequest/release
locks(2) Hold all locks until transaction
commits#
locks
time
Sample Locking System:
![Page 84: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/84.jpg)
84
Ti
Read(A),Write(B)
l(A),Read(A),l(B),Write(B)…
Read(A),Write(B)
Scheduler, part I
Scheduler, part II
DB
locktable
![Page 85: Notes 09: Transaction Processing](https://reader036.vdocuments.us/reader036/viewer/2022062810/56815a93550346895dc80c2f/html5/thumbnails/85.jpg)
Next class
• Distributed Database Systems
85