1 / 17

SOLUTIONS FOR THE FIRST 6340 REVIEW SESSION

This is a collection of solutions for the first review session on transaction management, including explanations, examples, and answers to relevant questions.

rsauer
Télécharger la présentation

SOLUTIONS FOR THE FIRST 6340 REVIEW SESSION

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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.

More Related