SOLUTIONS FOR THE FIRST 6340 REVIEW SESSION

1. SOLUTIONS FOR THE FIRST 6340 REVIEW SESSION Jehan-François Pâris Spring 2015

2. First question • Consider the two following transaction schedules and explain why they are or are not serializable and inwhich order: r1(A); r2(B); w1(A); r3(A); w2(B); w3(A); r1(B); w1(B); r1(A); r2(A); w1(A); w2(A); w2(C); r1(B); w1(B)

3. First question r1(A); r2(B); w1(A); r3(A); w2(B); w3(A); r1(B); w1(B); r2(B); r1(A);w1(A); r3(A); w2(B);r1(B);w3(A); w1(B); r2(B); r1(A);w1(A); w2(B);r3(A); r1(B);w1(B);w3(A); r2(B); r1(A);w2(B);w1(A); r1(B);r3(A); w1(B);w3(A); r2(B);w2(B);r1(A);w1(A); r1(B); w1(B); r3(A);w3(A); r2(B);w2(B);r1(A);w1(A); r1(B); w1(B);r3(A);w3(A);

4. Answer r1(A); r2(A); w1(A); w2(A); w2(C); r1(B); w1(B) • Cannot be serialized because of the sequence r1(A); r2(A); w1(A); w2(A); • r2(A); w1(A) does not commute

5. Second question • In the UNDO approach for managing transactions, • When can the <COMMIT> record be written to disk? • Why?

6. Answer • In the UNDO approach for managing transactions, the <COMMIT> record should be written to disk after all blocks modified by the transaction have been written to disk. • Until then, we may have to undo the effects of a transaction that did not complete.

7. Third question (I) • Consider the two tablesEMPLOYEE (EID, ENAME, ESALARY, EPLANTID, ….) andPLANT (PLANTID, PNAME, PCITY, PSTATE) where: • EID is the unique ID of an employee, ENAME, his of her full name, ESALARY, his or her salary, EPLANTIDthe unique ID of the plant where he or she is working, … • PLANTID the unique ID of a plant PNAME located in PCITY in PSTATE, …

8. Third question (II) • and the following SQL query: • SELECT ENAME, ESALARYFROM EMPLOYEE, PLANTWHERE EPLANTID = PLANTID AND ESALARY > 300000 AND PSTATE = "NM" • What would be your naïve execution plan for this request? • What would be a better execution plan?

9. Naive implementation • X = EMPLOYEE EPLANTID = PLANTIDPLANT • Y = σEsalary > 300000, PSTATE = "NM"(X) • πENAME, ESALARY(Y)

10. Better implementation • X = σEsalary > 300000 (EMPLOYEE) • Y =σ PSTATE = "NM" (PLANT) • Z = X EPLANTID = PLANTID Y • πENAME, ESALARY(Z)

11. What if ... • the query WHERE clause was: • WHERE EPLANTID = PLANTID AND ESALARY < 300000 AND PSTATE = "NM" • X = σ PSTATE = "NM" (PLANT) • Y = EMPLOYEE EPLANTID = PLANTID X • Z = σEsalary > 300000 (Y) • πENAME, ESALARY(Z) Most employees makeless than 300K

12. Fourth question • What does two-phase locking require? • Name a special case where two-phase locking is replaced by less stringent conditions.

13. Answer • What does two-phase locking require? • Transaction cannot acquire new locks once it has started releasing some. • Name a special case where two-phase locking is replaced by less stringent conditions. • Tree locking schemes

14. Fifth question • Consider two transactions T1 and T2 that read and update the same entity A. • Assume that T1 tries to update A while we have TS(T1) ≥ RT(A), TS(T1) ≥ WT(A), and C(A) = 0 • What should the scheduler do? • Why?

15. Answer • Consider two transactions T1 and T2 that read and update the same entity A. • Assume that T1 tries to update A while we have TS(T1) ≥ RT(A), TS(T1) ≥ WT(A), and C(A) = 0 • What should the scheduler do? • Delay the transaction until the previous transaction commits • Why? • Not to be overwritten

16. Sixth question • Explain why the wound-wait scheme eliminates all potential deadlocks.

17. Answer • Explain why the wound-wait scheme eliminates all potential deadlocks. • It ensures that transactions will never wait on locks held by older transactions, thus preventing any cycles in the transaction wait-for graph.