SLIDE 1
DATABASE SYSTEM IMPLEMENTATION GT 4420/6422 // SPRING 2019 // - - PowerPoint PPT Presentation
DATABASE SYSTEM IMPLEMENTATION GT 4420/6422 // SPRING 2019 // - - PowerPoint PPT Presentation
DATABASE SYSTEM IMPLEMENTATION GT 4420/6422 // SPRING 2019 // @JOY_ARULRAJ LECTURE #18: OPTIMISTIC CONCURRENCY CONTROL LOGISTICS Reminder: Project Updates due on next Tuesday (Apr 2). Grading Scheme: No Final Exam. 50% Project 30%
SLIDE 2
SLIDE 3
ANATOMY OF A DATABASE SYSTEM
Connection Manager + Admission Control Query Parser Query Optimizer Query Executor Lock Manager (Concurrency Control) Access Methods (or Indexes) Buffer Pool Manager Log Manager Memory Manager + Disk Manager Networking Manager
3
Query Transactional Storage Manager Query Processor Shared Utilities Process Manager
Source: Anatomy of a Database System
SLIDE 4
TODAY’S AGENDA
Stored Procedures Optimistic Concurrency Control Modern OCC Implementations
4
SLIDE 5
OBSERVATION
Disk stalls are (almost) gone when executing txns in an in-memory DBMS. There are still other stalls when an app uses conversational API to execute queries on DBMS
→ ODBC/JDBC → DBMS-specific wire protocols
5
SLIDE 6
CONVERSATIONAL DATABASE API
6
Application BEGIN SQL Program Logic SQL Program Logic ⋮ COMMIT
SLIDE 7
CONVERSATIONAL DATABASE API
7
Application BEGIN SQL Program Logic SQL Program Logic ⋮ COMMIT
SLIDE 8
CONVERSATIONAL DATABASE API
8
Application BEGIN SQL Program Logic SQL Program Logic ⋮ COMMIT
SLIDE 9
CONVERSATIONAL DATABASE API
9
Application BEGIN SQL Program Logic SQL Program Logic ⋮ COMMIT Parser Planner Optimizer Query Execution
SLIDE 10
CONVERSATIONAL DATABASE API
10
Application BEGIN SQL Program Logic SQL Program Logic ⋮ COMMIT Parser Planner Optimizer Query Execution
SLIDE 11
CONVERSATIONAL DATABASE API
11
Application BEGIN SQL Program Logic SQL Program Logic ⋮ COMMIT Parser Planner Optimizer Query Execution
SLIDE 12
CONVERSATIONAL DATABASE API
12
Application BEGIN SQL Program Logic SQL Program Logic ⋮ COMMIT Parser Planner Optimizer Query Execution
SLIDE 13
CONVERSATIONAL DATABASE API
13
Application BEGIN SQL Program Logic SQL Program Logic ⋮ COMMIT Parser Planner Optimizer Query Execution
SLIDE 14
SOLUTIONS
Prepared Statements
→ Removes query preparation overhead.
Query Batches
→ Reduces the number of network roundtrips.
Stored Procedures
→ Removes both preparation and network stalls.
14
SLIDE 15
STORED PROCEDURES
A stored procedure is a group of queries that form a logical unit and perform a particular task
- n behalf of an application directly inside of the
DBMS. Programming languages:
→ SQL/PSM (standard) → PL/SQL (Oracle / IBM / MySQL) → PL/pgSQL (Postgres) → Transact-SQL (Microsoft / Sybase)
15
SLIDE 16
STORED PROCEDURES
16
Application BEGIN SQL Program Logic SQL Program Logic ⋮ COMMIT
SLIDE 17
STORED PROCEDURES
17
Application PROC(x)
SLIDE 18
STORED PROCEDURES
18
Application CALL PROC(x=99) PROC(x)
SLIDE 19
STORED PROCEDURES
19
Application CALL PROC(x=99) PROC(x)
SLIDE 20
STORED PROCEDURE EXAMPLE
20
CREATE PROCEDURE testProc (num INT, name VARCHAR) RETURNS INT BEGIN DECLARE cnt INT DEFAULT 0; LOOP INSERT INTO student VALUES (cnt, name); SET cnt := cnt + 1; IF (cnt > num) THEN RETURN cnt; END IF; END LOOP; END;
SLIDE 21
ADVANTAGES
Reduce the number of round trips between application and database servers. Increased performance because queris are pre- compiled and stored in DBMS. Procedure reuse across applications. Server-side txn restarts on conflicts.
21
SLIDE 22
DISADVANTAGES
Not as many developers know how to write SQL/PSM code.
→ Safe Languages vs. Sandbox Languages
Outside the scope of the application so it is difficult to manage versions and hard to debug. Probably not be portable to other DBMSs. DBAs usually don’t give permissions out freely…
22
SLIDE 23
CONCURRENCY CONTROL
The protocol to allow txns to access a database in a multi-programmed fashion while preserving the illusion that each of them is executing alone on a dedicated system.
→ The goal is to have the effect of a group of txns on the database’s state is equivalent to any serial execution of all txns.
Provides Atomicity + Isolation in ACID
23
SLIDE 24
CONCURRENCY CONTROL SCHEMES
Two-Phase Locking (2PL)
→ Assume txns will conflict so they must acquire locks on database objects before they are allowed to access them.
Timestamp Ordering (T/O)
→ Assume that conflicts are rare so txns do not need to first acquire locks on database objects and instead check for conflicts at commit time.
24
SLIDE 25
TWO-PHASE LOCKING
25
Txn #1
BEGIN COMMIT
LOCK(A) LOCK(B) UNLOCK(A) UNLOCK(B) READ(A) WRITE(B)
SLIDE 26
TWO-PHASE LOCKING
26
Txn #1
BEGIN COMMIT
LOCK(A) LOCK(B) UNLOCK(A) UNLOCK(B) READ(A) WRITE(B) LOCK(A) LOCK(B)
SLIDE 27
TWO-PHASE LOCKING
27
Txn #1
BEGIN COMMIT
LOCK(A) LOCK(B) UNLOCK(A) UNLOCK(B) READ(A) WRITE(B) LOCK(A) LOCK(B)
SLIDE 28
TWO-PHASE LOCKING
28
Txn #1
BEGIN COMMIT
LOCK(A) LOCK(B) UNLOCK(A) UNLOCK(B) READ(A) WRITE(B) LOCK(A) LOCK(B)
Growing Phase
SLIDE 29
TWO-PHASE LOCKING
29
Txn #1
BEGIN COMMIT
LOCK(A) LOCK(B) UNLOCK(A) UNLOCK(B) READ(A) WRITE(B)
Shrinking Phase
LOCK(A) LOCK(B)
Growing Phase
SLIDE 30
TWO-PHASE LOCKING
30
Txn #1
BEGIN COMMIT
LOCK(A) LOCK(B) UNLOCK(A) UNLOCK(B) READ(A) WRITE(B) LOCK(A) LOCK(B)
SLIDE 31
TWO-PHASE LOCKING
31
Txn #2
BEGIN COMMIT
LOCK(B) LOCK(A) WRITE(A) UNLOCK(A) UNLOCK(B) WRITE(B)
Txn #1
BEGIN COMMIT
LOCK(A) LOCK(B) UNLOCK(A) UNLOCK(B) READ(A) WRITE(B) LOCK(A) LOCK(B)
SLIDE 32
TWO-PHASE LOCKING
32
Txn #2
BEGIN COMMIT
LOCK(B) LOCK(A) WRITE(A) UNLOCK(A) UNLOCK(B) WRITE(B)
Txn #1
BEGIN COMMIT
LOCK(A) LOCK(B) UNLOCK(A) UNLOCK(B) READ(A) WRITE(B) LOCK(A) LOCK(B)
SLIDE 33
TWO-PHASE LOCKING
33
Txn #2
BEGIN COMMIT
LOCK(B) LOCK(A) WRITE(A) UNLOCK(A) UNLOCK(B) WRITE(B)
Txn #1
BEGIN COMMIT
LOCK(A) LOCK(B) UNLOCK(A) UNLOCK(B) READ(A) WRITE(B) LOCK(A) LOCK(B)
SLIDE 34
TWO-PHASE LOCKING
34
Txn #2
BEGIN COMMIT
LOCK(B) LOCK(A) WRITE(A) UNLOCK(A) UNLOCK(B) WRITE(B)
Txn #1
BEGIN COMMIT
LOCK(A) LOCK(B) UNLOCK(A) UNLOCK(B) READ(A) WRITE(B) LOCK(A) LOCK(B)
SLIDE 35
TWO-PHASE LOCKING
35
Txn #2
BEGIN COMMIT
LOCK(B) LOCK(A) WRITE(A) UNLOCK(A) UNLOCK(B) WRITE(B)
Txn #1
BEGIN COMMIT
LOCK(A) LOCK(B) UNLOCK(A) UNLOCK(B) READ(A) WRITE(B) LOCK(A) LOCK(B)
SLIDE 36
TWO-PHASE LOCKING
36
Txn #2
BEGIN COMMIT
LOCK(B) LOCK(A) WRITE(A) UNLOCK(A) UNLOCK(B) WRITE(B)
Txn #1
BEGIN COMMIT
LOCK(A) LOCK(B) UNLOCK(A) UNLOCK(B) READ(A) WRITE(B) LOCK(A) LOCK(B)
SLIDE 37
TWO-PHASE LOCKING
37
Txn #2
BEGIN COMMIT
LOCK(B) LOCK(A) WRITE(A) UNLOCK(A) UNLOCK(B) WRITE(B)
Txn #1
BEGIN COMMIT
LOCK(A) LOCK(B) UNLOCK(A) UNLOCK(B) READ(A) WRITE(B) LOCK(A) LOCK(B)
SLIDE 38
TWO-PHASE LOCKING
38
Txn #2
BEGIN COMMIT
LOCK(B) LOCK(A) WRITE(A) UNLOCK(A) UNLOCK(B) WRITE(B)
Txn #1
BEGIN COMMIT
LOCK(A) LOCK(B) UNLOCK(A) UNLOCK(B) READ(A) WRITE(B) LOCK(A) LOCK(B)
SLIDE 39
TWO-PHASE LOCKING
Deadlock Detection
→ Each txn maintains a queue of the txns that hold the locks that it waiting for. → A separate thread checks these queues for deadlocks. → If deadlock found, use a heuristic to decide what txn to kill in order to break deadlock.
Deadlock Prevention
→ Check whether another txn already holds a lock when another txn requests it. → If lock is not available, the txn will either (1) wait, (2) commit suicide, or (3) kill the other txn.
39
SLIDE 40
TIMESTAMP ORDERING
40
Txn #1
BEGIN COMMIT
READ(A) WRITE(B) WRITE(A)
- • • •
- • •
SLIDE 41
TIMESTAMP ORDERING
41
Txn #1
BEGIN COMMIT
READ(A) WRITE(B) WRITE(A)
- • • •
- • •
SLIDE 42
TIMESTAMP ORDERING
42
Txn #1
BEGIN COMMIT
READ(A) WRITE(B) WRITE(A)
- • • •
- • •
10001
SLIDE 43
TIMESTAMP ORDERING
43 Record Read Timestamp Write Timestamp
A B 10000
Txn #1
BEGIN COMMIT
READ(A) WRITE(B) WRITE(A)
- • • •
10000 10000
- • •
10000
10001
SLIDE 44
TIMESTAMP ORDERING
44 Record Read Timestamp Write Timestamp
A B 10000
Txn #1
BEGIN COMMIT
READ(A) WRITE(B) WRITE(A)
- • • •
10000 10000
- • •
10000
10001
SLIDE 45
TIMESTAMP ORDERING
45 Record Read Timestamp Write Timestamp
A B 10000
Txn #1
BEGIN COMMIT
READ(A) WRITE(B) WRITE(A)
- • • •
10000 10000
- • •
10000
10001
SLIDE 46
TIMESTAMP ORDERING
46 Record Read Timestamp Write Timestamp
A B 10000
Txn #1
BEGIN COMMIT
READ(A) WRITE(B) WRITE(A)
- • • •
10001 10000
- • •
10000
10001
SLIDE 47
TIMESTAMP ORDERING
47 Record Read Timestamp Write Timestamp
A B 10000
Txn #1
BEGIN COMMIT
READ(A) WRITE(B) WRITE(A)
- • • •
10001 10000
- • •
10000
10001
SLIDE 48
TIMESTAMP ORDERING
48 Record Read Timestamp Write Timestamp
A B 10000
Txn #1
BEGIN COMMIT
READ(A) WRITE(B) WRITE(A)
- • • •
10001 10000
- • •
10000
10001
SLIDE 49
TIMESTAMP ORDERING
49 Record Read Timestamp Write Timestamp
A B 10000
Txn #1
BEGIN COMMIT
READ(A) WRITE(B) WRITE(A)
- • • •
10001 10001
- • •
10000
10001
SLIDE 50
TIMESTAMP ORDERING
50 Record Read Timestamp Write Timestamp
A B 10000
Txn #1
BEGIN COMMIT
READ(A) WRITE(B) WRITE(A)
- • • •
10001 10001
- • •
10000
10001
SLIDE 51
TIMESTAMP ORDERING
51 Record Read Timestamp Write Timestamp
A B 10000
Txn #1
BEGIN COMMIT
READ(A) WRITE(B) WRITE(A)
- • • •
10001 10001
- • •
10000
10001
SLIDE 52
TIMESTAMP ORDERING
52 Record Read Timestamp Write Timestamp
A B 10000
Txn #1
BEGIN COMMIT
READ(A) WRITE(B) WRITE(A)
- • • •
10001 10001
- • •
10005
10001
SLIDE 53
TIMESTAMP ORDERING
53 Record Read Timestamp Write Timestamp
A B 10000
Txn #1
BEGIN COMMIT
READ(A) WRITE(B) WRITE(A)
- • • •
10001 10001
- • •
10005
10001
SLIDE 54
TIMESTAMP ORDERING
54 Record Read Timestamp Write Timestamp
A B 10000
Txn #1
BEGIN COMMIT
READ(A) WRITE(B) WRITE(A)
- • • •
10001 10001
- • •
10005
10001
SLIDE 55
TIMESTAMP ORDERING
Basic T/O
→ Check for conflicts on each read/write. → Copy tuples on each access to ensure repeatable reads.
Optimistic Currency Control (OCC)
→ Store all changes in private workspace. → Check for conflicts at commit time and then merge.
55
SLIDE 56
OPTIMISTIC CONCURRENCY CONTROL
Timestamp-ordering scheme where txns copy data read/write into a private workspace that is not visible to other active txns. When a txn commits, the DBMS verifies that there are no conflicts. First proposed in 1981 at CMU by H.T. Kung.
56
ON OPTIMISTIC METHODS FOR CONCURRENCY CONTROL ACM Transactions on Database Systems 1981
SLIDE 57
OPTIMISTIC CONCURRENCY CONTROL
57
Txn #1
BEGIN
READ(A) WRITE(A) WRITE(B)
COMMIT
SLIDE 58
OPTIMISTIC CONCURRENCY CONTROL
58
Txn #1
BEGIN
READ(A) WRITE(A) WRITE(B)
Record Value Write Timestamp
B 456 10000 123 A 10000 COMMIT
SLIDE 59
OPTIMISTIC CONCURRENCY CONTROL
59
Txn #1
BEGIN
READ(A) WRITE(A) WRITE(B)
Record Value Write Timestamp
B 456 10000 123 A 10000 COMMIT
SLIDE 60
OPTIMISTIC CONCURRENCY CONTROL
60
Txn #1
BEGIN
READ(A) WRITE(A) WRITE(B)
Read Phase
Record Value Write Timestamp
B 456 10000 123 A 10000 COMMIT
SLIDE 61
OPTIMISTIC CONCURRENCY CONTROL
61
Txn #1
BEGIN
READ(A) WRITE(A) WRITE(B)
Record Value Write Timestamp
B 456 10000 123 A 10000 COMMIT
SLIDE 62
OPTIMISTIC CONCURRENCY CONTROL
62
Txn #1
BEGIN
READ(A) WRITE(A) WRITE(B)
Record Value Write Timestamp
B 456 10000 123 A 10000 COMMIT
SLIDE 63
OPTIMISTIC CONCURRENCY CONTROL
63
Txn #1
BEGIN
READ(A) WRITE(A) WRITE(B)
Workspace
Record Value Write Timestamp Record Value Write Timestamp
B 456 10000 123 A 10000 COMMIT
SLIDE 64
OPTIMISTIC CONCURRENCY CONTROL
64
Txn #1
BEGIN
READ(A) WRITE(A) WRITE(B)
Workspace
Record Value Write Timestamp
123 A 10000
Record Value Write Timestamp
B 456 10000 123 A 10000 COMMIT
SLIDE 65
OPTIMISTIC CONCURRENCY CONTROL
65
Txn #1
BEGIN
READ(A) WRITE(A) WRITE(B)
Workspace
Record Value Write Timestamp
123 A 10000
Record Value Write Timestamp
B 456 10000 123 A 10000 COMMIT
SLIDE 66
OPTIMISTIC CONCURRENCY CONTROL
66
Txn #1
BEGIN
READ(A) WRITE(A) WRITE(B)
Workspace
Record Value Write Timestamp
123 A 10000
Record Value Write Timestamp
B 456 10000 123 A 10000 COMMIT
SLIDE 67
OPTIMISTIC CONCURRENCY CONTROL
67
Txn #1
BEGIN
READ(A) WRITE(A) WRITE(B)
Workspace
Record Value Write Timestamp
123 A 10000
Record Value Write Timestamp
B 456 10000 123 A 10000 888
∞
COMMIT
SLIDE 68
OPTIMISTIC CONCURRENCY CONTROL
68
Txn #1
BEGIN
READ(A) WRITE(A) WRITE(B)
Workspace
Record Value Write Timestamp
123 A 10000
Record Value Write Timestamp
B 456 10000 123 A 10000 888
∞
COMMIT
SLIDE 69
OPTIMISTIC CONCURRENCY CONTROL
69
Txn #1
BEGIN
READ(A) WRITE(A) WRITE(B)
Workspace
Record Value Write Timestamp
B 456 10000 123 A 10000
Record Value Write Timestamp
B 456 10000 123 A 10000 888
∞
COMMIT
SLIDE 70
OPTIMISTIC CONCURRENCY CONTROL
70
Txn #1
BEGIN
READ(A) WRITE(A) WRITE(B)
Workspace
Record Value Write Timestamp
B 456 10000 123 A 10000
Record Value Write Timestamp
B 456 10000 123 A 10000 888
∞
999
∞
COMMIT
SLIDE 71
OPTIMISTIC CONCURRENCY CONTROL
71
Txn #1
BEGIN
READ(A) WRITE(A) WRITE(B)
Workspace
Record Value Write Timestamp
B 456 10000 123 A 10000
Record Value Write Timestamp
B 456 10000 123 A 10000 888
∞
999
∞
COMMIT
SLIDE 72
OPTIMISTIC CONCURRENCY CONTROL
72
Txn #1
BEGIN
READ(A) WRITE(A) WRITE(B) VALIDATE PHASE WRITE PHASE
Workspace
Record Value Write Timestamp
B 456 10000 123 A 10000
Record Value Write Timestamp
B 456 10000 123 A 10000 888
∞
999
∞
COMMIT
SLIDE 73
OPTIMISTIC CONCURRENCY CONTROL
73
Txn #1
BEGIN
READ(A) WRITE(A) WRITE(B) VALIDATE PHASE WRITE PHASE
Workspace
Record Value Write Timestamp
B 456 10000 123 A 10000
Record Value Write Timestamp
B 456 10000 123 A 10000 888
∞
999
∞
COMMIT
SLIDE 74
OPTIMISTIC CONCURRENCY CONTROL
74
Txn #1
BEGIN
READ(A) WRITE(A) WRITE(B) VALIDATE PHASE WRITE PHASE
Workspace
Record Value Write Timestamp
B 456 10000 123 A 10000
Record Value Write Timestamp
B 456 10000 123 A 10000 888
∞
999
∞
COMMIT
SLIDE 75
OPTIMISTIC CONCURRENCY CONTROL
75
Txn #1
BEGIN
READ(A) WRITE(A) WRITE(B) VALIDATE PHASE WRITE PHASE
10001
Workspace
Record Value Write Timestamp
B 456 10000 123 A 10000
Record Value Write Timestamp
B 456 10000 123 A 10000 888
∞
999
∞
COMMIT
SLIDE 76
OPTIMISTIC CONCURRENCY CONTROL
76
Txn #1
BEGIN
READ(A) WRITE(A) WRITE(B) VALIDATE PHASE WRITE PHASE
10001
Workspace
Record Value Write Timestamp
B 456 10000 123 A 10000
Record Value Write Timestamp
B 456 10000 123 A 10000 888
∞
999
∞
COMMIT 888 999 10001 10001
SLIDE 77
OPTIMISTIC CONCURRENCY CONTROL
77
Txn #1
BEGIN
READ(A) WRITE(A) WRITE(B) VALIDATE PHASE WRITE PHASE
10001
Record Value Write Timestamp
B 456 10000 123 A 10000 COMMIT 888 999 10001 10001
SLIDE 78
READ PHASE
Track the read/write sets of txns and store their writes in a private workspace. The DBMS copies every tuple that the txn accesses from the shared database to its workspace ensure repeatable reads.
78
SLIDE 79
VALIDATION PHASE
When the txn invokes COMMIT, the DBMS checks if it conflicts with other txns. Two methods for this phase:
→ Backward Validation → Forward Validation
79
SLIDE 80
BACKWARD VALIDATION
Check whether the committing txn intersects its read/write sets with those of any txns that have already committed.
80
Txn #1 Txn #2 Txn #3
TIME
COMMIT COMMIT COMMIT
SLIDE 81
BACKWARD VALIDATION
Check whether the committing txn intersects its read/write sets with those of any txns that have already committed.
81
Txn #1 Txn #2 Txn #3
TIME
COMMIT COMMIT COMMIT
SLIDE 82
BACKWARD VALIDATION
Check whether the committing txn intersects its read/write sets with those of any txns that have already committed.
82
Txn #1 Txn #2 Txn #3
TIME
COMMIT COMMIT COMMIT
SLIDE 83
BACKWARD VALIDATION
Check whether the committing txn intersects its read/write sets with those of any txns that have already committed.
83
Txn #1 Txn #2 Txn #3
TIME
COMMIT COMMIT COMMIT
SLIDE 84
BACKWARD VALIDATION
Check whether the committing txn intersects its read/write sets with those of any txns that have already committed.
84
Txn #1 Txn #2 Txn #3
TIME
COMMIT COMMIT COMMIT
Validation Scope
SLIDE 85
FORWARD VALIDATION
Check whether the committing txn intersects its read/write sets with any active txns that have not yet committed.
85
Txn #1 Txn #2 Txn #3
TIME
COMMIT COMMIT COMMIT
SLIDE 86
FORWARD VALIDATION
Check whether the committing txn intersects its read/write sets with any active txns that have not yet committed.
86
Txn #1 Txn #2 Txn #3
TIME
COMMIT COMMIT COMMIT
SLIDE 87
FORWARD VALIDATION
Check whether the committing txn intersects its read/write sets with any active txns that have not yet committed.
87
Txn #1 Txn #2 Txn #3
TIME
COMMIT COMMIT COMMIT
Validation Scope
SLIDE 88
VALIDATION PHASE
Original OCC uses serial validation. Parallel validation means that each txn must check the read/write sets of other txns that are trying to validate at the same time.
→ Each txn has to acquire locks for its write set records in some global order. → The txn does not need locks for read set records.
88
SLIDE 89
WRITE PHASE
The DBMS propagates the changes in the txn’s write set to the database and makes them visible to
- ther txns.
As each record is updated, the txn releases the lock acquired during the Validation Phase.
89
SLIDE 90
TIMESTAMP ALLOCATION
Mutex
→ Worst option. Mutexes are the "Hitler of Concurrency".
Atomic Addition
→ Requires cache invalidation on write.
Batched Atomic Addition
→ Needs a back-off mechanism to prevent fast burn.
Hardware Clock
→ Not sure if it will exist in future CPUs.
Hardware Counter
→ Not implemented in existing CPUs.
90
SLIDE 91
TIMESTAMP ALLOCATION
91
STARING INTO THE ABYSS: AN EVALUATION OF CONCURRENCY CONTROL WITH ONE THOUSAND CORES VLDB 2014
SLIDE 92
MODERN OCC
Harvard/MIT Silo MIT/CMU TicToc
92
SLIDE 93
SILO
Single-node, in-memory OLTP DBMS.
→ Serializable OCC with parallel backward validation. → Stored procedure-only API.
No writes to shared-memory for read txns. Batched timestamp allocation using epochs. Pure awesomeness from Eddie Kohler.
93
SPEEDY TRANSACTIONS IN MULTICORE IN-MEMORY DATABASES SOSP 2013
SLIDE 94
SILO
Single-node, in-memory OLTP DBMS.
→ Serializable OCC with parallel backward validation. → Stored procedure-only API.
No writes to shared-memory for read txns. Batched timestamp allocation using epochs. Pure awesomeness from Eddie Kohler.
94
SPEEDY TRANSACTIONS IN MULTICORE IN-MEMORY DATABASES SOSP 2013
SLIDE 95
SILO
Single-node, in-memory OLTP DBMS.
→ Serializable OCC with parallel backward validation. → Stored procedure-only API.
No writes to shared-memory for read txns. Batched timestamp allocation using epochs. Pure awesomeness from Eddie Kohler.
95
SPEEDY TRANSACTIONS IN MULTICORE IN-MEMORY DATABASES SOSP 2013
SLIDE 96
SILO: EPOCHS
Time is sliced into fixed-length epochs (40ms). All txns that start in the same epoch will be committed together at the end of the epoch.
→ Txns that span an epoch have to refresh themselves to be carried over into the next epoch.
Worker threads only need to synchronize at the beginning of each epoch.
96
SLIDE 97
SILO: TRANSACTION IDS
Each worker thread generates a unique txn id based on the current epoch number and the next value in its assigned batch.
97
Worker Worker Worker Worker Epoch Thread
SLIDE 98
SILO: TRANSACTION IDS
Each worker thread generates a unique txn id based on the current epoch number and the next value in its assigned batch.
98
Worker Worker Worker Worker Epoch Thread
Epoch=100
SLIDE 99
SILO: TRANSACTION IDS
Each worker thread generates a unique txn id based on the current epoch number and the next value in its assigned batch.
99
Worker Worker Worker Worker Epoch Thread
Epoch=100 [0,10] [11,20] [31,40] [21,30]
SLIDE 100
SILO: TRANSACTION IDS
Each worker thread generates a unique txn id based on the current epoch number and the next value in its assigned batch.
100
Worker Worker Worker Worker Epoch Thread
[0,10] [11,20] [31,40] [21,30] Epoch=200
SLIDE 101
SILO: COMMIT PROTOCOL
101
ATTR1 ATTR2 #-###-# John $100 #-###-# Tupac $999 #-###-# Wiz $67 #-###-# O.D.B. $13 TID Word
SLIDE 102
SILO: COMMIT PROTOCOL
102
ATTR1 ATTR2 #-###-# John $100 #-###-# Tupac $999 #-###-# Wiz $67 #-###-# O.D.B. $13
BATCH TIMESTAMP EPOCH EXTRA
TID Word
SLIDE 103
SILO: COMMIT PROTOCOL
103
ATTR1 ATTR2 #-###-# John $100 #-###-# Tupac $999 #-###-# Wiz $67 #-###-# O.D.B. $13
BATCH TIMESTAMP EPOCH EXTRA
TID Word
Write Lock Bit Latest Version Bit Absent Bit
SLIDE 104
SILO: COMMIT PROTOCOL
104
ATTR1 ATTR2 #-###-# John $100 #-###-# Tupac $999 #-###-# Wiz $67 #-###-# O.D.B. $13 TID Word
SLIDE 105
SILO: COMMIT PROTOCOL
105
ATTR1 ATTR2 #-###-# John $100 #-###-# Tupac $999 #-###-# Wiz $67 #-###-# O.D.B. $13
Workspace
Read Set Write Set #-###-# O.D.B. $13 Tupac $777 #-###-# Tupac $999 TID Word
SLIDE 106
SILO: COMMIT PROTOCOL
106
ATTR1 ATTR2 #-###-# John $100 #-###-# Tupac $999 #-###-# Wiz $67 #-###-# O.D.B. $13
Step #1: Lock Write Set Workspace
Read Set Write Set #-###-# O.D.B. $13 Tupac $777 #-###-# Tupac $999 TID Word
SLIDE 107
SILO: COMMIT PROTOCOL
107
ATTR1 ATTR2 #-###-# John $100 #-###-# Tupac $999 #-###-# Wiz $67 #-###-# O.D.B. $13
Step #1: Lock Write Set Workspace
Read Set Write Set #-###-# O.D.B. $13 Tupac $777 #-###-# Tupac $999 TID Word
SLIDE 108
SILO: COMMIT PROTOCOL
108
ATTR1 ATTR2 #-###-# John $100 #-###-# Tupac $999 #-###-# Wiz $67 #-###-# O.D.B. $13
Step #1: Lock Write Set Workspace
Read Set Write Set #-###-# O.D.B. $13 Tupac $777 #-###-# Tupac $999
Step #2: Examine Read Set
TID Word
SLIDE 109
SILO: COMMIT PROTOCOL
109
ATTR1 ATTR2 #-###-# John $100 #-###-# Tupac $999 #-###-# Wiz $67 #-###-# O.D.B. $13
Step #1: Lock Write Set Workspace
Read Set Write Set #-###-# O.D.B. $13 Tupac $777 #-###-# Tupac $999
Step #2: Examine Read Set
TID Word
SLIDE 110
SILO: COMMIT PROTOCOL
110
ATTR1 ATTR2 #-###-# John $100 #-###-# Tupac $999 #-###-# Wiz $67 #-###-# O.D.B. $13
Step #1: Lock Write Set Workspace
Read Set Write Set #-###-# O.D.B. $13 Tupac $777 #-###-# Tupac $999
Step #2: Examine Read Set
???
TID Word
SLIDE 111
SILO: COMMIT PROTOCOL
111
ATTR1 ATTR2 #-###-# John $100 #-###-# Tupac $999 #-###-# Wiz $67 #-###-# O.D.B. $13
Step #1: Lock Write Set Workspace
Read Set Write Set #-###-# O.D.B. $13 Tupac $777 #-###-# Tupac $999
Step #2: Examine Read Set
???
TID Word
SLIDE 112
SILO: COMMIT PROTOCOL
112
ATTR1 ATTR2 #-###-# John $100 #-###-# Tupac $999 #-###-# Wiz $67 #-###-# O.D.B. $13
Step #1: Lock Write Set Workspace
Read Set Write Set #-###-# O.D.B. $13 Tupac $777 #-###-# Tupac $999
Step #2: Examine Read Set
TID Word
SLIDE 113
SILO: COMMIT PROTOCOL
113
ATTR1 ATTR2 #-###-# John $100 #-###-# Tupac $999 #-###-# Wiz $67 #-###-# O.D.B. $13
Step #1: Lock Write Set Workspace
Read Set Write Set #-###-# O.D.B. $13 Tupac $777 #-###-# Tupac $999
Step #2: Examine Read Set Step #3: Install Write Set
TID Word
SLIDE 114
SILO: COMMIT PROTOCOL
114
ATTR1 ATTR2 #-###-# John $100 #-###-# Tupac $999 #-###-# Wiz $67 #-###-# O.D.B. $13
Step #1: Lock Write Set Workspace
Read Set Write Set #-###-# O.D.B. $13 Tupac $777 #-###-# Tupac $999
Step #2: Examine Read Set Step #3: Install Write Set
TID Word $777 #-###-#
SLIDE 115
SILO: GARBAGE COLLECTION
Cooperative threads GC. Each worker thread marks a deleted object with a reclamation epoch.
→ This is the epoch after which no thread could access the
- bject again, and thus can be safely removed.
→ Object references are maintained in thread-local storage to avoid unnecessary data movement.
115
SLIDE 116
SILO: RANGE QUERIES
DBMS handles phantoms by tracking the txn’s scan set (node set) on indexes.
→ Re-execute scans in the validation phase to see whether the index has changed. → Have to include “virtual” entries for keys that do not exist in the index.
We will discuss key-range and index gap locking next week…
116
SLIDE 117
SILO: PERFORMANCE
117
Source: Eddie Kohler
Database: TPC-C with 28 Warehouses Processor: 4 sockets, 8 cores per socket
SLIDE 118
SILO: PERFORMANCE
118
Source: Eddie Kohler
Database: TPC-C with 28 Warehouses Processor: 4 sockets, 8 cores per socket
SLIDE 119
PARTING THOUGHTS
Trade-off between aborting txns early or later.
→ Early: Avoid wasted work for txns that will eventually abort, but has checking overhead. → Later: No runtime overhead but lots of wasted work under high contention.
Silo is a very influential system.
119
SLIDE 120
NEXT CLASS
Multi-Version Concurrency Control
120
SLIDE 121
NEXT CLASS
Multi-Version Concurrency Control
121