CMSC 424 Database design Lecture 21 Concurrency recovery Mihai Pop Admin Office hours tomorrow 10am in AVW 3223 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 T2 read A tmp A 0 1 A A tmp write A read A tmp A 0 1 A A tmp read B read B B B 50 write B write A B B 50 write B read B B B tmp write B Effect Before A 100 B 50 After 45 105 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 T2 read A tmp A 0 1 A A tmp write A read A tmp A 0 1 A A tmp write A read B B B 50 write B read B B B 50 read B write B read B B B tmp write B Effect Before A 100 B 50 After 45 105 B B tmp write B Effect 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 T2 read A tmp A 0 1 A A tmp write A read A tmp A 0 1 A A tmp read B B B 50 read B B B 50 write B write A write B read B B B tmp write B Effect Before A 100 B 50 After 45 105 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 T2 read B B B 50 write B 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 Effect Before A 100 B 50 After 45 105 read B B B tmp write B Effect Before A 100 B 50 After 45 105 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 X Y Can t move Y below X read B and write B conflict Other options don t work either write A read B B B 50 write B B B tmp write B So Not Conflict Serializable Serializability In essence following set of instructions is not conflictserializable View Serializability Similarly following not conflict serializable BUT it is serializable Intuitively this is because the conflicting write instructions don t matter The final write is the only one that matters View serializability allows these Read up chap 15 Other notions of serializability Not conflict serializable or view serializable but serializable Mainly because of the only operations Requires analysis of the actual operations not just read write operations Most high performance transaction systems will allow these Testing for conflict serializability Given a schedule determine if it is conflict serializable Draw a precedence graph over the transactions A directed edge from T1 and T2 if they have conflicting instructions and T1 s conflicting instruction comes first If there is a cycle in the graph not conflict serializable Can be checked in at most O n e time where n is the number of vertices and e is the number of edges If there is none conflict serializable Testing for view serializability is NP hard Example Schedule Schedule A Precedence Graph T1 T2 T3 T4 T5 read X read Y read Z read V read W read W T1 T2 read Y write Y write Z read U read Y write Y read Z write Z read U write U T3 T4 Recap We discussed Serial schedules serializability Conflict serializability view serializability How to check for conflict serializability We haven t discussed How to guarantee serializability Allowing transactions to run and then aborting them if the schedules wasn t serializable is clearly not the way to go We instead use schemes to guarantee that the schedule will be conflict serializable Also recoverability Recoverability Serializability is good for consistency But what if transactions fail T2 has already committed A user might have been notified Now T1 abort creates a problem T2 has seen its effect so just aborting T1 is not enough T2 must be aborted as well and possibly restarted But T2 is committed T1 read A A A 50 write A T2 read A tmp A 0 1 A A tmp write A COMMIT read B B B 50 write B ABORT Recoverability Recoverable schedule If T1 has read something T2 has written T2 must commit before T1 Otherwise if T1 commits and T2 aborts we have a problem Cascading rollbacks If T10 aborts T11 must abort and hence T12 must abort and so on Recoverability Dirty read Reading a value written by a transaction that hasn t committed yet Cascadeless schedules A transaction only reads committed values So if T1 has written A but not committed it T2 can t read it No dirty reads Cascadeless No cascading rollbacks That s good We will try to guarantee that as well Recap We discussed Serial schedules serializability Conflict serializability view serializability How to check for conflict serializability Recoverability cascade less schedules We haven t discussed How to guarantee serializability Allowing transactions to run and then aborting them if the schedules wasn t serializable is clearly not the way to go We instead use schemes to guarantee that the schedule will be conflict serializable Concurrency control Approach Assumptions etc Approach Guarantee conflict serializability by allowing certain types of concurrency Lock based Assumptions Durability is not a problem So no crashes Though transactions may still abort Goal Serializability Minimize the bad effect of aborts cascade less schedules only Lock based Protocols A transaction must get a lock before operating on the data Two types of locks Shared S locks also called read locks Obtained if we want to only read an item Exclusive X locks also called write locks Obtained for updating a data item …
View Full Document
Unlocking...