Transactions Amol Deshpande CMSC424 Overview Transaction A sequence of database actions enclosed within special tags Properties Atomicity Entire transaction or nothing Consistency Transaction executed completely takes database from one consistent state to another Isolation Concurrent transactions appear to run in isolation Durability Effects of committed transactions are not lost Consistency Transaction programmer needs to guarantee that DBMS can do a few things e g enforce constraints on the data Rest DBMS guarantees How does this relate to queries that we discussed Queries don t update data so durability and consistency not relevant Would want concurrency Consider a query computing total balance at the end of the day Would want isolation What if somebody makes a transfer while we are computing the balance Typically not guaranteed for such long running queries TPC C vs TPC H Assumptions and Goals Assumptions The system can crash at any time Similarly the power can go out at any point Contents of the main memory won t survive a crash or power outage BUT disks are durable They might stop but data is not lost For now Disks only guarantee atomic sector writes nothing more Transactions are by themselves consistent Goals Guaranteed durability atomicity As much concurrency as possible while not compromising isolation and or consistency Two transactions updating the same account balance NO Two transactions updating different account balances YES Next States of a transaction A simple solution called shadow copy Satisfies Atomicity Durability and Consistency but no Concurrency Very inefficient Transaction states Shadow Copy Make updates on a copy of the database Switch pointers atomically after done Some text editors work this way Shadow Copy Atomicity As long as the DB pointer switch is atomic Okay if DB pointer is in a single block Concurrency No Isolation No concurrency so isolation is guaranteed Durability Assuming disk is durable we will assume this for now Very inefficient Databases tend to be very large Making extra copies not feasible Further no concurrency Next Concurrency control schemes A CC scheme is used to guarantee that concurrency does not lead to problems For now we will assume durability is not a problem So no crashes Though transactions may still abort Schedules When is concurrency okay Serial schedules Serializability A Schedule Transactions T1 transfers 50 from A to B T2 transfers 10 of A to B Database constraint A B is constant checking saving accts T1 read A A A 50 write A read B B B 50 write B T2 Effect read A tmp A 0 1 A A tmp write A read B B B tmp write B Before A 100 B 50 After 45 105 Each transaction obeys the constraint This schedule does too Schedules A schedule is simply a possibly interleaved execution sequence of transaction instructions Serial Schedule A schedule in which transaction appear one after the other ie No interleaving Serial schedules satisfy isolation and consistency Since each transaction by itself does not introduce inconsistency Example Schedule Another serial schedule T1 read A A A 50 write A read B B B 50 write B T2 read A tmp A 0 1 A A tmp write A read B B B tmp write B Effect Before A 100 B 50 After 40 110 Consistent Constraint is satisfied Since each Xion is consistent any serial schedule must be consistent Another schedule T1 read A A A 50 write A T2 Is this schedule okay read A tmp A 0 1 A A tmp write A read B B B 50 write B read B B B tmp write B Lets look at the final effect Effect Before A 100 B 50 After 45 105 Consistent So this schedule is okay too Another schedule T1 read A A A 50 write A T2 Is this schedule okay read A tmp A 0 1 A A tmp write A read B B B 50 write B read B B B tmp write B Lets look at the final effect Effect Before A 100 B 50 After 45 105 Further the effect same as the serial schedule 1 Called serializable Example Schedules Cont A bad schedule T1 read A A A 50 T2 read A tmp A 0 1 A A tmp write A read B write A read B B B 50 write B B B tmp write B Effect Before A 100 B 50 Not consistent After 50 60 Serializability A schedule is called serializable if its final effect is the same as that of a serial schedule Serializability schedule is fine and does not result in inconsistent database Since serial schedules are fine Non serializable schedules are unlikely to result in consistent databases We will ensure serializability Typically relaxed in real high throughput environments Serializability Not possible to look at all n serial schedules to check if the effect is the same Instead we ensure serializability by allowing or not allowing certain schedules Conflict serializability View serializability View serializability allows more schedules Conflict Serializability Two read write instructions conflict if They are by different transactions They operate on the same data item At least one is a write instruction Why do we care If two read write instructions don t conflict they can be swapped without any change in the final effect However if they conflict they CAN T be swapped without changing the final effect Equivalence by Swapping T1 read A A A 50 write A T1 read A A A 50 write A T2 read A tmp A 0 1 A A tmp write A read B read B B B 50 write B B B 50 write B read B B B tmp write B Effect Before A 100 B 50 After 45 105 T2 read A tmp A 0 1 A A tmp write A read B B B tmp write B Effect Before A 100 B 50 After 45 105 Equivalence by Swapping T1 read A A A 50 write A T1 read A A A 50 write A T2 read A tmp A 0 1 A A tmp write A read B B B 50 write B read B B B 50 write B read B B B tmp write B Effect Before A 100 B 50 After 45 105 Effect T2 read A tmp A 0 1 A A tmp write A read B B B tmp write B Before A 100 B 50 After 45 55 Conflict Serializability Conflict equivalent schedules If S can be transformed into S through a series of swaps S and S are called conflict equivalent conflict equivalent guarantees same final effect on the database A schedule S is conflict serializable if it is conflict equivalent to a serial schedule Equivalence by Swapping T1 read A A A 50 write A T1 read A A A 50 write A T2 read A tmp A 0 1 A A tmp write A read B B B 50 read B B B 50 write B write B read B B B tmp write B Effect Before A 100 B 50 After 45 105 T2 read A tmp A 0 1 A A tmp write A read B B B tmp write B Effect Before …
View Full Document
Unlocking...