Microsoft PowerPoint – discussion_transactions
Outline
• Q1: Conflict-serializability Vs. Serializability.
• Q2: Schedule feasibility under locking.
• Q3: Isolation levels.
Q1: Check conflict-serializability (a)
• For the given schedule, determine if it is conflict serializable or not.
Transaction T1 Transaction T2 Transaction T3
R(A)
W(A)
W(A)
W(A)
A1: Check conflict-serializability (a)
Transaction T1 Transaction T2 Transaction T3
R(A)
W(A)
W(A)
W(A)
𝑇 𝑇
𝑇
Q1: Check serializability? (b)
• For the given schedule, determine if it is serializable or not.
Transaction T1 Transaction T2 Transaction T3
R(A)
W(A)
W(A)
W(A)
A1: Check serializability? (b)
Transaction T1 Transaction T2 Transaction T3
R(A)
W(A)
W(A)
W(A)
Transaction T1 Transaction T2 Transaction T3
R(A)
W(A)
W(A)
W(A)
Given
schedule
A serial
schedule
What is the difference?
Transaction T1 Transaction T2 Transaction T3
R(A)
W(A)
W(A)
W(A)
Transaction T1 Transaction T2 Transaction T3
R(A)
W(A)
W(A)
W(A)
• T1 reads the same value of A.
• T2, T3 write the values (no reads).
• After all operations, database is in
the same state -> A written by T3.
• Blind write by T3.
Both schedules are equivalent – yet
not conflict-equivalent.
Serializable !
Q2: Check Strict 2PL feasibility (a)
• For the schedule below, determine if it is possible to execute under strict 2 PL.
• If so: Show a placement of shared locks (S), exclusive locks (X) and lock releases
(Rel)
Transaction T1 Transaction T2 Transaction T3
R(A)
R(A)
R(B)
W(A)
W(B)
R(B)
A2: Check Strict 2PL feasibility (a)
Transaction T1 Transaction T2 Transaction T3
Slock(A)
R(A)
R(A)
R(B)
W(A)
W(B)
R(B)
A2: Check Strict 2PL feasibility (a)
Transaction T1 Transaction T2 Transaction T3
Slock(A)
R(A)
Slock(A)
R(A)
Rel(A)
R(B)
W(A)
W(B)
R(B)
A2: Check Strict 2PL feasibility (a)
Transaction T1 Transaction T2 Transaction T3
Slock(A)
R(A)
Slock(A)
R(A)
Rel(A)
Slock(B)
R(B)
W(A)
W(B)
R(B)
A2: Check Strict 2PL feasibility (a)
Transaction T1 Transaction T2 Transaction T3
Slock(A)
R(A)
Slock(A)
R(A)
Rel(A)
Slock(B)
R(B)
Xlock(A)? -> Must happen
before W(B) by T1. Lock
held by A.
W(A)
W(B)
R(B)
Q2: Check Strict 2PL feasibility (b)
• For the schedule below, determine if it is possible to execute under strict 2 PL.
• If so: Show a placement of shared locks (S), exclusive locks (X) and lock releases
(Rel)
Transaction T1 Transaction T2 Transaction T3
R(A)
R(A)
R(B)
R(A)
W(B)
R(B)
A2: Check Strict 2PL feasibility (b)
Transaction T1 Transaction T2 Transaction T3
Slock(A)
R(A)
R(A)
R(B)
R(A)
W(B)
R(B)
A2: Check Strict 2PL feasibility (b)
Transaction T1 Transaction T2 Transaction T3
Slock(A)
R(A)
Slock(A)
R(A)
Rel(A)
R(B)
R(A)
W(B)
R(B)
A2: Check Strict 2PL feasibility (b)
Transaction T1 Transaction T2 Transaction T3
Slock(A)
R(A)
Slock(A)
R(A)
Rel(A)
Slock(B)
R(B)
R(A)
W(B)
R(B)
A2: Check Strict 2PL feasibility (b)
Transaction T1 Transaction T2 Transaction T3
Slock(A)
R(A)
Slock(A)
R(A)
Rel(A)
Slock(B)
R(B)
Slock(A)
R(A)
W(B)
R(B)
A2: Check Strict 2PL feasibility (1)
Transaction T1 Transaction T2 Transaction T3
Slock(A)
R(A)
Slock(A)
R(A)
Rel(A)
Slock(B)
R(B)
Slock(A)
R(A)
Xlock(B)
W(B)
Rel(A) Rel(B)
R(B)
A2: Check Strict 2PL feasibility (b)
Transaction T1 Transaction T2 Transaction T3
Slock(A)
R(A)
Slock(A)
R(A)
Rel(A)
Slock(B)
R(B)
Slock(A)
R(A)
Xlock(B)
W(B)
Rel(A) Rel(B)
Slock(B)
R(B)
Rel(A) Rel(B)
Q3: Isolation levels (a)
• For the schedule below, determine the isolation levels under which the given
schedule can successfully execute.
• Possible levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ
Transaction T1 Transaction T2 Transaction T3
R(A)
R(B)
R(C)
W(A)
W(B)
W(C)
A3: Isolation levels (a)
• The schedule has no dependencies among transactions.
• READ COMMITTED and REPEATABLE READ are possible.
Transaction T1 Transaction T2 Transaction T3
R(A)
R(B)
R(C)
W(A)
W(B)
W(C)
Q3: Isolation levels (b)
• For the schedule below, determine the isolation levels under which the given
schedule can successfully execute.
Transaction T1 Transaction T2
R(A)
W(A)
R(B)
W(B)
R(B)
W(B)
R(C)
W(C)
A3: Isolation levels (b)
• READ UNCOMMITTED -> not possible since both transactions involve reads and
writes.
Transaction T1 Transaction T2
R(A)
W(A)
R(B)
W(B)
R(B)
W(B)
R(C)
W(C)
A3: Isolation levels (b)
• Check READ COMMITTED.
• Place locks one by one according
to the rules.
Transaction T1 Transaction T2
Slock(A)
R(A)
Rel(A)
W(A)
R(B)
W(B)
R(B)
W(B)
R(C)
W(C)
A3: Isolation levels (b)
• Check READ COMMITTED.
• Place locks one by one according
to the rules.
Transaction T1 Transaction T2
Slock(A)
R(A)
Rel(A)
Xlock(A)
W(A)
R(B)
W(B)
R(B)
W(B)
R(C)
W(C)
A3: Isolation levels (b)
• Check READ COMMITTED.
• Place locks one by one according
to the rules.
Transaction T1 Transaction T2
Slock(A)
R(A)
Rel(A)
Xlock(A)
W(A)
Slock(B)
R(B)
Rel(B)
W(B)
R(B)
W(B)
R(C)
W(C)
A3: Isolation levels (b)
• Check READ COMMITTED.
• Place locks one by one according
to the rules.
Transaction T1 Transaction T2
Slock(A)
R(A)
Rel(A)
Xlock(A)
W(A)
Slock(B)
R(B)
Rel(B)
Xlock(B)
W(B)
R(B)
W(B)
R(C)
W(C)
A3: Isolation levels (b)
• Check READ COMMITTED.
• Slock(B) by T1 is not possible since
T2 will not release lock on B till it
commits.
• This implies READ COMMITTED and
REPEATABLE READ are not possible.
• NOTE: Schedules T1 and T2 are
conflict-serializable.
Transaction T1 Transaction T2
Slock(A)
R(A)
Rel(A)
Xlock(A)
W(A)
Slock(B)
R(B)
Rel(B)
Xlock(B)
W(B)
Slock(B)? – Not possible
R(B)
W(B)
R(C)
W(C)