1 / 40

Concurrency Control on Relational Databases

Concurrency Control on Relational Databases. Seminar: Transaction Processing (Bachelor) SS 2009 Dennis Stratmann. Outline. Goal and Overview Examine Three Approaches to CC on RD: 1. Predicate-Oriented Concurrency Control 2. Relational Update Transactions Syntax and Semantics

caraf
Download Presentation

Concurrency Control on Relational Databases

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. Concurrency Control on Relational Databases Seminar: Transaction Processing (Bachelor) SS 2009 Dennis Stratmann

  2. Outline • Goal and Overview • Examine Three Approaches to CC on RD: • 1. Predicate-Oriented Concurrency Control • 2. Relational Update Transactions • Syntax and Semantics • Histories and Final State Serializability • Conflict Serializability • Extended Conflict Serializability • 3. Exploiting Transaction Program Knowledge • Transaction Chopping • Applicability of Chopping • Summary • Questions & Answers Concurrency Control on Relational Databases

  3. Goal and Overview Concurrency Control on Relational Databases

  4. Goal and Overview • Semantic approach to Concurrency Control • Possible to exploit semantic knowledge at higher abstraction level Concurrency Control on Relational Databases

  5. Goal and Overview • Three Approaches to CC on RD: • Predicate-Oriented Concurrency Control • Relational Update Transactions • Exploiting Transaction Program Knowledge Concurrency Control on Relational Databases

  6. Three Approaches to CC on RD: Concurrency Control on Relational Databases 1. Predicate-Oriented Concurrency Control

  7. 1. Predicate-Oriented CC on RD • Relational Database Concurrency Control on Relational Databases

  8. 1. Predicate-Oriented CC on RD • Lock entire Relation Concurrency Control on Relational Databases

  9. 1. Predicate-Oriented CC on RD • Lock individual Tuples Concurrency Control on Relational Databases

  10. 1. Predicate-Oriented CC on RD • DELETE FROM Emp • WHERE Department = ‘Service’ • AND Position = ‘Manager’ • UPDATE Emp • SET Department = ‘Sales’ • WHERE Department = ‘Service’ • AND Position <> ‘Manager’ • INSERT INTO Emp • VALUES (‘Sone’, ‘Serivce’, ‘Clerk’, 13000) • Phantom Problem: • Transaction 1: • Transaction 2: • INSERT INTO Emp • VALUES (‘Smith’, ‘Service’, ‘Manager’, 40000) • SELECT Name, Position, Salary • FROM Emp • WHERE Department = ‘Service’ Concurrency Control on Relational Databases

  11. 1. Predicate-Oriented CC on RD • Predicate Locking Concurrency Control on Relational Databases

  12. 1. Predicate-Oriented CC on RD • DELETE FROM Emp • WHERE Department = ‘Service’ • AND Position = ‘Manager’ • UPDATE Emp • SET Department = ‘Sales’ • WHERE Department = ‘Service’ • AND Position <> ‘Manager’ • INSERT INTO Emp • VALUES (‘Sone’, ‘Serivce’, ‘Clerk’, 13000) • Transaction 1: Ca: Department = ‘Service‘ Position = ‘Manager‘ Cb: Name = ‘Smith‘ Department = ‘Service‘ Position = ‘Manager‘ Salary = ‘40000‘ Cc1: Department = ‘Service‘ Position ‘Manager‘ Cc2: Department = ‘Sales‘ Position ‘Manager‘ Cd: Name = ‘Stone‘ Department = ‘Service‘ Position = ‘Clerk‘ Salary = ‘13000‘ • INSERT INTO Emp • VALUES (‘Smith’, ‘Service’, ‘Manager’, 40000) Concurrency Control on Relational Databases

  13. 1. Predicate-Oriented CC on RD • Transaction 2: Cq: Department = ‘Service‘ • Transaction 3: Cp: Department = ‘Sales‘ • Example: • H(Ca) H(Cq) ∅ • H(Cb) H(Cq) ∅ • H(Cc1) H(Cq) ∅ • H(Cc2) H(Cq) = ∅ • H(Cd) H(Cq) ∅ • SELECT Name, Position, Salary • FROM Emp • WHERE Department = ‘Service’ • SELECT Name, Position, Salary • FROM Emp • WHERE Department = ‘Sales’ Concurrency Control on Relational Databases • H(Ca) H(Cp) = ∅ • H(Cb) H(Cp) = ∅ • H(Cc1) H(Cp) = ∅ • H(Cc2) H(Cp) ∅ • H(Cd) H(Cp) = ∅

  14. 1. Predicate-Oriented CC on RD • Scheduler Concurrency Control on Relational Databases

  15. Three Approaches to CC on RD: Concurrency Control on Relational Databases 2. Relational Update Transactions

  16. 2. Relational Update TransactionsSyntax and Semantics • IDM Transaction Model • Insertion: iR(C) • Deletion: dR(C) • Modify: mR(C1;C2) • Semantics is called effect Concurrency Control on Relational Databases

  17. 2. Relational Update TransactionsSyntax and Semantics • Transaction Equivalence • Two IDM Transactions t and t’ are equivalent, if eff(t) = eff(t’) • Written: t t’ • Commutativity Rules • Simplification Rules Concurrency Control on Relational Databases

  18. 2. Relational Update TransactionsHistories and Final State Serializability • A history s is serial, if all Transactions appear strictly one after the other • A history s is Final State Serializable if s s’ for some history s’ • FSRIDM denotes class of all Final State Serializable histories Concurrency Control on Relational Databases

  19. 2. Relational Update TransactionsHistories and Final State Serializability • Example: Concurrency Control on Relational Databases

  20. 2. Relational Update TransactionsHistories and Final State Serializability Concurrency Control on Relational Databases

  21. 2. Relational Update TransactionsConflict Serializability • A history s for a set T of n transactions is conflict serializable, if stp(1)...tp(n) using only the Commutativity Rules • CSRIDM denotes class of all Conflict Serializable histories • Conflict Graph G(s) = (T,E) • History s CSRIDM, if G(s) is acyclic Concurrency Control on Relational Databases

  22. 2. Relational Update TransactionsConflict Serializability • Consider s = m2(1;2) m1(2;3) m2(3;2) • G(s) is cyclic, so s is not in CSRIDM • On the other hand, s  m1(2;3) m2(1;2) m2(3;2) t1 t2 s is in FSRIDM • CSRIDM FSRIDM t1 t2 Concurrency Control on Relational Databases

  23. 2. Relational Update TransactionsExtended Conflict Serializability • Sometimes, the context in which a conflict occurs can make a difference:Example: Let s = d1(0) m1(0;1) m2(1;2) m1(2;3) • G(s) is cyclic, but s  m2(1;2) d1(0) m1(0;1) m1(2;3)  t2 t1 • Intuitively the conflict involving m1(0;1) does not exist (due to d1(0) ) ! • A history s for a set T of n transactions is extended conflict serializable, if Extended Conflict Graph EG(s) = (T,E) is acyclic • ECSRIDM denotes class of all Extended Conflict Serializable histories Concurrency Control on Relational Databases

  24. 2. Relational Update TransactionsExtended Conflict Serializability • CSRIDMECSRIDM FSRIDM Concurrency Control on Relational Databases

  25. Three Approaches to CC on RD: Concurrency Control on Relational Databases 3. Exploiting Transaction Program Knowledge

  26. 3. Exploiting Transaction Program Knowledge Concurrency Control on Relational Databases

  27. 3. Exploiting Transaction Program Knowledge Transaction Chopping • Transaction Chopping • Short Transactions need less locks • Short Transactions cause potentially less lock contention • Chopping depends on concurrent Transactions Concurrency Control on Relational Databases

  28. 3. Exploiting Transaction Program Knowledge Transaction Chopping • Definition of Transaction Chopping • Every database operation invoked by the chopped transactions is contained in exactly one piece, and the order of operation invocations is preserved Concurrency Control on Relational Databases

  29. 3. Exploiting Transaction Program Knowledge Transaction Chopping • Deadlock / Rollback situation • Atomicity of original Transaction needs to be preserved Concurrency Control on Relational Databases

  30. 3. Exploiting Transaction Program Knowledge Transaction Chopping Three types of Transactions: • A Transaction updating a single Customer’s Account as well as the corresponding Branch • A Transaction reading a Customer’s Account Balance • A Transaction comparing the grand Total of all Account Balances with the Sum of the Branch Balances Concurrency Control on Relational Databases

  31. 3. Exploiting Transaction Program Knowledge Transaction Chopping t1 = r1(A1)w1(A1)r1(B1)w1(B1) t2 = r2(A3)w2(A3)r2(B1)w2(B1) t3 = r3(A4)w3(A4)r3(B2)w3(B2) t4 = r4(A2) t5 = r5(A4) t6 = r6(A1)r6(A2)r6(A3)r6(B1)r6(A4)r6(A5)r6(B2) Concurrency Control on Relational Databases SELECT Balance INTO :oldbalance FROM Accounts WHERE AccountNo = A1; UPDATE Accounts SET Balance = :newbalance WHERE AccountNo = A1; SELECT Total INTO :oldtotal FROM Branches WHERE BranchNo = B1; UPDATE Branches SET TOTAL = :newtotal WHERE Branches = B1;

  32. 3. Exploiting Transaction Program Knowledge Transaction Chopping • Test if a given chopping is correct with a Chopping Graph • Example t6 = r6(A1)r6(A2)r6(A3)r6(B1)r6(A4)r6(A5)r6(B2) • Chop into two pieces: t61 = r61(A1)r61(A2)r61(A3)r61(B1) t62 = r62(A4)r62(A5)r62(B2) • Corresponding Chopping Graph: s: sibling c: conflict A chopping is correct if the associated graph does not contain an sc cycle. Concurrency Control on Relational Databases

  33. 3. Exploiting Transaction Program Knowledge Transaction Chopping • Further in the example: t1 = r1(A1)w1(A1)r1(B1)w1(B1) • Chop into two pieces: t11 = r1(A1)w1(A1) t12 = r1(B1)w1(B1) • Corresponding Chopping Graph: • Making chopping finer can introduce sc cycles Concurrency Control on Relational Databases

  34. 3. Exploiting Transaction Program Knowledge Applicability of Chopping • Applicability of Chopping • To apply chopping algorithm, semantic knowledge is necessary • Semantic knowledge derived form predicates • Real World Example: • High Level conflict Record-Level conflict • SELECT AccountNo, Balance FROM Accounts • WHERE City = ‘Konstanz’ • UPDATE Accounts SET Balance = Balance * 1.05 • WHERE City = ‘Stuttgart’ Concurrency Control on Relational Databases

  35. 3. Exploiting Transaction Program Knowledge Applicability of Chopping • Gain chopping relevant information from parameterized SQL statements: • The chopping method is in limited settings ready for practical use, if • No control-flow branching is used • No loops are used • No If-then-else constructs are used • SELECT AccountNo, Balance FROM Accounts • WHERE AccountType = ‘savings’ AND City = :x • UPDATE Accounts SET Balance = Balance * 1.05 • WHERE AccountType = ‘checking’ AND City = :y Concurrency Control on Relational Databases

  36. 3. Exploiting Transaction Program Knowledge Applicability of Chopping • Rewrite SQL statement to a parameter-less statement • SELECT AccountNo, Balance FROM Accounts • WHERE AccountType = ‘savings’ AND City = :x; • If not found then • SELECT AccountNo, Balance FROM Accounts • WHERE AccountTyoe = ‘checking’ AND City = :x; • fi; • SELECT AccountNo, Balance FROM Accounts • WHERE AccountType = ‘savings’; • SELECT AccountNo, Balance FROM Accounts • WHERE AccountTyoe = ‘checking’; Concurrency Control on Relational Databases

  37. Summary Concurrency Control on Relational Databases

  38. Summary Concurrency Control on Relational Databases

  39. Thanks for listening Concurrency Control on Relational Databases

  40. Questions & Answers Concurrency Control on Relational Databases

More Related