15 721
play

15-721 DATABASE SYSTEMS [Source] Lecture #03 Concurrency Control - PowerPoint PPT Presentation

15-721 DATABASE SYSTEMS [Source] Lecture #03 Concurrency Control Part I Andy Pavlo / / Carnegie Mellon University / / Spring 2016 2 TODAYS AGENDA Transaction Models Concurrency Control Overview Many-Core Evaluation CMU 15-721


  1. 13 TRANSACTION CHAINS Txn #1 BEGIN WRITE(A) Txn #2 COMMIT BEGIN WRITE(B) Txn #3 COMMIT BEGIN WRITE(C) ROLLBACK CMU 15-721 (Spring 2016)

  2. 13 TRANSACTION CHAINS Txn #1 BEGIN WRITE(A) Txn #2 COMMIT BEGIN WRITE(B) Txn #3 COMMIT BEGIN WRITE(C) ROLLBACK CMU 15-721 (Spring 2016)

  3. 14 NESTED TRANSACTIONS Savepoints organize a transaction as a sequence of actions that can be rolled back individually. Nested txns form a hierarchy of work. → The outcome of a child txn depends on the outcome of its parent txn. CMU 15-721 (Spring 2016)

  4. 15 NESTED TRANSACTIONS Txn #1 BEGIN WRITE(A) BEGIN BEGIN WRITE(B) BEGIN WRITE(C) COMMIT WRITE(D) ROLLBACK COMMIT CMU 15-721 (Spring 2016)

  5. 15 NESTED TRANSACTIONS Txn #1 BEGIN WRITE(A) BEGIN BEGIN WRITE(B) BEGIN WRITE(C) COMMIT WRITE(D) ROLLBACK COMMIT CMU 15-721 (Spring 2016)

  6. 15 NESTED TRANSACTIONS Txn #1 BEGIN WRITE(A) BEGIN BEGIN WRITE(B) BEGIN WRITE(C) COMMIT WRITE(D) ROLLBACK COMMIT CMU 15-721 (Spring 2016)

  7. 15 NESTED TRANSACTIONS Txn #1 BEGIN WRITE(A) BEGIN BEGIN WRITE(B) BEGIN WRITE(C) COMMIT WRITE(D) ROLLBACK COMMIT CMU 15-721 (Spring 2016)

  8. 15 NESTED TRANSACTIONS Txn #1 BEGIN WRITE(A) BEGIN BEGIN WRITE(B) BEGIN WRITE(C) COMMIT WRITE(D) ROLLBACK COMMIT CMU 15-721 (Spring 2016)

  9. 15 NESTED TRANSACTIONS Txn #1 BEGIN WRITE(A) BEGIN BEGIN WRITE(B) BEGIN WRITE(C) COMMIT WRITE(D) ROLLBACK COMMIT CMU 15-721 (Spring 2016)

  10. 15 NESTED TRANSACTIONS Txn #1 BEGIN Sub-Txn #1.1 WRITE(A) BEGIN BEGIN WRITE(B) BEGIN WRITE(C) COMMIT WRITE(D) ROLLBACK COMMIT CMU 15-721 (Spring 2016)

  11. 15 NESTED TRANSACTIONS Txn #1 BEGIN Sub-Txn #1.1 WRITE(A) BEGIN BEGIN WRITE(B) BEGIN WRITE(C) COMMIT WRITE(D) ROLLBACK COMMIT CMU 15-721 (Spring 2016)

  12. 15 NESTED TRANSACTIONS Txn #1 BEGIN Sub-Txn #1.1 WRITE(A) BEGIN BEGIN WRITE(B) BEGIN WRITE(C) COMMIT WRITE(D) ROLLBACK COMMIT CMU 15-721 (Spring 2016)

  13. 15 NESTED TRANSACTIONS Txn #1 BEGIN Sub-Txn #1.1 WRITE(A) BEGIN BEGIN WRITE(B) BEGIN WRITE(C) COMMIT WRITE(D) ROLLBACK COMMIT CMU 15-721 (Spring 2016)

  14. 15 NESTED TRANSACTIONS Txn #1 BEGIN Sub-Txn #1.1 WRITE(A) BEGIN BEGIN Sub-Txn #1.1.1 WRITE(B) BEGIN WRITE(C) COMMIT WRITE(D) ROLLBACK COMMIT CMU 15-721 (Spring 2016)

  15. 15 NESTED TRANSACTIONS Txn #1 BEGIN Sub-Txn #1.1 WRITE(A) BEGIN BEGIN Sub-Txn #1.1.1 WRITE(B) BEGIN BEGIN WRITE(C) COMMIT WRITE(D) ROLLBACK COMMIT CMU 15-721 (Spring 2016)

  16. 15 NESTED TRANSACTIONS Txn #1 BEGIN Sub-Txn #1.1 WRITE(A) BEGIN BEGIN Sub-Txn #1.1.1 WRITE(B) BEGIN BEGIN WRITE(C) COMMIT WRITE(D) ROLLBACK COMMIT CMU 15-721 (Spring 2016)

  17. 15 NESTED TRANSACTIONS Txn #1 BEGIN Sub-Txn #1.1 WRITE(A) BEGIN BEGIN Sub-Txn #1.1.1 WRITE(B) BEGIN BEGIN WRITE(C) COMMIT WRITE(D) ROLLBACK COMMIT CMU 15-721 (Spring 2016)

  18. 15 NESTED TRANSACTIONS Txn #1 BEGIN Sub-Txn #1.1 WRITE(A) BEGIN BEGIN Sub-Txn #1.1.1 WRITE(B) BEGIN BEGIN WRITE(C) COMMIT WRITE(D) ROLLBACK COMMIT CMU 15-721 (Spring 2016)

  19. 15 NESTED TRANSACTIONS Txn #1 BEGIN Sub-Txn #1.1 WRITE(A) BEGIN BEGIN Sub-Txn #1.1.1 WRITE(B) BEGIN BEGIN WRITE(C) COMMIT WRITE(D) ROLLBACK COMMIT CMU 15-721 (Spring 2016)

  20. 15 NESTED TRANSACTIONS Txn #1 BEGIN Sub-Txn #1.1 WRITE(A) BEGIN BEGIN Sub-Txn #1.1.1 X WRITE(B) BEGIN BEGIN X WRITE(C) COMMIT X WRITE(D) ROLLBACK COMMIT CMU 15-721 (Spring 2016)

  21. 15 NESTED TRANSACTIONS Txn #1 BEGIN Sub-Txn #1.1 ✓ WRITE(A) BEGIN BEGIN Sub-Txn #1.1.1 X WRITE(B) BEGIN BEGIN X WRITE(C) COMMIT X WRITE(D) ROLLBACK COMMIT CMU 15-721 (Spring 2016)

  22. 16 BULK UPDATE PROBLEM These other txn models are nice, but they still do not solve our bulk update problem. Chained txns seems like the right idea but they require the application to handle failures and maintain it’s own state. → Has to be able to reverse changes when things fail. CMU 15-721 (Spring 2016)

  23. 17 COMPENSATING TRANSACTIONS A special type of txn that is designed to semantically reverse the effects of another already committed txn. Reversal has to be logical instead of physical. → Example: Decrement a counter by one instead of reverting to the original value. CMU 15-721 (Spring 2016)

  24. 18 SAGA TRANSACTIONS A sequence of chained txns T 1 ,…,T n and compensating txns C 1 ,…,C n-1 where one of the following is guaranteed: → The txns will commit in the order T1 ,…,Tn → The txns will commit in the order T 1 ,…,T j ,C j ,…,C 1 (where j < n) SAGAS SIGMOD, pp. 249-259, 2014. CMU 15-721 (Spring 2016)

  25. 19 SAGA TRANSACTIONS Txn #1 Txn #2 Txn #3 BEGIN BEGIN BEGIN WRITE(A+1) WRITE(B+1) WRITE(C+1) COMMIT COMMIT Comp Txn #1 Comp Txn #2 Comp Txn #3 BEGIN BEGIN BEGIN WRITE(A-1) WRITE(B-1) WRITE(C-1) COMMIT COMMIT COMMIT CMU 15-721 (Spring 2016)

  26. 19 SAGA TRANSACTIONS Txn #1 Txn #2 Txn #3 BEGIN BEGIN BEGIN WRITE(A+1) WRITE(B+1) WRITE(C+1) COMMIT COMMIT Comp Txn #1 Comp Txn #2 Comp Txn #3 BEGIN BEGIN BEGIN WRITE(A-1) WRITE(B-1) WRITE(C-1) COMMIT COMMIT COMMIT CMU 15-721 (Spring 2016)

  27. 19 SAGA TRANSACTIONS Txn #1 Txn #2 Txn #3 BEGIN BEGIN BEGIN WRITE(A+1) WRITE(B+1) WRITE(C+1) COMMIT COMMIT Comp Txn #1 Comp Txn #2 Comp Txn #3 BEGIN BEGIN BEGIN WRITE(A-1) WRITE(B-1) WRITE(C-1) COMMIT COMMIT COMMIT CMU 15-721 (Spring 2016)

  28. 19 SAGA TRANSACTIONS Txn #1 Txn #2 Txn #3 BEGIN BEGIN BEGIN WRITE(A+1) WRITE(B+1) WRITE(C+1) COMMIT COMMIT Comp Txn #1 Comp Txn #2 Comp Txn #3 BEGIN BEGIN BEGIN WRITE(A-1) WRITE(B-1) WRITE(C-1) COMMIT COMMIT COMMIT CMU 15-721 (Spring 2016)

  29. 19 SAGA TRANSACTIONS Txn #1 Txn #2 Txn #3 BEGIN BEGIN BEGIN WRITE(A+1) WRITE(B+1) WRITE(C+1) COMMIT COMMIT Comp Txn #1 Comp Txn #2 Comp Txn #3 BEGIN BEGIN BEGIN WRITE(A-1) WRITE(B-1) WRITE(C-1) COMMIT COMMIT COMMIT CMU 15-721 (Spring 2016)

  30. 19 SAGA TRANSACTIONS Txn #1 Txn #2 Txn #3 BEGIN BEGIN BEGIN WRITE(A+1) WRITE(B+1) WRITE(C+1) COMMIT COMMIT Comp Txn #1 Comp Txn #2 Comp Txn #3 BEGIN BEGIN BEGIN WRITE(A-1) WRITE(B-1) WRITE(C-1) COMMIT COMMIT COMMIT CMU 15-721 (Spring 2016)

  31. 19 SAGA TRANSACTIONS Txn #1 Txn #2 Txn #3 BEGIN BEGIN BEGIN WRITE(A+1) WRITE(B+1) WRITE(C+1) COMMIT COMMIT Comp Txn #1 Comp Txn #2 Comp Txn #3 BEGIN BEGIN BEGIN WRITE(A-1) WRITE(B-1) WRITE(C-1) COMMIT COMMIT COMMIT CMU 15-721 (Spring 2016)

  32. 20 CONCURRENCY CONTROL The protocol to allow txns to access a database in a multi-programmed fashion while preserving the illusion that each of them is executing alone on a dedicated system. → The goal is to have the effect of a group of txns on the database’s state is equivalent to any serial execution of all txns. Provides A tomicity + I solation in ACID CMU 15-721 (Spring 2016)

  33. 21 TXN INTERNAL STATE Undo Log Entries → Stored in an in-memory data structure. → Dropped on commit. Redo Log Entries → Append to the in-memory tail of WAL. → Flushed to disk on commit. Read/Write Set → Depends on the concurrency control scheme. CMU 15-721 (Spring 2016)

  34. 22 CONCURRENCY CONTROL SCHEMES Two-Phase Locking (2PL) → Assume txns will conflict so they must acquire locks on elements before they are allowed to access them. Timestamp Ordering (T/O) → Assume that conflicts are rare so txns do not need to acquire locks and instead check for conflicts at commit time. CMU 15-721 (Spring 2016)

  35. 23 TWO-PHASE LOCKING Txn #1 COMMIT BEGIN LOCK(A ) READ(A) LOCK(B) WRITE(B) UNLOCK(A) UNLOCK(B) CMU 15-721 (Spring 2016)

  36. 23 TWO-PHASE LOCKING Txn #1 COMMIT BEGIN LOCK(A ) LOCK(A) READ(A) LOCK(B) LOCK(B) WRITE(B) UNLOCK(A) UNLOCK(B) CMU 15-721 (Spring 2016)

  37. 23 TWO-PHASE LOCKING Txn #1 COMMIT BEGIN LOCK(A ) LOCK(A) READ(A) LOCK(B) LOCK(B) WRITE(B) UNLOCK(A) UNLOCK(B) CMU 15-721 (Spring 2016)

  38. 23 TWO-PHASE LOCKING Txn #1 COMMIT BEGIN LOCK(A ) LOCK(A) READ(A) LOCK(B) LOCK(B) WRITE(B) UNLOCK(A) UNLOCK(B) Growing Phase CMU 15-721 (Spring 2016)

  39. 23 TWO-PHASE LOCKING Txn #1 COMMIT BEGIN LOCK(A ) LOCK(A) READ(A) LOCK(B) LOCK(B) WRITE(B) UNLOCK(A) UNLOCK(B) Growing Phase Shrinking Phase CMU 15-721 (Spring 2016)

  40. 23 TWO-PHASE LOCKING Txn #1 COMMIT BEGIN LOCK(A ) LOCK(A) READ(A) LOCK(B) LOCK(B) WRITE(B) UNLOCK(A) UNLOCK(B) CMU 15-721 (Spring 2016)

  41. 23 TWO-PHASE LOCKING Txn #1 COMMIT BEGIN LOCK(A ) LOCK(A) READ(A) LOCK(B) LOCK(B) WRITE(B) UNLOCK(A) UNLOCK(B) Txn #2 COMMIT BEGIN LOCK(B) WRITE(B) LOCK(A) WRITE(A) UNLOCK(A) UNLOCK(B) CMU 15-721 (Spring 2016)

  42. 23 TWO-PHASE LOCKING Txn #1 COMMIT BEGIN LOCK(A ) LOCK(A) READ(A) LOCK(B) LOCK(B) WRITE(B) UNLOCK(A) UNLOCK(B) Txn #2 COMMIT BEGIN LOCK(B) WRITE(B) LOCK(A) WRITE(A) UNLOCK(A) UNLOCK(B) CMU 15-721 (Spring 2016)

  43. 23 TWO-PHASE LOCKING Txn #1 COMMIT BEGIN LOCK(A ) LOCK(A) READ(A) LOCK(B) LOCK(B) WRITE(B) UNLOCK(A) UNLOCK(B) Txn #2 COMMIT BEGIN LOCK(B) WRITE(B) LOCK(A) WRITE(A) UNLOCK(A) UNLOCK(B) CMU 15-721 (Spring 2016)

  44. 23 TWO-PHASE LOCKING Txn #1 COMMIT BEGIN LOCK(A ) LOCK(A) READ(A) LOCK(B) LOCK(B) WRITE(B) UNLOCK(A) UNLOCK(B) Txn #2 COMMIT BEGIN LOCK(B) WRITE(B) LOCK(A) WRITE(A) UNLOCK(A) UNLOCK(B) CMU 15-721 (Spring 2016)

  45. 23 TWO-PHASE LOCKING Txn #1 COMMIT BEGIN LOCK(A ) LOCK(A) READ(A) LOCK(B) LOCK(B) WRITE(B) UNLOCK(A) UNLOCK(B) Txn #2 COMMIT BEGIN LOCK(B) WRITE(B) LOCK(A) WRITE(A) UNLOCK(A) UNLOCK(B) CMU 15-721 (Spring 2016)

  46. 23 TWO-PHASE LOCKING Txn #1 COMMIT BEGIN LOCK(A ) LOCK(A) READ(A) LOCK(B) LOCK(B) WRITE(B) UNLOCK(A) UNLOCK(B) Txn #2 COMMIT BEGIN LOCK(B) WRITE(B) LOCK(A) WRITE(A) UNLOCK(A) UNLOCK(B) CMU 15-721 (Spring 2016)

  47. 23 TWO-PHASE LOCKING Txn #1 COMMIT BEGIN LOCK(A ) LOCK(A) READ(A) LOCK(B) LOCK(B) WRITE(B) UNLOCK(A) UNLOCK(B) Txn #2 COMMIT BEGIN LOCK(B) WRITE(B) LOCK(A) WRITE(A) UNLOCK(A) UNLOCK(B) CMU 15-721 (Spring 2016)

  48. 23 TWO-PHASE LOCKING Txn #1 COMMIT BEGIN LOCK(A ) LOCK(A) READ(A) LOCK(B) LOCK(B) WRITE(B) UNLOCK(A) UNLOCK(B) Txn #2 COMMIT BEGIN LOCK(B) WRITE(B) LOCK(A) WRITE(A) UNLOCK(A) UNLOCK(B) CMU 15-721 (Spring 2016)

  49. 24 TWO-PHASE LOCKING Deadlock Detection → Each txn maintains a queue of the txns that hold the locks that it waiting for. → A separate thread checks these queues for deadlocks. → If deadlock found, use a heuristic to decide what txn to kill in order to break deadlock. Deadlock Prevention → Check whether another txn already holds a lock when another txn requests it. → If lock is not available, the txn will either (1) wait, (2) commit suicide, or (3) kill the other txn. CMU 15-721 (Spring 2016)

  50. • • • • • • • 25 TIMESTAMP ORDERING Txn #1 COMMIT BEGIN READ(A) WRITE(B) WRITE(A) CMU 15-721 (Spring 2016)

  51. • • • • • • • 25 TIMESTAMP ORDERING Txn #1 COMMIT BEGIN READ(A) WRITE(B) WRITE(A) CMU 15-721 (Spring 2016)

  52. • • • • • • • 25 TIMESTAMP ORDERING 10001 Txn #1 COMMIT BEGIN READ(A) WRITE(B) WRITE(A) CMU 15-721 (Spring 2016)

  53. • • • • • • • 25 TIMESTAMP ORDERING 10001 Txn #1 COMMIT BEGIN READ(A) WRITE(B) WRITE(A) Read Write Record Timestamp Timestamp A 10000 10000 B 10000 10000 CMU 15-721 (Spring 2016)

  54. • • • • • • • 25 TIMESTAMP ORDERING 10001 Txn #1 COMMIT BEGIN READ(A) WRITE(B) WRITE(A) Read Write Record Timestamp Timestamp A 10000 10000 B 10000 10000 CMU 15-721 (Spring 2016)

  55. • • • • • • • 25 TIMESTAMP ORDERING 10001 Txn #1 COMMIT BEGIN READ(A) WRITE(B) WRITE(A) Read Write Record Timestamp Timestamp A 10001 10000 B 10000 10000 CMU 15-721 (Spring 2016)

Download Presentation
Download Policy: The content available on the website is offered to you 'AS IS' for your personal information and use only. It cannot be commercialized, licensed, or distributed on other websites without prior consent from the author. To download a presentation, simply click this link. If you encounter any difficulties during the download process, it's possible that the publisher has removed the file from their server.

Recommend


More recommend