1 / 13

Database System Transactions: Ensuring Data Consistency and Integrity

Explore how transactions, constraints, and actions preserve database integrity. Learn methods to prevent and fix data consistency violations in DBMS. Dive into transaction processing, crash recovery, and concurrency control.

nura
Download Presentation

Database System Transactions: Ensuring Data 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. CS4432: Database Systems II Lecture #19 Database Consistency and Transactions Professor Elke A. Rundensteiner

  2. Transactions, etc. • Crash recovery Ch. [17] • Concurrency control Ch. [18] • Transaction processing Ch. [19]

  3. Name Age White Green Gray 52 3421 1 Integrity or correctness of data ? • We would like data in our database to be “accurate” ( “correct” ) at all times. EMP • How DBMS decides if data is consistent?

  4. Integrity or consistency constraints • Utilize predicates data must satisfy • Examples: - x is key of relation R - x  y holds in R - Domain(x) = {Red, Blue, Green} - no employee should make more than twice the average salary

  5. Such constraints may not capture “full correctness” Example 1 :Transaction constraints • When salary is updated, new salary > old salary • When account record is deleted, balance = 0

  6. in any case, continue with constraints ... Observation: DB cannot be consistent always Example: Constraint : a1 + a2 +…. an = TOT Action: Deposit $100 in a2: a2 a2 + 100 TOT  TOT + 100

  7. Example: a1 + a2 +…. an = TOT (constraint) Deposit $100 in a2: a2 a2 + 100 TOT  TOT + 100 a2 TOT . . . . . . 50 150 150 . . . . . . 1000 1000 1100

  8. Transaction: a collection of actions that preserve consistency Consistent DB Consistent DB’ T

  9. Big assumption: If T starts with consistent state AND T executes in isolation  T leaves consistent state

  10. Correctness(informally) • If we stop running transaction(s), DB left consistent • Each transaction sees a consistent DB

  11. How can constraints be violated? • Transaction bug • DBMS bug • Hardware failure e.g., disk crash alters balance of account • Data sharing e.g.: T1: give 10% raise to programmers T2: change programmers  systems analysts

  12. Will not consider: • How to write correct transactions • How to write correct DBMS system • Constraint checking & repair

  13. How can we prevent/fix violations? • Chapter 17: due to failures only • Chapter 18: due to data sharing only • Chapter 19: due to failures and sharing

More Related