Concurrency Control Lecture # 18 Database Systems Andy Pavlo AP - PowerPoint PPT Presentation

Timestamp Ordering Concurrency Control Lecture # 18 Database Systems Andy Pavlo AP AP Computer Science 15-445/15-645 Carnegie Mellon Univ. Fall 2018

2 ADM IN ISTRIVIA Homework #4 : Monday Nov 12 th @ 11:59pm Project #3 : Monday Nov 19 th @ 11:59am CMU 15-445/645 (Fall 2018)

3 CO N CURREN CY CO N TRO L APPROACH ES Two-Phase Locking (2PL) → Determine serializability order of conflicting operations at runtime while txns execute. Timestamp Ordering (T/O) → Determine serializability order of txns before they execute. CMU 15-445/645 (Fall 2018)

3 CO N CURREN CY CO N TRO L APPROACH ES Two-Phase Locking (2PL) Pessimistic → Determine serializability order of conflicting operations at runtime while txns execute. Timestamp Ordering (T/O) Optimistic → Determine serializability order of txns before they execute. CMU 15-445/645 (Fall 2018)

4 T/ O CO N CURREN CY CO N TRO L Use timestamps to determine the serializability order of txns. If TS(T i ) < TS(T j ) , then the DBMS must ensure that the execution schedule is equivalent to a serial schedule where T i appears before T j . CMU 15-445/645 (Fall 2018)

5 TIM ESTAM P ALLO CATIO N Each txn T i is assigned a unique fixed timestamp that is monotonically increasing. → Let TS(T i ) be the timestamp allocated to txn T i . → Different schemes assign timestamps at different times during the txn. Multiple implementation strategies: → System Clock. → Logical Counter. → Hybrid. CMU 15-445/645 (Fall 2018)

6 TO DAY'S AGEN DA Basic Timestamp Ordering Protocol Optimistic Concurrency Control Partition-based Timestamp Ordering Isolation Levels CMU 15-445/645 (Fall 2018)

7 BASIC T/ O Txns read and write objects without locks. Every object X is tagged with timestamp of the last txn that successfully did read/write: → W-TS(X) – Write timestamp on X → R-TS(X) – Read timestamp on X Check timestamps for every operation: → If txn tries to access an object "from the future", it aborts and restarts. CMU 15-445/645 (Fall 2018)

8 BASIC T/ O READS If TS(T i ) < W-TS(X) , this violates timestamp order of T i with regard to the writer of X . → Abort T i and restart it with same TS. Else: → Allow T i to read X . → Update R-TS(X) to max(R-TS(X), TS(T i )) → Have to make a local copy of X to ensure repeatable reads for T i . CMU 15-445/645 (Fall 2018)

9 BASIC T/ O WRITES If TS(T i ) < R-TS(X) or TS(T i ) < W-TS(X) → Abort and restart T i . Else: → Allow T i to write X and update W-TS(X) → Also have to make a local copy of X to ensure repeatable reads for T i . CMU 15-445/645 (Fall 2018)

10 BASIC T/ O EXAM PLE # 1 Schedule Database TS(T 1 )=1 TS(T 2 )=2 T 1 T 2 Object R-TS W-TS BEGIN A 0 0 R(B) B 0 0 BEGIN R(B) TIM E W(B) R(A) R(A) W(A) COMMIT COMMIT CMU 15-445/645 (Fall 2018)

10 BASIC T/ O EXAM PLE # 1 Schedule Database TS(T 1 )=1 TS(T 2 )=2 T 1 T 2 Object R-TS W-TS BEGIN A 0 0 R(B) 1 B 0 0 BEGIN R(B) TIM E W(B) R(A) R(A) W(A) COMMIT COMMIT CMU 15-445/645 (Fall 2018)

10 BASIC T/ O EXAM PLE # 1 Schedule Database TS(T 1 )=1 TS(T 2 )=2 T 1 T 2 Object R-TS W-TS BEGIN A 0 0 R(B) 2 1 B 0 0 BEGIN R(B) TIM E W(B) R(A) R(A) W(A) COMMIT COMMIT CMU 15-445/645 (Fall 2018)

10 BASIC T/ O EXAM PLE # 1 Schedule Database TS(T 1 )=1 TS(T 2 )=2 T 1 T 2 Object R-TS W-TS BEGIN A 0 0 R(B) 2 1 2 B 0 0 BEGIN R(B) TIM E W(B) R(A) R(A) W(A) COMMIT COMMIT CMU 15-445/645 (Fall 2018)

10 BASIC T/ O EXAM PLE # 1 Schedule Database TS(T 1 )=1 TS(T 2 )=2 T 1 T 2 Object R-TS W-TS BEGIN A 0 1 0 R(B) 2 1 2 B 0 0 BEGIN R(B) TIM E W(B) R(A) R(A) W(A) COMMIT COMMIT CMU 15-445/645 (Fall 2018)

10 BASIC T/ O EXAM PLE # 1 Schedule Database TS(T 1 )=1 TS(T 2 )=2 T 1 T 2 Object R-TS W-TS BEGIN A 0 2 1 0 R(B) 2 1 2 B 0 0 BEGIN R(B) TIM E W(B) R(A) R(A) W(A) COMMIT COMMIT CMU 15-445/645 (Fall 2018)

10 BASIC T/ O EXAM PLE # 1 Schedule Database TS(T 1 )=1 TS(T 2 )=2 T 1 T 2 Object R-TS W-TS BEGIN A 0 2 1 0 2 R(B) 2 1 2 B 0 0 BEGIN R(B) TIM E W(B) No violations so both txns R(A) are safe to commit. R(A) W(A) COMMIT COMMIT CMU 15-445/645 (Fall 2018)

11 BASIC T/ O EXAM PLE # 2 Schedule Database T 1 T 2 Object R-TS W-TS BEGIN A 0 1 0 R(A) B 0 0 BEGIN W(A) TIM E COMMIT W(A) R(A) COMMIT CMU 15-445/645 (Fall 2018)

11 BASIC T/ O EXAM PLE # 2 Schedule Database T 1 T 2 Object R-TS W-TS BEGIN A 0 1 0 2 R(A) B 0 0 BEGIN W(A) TIM E COMMIT W(A) R(A) COMMIT CMU 15-445/645 (Fall 2018)

11 BASIC T/ O EXAM PLE # 2 Schedule Database T 1 T 2 Object R-TS W-TS BEGIN A 0 1 0 2 R(A) B 0 0 BEGIN W(A) TIM E Violation: COMMIT TS(T 1 ) < W-TS(A) W(A) R(A) COMMIT T 1 cannot overwrite update by T 2 , so the DBMS has to abort it! CMU 15-445/645 (Fall 2018)

12 TH O M AS WRITE RULE If TS(T i ) < R-TS(X) : → Abort and restart T i . If TS(T i ) < W-TS(X) : → Thomas Write Rule : Ignore the write and allow the txn to continue. → This violates timestamp order of T i . Else: → Allow T i to write X and update W-TS(X) CMU 15-445/645 (Fall 2018)

13 BASIC T/ O EXAM PLE # 2 Schedule Database T 1 T 2 Object R-TS W-TS BEGIN A 0 1 0 R(A) B 0 0 BEGIN W(A) TIM E COMMIT W(A) R(A) COMMIT CMU 15-445/645 (Fall 2018)

13 BASIC T/ O EXAM PLE # 2 Schedule Database T 1 T 2 Object R-TS W-TS BEGIN A 0 1 0 2 R(A) B 0 0 BEGIN W(A) TIM E COMMIT W(A) R(A) COMMIT CMU 15-445/645 (Fall 2018)

13 BASIC T/ O EXAM PLE # 2 Schedule Database T 1 T 2 Object R-TS W-TS BEGIN A 0 1 0 2 R(A) B 0 0 BEGIN W(A) TIM E We do not update COMMIT W-TS(A) W(A) R(A) COMMIT Ignore the write and allow T 1 to commit. CMU 15-445/645 (Fall 2018)

14 BASIC T/ O Generates a schedule that is conflict serializable if you do not use the Thomas Write Rule. → No deadlocks because no txn ever waits. → Possibility of starvation for long txns if short txns keep causing conflicts. Permits schedules that are not recoverable . CMU 15-445/645 (Fall 2018)

15 RECOVERABLE SCH EDULES A schedule is recoverable if txns commit only after all txns whose changes they read, commit. Otherwise, the DBMS cannot guarantee that txns read data that will be restored after recovering from a crash. CMU 15-445/645 (Fall 2018)

16 RECOVERABLE SCH EDULES Schedule T 1 T 2 BEGIN W(A) BEGIN ⋮ T 2 is allowed to read the R(A) TIM E writes of T 1 . W(B) COMMIT CMU 15-445/645 (Fall 2018)

16 RECOVERABLE SCH EDULES Schedule T 1 T 2 BEGIN W(A) BEGIN ⋮ T 2 is allowed to read the R(A) TIM E writes of T 1 . W(B) COMMIT This is not recoverable ABORT because we cannot restart T 1 . T 1 aborts after T 2 has committed. CMU 15-445/645 (Fall 2018)

17 BASIC T/ O PERFO RM AN CE ISSUES High overhead from copying data to txn's workspace and from updating timestamps. Long running txns can get starved. → The likelihood that a txn will read something from a newer txn increases. CMU 15-445/645 (Fall 2018)

18 O BSERVATIO N If you assume that conflicts between txns are rare and that most txns are short-lived , then forcing txns to wait to acquire locks adds a lot of overhead. A better approach is to optimize for the no- conflict case. CMU 15-445/645 (Fall 2018)

19 O PTIM ISTIC CO N CURREN CY CO N TRO L The DBMS creates a private workspace for each txn. → Any object read is copied into workspace. → Modifications are applied to workspace. When a txn commits, the DBMS compares workspace write set to see whether it conflicts with other txns. If there are no conflicts, the write set is installed into the "global" database. CMU 15-445/645 (Fall 2018)

20 O CC PH ASES #1 – Read Phase : → Track the read/write sets of txns and store their writes in a private workspace. #2 – Validation Phase : → When a txn commits, check whether it conflicts with other txns. #3 – Write Phase: → If validation succeeds, apply private changes to database. Otherwise abort and restart the txn. CMU 15-445/645 (Fall 2018)

21 O CC EXAM PLE Schedule Database T 1 T 2 Object Value W-TS BEGIN BEGIN A 123 0 READ - - - R(A) READ R(A) TIM E VALIDATE WRITE COMMIT W(A) VALIDATE WRITE COMMIT CMU 15-445/645 (Fall 2018)

21 O CC EXAM PLE Schedule Database T 1 T 2 Object Value W-TS BEGIN BEGIN A 123 0 READ - - - R(A) READ R(A) TIM E VALIDATE WRITE COMMIT W(A) VALIDATE WRITE COMMIT CMU 15-445/645 (Fall 2018)

21 O CC EXAM PLE Schedule Database T 1 T 2 Object Value W-TS BEGIN BEGIN A 123 0 READ - - - R(A) READ R(A) TIM E VALIDATE T 1 Workspace WRITE COMMIT Object Value W-TS W(A) - - - VALIDATE - - - WRITE COMMIT CMU 15-445/645 (Fall 2018)

21 O CC EXAM PLE Schedule Database T 1 T 2 Object Value W-TS BEGIN BEGIN A 123 0 READ - - - R(A) READ R(A) TIM E VALIDATE T 1 Workspace WRITE COMMIT Object Value W-TS W(A) A 123 0 - - - VALIDATE - - - WRITE COMMIT CMU 15-445/645 (Fall 2018)