SOLUTIONS FOR THE FIRST 6340 REVIEW SESSION JehanFranois

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

First question n Consider the two following transaction schedules and explain why they are or are not serializable and in which order: r 1(A); r 2(B); w 1(A); r 3(A); w 2(B); w 3(A); r 1(B); w 1(B); r 1(A); r 2(A); w 1(A); w 2(C); r 1(B); w 1(B)

First question r 1(A); r 2(B); w 1(A); r 3(A); w 2(B); w 3(A); r 1(B); w 1(B); r 2(B); r 1(A); w 1(A); r 3(A); w 2(B); r 1(B); w 3(A); w 1(B); r 2(B); r 1(A); w 2(B); r 3(A); r 1(B); w 3(A); r 2(B); r 1(A); w 2(B); w 1(A); r 1(B); r 3(A); w 1(B); w 3(A); r 2(B); w 2(B); r 1(A); w 1(A); r 1(B); w 1(B); r 3(A); w 3(A);

Answer r 1(A); r 2(A); w 1(A); w 2(C); r 1(B); w 1(B) ¨ Cannot be serialized because of the sequence r 1(A); r 2(A); w 1(A); w 2(A); n r 2(A); w 1(A) does not commute

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

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.

Third question (I) n Consider the two tables EMPLOYEE (EID, ENAME, ESALARY, EPLANTID, …. ) and PLANT (PLANTID, PNAME, PCITY, PSTATE) where: ¨ EID is the unique ID of an employee, ENAME, his of her full name, ESALARY, his or her salary, EPLANTID the unique ID of the plant where he or she is working, … ¨ PLANTID the unique ID of a plant PNAME located in PCITY in PSTATE, …

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

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

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

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

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

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

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

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

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

Answer n 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 waitfor graph.