代写351/751 Database Systems,

351/751 Database Systems, 2023, Semester 1
Lab 07
due Sat 13 May 2023 11:59pm (15 marks in this lab = 1.5% towards final grade)
This Lab has an attendance component of (3 marks)
1. Transactions, Phenomena. Say for each of the following schedules: does the
schedule contain phenomena or any other violation of the locking rules of the
common scheduler? If not, give an explanation why not. If yes, say on which data
object the phenomenon occurs; describe the phenomenon. State the highest
isolation level that the schedule can be performed on.
(a) s1 : r1[x], r2[y], r3[y], w1[x], w3[y], c3, r2[x], c2, c1
(1 mark)
(b) s2 : r1[x], r2[y], r3[y], w1[x], w3[y], c3, r2[y], c2, c1
(1 mark)

(c) s3 : r1[x], r2[y], r3[y], w1[x], w3[y], c3, w2[y], c2, c1
(1 mark)

(d) r1[x], r2[y], r1[y], r3[y], r2[x], r3[x], w1[x], c3, c1, w2[y], c2
(1 mark)
2. Transactions, Deadlocks. Consider the following sets of transactions. Can the set of
transactions run into a deadlock if we use the common scheduler? If yes, give a
scheduling diagram showing the deadlock, if no say why not.
(a) (2 marks)

TA1: w1[k], r1[x]
TA2: r2[z], r2[x]
TA3: r3[x], w3[z]


(b) (2 marks)

TA1: r1[k], r1[x], w1[x]
TA2: r2[z], r2[x], w2[z], w2[x]
TA3: r3[x], r3[z]
3. Crash recovery for steal, no-force policy: The following list gives pages, objects on
these pages and their values in the stable database at a certain point in time:
Page 1:
x = 62
y = 43
Page 2:
z = 46
k = 12
The following is the list of the most recent stable log records at the same point in
time. The database uses the steal, no-force policy.
[nr: 321, ta: 62, obj: x, b: 31, a: 62]
[nr: 324, ta: 62, obj: k, b: 12, a: 58]
[nr: 322, ta: 63, obj: y, b: 34, a: 43]
[nr: 323, ta: 62, obj: x, b: 62, a: 54]
[nr: 325, ta: 62, commit]
[nr: 327, ta: 64, obj: z, b: 46, a: 89]
[nr: 328, ta: 64, obj: k, b: 58, a: 91]
a) You are supposed to perform crash recovery. What operations do you have to
perform on which transactions? Give the content of the stable database after
the crash recovery.
(1 marks)
b) Was a database buffer page with an uncommitted write written to the stable
database?
If yes, say which page and identify the time interval when it was written to the
stable database. Give the interval as two log sequence numbers before and
after, and say how you came to that conclusion. If no, give reasons for your
answer.
(3 marks)