Concurrency Control 18.1 Serial and Serializable Schedules ~ 18.2 Conflict-Serializability

1. Concurrency Control18.1 Serial and Serializable Schedules~18.2 Conflict-Serializability 2008. 5. 21 황원석 데이터 마이닝 및 지식공학 연구실 한양대학교

2. Concurrency Control • 여러 Transactions의 상호작용에 의해 DB가 inconsistent해 질 수 있음 • Transaction • User나 application에 의해 실행되는 action의 시리즈 • DB의 컨텐츠를 접근(access)하거나, 변경(change)함 • Concurrency Control • DB를 consistent하게 유지할 수 있도록 제어하는 방법 • 다른 transactions에 속하는 여러 actions의 실행 순서를 제약하는 방법 • Scheduler • DBMS의 컴포넌트 • Concurrency Control을 수행 • Transactions의액션을 실행하거나, 보류시킴

3. Concurrency Control • DB의 consistency가 유지되기 위하여 필요한 요소 • 추상적으로 필요한 조건(abstract requirement) • Serializability • 중요한 구체적인 제약 조건(important, stronger condition) • Conflict-serializability • Scheduler에 구현되어야 할 techniques • Locking • Timestamping • Validation

4. Concurrency Control • Correctness Principle • Transactions는 각각 독립적으로 실행됨 • Consistent state에서 consistent state로 변형됨 • 실제로는 여러 Transactions이 동시에 함께 실행되므로 지켜지기 어려움 • “schedules”를 고려해야 함 • Schedules • Transaction이 하나씩 실행된 결과와 같은 결과를 같은 결과를 가지도록 보장 T1 T2 … Tn DB (consistency constraints)

5. 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 (A와 B가 최초에는 같은 값을 가지고 있다고 가정)

6. 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); Schedule A is serial (각 transaction들의 action이 섞이지 않은 상태로 실행됨)

7. 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); Schedule B is serial

8. 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); Schedule C is serializable but not serial (어떤 schedule이 실행된 결과가 어떤 serial schedule이 수행된 결과와 동일한 경우)

9. 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); Schedule D is not serializable but not serial

10. 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); Schedule E is serializable (현실적으로 이러한 연산이 Serializable인지 파악하기 어려움)

11. Want schedules that are “good”, regardless of • initial state • transaction semantics • Only look at order of read and writes • Example: • Sc = r1(A)w1(A)r2(A)w2(A)r1(B)w1(B)r2(B)w2(B)

12. Conflict Serializability • Transaction • sequence of ri(x), wi(x) actions • Schedule • represents chronological order in which actions are executed • Serial schedule • no interleaving of actions or transactions • Conflict Serializability • A condition that is sufficient to assure that a schedule is serializable • Conflict • A pair of consecutive actions in a schedule • If their order is interchanged, then the behavior of at least one of the transactions involved can change • Conflicting actions • r1(A), w2(A) • w2(A), r1(A) • w1(A), w2(A)

13. T2 issues write(B,S) input(B) completes System issues output(B) System issues input(B) output(B) completes B  S What about concurrent actions? Ti issues System Input(x) t  x read(x,t) issues completes input(x) time

14. Notes 09 So net effect is either • S=…r1(x)…w2(B)… or • S=…w2(B)…r1(x)…

15. What about conflicting, concurrent actions on same object? start r1(A) end r1(A) start w2(A) end w2(A) time • Assume equivalent to either r1(A) w2(A) or w2(A) r1(A) •  low level synchronization mechanism • Assumption called “atomic actions”

16. Definition • S1, S2 are conflict equivalent schedules • if S1 can be transformed into S2 by a series of swaps on non-conflicting actions. • A schedule is conflict serializableif it is conflict equivalent to some serial schedule. • Example Serial schedule Conflict serializable Not conflict serializable

17. Precedence graph, P(S) • Nodes • transactions in S • Arcs(Ti → Tj ) • pi(A), qj(A) are actions in S • pi(A) <S qj(A) • at least one of pi, qj is a write • P(S) is acyclic • P(S) is conflict-serializable • P(S) is cyclic • P(S) is not conflict-serializable

18. Exercise • What is P(S) for S = w3(A)w2(C)r1(A)w1(B)r1(C)w2(A)r4(A)w4(D) • Is S serializable? • No • Precedence graph가 cyclic이면, conflict-serializable이 아님 T3 T2 T1 T4

19. Exercise • What is P(S) for S = w1(A)r2(A)r3(A)w4(A) T1 T2 T4 T3

20. Lemma • 두 schedule이 conflict equivalent하다는 것은 P(S1) = P(S2)하기 위한 충분조건임 • 증명 • S1, S2 conflict equivalent ⇒ P(S1) = P(S2) • Assume P(S1) ≠ P(S2) • ∃ Ti: Ti → Tj inS1 and not in S2 • S1 = …pi(A) … qj(A) … S2 = …qj(A) … pi(A) … (단, pi, qj는 conflicting actions) • S1, S2 not conflict equivalent • P(S1) = P(S2)  S1, S2 conflict equivalent • Counter example • S1 = w1(A)r2(A)w2(B)r1(B) • S2 = r2(A)w1(A)r1(B)w2(B) T2 T1

21. Theorem • P(S1) acyclic ⇔ S1 conflict serializable • 증명 • P(S1) acyclic  S1 conflict serializable • Assume S1 is conflict serializable • ∃Ss: Ss, S1 conflict equivalent • P(Ss) = P(S1) • P(S1) acyclic since P(Ss) is acyclic • P(S1) acyclic ⇒ S1 conflict serializable • Assume P(S1) is acyclic • Transform S1 as follows: • (1) Take T1 to be transaction with no incident arcs • (2) Move all T1 actions to the front • S1 = ……. qj(A)…….p1(A)….. • (3) we now have S1 = < T1 actions ><... rest ...> • (4) repeat above steps to serialize rest! T1 T2 T3 T4

22. Slide 10 Sc’=r1(A)w1(A) r1(B)w1(B)r2(A)w2(A)r2(B)w2(B) Sc = r1(A)w1(A)r2(A)w2(A)r1(B)w1(B)r2(B)w2(B) T1 T2

23. Slide 11 However, for Sd: Sd = r1(A)w1(A)r2(A)w2(A) r2(B)w2(B)r1(B)w1(B) X X • as a matter of fact, • T2 must precede T1 • in any equivalent schedule, • i.e., T2 T1

24. Slide 12 T1 T2 Sd cannot be rearranged into a serial schedule Sd is not “equivalent” to any serial schedule Sd is “bad” • T2 T1 • Also, T1 T2

25. Returning to Sc Slide 13 Sc=r1(A)w1(A)r2(A)w2(A)r1(B)w1(B)r2(B)w2(B) T1 T2 T1 T2 no cycles  Sc is “equivalent” to a serial schedule (in this case (T1,T2) )