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

Multi-Version Concurrency Control Lecture # 19 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 M ULTI- VERSIO N CO N CURREN CY CO N TRO L The DBMS maintains multiple physical versions of a single logical object in the database: → When a txn writes to an object, the DBMS creates a new version of that object. → When a txn reads an object, it reads the newest version that existed when the txn started. CMU 15-445/645 (Fall 2018)

4 M VCC H ISTO RY Protocol was first proposed in 1978 MIT PhD dissertation. First implementations was Rdb/VMS and InterBase at DEC in early 1980s. → Both were by Jim Starkey, co-founder of NuoDB. → DEC Rdb/VMS is now "Oracle Rdb" → InterBase was open-sourced as Firebird. CMU 15-445/645 (Fall 2018)

5 M ULTI- VERSIO N CO N CURREN CY CO N TRO L Writers don't block readers. Readers don't block writers. Read-only txns can read a consistent snapshot without acquiring locks. → Use timestamps to determine visibility. Easily support time-travel queries. CMU 15-445/645 (Fall 2018)

6 M VCC EXAM PLE # 1 Schedule Database T 1 T 2 Version Value Begin End BEGIN A 0 123 0 - R(A) BEGIN W(A) TIM E R(A) COMMIT COMMIT CMU 15-445/645 (Fall 2018)

6 M VCC EXAM PLE # 1 Schedule Database T 1 T 2 Version Value Begin End BEGIN A 0 123 0 - R(A) BEGIN W(A) TIM E R(A) COMMIT COMMIT CMU 15-445/645 (Fall 2018)

6 M VCC EXAM PLE # 1 Schedule Database TS(T 1 )=1 TS(T 2 )=2 T 1 T 2 Version Value Begin End BEGIN A 0 123 0 - R(A) BEGIN W(A) TIM E R(A) COMMIT COMMIT CMU 15-445/645 (Fall 2018)

6 M VCC EXAM PLE # 1 Schedule Database TS(T 1 )=1 TS(T 2 )=2 T 1 T 2 Version Value Begin End BEGIN A 0 123 0 - R(A) BEGIN W(A) TIM E R(A) COMMIT COMMIT CMU 15-445/645 (Fall 2018)

6 M VCC EXAM PLE # 1 Schedule Database TS(T 1 )=1 TS(T 2 )=2 T 1 T 2 Version Value Begin End BEGIN A 0 123 0 - R(A) A 1 456 2 - BEGIN W(A) TIM E R(A) COMMIT T 2 creates version A 1 COMMIT and sets A 0 End-TS. CMU 15-445/645 (Fall 2018)

6 M VCC EXAM PLE # 1 Schedule Database TS(T 1 )=1 TS(T 2 )=2 T 1 T 2 Version Value Begin End BEGIN A 0 123 0 - 2 R(A) A 1 456 2 - BEGIN W(A) TIM E R(A) COMMIT Txn Status Table T 2 creates version A 1 COMMIT and sets A 0 End-TS. TxnId Timestamp Status T 1 1 Active T 2 2 Active CMU 15-445/645 (Fall 2018)

6 M VCC EXAM PLE # 1 Schedule Database TS(T 1 )=1 TS(T 2 )=2 T 1 T 2 Version Value Begin End BEGIN A 0 123 0 - 2 R(A) A 1 456 2 - BEGIN W(A) TIM E R(A) COMMIT Txn Status Table COMMIT T 1 reads version A 0 . TxnId Timestamp Status T 1 1 Active T 2 2 Active CMU 15-445/645 (Fall 2018)

7 M VCC EXAM PLE # 2 Schedule Database TS(T 1 )=1 TS(T 2 )=2 T 1 T 2 Version Value Begin End BEGIN A 0 123 0 R(A) W(A) BEGIN R(A) TIM E W(A) R(A) Txn Status Table COMMIT COMMIT TxnId Timestamp Status T 1 1 Active CMU 15-445/645 (Fall 2018)

7 M VCC EXAM PLE # 2 Schedule Database TS(T 1 )=1 TS(T 2 )=2 T 1 T 2 Version Value Begin End BEGIN A 0 123 0 R(A) W(A) BEGIN R(A) TIM E W(A) R(A) Txn Status Table COMMIT COMMIT TxnId Timestamp Status T 1 1 Active CMU 15-445/645 (Fall 2018)

7 M VCC EXAM PLE # 2 Schedule Database TS(T 1 )=1 TS(T 2 )=2 T 1 T 2 Version Value Begin End BEGIN A 0 123 0 R(A) A 1 456 1 - W(A) BEGIN R(A) TIM E W(A) R(A) Txn Status Table COMMIT COMMIT TxnId Timestamp Status T 1 1 Active CMU 15-445/645 (Fall 2018)

7 M VCC EXAM PLE # 2 Schedule Database TS(T 1 )=1 TS(T 2 )=2 T 1 T 2 Version Value Begin End BEGIN A 0 123 0 1 R(A) A 1 456 1 - W(A) BEGIN R(A) TIM E W(A) R(A) Txn Status Table COMMIT COMMIT TxnId Timestamp Status T 1 1 Active CMU 15-445/645 (Fall 2018)

7 M VCC EXAM PLE # 2 Schedule Database TS(T 1 )=1 TS(T 2 )=2 T 1 T 2 Version Value Begin End BEGIN A 0 123 0 1 R(A) A 1 456 1 - W(A) BEGIN R(A) TIM E W(A) R(A) Txn Status Table COMMIT T 2 reads version A 0 because T 1 has not COMMIT TxnId Timestamp Status committed yet. T 1 1 Active T 2 2 Active CMU 15-445/645 (Fall 2018)

7 M VCC EXAM PLE # 2 Schedule Database TS(T 1 )=1 TS(T 2 )=2 T 1 T 2 Version Value Begin End BEGIN A 0 123 0 1 R(A) A 1 456 1 - W(A) BEGIN R(A) TIM E W(A) R(A) Txn Status Table COMMIT T 2 has to stall until T 1 commits. COMMIT TxnId Timestamp Status T 1 1 Active T 2 2 Active CMU 15-445/645 (Fall 2018)

7 M VCC EXAM PLE # 2 Schedule Database TS(T 1 )=1 TS(T 2 )=2 T 1 T 2 Version Value Begin End BEGIN A 0 123 0 1 R(A) A 1 456 1 - W(A) BEGIN R(A) TIM E W(A) R(A) Txn Status Table COMMIT COMMIT TxnId Timestamp Status T 1 reads version A 1 that it T 1 1 Active wrote earlier. T 2 2 Active CMU 15-445/645 (Fall 2018)

7 M VCC EXAM PLE # 2 Schedule Database TS(T 1 )=1 TS(T 2 )=2 T 1 T 2 Version Value Begin End BEGIN A 0 123 0 1 R(A) A 1 456 1 - W(A) BEGIN R(A) TIM E W(A) R(A) Txn Status Table COMMIT COMMIT TxnId Timestamp Status T 1 T 1 1 1 Active Committed T 2 2 Active CMU 15-445/645 (Fall 2018)

7 M VCC EXAM PLE # 2 Schedule Database TS(T 1 )=1 TS(T 2 )=2 T 1 T 2 Version Value Begin End BEGIN A 0 123 0 1 R(A) A 1 456 1 - 2 W(A) BEGIN A 2 789 2 - R(A) TIM E W(A) R(A) Txn Status Table COMMIT COMMIT TxnId Timestamp Status T 1 T 1 1 1 Active Committed T 2 2 Active Now T 2 can create the new version. CMU 15-445/645 (Fall 2018)

8 M ULTI- VERSIO N CO N CURREN CY CO N TRO L MVCC is more than just a concurrency control protocol. It completely affects how the DBMS manages transactions and the database. CMU 15-445/645 (Fall 2018)

9 M VCC DESIGN DECISIO N S Concurrency Control Protocol Version Storage Garbage Collection Index Management CMU 15-445/645 (Fall 2018)

10 CO N CURREN CY CO N TRO L PROTO CO L Approach #1: Timestamp Ordering → Assign txns timestamps that determine serial order. Approach #2: Optimistic Concurrency Control → Three-phase protocol from last class. → Use private workspace for new versions. Approach #3: Two-Phase Locking → Txns acquire appropriate lock on physical version before they can read/write a logical tuple. CMU 15-445/645 (Fall 2018)

11 VERSIO N STO RAGE The DBMS uses the tuples’ pointer field to create a version chain per logical tuple. → This allows the DBMS to find the version that is visible to a particular txn at runtime. → Indexes always point to the “head” of the chain. Different storage schemes determine where/what to store for each version. CMU 15-445/645 (Fall 2018)

12 VERSIO N STO RAGE Approach #1: Append-Only Storage → New versions are appended to the same table space. Approach #2: Time-Travel Storage → Old versions are copied to separate table space. Approach #3: Delta Storage → The original values of the modified attributes are copied into a separate delta record space. CMU 15-445/645 (Fall 2018)

13 APPEN D- O N LY STO RAGE Main Table All of the physical versions of a logical tuple are stored in the same table VERSION VALUE POINTER space. The versions are mixed A 0 $111 together. Ø A 1 $222 Ø B 1 $10 On every update, append a new version of the tuple into an empty space in the table. CMU 15-445/645 (Fall 2018)

13 APPEN D- O N LY STO RAGE Main Table All of the physical versions of a logical tuple are stored in the same table VERSION VALUE POINTER space. The versions are mixed A 0 $111 together. Ø A 1 $222 Ø B 1 $10 On every update, append a new $333 Ø A 2 version of the tuple into an empty space in the table. CMU 15-445/645 (Fall 2018)

13 APPEN D- O N LY STO RAGE Main Table All of the physical versions of a logical tuple are stored in the same table VERSION VALUE POINTER space. The versions are mixed A 0 $111 together. Ø A 1 $222 Ø B 1 $10 On every update, append a new $333 Ø A 2 version of the tuple into an empty space in the table. CMU 15-445/645 (Fall 2018)

13 APPEN D- O N LY STO RAGE Main Table All of the physical versions of a logical tuple are stored in the same table VERSION VALUE POINTER space. The versions are mixed A 0 $111 together. A 1 $222 Ø B 1 $10 On every update, append a new $333 Ø A 2 version of the tuple into an empty space in the table. CMU 15-445/645 (Fall 2018)

14 VERSIO N CH AIN O RDERIN G Approach #1: Oldest-to-Newest (O2N) → Just append new version to end of the chain. → Have to traverse chain on look-ups. Approach #2: Newest-to-Oldest (N2O) → Have to update index pointers for every new version. → Don’t have to traverse chain on look ups. CMU 15-445/645 (Fall 2018)

15 TIM E- TRAVEL STO RAGE Main Table Time-Travel Table VERSION VALUE POINTER VERSION VALUE POINTER A 2 $222 A 1 $111 Ø B 1 $10 On every update, copy the current version to the time- travel table. Update pointers. CMU 15-445/645 (Fall 2018)

15 TIM E- TRAVEL STO RAGE Main Table Time-Travel Table VERSION VALUE POINTER VERSION VALUE POINTER A 2 $222 A 1 $111 Ø B 1 $10 A 2 $222 On every update, copy the current version to the time- travel table. Update pointers. CMU 15-445/645 (Fall 2018)