Consider a database with objects X and Y and assume that there are two transactions T1 and T2. Transaction T1 reads objects X and Y and then writes object X. Transaction T2 reads objects X and Y and then writes objects X and Y.

(a) Give an example schedule with actions of transactions T1 and T2 on objects X and Y that results in a write-read conflict.

(b) Give an example schedule with actions of transactions T1 and T2 on objects X and Y that results in a read-write conflict.

(c) Give an example schedule with actions of transactions T1 and T2 on objects X and Y that results in a write-write conflict.

(d) For each of the three schedules, show that Strict 2PL disallows the schedule.

Given that

T1 = r1(X), r1(Y), w1(X) T2 = r2(X), r2(Y), w2(X), w2(Y)

we design the following schedules which have the asked conflicts.

(a) Write-read conflict (which necessarily means reading uncommitted data, aka dirty read which results in cycle in the precedence graph):

S1 = r2(X), r2(Y),**w2(X), r1(X)**, r1(Y), w1(X), w2(Y)

The WR conflict is written in bold. Note that, it is not necessary to have the W and R statement immediately after each other to create a WR conflict.

(b) Read-write conflict (which necessarily means unrepeatable read which causes cycle in the precedence graph):

S2 = r2(X), r2(Y), w2(X), r1(X), **r1(Y), w2(Y)**, w1(X)

The RW conflict is written in bold.

(c) Write-write conflict (which necessarily means overwriting uncommitted data or blind writes resulting in cycle in the precedence graph):

S3 = r2(X), r2(Y), r1(X), r1(Y), **w2(X), w1(X)**, w2(Y)

The WW conflict is written in bold.

(d) If we use strict 2PL the locking and unlocking will be done in two phases and all the exclusive locks will be held till the transaction commits or aborts and shared locks can be released any time during the second phase. If we apply strict 2PL, only serializable schedules will be allowed and all the three schedules listed above will be disallowed. Let us denote taking exclusive lock by x and shared lock by s, and release of lock by u, and commit by c. So, following will be the execution of the schedules where strict 2PL will ensure serializability by not granting locks which may create conflicts.

S1 = s2(X), r2(X), s2(Y), r2(Y), x2(X), w2(X), s1(X)-request not granted, x2(Y), w2(Y), u2(X), u2(Y), c2, s1(X), r1(X), s1(Y), r1(Y), x1(X), w1(X), u1(X), u1(Y), c1.

S2 = s2(X), r2(X), s2(Y), r2(Y), x2(X), w2(X), s1(X)-request not granted, x2(Y), w2(Y), u2(X), u2(Y), c2, s1(X), r1(X), s1(Y), r1(Y), x1(X), w1(X), u1(X), u1(Y), c1.

S3 = s2(X), r2(X), s2(Y), r2(Y), s1(X)-request not granted, x2(X), w2(X), x2(Y), w2(Y), u2(X), u2(Y), c2, s1(X), r1(X), s1(Y), r1(Y), x1(X), w1(X), u1(X), u1(Y), c1.