![Page 1: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/1.jpg)
Andy Pavlo // Carnegie Mellon University // Spring 2016
Lecture #04 – Concurrency Control Part II
DATABASE SYSTEMS
15-721
[Source]
![Page 2: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/2.jpg)
CMU 15-721 (Spring 2016)
TODAY ’S AGENDA
Isolation Levels Modern Multi-Version Concurrency Control
2
![Page 3: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/3.jpg)
CMU 15-721 (Spring 2016)
OBSERVATION
Serializability is useful because it allows programmers to ignore concurrency issues but enforcing it may allow too little parallelism and limit performance. We may want to use a weaker level of consistency to improve scalability.
3
![Page 4: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/4.jpg)
CMU 15-721 (Spring 2016)
ISOLATION LEVELS
Controls the extent that a txn is exposed to the actions of other concurrent txns. Provides for greater concurrency at the cost of exposing txns to uncommitted changes: → Dirty Read Anomaly → Unrepeatable Reads Anomaly → Phantom Reads Anomaly
4
![Page 5: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/5.jpg)
CMU 15-721 (Spring 2016)
ANSI ISOLATION LEVELS
SERIALIZABLE → No phantoms, all reads repeatable, no dirty reads.
REPEATABLE READS → Phantoms may happen.
READ COMMITTED → Phantoms and unrepeatable reads may happen.
READ UNCOMMITTED → All of them may happen.
5
Isol
atio
n (H
igh→
Low
)
![Page 6: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/6.jpg)
CMU 15-721 (Spring 2016)
ISOLATION LEVEL HIERARCHY
6
REPEATABLE READS
READ UNCOMMITTED
SERIALIZABLE
READ COMMITTED
![Page 7: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/7.jpg)
CMU 15-721 (Spring 2016)
ANSI ISOLATION LEVELS
7
Default Maximum Actian Ingres 10.0/10S SERIALIZABLE SERIALIZABLE
Greenplum 4.1 READ COMMITTED SERIALIZABLE
MySQL 5.6 REPEATABLE READS SERIALIZABLE
MemSQL 1b READ COMMITTED READ COMMITTED
MS SQL Server 2012 READ COMMITTED SERIALIZABLE
Oracle 11g READ COMMITTED SNAPSHOT ISOLATION
Postgres 9.2.2 READ COMMITTED SERIALIZABLE
SAP HANA READ COMMITTED SERIALIZABLE
ScaleDB 1.02 READ COMMITTED READ COMMITTED
VoltDB SERIALIZABLE SERIALIZABLE Source: Peter Bailis
![Page 8: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/8.jpg)
CMU 15-721 (Spring 2016)
ANSI ISOLATION LEVELS
7
Default Maximum Actian Ingres 10.0/10S SERIALIZABLE SERIALIZABLE
Greenplum 4.1 READ COMMITTED SERIALIZABLE
MySQL 5.6 REPEATABLE READS SERIALIZABLE
MemSQL 1b READ COMMITTED READ COMMITTED
MS SQL Server 2012 READ COMMITTED SERIALIZABLE
Oracle 11g READ COMMITTED SNAPSHOT ISOLATION
Postgres 9.2.2 READ COMMITTED SERIALIZABLE
SAP HANA READ COMMITTED SERIALIZABLE
ScaleDB 1.02 READ COMMITTED READ COMMITTED
VoltDB SERIALIZABLE SERIALIZABLE Source: Peter Bailis
![Page 9: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/9.jpg)
CMU 15-721 (Spring 2016)
CRITICISM OF ISOLATION LEVELS
The isolation levels defined as part of SQL-92 standard only focused on anomalies that can occur in a 2PL-based DBMS. Two additional isolation levels: → CURSOR STABILITY → SNAPSHOT ISOLATION
8
A CRITIQUE OF ANSI SQL ISOLATION LEVELS SIGMOD 1995
![Page 10: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/10.jpg)
CMU 15-721 (Spring 2016)
CURSOR STABIL ITY (CS)
The DBMS’s internal cursor maintains a lock on a item in the database until it moves on to the next item. CS is a stronger isolation level in between REPEATABLE READS and READ COMMITTED that can (sometimes) prevent the Lost Update Anomaly.
9
![Page 11: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/11.jpg)
CMU 15-721 (Spring 2016)
LOST UPDATE ANOMALY
10
Txn #2
BEGIN
COMMIT
WRITE(A)
Txn #1
BEGIN
COMMIT
READ(A) WRITE(A) • • •
• • • • • • • •
![Page 12: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/12.jpg)
CMU 15-721 (Spring 2016)
LOST UPDATE ANOMALY
10
Txn #2
BEGIN
COMMIT
WRITE(A)
Txn #1
BEGIN
COMMIT
READ(A) WRITE(A) • • •
• • • • • • • •
![Page 13: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/13.jpg)
CMU 15-721 (Spring 2016)
LOST UPDATE ANOMALY
10
Txn #2
BEGIN
COMMIT
WRITE(A)
Txn #1
BEGIN
COMMIT
READ(A) WRITE(A) • • •
• • • • • • • •
![Page 14: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/14.jpg)
CMU 15-721 (Spring 2016)
LOST UPDATE ANOMALY
10
Txn #2
BEGIN
COMMIT
WRITE(A)
Txn #1
BEGIN
COMMIT
READ(A) WRITE(A) • • •
• • • • • • • •
![Page 15: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/15.jpg)
CMU 15-721 (Spring 2016)
LOST UPDATE ANOMALY
10
Txn #2
BEGIN
COMMIT
WRITE(A)
Txn #1
BEGIN
COMMIT
READ(A) WRITE(A) • • •
• • • • • • • •
![Page 16: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/16.jpg)
CMU 15-721 (Spring 2016)
LOST UPDATE ANOMALY
10
Txn #2
BEGIN
COMMIT
WRITE(A)
Txn #1
BEGIN
COMMIT
READ(A) WRITE(A) • • •
• • • • • • • •
![Page 17: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/17.jpg)
CMU 15-721 (Spring 2016)
LOST UPDATE ANOMALY
Txn #2’s write to A will be lost even though it commits after Txn #1.
10
Txn #2
BEGIN
COMMIT
WRITE(A)
Txn #1
BEGIN
COMMIT
READ(A) WRITE(A) • • •
• • • • • • • •
![Page 18: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/18.jpg)
CMU 15-721 (Spring 2016)
LOST UPDATE ANOMALY
Txn #2’s write to A will be lost even though it commits after Txn #1.
10
Txn #2
BEGIN
COMMIT
WRITE(A)
Txn #1
BEGIN
COMMIT
READ(A) WRITE(A) • • •
• • • • • • • •
A cursor lock on A would prevent this problem (but not always).
![Page 19: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/19.jpg)
CMU 15-721 (Spring 2016)
SNAPSHOT ISOLATION (S I )
Guarantees that all reads made in a txn see a consistent snapshot of the database that existed at the time the txn started. → A txn will commit under SI only if its writes do not
conflict with any concurrent updates made since that snapshot.
SI is susceptible to the Write Skew Anomaly
11
![Page 20: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/20.jpg)
CMU 15-721 (Spring 2016)
WRITE SKEW ANOMALY
12
![Page 21: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/21.jpg)
CMU 15-721 (Spring 2016)
WRITE SKEW ANOMALY
12
![Page 22: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/22.jpg)
CMU 15-721 (Spring 2016)
WRITE SKEW ANOMALY
12
Txn #1 Change white marbles to black.
Txn #2 Change black marbles to white.
![Page 23: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/23.jpg)
CMU 15-721 (Spring 2016)
WRITE SKEW ANOMALY
12
Txn #1 Change white marbles to black.
Txn #2 Change black marbles to white.
![Page 24: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/24.jpg)
CMU 15-721 (Spring 2016)
WRITE SKEW ANOMALY
12
Txn #1 Change white marbles to black.
Txn #2 Change black marbles to white.
![Page 25: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/25.jpg)
CMU 15-721 (Spring 2016)
WRITE SKEW ANOMALY
12
Txn #1 Change white marbles to black.
Txn #2 Change black marbles to white.
![Page 26: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/26.jpg)
CMU 15-721 (Spring 2016)
WRITE SKEW ANOMALY
12
Txn #1 Change white marbles to black.
Txn #2 Change black marbles to white.
![Page 27: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/27.jpg)
CMU 15-721 (Spring 2016)
WRITE SKEW ANOMALY
12
![Page 28: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/28.jpg)
CMU 15-721 (Spring 2016)
WRITE SKEW ANOMALY
12
Txn #1 Change white marbles to black.
Txn #2 Change black marbles to white.
![Page 29: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/29.jpg)
CMU 15-721 (Spring 2016)
ISOLATION LEVEL HIERARCHY
13
REPEATABLE READS SNAPSHOT ISOLATION
READ UNCOMMITTED
CURSOR STABILITY
SERIALIZABLE
READ COMMITTED
![Page 30: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/30.jpg)
CMU 15-721 (Spring 2016)
MULTI -VERSION CONCURRENCY CONTROL
Timestamp-ordering scheme that maintains multiple versions of database objects: → 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.
First proposed in 1978 MIT PhD dissertation.
14
![Page 31: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/31.jpg)
CMU 15-721 (Spring 2016)
MULTI -VERSION CONCURRENCY CONTROL
15
Txn #1
BEGIN
COMMIT
READ(A) WRITE(B) WRITE(A)
• • • • • • •
![Page 32: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/32.jpg)
CMU 15-721 (Spring 2016)
MULTI -VERSION CONCURRENCY CONTROL
15
Txn #1
BEGIN
COMMIT
READ(A) WRITE(B) WRITE(A)
• • • • • • •
Record Write Timestamp
A1 10000
B1 10000
![Page 33: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/33.jpg)
CMU 15-721 (Spring 2016)
MULTI -VERSION CONCURRENCY CONTROL
15
Txn #1
BEGIN
COMMIT
READ(A) WRITE(B) WRITE(A)
• • • • • • •
Record Write Timestamp
A1 10000
B1 10000
10001
![Page 34: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/34.jpg)
CMU 15-721 (Spring 2016)
MULTI -VERSION CONCURRENCY CONTROL
15
Txn #1
BEGIN
COMMIT
READ(A) WRITE(B) WRITE(A)
• • • • • • •
Record Write Timestamp
A1 10000
B1 10000
10001
![Page 35: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/35.jpg)
CMU 15-721 (Spring 2016)
MULTI -VERSION CONCURRENCY CONTROL
15
Txn #1
BEGIN
COMMIT
READ(A) WRITE(B) WRITE(A)
• • • • • • •
Record Write Timestamp
A1 10000
B1 10000
10001
![Page 36: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/36.jpg)
CMU 15-721 (Spring 2016)
MULTI -VERSION CONCURRENCY CONTROL
15
Txn #1
BEGIN
COMMIT
READ(A) WRITE(B) WRITE(A)
• • • • • • •
Record Write Timestamp
A1 10000
B1 10000
10001 B2
10001
![Page 37: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/37.jpg)
CMU 15-721 (Spring 2016)
MULTI -VERSION CONCURRENCY CONTROL
15
Txn #1
BEGIN
COMMIT
READ(A) WRITE(B) WRITE(A)
• • • • • • •
Record Write Timestamp
A1 10000
B1 10000
10001 B2
10001
![Page 38: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/38.jpg)
CMU 15-721 (Spring 2016)
MULTI -VERSION CONCURRENCY CONTROL
15
Txn #1
BEGIN
COMMIT
READ(A) WRITE(B) WRITE(A)
• • • • • • •
Record Write Timestamp
A1 10000
B1 10000
10001 B2
10001
![Page 39: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/39.jpg)
CMU 15-721 (Spring 2016)
MULTI -VERSION CONCURRENCY CONTROL
15
Txn #1
BEGIN
COMMIT
READ(A) WRITE(B) WRITE(A)
• • • • • • •
Record Write Timestamp
A1 10000
B1 10000
10001 B2 10003 A2
10001
![Page 40: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/40.jpg)
CMU 15-721 (Spring 2016)
MULTI -VERSION CONCURRENCY CONTROL
15
Txn #1
BEGIN
COMMIT
READ(A) WRITE(B) WRITE(A)
• • • • • • •
Record Write Timestamp
A1 10000
B1 10000
10001 B2 10003 A2
10001
![Page 41: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/41.jpg)
CMU 15-721 (Spring 2016)
MULTI -VERSION CONCURRENCY CONTROL
15
Txn #1
BEGIN
COMMIT
READ(A) WRITE(B) WRITE(A)
• • • • • • •
Record Write Timestamp
A1 10000
B1 10000
10001 B2 10003 A2
10001
![Page 42: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/42.jpg)
CMU 15-721 (Spring 2016)
MODERN MVCC
Microsoft Hekaton (SQL Server) TUM HyPer HPI HYRISE SAP HANA
16
![Page 43: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/43.jpg)
CMU 15-721 (Spring 2016)
MICROSOFT HEKATON
Incubator project started in 2008 to create new OLTP engine for MSFT SQL Server (MSSQL). → Led by DB ballers Paul Larson and Mike Zwilling
Had to integrate with MSSQL ecosystem. Had to support all possible OLTP workloads with predictable performance. → Single-threaded partitioning (e.g., H-Store) works
well for some applications but terrible for others.
17
![Page 44: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/44.jpg)
CMU 15-721 (Spring 2016)
HEKATON MVCC
Every txn is assigned a timestamp (TS) when they begin and when they commit. DBMS maintains “chain” of versions per tuple: → BEGIN: The BeginTS of the active txn or the EndTS of
the committed txn that created it. → END: The BeginTS of the active txn that created the
next version or infinity or the EndTS of the committed txn that created it.
→ POINTER: Location of the next version in the chain.
18
HIGH-PERFORMANCE CONCURRENCY CONTROL MECHANISMS FOR MAIN-MEMORY DATABASES VLDB 2011
![Page 45: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/45.jpg)
CMU 15-721 (Spring 2016)
BEGIN END POINTER ATTR1 ATTR2
HEKATON: OPERATIONS
19
10 20 John $100
20 John $110 ∞
INDEX
![Page 46: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/46.jpg)
CMU 15-721 (Spring 2016)
BEGIN END POINTER ATTR1 ATTR2
HEKATON: OPERATIONS
19
10 20 John $100
20 John $110 ∞
BEGIN @ 25 INDEX
![Page 47: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/47.jpg)
CMU 15-721 (Spring 2016)
BEGIN END POINTER ATTR1 ATTR2
HEKATON: OPERATIONS
19
10 20 John $100
20 John $110 ∞
BEGIN @ 25 INDEX
Read “John”
![Page 48: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/48.jpg)
CMU 15-721 (Spring 2016)
BEGIN END POINTER ATTR1 ATTR2
HEKATON: OPERATIONS
19
10 20 John $100
20 John $110 ∞
BEGIN @ 25 INDEX
Read “John”
![Page 49: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/49.jpg)
CMU 15-721 (Spring 2016)
BEGIN END POINTER ATTR1 ATTR2
HEKATON: OPERATIONS
19
10 20 John $100
20 John $110 ∞
BEGIN @ 25 INDEX
Read “John”
![Page 50: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/50.jpg)
CMU 15-721 (Spring 2016)
BEGIN END POINTER ATTR1 ATTR2
HEKATON: OPERATIONS
19
10 20 John $100
20 John $110 ∞
BEGIN @ 25 INDEX
Read “John”
![Page 51: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/51.jpg)
CMU 15-721 (Spring 2016)
BEGIN END POINTER ATTR1 ATTR2
HEKATON: OPERATIONS
19
10 20 John $100
20 John $110 ∞
BEGIN @ 25 INDEX
Update “John” Read “John”
![Page 52: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/52.jpg)
CMU 15-721 (Spring 2016)
BEGIN END POINTER ATTR1 ATTR2
HEKATON: OPERATIONS
19
10 20 John $100
20 John $110 ∞
BEGIN @ 25 INDEX
Update “John” Read “John”
![Page 53: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/53.jpg)
CMU 15-721 (Spring 2016)
BEGIN END POINTER ATTR1 ATTR2
HEKATON: OPERATIONS
19
10 20 John $100
20 John $110 ∞
BEGIN @ 25 INDEX
Update “John” Read “John”
![Page 54: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/54.jpg)
CMU 15-721 (Spring 2016)
BEGIN END POINTER ATTR1 ATTR2
HEKATON: OPERATIONS
19
10 20 John $100
20 John $110 ∞ Txn25
BEGIN @ 25 INDEX
Update “John” Read “John”
![Page 55: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/55.jpg)
CMU 15-721 (Spring 2016)
BEGIN END POINTER ATTR1 ATTR2
HEKATON: OPERATIONS
19
10 20 John $100
Txn25 ∞ John $130
20 John $110 ∞ Txn25
BEGIN @ 25 INDEX
Update “John” Read “John”
![Page 56: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/56.jpg)
CMU 15-721 (Spring 2016)
BEGIN END POINTER ATTR1 ATTR2
HEKATON: OPERATIONS
19
10 20 John $100
Txn25 ∞ John $130
20 John $110 ∞ Txn25
BEGIN @ 25 INDEX
Update “John” Read “John”
![Page 57: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/57.jpg)
CMU 15-721 (Spring 2016)
BEGIN END POINTER ATTR1 ATTR2
HEKATON: OPERATIONS
19
10 20 John $100
Txn25 ∞ John $130
20 John $110 ∞ Txn25
BEGIN @ 25 INDEX
Update “John” Read “John”
COMMIT @ 35
![Page 58: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/58.jpg)
CMU 15-721 (Spring 2016)
BEGIN END POINTER ATTR1 ATTR2
HEKATON: OPERATIONS
19
10 20 John $100
Txn25 ∞ John $130
20 John $110 ∞ Txn25
BEGIN @ 25 INDEX
Update “John” Read “John”
COMMIT @ 35
![Page 59: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/59.jpg)
CMU 15-721 (Spring 2016)
BEGIN END POINTER ATTR1 ATTR2
HEKATON: OPERATIONS
19
10 20 John $100
Txn25 ∞ John $130
20 John $110 ∞ Txn25
BEGIN @ 25 INDEX
Update “John” Read “John”
COMMIT @ 35
35
35
![Page 60: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/60.jpg)
CMU 15-721 (Spring 2016)
BEGIN END POINTER ATTR1 ATTR2
HEKATON: OPERATIONS
19
10 20 John $100
Txn25 ∞ John $130
20 John $110 ∞ Txn25
BEGIN @ 25 INDEX
Update “John” Read “John”
COMMIT @ 35
35
35
REWIND
![Page 61: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/61.jpg)
CMU 15-721 (Spring 2016)
BEGIN END POINTER ATTR1 ATTR2
HEKATON: OPERATIONS
20
10 20 John $100
Txn25 ∞ John $130
20 John $110 ∞ Txn25
BEGIN @ 25 INDEX
Update “John” Read “John”
![Page 62: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/62.jpg)
CMU 15-721 (Spring 2016)
BEGIN END POINTER ATTR1 ATTR2
HEKATON: OPERATIONS
20
10 20 John $100
Txn25 ∞ John $130
20 John $110 ∞ Txn25
BEGIN @ 25 INDEX
Update “John” Read “John”
BEGIN @ 30
![Page 63: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/63.jpg)
CMU 15-721 (Spring 2016)
BEGIN END POINTER ATTR1 ATTR2
HEKATON: OPERATIONS
20
10 20 John $100
Txn25 ∞ John $130
20 John $110 ∞ Txn25
BEGIN @ 25 INDEX
Update “John” Read “John”
BEGIN @ 30 Read “John”
![Page 64: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/64.jpg)
CMU 15-721 (Spring 2016)
BEGIN END POINTER ATTR1 ATTR2
HEKATON: OPERATIONS
20
10 20 John $100
Txn25 ∞ John $130
20 John $110 ∞ Txn25
BEGIN @ 25 INDEX
Update “John” Read “John”
BEGIN @ 30 Read “John”
![Page 65: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/65.jpg)
CMU 15-721 (Spring 2016)
BEGIN END POINTER ATTR1 ATTR2
HEKATON: OPERATIONS
20
10 20 John $100
Txn25 ∞ John $130
20 John $110 ∞ Txn25
BEGIN @ 25 INDEX
Update “John” Read “John”
BEGIN @ 30 Read “John”
![Page 66: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/66.jpg)
CMU 15-721 (Spring 2016)
BEGIN END POINTER ATTR1 ATTR2
HEKATON: OPERATIONS
20
10 20 John $100
Txn25 ∞ John $130
20 John $110 ∞ Txn25
BEGIN @ 25 INDEX
Update “John” Read “John”
BEGIN @ 30 Read “John” Update “John”
![Page 67: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/67.jpg)
CMU 15-721 (Spring 2016)
BEGIN END POINTER ATTR1 ATTR2
HEKATON: OPERATIONS
20
10 20 John $100
Txn25 ∞ John $130
20 John $110 ∞ Txn25
BEGIN @ 25 INDEX
Update “John” Read “John”
BEGIN @ 30 Read “John” Update “John”
![Page 68: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/68.jpg)
CMU 15-721 (Spring 2016)
BEGIN END POINTER ATTR1 ATTR2
HEKATON: OPERATIONS
20
10 20 John $100
Txn25 ∞ John $130
20 John $110 ∞ Txn25
BEGIN @ 25 INDEX
Update “John” Read “John”
BEGIN @ 30 Read “John” Update “John”
![Page 69: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/69.jpg)
CMU 15-721 (Spring 2016)
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.
21
![Page 70: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/70.jpg)
CMU 15-721 (Spring 2016)
HEKATON: TRANSACTION L IFECYCLE
22
Txn events
Txn phases
Source: Paul Larson
![Page 71: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/71.jpg)
CMU 15-721 (Spring 2016)
HEKATON: TRANSACTION L IFECYCLE
22
Get txn start timestamp, set state to ACTIVE Begin
Txn events
Txn phases
Source: Paul Larson
![Page 72: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/72.jpg)
CMU 15-721 (Spring 2016)
HEKATON: TRANSACTION L IFECYCLE
22
Get txn start timestamp, set state to ACTIVE Begin Normal processing
Txn events
Txn phases
Source: Paul Larson
Perform normal processing → Track txn’s read set, scan set, and write set.
![Page 73: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/73.jpg)
CMU 15-721 (Spring 2016)
HEKATON: TRANSACTION L IFECYCLE
22
Get txn start timestamp, set state to ACTIVE Begin
Precommit
Normal processing
Txn events
Txn phases
Source: Paul Larson
Perform normal processing → Track txn’s read set, scan set, and write set.
Get txn end timestamp, set state to VALIDATING
![Page 74: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/74.jpg)
CMU 15-721 (Spring 2016)
HEKATON: TRANSACTION L IFECYCLE
22
Get txn start timestamp, set state to ACTIVE Begin
Precommit
Normal processing
Validation
Txn events
Txn phases
Source: Paul Larson
Perform normal processing → Track txn’s read set, scan set, and write set.
Get txn end timestamp, set state to VALIDATING
Validate reads and scans → If validation OK, write new versions to redo log
![Page 75: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/75.jpg)
CMU 15-721 (Spring 2016)
HEKATON: TRANSACTION L IFECYCLE
22
Get txn start timestamp, set state to ACTIVE Begin
Precommit
Commit
Normal processing
Validation
Txn events
Txn phases
Source: Paul Larson
Perform normal processing → Track txn’s read set, scan set, and write set.
Get txn end timestamp, set state to VALIDATING
Validate reads and scans → If validation OK, write new versions to redo log
Set txn state to COMMITTED
![Page 76: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/76.jpg)
CMU 15-721 (Spring 2016)
HEKATON: TRANSACTION L IFECYCLE
22
Get txn start timestamp, set state to ACTIVE Begin
Precommit
Commit
Normal processing
Validation
Post-processing
Txn events
Txn phases
Source: Paul Larson
Perform normal processing → Track txn’s read set, scan set, and write set.
Get txn end timestamp, set state to VALIDATING
Validate reads and scans → If validation OK, write new versions to redo log
Set txn state to COMMITTED Fix up version timestamps → Begin TS in new versions, end TS in old versions
![Page 77: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/77.jpg)
CMU 15-721 (Spring 2016)
HEKATON: TRANSACTION L IFECYCLE
22
Get txn start timestamp, set state to ACTIVE Begin
Precommit
Commit
Terminate
Normal processing
Validation
Post-processing
Txn events
Txn phases
Source: Paul Larson
Perform normal processing → Track txn’s read set, scan set, and write set.
Get txn end timestamp, set state to VALIDATING
Validate reads and scans → If validation OK, write new versions to redo log
Set txn state to COMMITTED Fix up version timestamps → Begin TS in new versions, end TS in old versions
Set txn state to TERMINATED Remove from txn map
![Page 78: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/78.jpg)
CMU 15-721 (Spring 2016)
HEKATON: TRANSACTION META-DATA
Read Set → Pointers to every version read.
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.
Commit Dependencies → List of txns that are waiting for this txn to finish.
23
![Page 79: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/79.jpg)
CMU 15-721 (Spring 2016)
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
24
![Page 80: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/80.jpg)
CMU 15-721 (Spring 2016)
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.
25
![Page 81: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/81.jpg)
CMU 15-721 (Spring 2016)
HEKATON: OPTIMISTIC VS. PESSIMISTIC
26
0
0.5
1
1.5
2
0 6 12 18 24Thro
ughp
ut (t
xn/s
ec)
Mil
lion
s
# Threads
Optimistic Pessimistic
Source: Paul Larson
Database: Single table with 1000 tuples Workload: 80% read-only txns + 20% update txns
Processor: 2 sockets, 12 cores
![Page 82: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/82.jpg)
CMU 15-721 (Spring 2016)
HEKATON: IMPLEMENTATION
Use only lock-free data structures → No latches, spin locks, or critical sections → Indexes, txn map, memory alloc, garbage collector → We will discuss Bw-Trees + Skip Lists later…
Only one single serialization point in the DBMS to get the txn’s begin and commit timestamp → Atomic Addition (CAS)
27
![Page 83: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/83.jpg)
CMU 15-721 (Spring 2016)
HEKATON: PERFORMANCE
Bwin – Large online betting company → Before: 15,000 requests/sec → Hekaton: 250,000 requests/sec
EdgeNet – Up-to-date inventory status → Before: 7,450 rows/sec (ingestion rate) → Hekaton: 126,665 rows/sec
SBI Liquidity Market – FOREX broker → Before: 2,812 txn/sec with 4 sec latency → Hekaton: 5,313 txn/sec with <1 sec latency
28
Source: Paul Larson
![Page 84: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/84.jpg)
CMU 15-721 (Spring 2016)
MVCC DESIGN CHOICES
Version Chains Version Storage Garbage Collection
29
![Page 85: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/85.jpg)
CMU 15-721 (Spring 2016)
VERSION CHAINS
Approach #1: Oldest-to-Newest → Just append new version to end of the chain. → Have to traverse chain on look-ups.
Approach #2: Newest-to-Oldest → Have to update index pointers for every new version. → Don’t have to traverse chain on look ups.
The ordering of the chain has different performance trade-offs.
30
![Page 86: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/86.jpg)
CMU 15-721 (Spring 2016)
VERSION STORAGE
Approach #1: Insert Method → New versions are added as new tuples to the table.
Approach #2: Delta Method → Copy the current version to a separate storage
location and then overwrite it with the new data. → Rollback segment with deltas, Time-travel table
31
![Page 87: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/87.jpg)
CMU 15-721 (Spring 2016)
ROLLBACK SEGMENTS
32
BEGIN END ATTR1 ATTR2
10 20 John $100
Main Data Table
![Page 88: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/88.jpg)
CMU 15-721 (Spring 2016)
ROLLBACK SEGMENTS
32
BEGIN END ATTR1 ATTR2
10 20 John $100
Main Data Table
![Page 89: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/89.jpg)
CMU 15-721 (Spring 2016)
ROLLBACK SEGMENTS
On every update, copy the old version to the rollback segment and overwrite the tuple in the main data table.
32
BEGIN END ATTR1 ATTR2
10 20 John $100
Main Data Table
![Page 90: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/90.jpg)
CMU 15-721 (Spring 2016)
ROLLBACK SEGMENTS
On every update, copy the old version to the rollback segment and overwrite the tuple in the main data table.
32
Rollback Segment (Per Tuple)
BEGIN END DELTA BEGIN END ATTR1 ATTR2
10 20 John $100
Main Data Table
![Page 91: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/91.jpg)
CMU 15-721 (Spring 2016)
ROLLBACK SEGMENTS
On every update, copy the old version to the rollback segment and overwrite the tuple in the main data table.
32
Rollback Segment (Per Tuple)
10 20 (ATTR2→$100)
BEGIN END DELTA BEGIN END ATTR1 ATTR2
10 20 John $100
Main Data Table
![Page 92: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/92.jpg)
CMU 15-721 (Spring 2016)
ROLLBACK SEGMENTS
On every update, copy the old version to the rollback segment and overwrite the tuple in the main data table.
32
Rollback Segment (Per Tuple)
10 20 (ATTR2→$100)
BEGIN END DELTA BEGIN END ATTR1 ATTR2
10 20 John $100
Main Data Table
$110 20 25
![Page 93: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/93.jpg)
CMU 15-721 (Spring 2016)
ROLLBACK SEGMENTS
On every update, copy the old version to the rollback segment and overwrite the tuple in the main data table.
32
Rollback Segment (Per Tuple)
10 20 (ATTR2→$100)
BEGIN END DELTA
20 25 (ATTR2→$110)
BEGIN END ATTR1 ATTR2
10 20 John $100
Main Data Table
$110 20 25 $130 30 35
![Page 94: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/94.jpg)
CMU 15-721 (Spring 2016)
ROLLBACK SEGMENTS
On every update, copy the old version to the rollback segment and overwrite the tuple in the main data table.
32
Rollback Segment (Per Tuple)
10 20 (ATTR2→$100)
BEGIN END DELTA
20 25 (ATTR2→$110)
BEGIN END ATTR1 ATTR2
10 20 John $100
Main Data Table
$110 20 25 $130 30 35
Txns can recreate old versions by applying the delta in reverse order.
![Page 95: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/95.jpg)
CMU 15-721 (Spring 2016)
GARBAGE COLLECTION
Approach #1: Vacuum Thread → Use a separate background thread to find old
versions and delete them.
Approach #2: Cooperative Threads → Worker threads remove old versions that they
encounter during scans.
GC overhead depends on read/write ratio → Hekaton authors report about a 15% overhead on a
write-heavy workload. Typically much less.
33
![Page 96: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/96.jpg)
CMU 15-721 (Spring 2016)
OBSERVATIONS
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.
34
![Page 97: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/97.jpg)
CMU 15-721 (Spring 2016)
HYPER MVCC
Rollback Segment with Deltas → In-Place updates for non-indexed attributes → Delete/Insert updates for indexed attributes.
Newest-to-Oldest Version Chains No Predicate Locks Avoids write-write conflicts by aborting txns that try to update an uncommitted object.
35
FAST SERIALIZABLE MULTI-VERSION CONCURRENCY CONTROL FOR MAIN-MEMORY DATABASE SYSTEMS SIGMOD 2015
![Page 98: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/98.jpg)
CMU 15-721 (Spring 2016)
HYPER MVCC
36
Rollback Segment (Per Txn) Main Data Table
ATTR1
Tupac
IceT
B.I.G
DrDre
ATTR2
$100
$200
$150
$99
Version Vector
(ATTR2→$122) Txn 263+1
(ATTR2→$199) Txn 123
(ATTR2→$100) Txn 263
(ATTR2→$139)
![Page 99: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/99.jpg)
CMU 15-721 (Spring 2016)
HYRISE MVCC
Insert Method (no rollback segment) Oldest-to-Newest No garbage collection. All updates are executed as DELETE/INSERT.
37
EFFICIENT TRANSACTION PROCESSING FOR HYRISE IN MIXED WORKLOAD ENVIRONMENTS IMDM 2014
![Page 100: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/100.jpg)
CMU 15-721 (Spring 2016)
SAP HANA MVCC
Insert Method (no rollback segment) Background GC thread (optional) It’s not clear what else they are doing…
38
HIGH-PERFORMANCE TRANSACTION PROCESSING IN SAP HANA IEEE Data Engineering Bulletin 2013
![Page 101: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/101.jpg)
CMU 15-721 (Spring 2016)
PARTING THOUGHTS
MVCC is currently the best approach for supporting txns in mixed workoads → Readers are not blocked by writers.
HyPer’s MVCC makes a lot of good decisions for HTAP workloads.
39
![Page 102: CMU SCS 15-721 :: Concurrency Control (Part II) · Modern Multi-Version Concurrency Control . 2 . CMU 15-721 (Spring 2016) OBSERVATION . Serializability is useful because it allows](https://reader031.vdocuments.us/reader031/viewer/2022040712/5e16cad1c415a2133753b375/html5/thumbnails/102.jpg)
CMU 15-721 (Spring 2016)
NEXT CLASS
Stored Procedures Optimistic Concurrency Control
40