1 / 38

CS542

CS542. Transaction Management Overview. Professor Elke A. Rundensteiner. Name. Age. White Green Gray. 52 3421 1. Integrity of data ?. We would like data in our database to be “accurate” ( “correct” ) at all times. EMP How DBMS decides if data is consistent?.

mamieo
Download Presentation

CS542

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. CS542 Transaction Management Overview Professor Elke A. Rundensteiner

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

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

  4. Definitions: • Consistent state: satisfies all constraints • Consistent DB: DB in consistent state

  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. 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. Transactions • Concurrent execution of user programs is essential for good DBMS performance. • Because disk accesses are frequent and slow, must keep CPU busy by working on several user programs concurrently. • A user’s program may carry out many operations on data, but DBMS only concerned about what data is read/written from/to database. • Transactionis DBMS’s abstract view of user program: a sequence of reads and writes.

  10. Transaction ACID Properties • Atomic • Either all actions are carried out or none are. • Not worry about incomplete transaction. • Consistency • DBMS assumes that the consistency holds for each transaction. • User is responsible. • Isolation • Transactions are isolated, or protected, from effects of concurrently scheduling other transactions. • Durability • The effects of transaction persist if DBMS informs the user about successful execution.

  11. Atomicity of Transactions • A transaction is seen by DBMS as a series of actions. • read/write database object. • A transaction might : • commitafter completing all its actions, or • could abort(aborted by DBMS) after executing some actions. • Transactions are atomic: • user can think of a transaction as always executing all its actions in one step, or not executing any actions at all. • DBMS logs all actions so that it can undoactions of aborted transactions.

  12. Concurrency in a DBMS • Users submit transactions, and can think of each transaction as executing by itself. • Concurrency : DBMS interleaves actions (reads/writes of DB objects) of various transactions. • Each transaction must leave database in consistent state if DB is consistent when transaction begins. • DBMS will enforce ICs declared in CREATE TABLE statements. • Beyond this, DBMS does not understand semantics of data. • Issues: Effect ofinterleaving transactions, and crashes.

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

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

  15. 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 Violations due to sharing (concurrency control)!

  16. Concurrency Control T1 T2 … Tn DB (consistency constraints)

  17. Example: T1: Read(A) T2: Read(A) A  A+100 A  A2 Write(A) Write(A) Read(B) Read(B) B  B+100 B  B2 Write(B) Write(B) Constraint: A=B

  18. A schedule An ordering of operations inside one or more transactions over time Why interleave operations?

  19. A B 25 25 125 125 250 250 250 250 Schedule A T1 T2 Read(A); A  A+100 Write(A); Read(B); B  B+100; Write(B); Read(A);A  A2; Write(A); Read(B);B  B2; Write(B);

  20. A B 25 25 50 50 150 150 150 150 Schedule B T1 T2 Read(A);A  A2; Write(A); Read(B);B  B2; Write(B); Read(A); A  A+100 Write(A); Read(B); B  B+100; Write(B);

  21. Serial Schedules ! • Any serial schedule is “good”.

  22. Interleave Transactionsina Schedule?

  23. A B 25 25 125 250 125 250 250 250 Schedule C T1 T2 Read(A); A  A+100 Write(A); Read(A);A  A2; Write(A); Read(B); B  B+100; Write(B); Read(B);B  B2; Write(B);

  24. A B 25 25 125 250 50 150 250 150 Schedule D T1 T2 Read(A); A  A+100 Write(A); Read(A);A  A2; Write(A); Read(B);B  B2; Write(B); Read(B); B  B+100; Write(B);

  25. A B 25 25 125 125 25 125 125 125 Same as Schedule D but with new T2’ Schedule E T1 T2’ Read(A); A  A+100 Write(A); Read(A);A  A1; Write(A); Read(B);B  B1; Write(B); Read(B); B  B+100; Write(B);

  26. What is a ‘good’ schedule?

  27. We want : • schedules that are “good” regardless of • initial state and • transaction semantics • Hence we consider : • Only order of read and writes Example: Sc=r1(A)w1(A)r2(A)w2(A)r1(B)w1(B)r2(B)w2(B)

  28. Example: Sc=r1(A)w1(A)r2(A)w2(A)r1(B)w1(B)r2(B)w2(B) Sc’=r1(A)w1(A) r1(B)w1(B)r2(A)w2(A)r2(B)w2(B) T1 T2

  29. Remember Schedule C Sc=r1(A)w1(A)r2(A)w2(A)r1(B)w1(B)r2(B)w2(B) T1 T2 T1 T2  no cycles  C is “equivalent” to a serial schedule, I.e., in this case (T1,T2).

  30. A B 25 25 125 250 50 150 250 150 Schedule D T1 T2 Read(A); A  A+100 Write(A); Read(A);A  A2; Write(A); Read(B);B  B2; Write(B); Read(B); B  B+100; Write(B);

  31. Let’s look at schedule Sd: Sd=r1(A)w1(A)r2(A)w2(A) r2(B)w2(B)r1(B)w1(B) Sd=r1(A)w1(A) r1(B)w1(B)r2(A)w2(A)r2(B)w2(B) T2 T1

  32. Or, let’s try for Sd: Sd=r1(A)w1(A)r2(A)w2(A) r2(B)w2(B)r1(B)w1(B) Sd=r2(A)w2(A)r2(B)w2(B) r1(A)w1(A)r1(B)w1(B) T2 T1

  33. We cannot do the needed swaps for Sd: Sd=r1(A)w1(A)r2(A)w2(A) r2(B)w2(B)r1(B)w1(B) • there seems to be no save way to transform Sd into an equivalent serial schedule !?!

  34. For Schedule D: Sd=r1(A)w1(A)r2(A)w2(A) r2(B)w2(B)r1(B)w1(B) T1 T2 T2 T1 T1 T2

  35. For Schedule D: Sd=r1(A)w1(A)r2(A)w2(A) r2(B)w2(B)r1(B)w1(B) T1 T2 T2 T1 T1 T2 Cycle in schedule D.

  36. If T2 T1 • and T1 T2 T1 T2 Sd cannot be rearranged into a serial schedule Sd is not “equivalent” to any serial schedule Sd thus is a “bad” schedule! If cycle of dependencies between transactions:

  37. Algorithm Idea: Swap non-conflicting operation pairs to see if you can go to a serial schedule.

  38. Summary : Review of Concepts Transaction: sequence of ri(x), wi(x) actions Conflicting actions (if on same resource): r1(A) w2(A) w1(A) w2(A) r1(A) w2(A) Schedule: represents chronological order in which actions are executed Serial schedule: no interleaving of actions or transactions

More Related