程序代写代做代考 database Microsoft PowerPoint – discussion_transactions

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)