1 / 35

Data Concurrency, Consistency and Integrity

This document explores essential principles in managing data within database systems, focusing on concurrency, consistency, and integrity. It outlines foundational goals such as maintaining data validity and accessibility while illustrating concepts through simple to complex models. Key topics include transaction properties (ACID), isolation levels, fund transfer operations, and the Oracle implementation of multiversion concurrency control. Practical examples highlight the interplay between read and write processes, deadlock avoidance, and the enforcement of business rules, showcasing the importance of thoughtful data management.

keefe-jimenez
Télécharger la présentation

Data Concurrency, Consistency and Integrity

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. Data Concurrency, Consistency and Integrity

  2. My goals • Managing Data • Keep it valid • Let people access it • Start from simple models • Build more complex ones • High-Level Abstraction • Oracle implementation • No Code

  3. You • Programming • Databases • Concurrency • SQL • Oracle

  4. A Simple Model Read Process Write Read Process Write … Processor Read Write Memory

  5. Increase a counter: Single User 1+1 ⇒ 2 2+1 ⇒ 3 1 2 2 3 Time

  6. Increase a counter: Multi User 1+1 ⇒ 2 1 1 2 2 Time 1+1 ⇒ 2

  7. Increase a counter: Multi User 1+1 ⇒ 2 1 1 2 2 3 2 Time 1+1 ⇒ 2 2+1 ⇒ 3

  8. Locking 1+1 ⇒ 2 1 1 2 2 Time Read Lock Write Lock 1+1 ⇒ 2

  9. Concurrency Data Concurrency means that many users can access data at the same time Locks limit concurrency

  10. Transfer Funds: Consistency Transfer 50€: (100,100)⇒(50,150) A:50€ B:100€ A:50€ B:150€ A:100€ B:100€ T:200€ T:150€ T:200€

  11. Transaction A transaction comprises a unit of work treated in a coherent and reliable way independent of other transactions.

  12. Transfer Funds: Failures Transfer 50€: (100,100)⇒(50,150) A:50€ B:100€ A:100€ B:100€ Anything can go wrong at any time

  13. Transaction Properties • Atomicity (all or nothing) • Consistency (From Valid to Valid) • Isolation (No interference) • Durability (Changes are reliably persisted) ACID

  14. Commit 1+1 ⇒ 2 Transaction #1 Commit 1 2 Transaction #2 1 1 2 Multiple versions must be kept

  15. Transfer Funds: Commit Transfer 50€ T#1 Commit A:50€ B:100€ A:50€ B:150€ A:100€ B:100€ T#2 T:200€ T:200€ T:200€

  16. Transfer Funds: Rollback Transfer 50€ Rollback A:50€ B:100€ A:100€ B:100€ A:100€ B:100€ Automatic or user initiated

  17. Transaction Isolation Levels • Read Phenomena • Dirty Reads: read uncommitted data of another transaction • Nonrepeatable & Phantom Reads: read changes committed by another transaction

  18. Oracle Read Consistency Statement-Level Read Consistency is always guaranteed Session-Level Read Consistency is guaranteed in only serializable mode

  19. Oracle Read Consistency • Same read ⇒ 100€ • Multiple reads • Read committed ⇒ 150€ • Serializable ⇒ 100€ T#1 Transfer 50€ A:100€ B:100€ A:50€ B:150€ Same read? A:100€ B:100 or 150€? T#2

  20. Multiversion Concurrency Control System Change Number (SCN): the Oracle database “clock”, incremented at every insert, update, delete. A query made at (SCN) time T return the most recent (highest SCN) records whose SCN is less or equal to T Read SCN = 1 Readers never block Writers Writers never block Readers Read SCN = 10 * *except when they do

  21. Updates: Locks Write 1 T#1 Commit 1 2 Wait T#2 Commit Write 2

  22. Locks: Implicit Vs Explicit • Implicit Locks • Automatic for every operation • Locks as little as possible for highest concurrency • Explicit Locking • transaction-level read consistency (repeatable reads) • Cannot afford to wait once it has started

  23. Increase a Counter 1 Read Compute Update Commit 1 Read Compute Update Commit 1 2 2 2 Wait 2

  24. Increase a Counter 1 Lock Read Compute Update Commit Lock Read Compute Update 5. Commit 1 2 Wait 2 2 3 3

  25. Optimistic Locking A|1 Read Value & Version Compute Lock Check if version changed. If no (1=1) then Update Value & Version Commit Read Value & Version Compute Lock Check version ⇒ Exception A|1 A|1 B C B|2

  26. Deadlocks Avoid by always locking in the same order. Transaction #1 Update A Update B Transaction #2 Update B Update A Wait #2 Wait #1 Deadlock!

  27. Data Integrity • Enforce Business Rules • Player • have a last name • have age > 0 • have a unique login name • belong to a team (reference)

  28. Unique Constraint • Insert new player with login “chierico” • Check that no other “chierico” exists • Insert new “chierico” record • Make sure no one insert it between your “check” and your “insert” ⇒ • Lock whole table ⇒ Bad for Concurrency • Check • Insert • Commit DBs offer a proper “unique” constraint.

  29. Lessons Learned • Always think about how others might use the data • No “one solution fits all” • Databases offer valuable abstractions • Flexible • Safe • Declarative • Not all databases behave the same way

  30. Q&A

  31. спасибо Globe of Science and Innovation, CERN

More Related