CMSC 424 – Database designLecture 21Concurrency/recoveryMihai PopAdmin•Office hours tomorrow @ 10am in AVW 3223Serializability•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 schedulesConflict 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 effectEquivalence by SwappingT1read(A)A = A -50write(A)read(B)B=B+50write(B)T2read(A)tmp = A*0.1A = A – tmpwrite(A)read(B)B = B+ tmpwrite(B)T1read(A)A = A -50write(A)read(B)B=B+50write(B)T2read(A)tmp = A*0.1A = A – tmpwrite(A)read(B)B = B+ tmpwrite(B)Effect: Before After A 100 45 B 50 105Effect: Before After A 100 45 B 50 105==Equivalence by SwappingT1read(A)A = A -50write(A)read(B)B=B+50write(B)T2read(A)tmp = A*0.1A = A – tmpwrite(A)read(B)B = B+ tmpwrite(B)T1read(A)A = A -50write(A)read(B)B=B+50write(B)T2read(A)tmp = A*0.1A = A – tmpwrite(A)read(B)B = B+ tmpwrite(B)Effect: Before After A 100 45 B 50 105Effect: Before After A 100 45 B 50 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 scheduleEquivalence by SwappingT1read(A)A = A -50write(A)read(B)B=B+50write(B)T2read(A)tmp = A*0.1A = A – tmpwrite(A)read(B)B = B+ tmpwrite(B)T1read(A)A = A -50write(A)read(B)B=B+50write(B)T2read(A)tmp = A*0.1A = A – tmpwrite(A)read(B)B = B+ tmpwrite(B)Effect: Before After A 100 45 B 50 105Effect: Before After A 100 45 B 50 105==Equivalence by SwappingT1read(A)A = A -50write(A)read(B)B=B+50write(B)T2read(A)tmp = A*0.1A = A – tmpwrite(A)read(B)B = B+ tmpwrite(B)T1read(A)A = A -50write(A)read(B)B=B+50write(B)T2read(A)tmp = A*0.1A = A – tmpwrite(A)read(B)B = B+ tmpwrite(B)Effect: Before After A 100 45 B 50 105Effect: Before After A 100 45 B 50 105==Example Schedules (Cont.) A “bad” scheduleT1read(A)A = A -50write(A)read(B)B=B+50write(B)T2read(A)tmp = A*0.1A = A – tmpwrite(A)read(B)B = B+ tmpwrite(B)XYCan’t move Y below X read(B) and write(B) conflictOther options don’t work eitherSo: Not Conflict SerializableSerializability•In essence, following set of instructions is not conflict-serializable: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 theseTesting 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 GraphT1 T2 T3 T4 T5read(X)read(Y)read(Z)read(V)read(W)read(W)read(Y)write(Y)write(Z)read(U)read(Y)write(Y)read(Z)write(Z)read(U)write(U)T3T4T1T2Recap•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 committedT1read(A)A = A -50write(A)read(B)B=B+50write(B)ABORTT2read(A)tmp = A*0.1A = A – tmpwrite(A)COMMITRecoverability•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 wellRecap•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-serializableConcurrency controlApproach, Assumptions etc..•Approach–Guarantee conflict-serializability by allowing certain types of
View Full Document
Unlocking...