lecture 18: case studies
TRANSCRIPT
![Page 1: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/1.jpg)
1 / 82
Multi-Version Concurrency Control
Lecture 18: Case Studies
![Page 2: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/2.jpg)
2 / 82
Multi-Version Concurrency Control Recap
Recap
![Page 3: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/3.jpg)
3 / 82
Multi-Version Concurrency Control Recap
Multi-Version Concurrency Control
• The DBMS maintains multiple physical versions of a single logical object in thedatabase:▶ 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.
![Page 4: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/4.jpg)
4 / 82
Multi-Version Concurrency Control Recap
Multi-Version Concurrency Control
• Writers don’t block readers. Readers don’t block writers.• Read-only txns can read a consistent snapshot without acquiring locks or txn ids.
▶ Use timestamps to determine visibility.
• Easily support time-travel queries.
![Page 5: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/5.jpg)
5 / 82
Multi-Version Concurrency Control Recap
Today’s Agenda
• MVCC Protocols• Microsoft Hekaton (SQL Server)• TUM HyPer• SAP HANA• CMU Cicada
![Page 6: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/6.jpg)
6 / 82
Multi-Version Concurrency Control MVCC Protocols
MVCC Protocols
![Page 7: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/7.jpg)
7 / 82
Multi-Version Concurrency Control MVCC Protocols
Snapshot Isolation (SI)
• When a txn starts, it sees a consistent snapshot of the database that existed when thatthe txn started.▶ No torn writes from active txns.▶ If two txns update the same object, then first writer wins.
• SI is susceptible to the Write Skew Anomaly.
![Page 8: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/8.jpg)
8 / 82
Multi-Version Concurrency Control MVCC Protocols
Write Skew Anomaly
![Page 9: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/9.jpg)
9 / 82
Multi-Version Concurrency Control MVCC Protocols
Write Skew Anomaly
![Page 10: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/10.jpg)
10 / 82
Multi-Version Concurrency Control MVCC Protocols
Isolation Level Hierarchy
![Page 11: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/11.jpg)
11 / 82
Multi-Version Concurrency Control MVCC Protocols
Concurrency Control Protocol
• Approach 1: Timestamp Ordering▶ Assign txns timestamps that determine serial order.▶ Considered to be original MVCC protocol.
• 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.
![Page 12: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/12.jpg)
12 / 82
Multi-Version Concurrency Control MVCC Protocols
Tuple Format
![Page 13: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/13.jpg)
13 / 82
Multi-Version Concurrency Control MVCC Protocols
Timestamp Ordering (MVTO)
![Page 14: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/14.jpg)
14 / 82
Multi-Version Concurrency Control MVCC Protocols
Timestamp Ordering (MVTO)
![Page 15: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/15.jpg)
15 / 82
Multi-Version Concurrency Control MVCC Protocols
Timestamp Ordering (MVTO)
![Page 16: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/16.jpg)
16 / 82
Multi-Version Concurrency Control MVCC Protocols
Timestamp Ordering (MVTO)
![Page 17: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/17.jpg)
17 / 82
Multi-Version Concurrency Control MVCC Protocols
Timestamp Ordering (MVTO)
![Page 18: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/18.jpg)
18 / 82
Multi-Version Concurrency Control MVCC Protocols
Timestamp Ordering (MVTO)
![Page 19: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/19.jpg)
19 / 82
Multi-Version Concurrency Control MVCC Protocols
Timestamp Ordering (MVTO)
![Page 20: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/20.jpg)
20 / 82
Multi-Version Concurrency Control MVCC Protocols
Two-Phase Locking (MV2PL)
![Page 21: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/21.jpg)
21 / 82
Multi-Version Concurrency Control MVCC Protocols
Two-Phase Locking (MV2PL)
![Page 22: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/22.jpg)
22 / 82
Multi-Version Concurrency Control MVCC Protocols
Two-Phase Locking (MV2PL)
![Page 23: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/23.jpg)
23 / 82
Multi-Version Concurrency Control MVCC Protocols
Two-Phase Locking (MV2PL)
![Page 24: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/24.jpg)
24 / 82
Multi-Version Concurrency Control MVCC Protocols
Two-Phase Locking (MV2PL)
![Page 25: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/25.jpg)
25 / 82
Multi-Version Concurrency Control MVCC Protocols
Observation
![Page 26: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/26.jpg)
26 / 82
Multi-Version Concurrency Control MVCC Protocols
Observation
![Page 27: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/27.jpg)
27 / 82
Multi-Version Concurrency Control MVCC Protocols
Observation
![Page 28: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/28.jpg)
28 / 82
Multi-Version Concurrency Control MVCC Protocols
Observation
![Page 29: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/29.jpg)
29 / 82
Multi-Version Concurrency Control MVCC Protocols
PostgreSQL: Txn Id Wraparound
• Set a flag in each tuple header that says that it is frozen in the past. Any new txn idwill always be newer than a frozen version.
• Runs the vacuum before the system gets close to this upper limit.• Otherwise it must stop accepting new commands when the system gets close to the
max txn id.
![Page 30: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/30.jpg)
30 / 82
Multi-Version Concurrency Control Microsoft Hekaton
Microsoft Hekaton
![Page 31: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/31.jpg)
31 / 82
Multi-Version Concurrency Control Microsoft Hekaton
Microsoft Hekaton
• Incubator project started in 2008 to create new OLTP engine for MSFT SQL Server(MSSQL).▶ Reference
• Had to integrate with MSSQL ecosystem.• Had to support all possible OLTP workloads with predictable performance.
▶ Single-threaded partitioning (e.g., H-Store/VoltDB) works well for some applications butterrible for others.
![Page 32: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/32.jpg)
32 / 82
Multi-Version Concurrency Control Microsoft Hekaton
Hekaton MVCC
• Each txn is assigned a timestamp when they begin (BeginTS) and when they commit(CommitTS).
• Each tuple contains two timestamps that represents their visibility and current state:▶ BEGIN-TS: The BeginTS of the active txn or the CommitTS of the committed txn that
created it.▶ END-TS: The BeginTS of the active txn that created the next version or infinity or the
CommitTS of the committed txn that created it.
![Page 33: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/33.jpg)
33 / 82
Multi-Version Concurrency Control Microsoft Hekaton
Hekaton: Operations
![Page 34: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/34.jpg)
34 / 82
Multi-Version Concurrency Control Microsoft Hekaton
Hekaton: Operations
![Page 35: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/35.jpg)
35 / 82
Multi-Version Concurrency Control Microsoft Hekaton
Hekaton: Operations
![Page 36: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/36.jpg)
36 / 82
Multi-Version Concurrency Control Microsoft Hekaton
Hekaton: Operations
![Page 37: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/37.jpg)
37 / 82
Multi-Version Concurrency Control Microsoft Hekaton
Hekaton: Operations
![Page 38: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/38.jpg)
38 / 82
Multi-Version Concurrency Control Microsoft Hekaton
Hekaton: Operations
![Page 39: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/39.jpg)
39 / 82
Multi-Version Concurrency Control Microsoft Hekaton
Hekaton: Operations
![Page 40: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/40.jpg)
40 / 82
Multi-Version Concurrency Control Microsoft Hekaton
Hekaton: Operations
![Page 41: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/41.jpg)
41 / 82
Multi-Version Concurrency Control Microsoft Hekaton
Hekaton: Operations
![Page 42: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/42.jpg)
42 / 82
Multi-Version Concurrency Control Microsoft Hekaton
Hekaton: Operations
![Page 43: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/43.jpg)
43 / 82
Multi-Version Concurrency Control Microsoft Hekaton
Hekaton: Transaction State Map
• Global map of all txns’ states in the system:▶ ACTIVE: The txn is executing read/write operations.▶ VALIDATING: The txn has invoked commit and the DBMS is checking whether it is
valid.▶ COMMITTED: The txn is finished but may have not updated its versions’ TS.▶ TERMINATED: The txn has updated the TS for all of the versions that it created.
![Page 44: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/44.jpg)
44 / 82
Multi-Version Concurrency Control Microsoft Hekaton
Hekaton: Transaction Lifecycle
![Page 45: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/45.jpg)
45 / 82
Multi-Version Concurrency Control Microsoft Hekaton
Hekaton: Transaction Meta-Data
• Read Set▶ Pointers to physical versions returned to access method.
• Write Set▶ Pointers to versions updated (old and new), versions deleted (old), and version inserted
(new).• Scan Set
▶ Stores enough information needed to perform each scan operation again to check result.• Commit Dependencies
▶ List of txns that are waiting for this txn to finish.
![Page 46: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/46.jpg)
46 / 82
Multi-Version Concurrency Control Microsoft Hekaton
Hekaton: Transaction Validation
• Read Stability▶ Check that each version read is still visible as of the end of the txn.
• Phantom Avoidance▶ Repeat each scan to check whether new versions have become visible since the txn began.
• Extent of validation depends on isolation level:▶ SERIALIZABLE: Read Stability + Phantom Avoidance▶ REPEATABLE READS: Read Stability▶ SNAPSHOT ISOLATION: None▶ READ COMMITTED: None
![Page 47: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/47.jpg)
47 / 82
Multi-Version Concurrency Control Microsoft Hekaton
Hekaton: Optimistic vs. Pessimistic
• Optimistic Txns:▶ Check whether a version read is still visible at the end of the txn.▶ Repeat all index scans to check for phantoms.
• Pessimistic Txns:▶ Use shared & exclusive locks on records and buckets.▶ No validation is needed.▶ Separate background thread to detect deadlocks.
![Page 48: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/48.jpg)
48 / 82
Multi-Version Concurrency Control Microsoft Hekaton
Hekaton: Optimistic vs. Pessimistic
![Page 49: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/49.jpg)
49 / 82
Multi-Version Concurrency Control Microsoft Hekaton
Hekaton: Lessons
• Use only lock-free data structures▶ No latches, spin locks, or critical sections▶ Indexes, txn map, memory alloc, garbage collector▶ Example: Bw-Trees
• Only one single serialization point in the DBMS to get the txn’s begin and committimestamp▶ Atomic Addition (CAS)
![Page 50: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/50.jpg)
50 / 82
Multi-Version Concurrency Control Microsoft Hekaton
Observation
• Read/scan set validations are expensive if the txns access a lot of data.• Appending new versions hurts the performance of OLAP scans due to pointer chasing
& branching.• Record-level conflict checks may be too coarse-grained and incur false positives.
![Page 51: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/51.jpg)
51 / 82
Multi-Version Concurrency Control Hyper
Hyper
![Page 52: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/52.jpg)
52 / 82
Multi-Version Concurrency Control Hyper
Hyper MVCC
• Column-store with delta record versioning.• Reference
▶ In-Place updates for non-indexed attributes▶ Delete/Insert updates for indexed attributes.▶ Newest-to-Oldest Version Chains▶ No Predicate Locks / No Scan Checks
• Avoids write-write conflicts by aborting txns that try to update an uncommitted object.
![Page 53: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/53.jpg)
53 / 82
Multi-Version Concurrency Control Hyper
Hyper: Storage Architecture
![Page 54: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/54.jpg)
54 / 82
Multi-Version Concurrency Control Hyper
Hyper: Storage Architecture
![Page 55: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/55.jpg)
55 / 82
Multi-Version Concurrency Control Hyper
Hyper: Storage Architecture
![Page 56: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/56.jpg)
56 / 82
Multi-Version Concurrency Control Hyper
Hyper: Storage Architecture
![Page 57: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/57.jpg)
57 / 82
Multi-Version Concurrency Control Hyper
Hyper: Validation
• First-Writer Wins▶ If version vector is not null, then it always points to the last committed version.▶ Do not need to check whether write-sets overlap.
• Check the redo buffers of txns that committed after the validating txn started.▶ Compare the committed txn’s write set for phantoms using Precision Locking.▶ Only need to store the txn’s read predicates and not its entire read set.
![Page 58: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/58.jpg)
58 / 82
Multi-Version Concurrency Control Hyper
Hyper: Precision Locking
![Page 59: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/59.jpg)
59 / 82
Multi-Version Concurrency Control Hyper
Hyper: Precision Locking
![Page 60: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/60.jpg)
60 / 82
Multi-Version Concurrency Control Hyper
Hyper: Precision Locking
![Page 61: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/61.jpg)
61 / 82
Multi-Version Concurrency Control Hyper
Hyper: Precision Locking
![Page 62: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/62.jpg)
62 / 82
Multi-Version Concurrency Control Hyper
Hyper: Version Synopses
![Page 63: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/63.jpg)
63 / 82
Multi-Version Concurrency Control Hyper
Hyper: Version Synopses
![Page 64: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/64.jpg)
64 / 82
Multi-Version Concurrency Control SAP HANA
SAP HANA
![Page 65: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/65.jpg)
65 / 82
Multi-Version Concurrency Control SAP HANA
SAP HANA
• In-memory HTAP DBMS with time-travel version storage (N2O).• Reference
▶ Supports both optimistic and pessimistic MVCC.▶ Latest versions are stored in time-travel space.▶ Hybrid storage layout (row + columnar).
• Based on P*TIME, TREX, and MaxDB.• First released in 2012.
![Page 66: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/66.jpg)
66 / 82
Multi-Version Concurrency Control SAP HANA
SAP HANA: Version Storage
• Store the oldest version in the main data table.• Each tuple maintains a flag to denote whether there exists newer versions in the
version space.• Maintain a separate hash table that maps record identifiers to the head of version chain.
![Page 67: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/67.jpg)
67 / 82
Multi-Version Concurrency Control SAP HANA
SAP HANA: Version Storage
![Page 68: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/68.jpg)
68 / 82
Multi-Version Concurrency Control SAP HANA
SAP HANA: Transactions
• Instead of embedding meta-data about the txn that created a version with the data,store a pointer to a context object.▶ Reads are slower because you must follow pointers.▶ Large updates are faster because it’s a single write to update the status of all tuples.
• Store meta-data about whether a txn has committed in a separate object as well.
![Page 69: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/69.jpg)
69 / 82
Multi-Version Concurrency Control SAP HANA
SAP HANA: Version Storage
![Page 70: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/70.jpg)
70 / 82
Multi-Version Concurrency Control SAP HANA
SAP HANA: Version Storage
![Page 71: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/71.jpg)
71 / 82
Multi-Version Concurrency Control Cicada
Cicada
![Page 72: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/72.jpg)
72 / 82
Multi-Version Concurrency Control Cicada
MVCC Limitations
• Computation & Storage Overhead▶ Most MVCC schemes use indirection to search a tuple’s version chain. This increases CPU
cache misses.▶ Also requires frequent garbage collection to minimize the number versions that a thread
must evaluate.• Shared Memory Writes
▶ Most MVCC schemes store versions in "global" memory in the heap without consideringlocality.
• Timestamp Allocation▶ All threads access single shared counter.
![Page 73: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/73.jpg)
73 / 82
Multi-Version Concurrency Control Cicada
OCC Limitations
• Frequent Aborts▶ Txns will abort too quickly under high contention, causing high churn.
• Extra Reads & Writes▶ Each txn must copy tuples into their private workspace to ensure repeatable reads. It then
has to check whether it read consistent data when it commits.• Index Contention
▶ Txns install "virtual" index entries to ensure unique-key invariants.
![Page 74: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/74.jpg)
74 / 82
Multi-Version Concurrency Control Cicada
CMU Cicada
• In-memory OLTP engine based on optimistic MVCC with append-only storage (N2O).• Reference
▶ Best-effort Inlining▶ Loosely Synchronized Clocks▶ Contention-Aware Validation▶ Index Nodes Stored in Tables
• Designed to be scalable for both low- and high-contention workloads.
![Page 75: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/75.jpg)
75 / 82
Multi-Version Concurrency Control Cicada
Cicada: Best-Effort Inlining
• Record Meta-data
• Record meta-data is stored ina fixed location.
• Threads will attempt to inlineread-mostly version withinthis meta-data to reduceversion chain traversals.
![Page 76: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/76.jpg)
76 / 82
Multi-Version Concurrency Control Cicada
Cicada: Fast Validation
• Contention-aware Validation▶ Validate access to recently modified records first.
• Early Consistency Check▶ Pre-validate access set before making global writes.▶ Skip if all recent txns committed successfully.
• Incremental Version Search▶ Resume from last search location in version list.
![Page 77: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/77.jpg)
77 / 82
Multi-Version Concurrency Control Cicada
Cicada: Index Storage
![Page 78: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/78.jpg)
78 / 82
Multi-Version Concurrency Control Cicada
Cicada: Low Contention
![Page 79: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/79.jpg)
79 / 82
Multi-Version Concurrency Control Cicada
Cicada: High Contention
![Page 80: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/80.jpg)
80 / 82
Multi-Version Concurrency Control Conclusion
Conclusion
![Page 81: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/81.jpg)
81 / 82
Multi-Version Concurrency Control Conclusion
Parting Thoughts
• There are several other implementation factors for an MVCC DBMS beyond the fourmain design decisions that we discussed last class.
• Need to balance the trade-offs between indirection and performance.
![Page 82: Lecture 18: Case Studies](https://reader031.vdocuments.us/reader031/viewer/2022012508/618581aa46786d4f606e4ce8/html5/thumbnails/82.jpg)
82 / 82
Multi-Version Concurrency Control Conclusion
Next Class
• Wellness Day• Midterm Exam• Query Optimization