351/751 Database Systems Lab 07: Transactions, Deadlocks and Crash Recovery
351/751 Database Systems , 202 3, Semester 1 Lab 07
This Lab has an attendance component of (3 marks)
- Transactions, Phenomena. Say for each of the following schedules: does the schedul e 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)
- Transactions, Deadlocks. Consider the following set s 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]
- 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)
