maintaining sql invariants in weakly consistent … · sonic pacman mario tournament a b player...
TRANSCRIPT
MAINTAINING SQL INVARIANTS IN WEAKLY CONSISTENT DATABASES
Nuno Preguiça (NOVA LINCS, FCT/Universidade NOVA de Lisboa)
Joint work with:Valter Balegas, Cheng Li (MPI, now Oracle), João Sousa, David Lopes, Sérgio Duarte, Carla Ferreira, João Leitão, Allen Clement (MPI, now Google), Viktor Vafeiadis (MPI), Rodrigo Rodrigues (now Inesc-Id/IST)
INTERNET SERVICES NOWADAYS
• Services operate on a global scale.
• An unprecedented number of people are using Internet services.
• Systems use geo-replication for low latency and high availability.
2
GEO-REPLICATION
3
20 ms
150 ms
20 ms
SYNC DC2
DC1
GEO-REPLICATION
4
DC2
DC1
GEO-REPLICATION
5
Tournament
A
B
Player
Sonic
Pacman
Mario
Tournament
A
B
Player
Sonic
Pacman
Mario
DC2
DC1
PT
Sonic, A
Sonic, B
PT
Sonic, A
Sonic, B
create table player( id varchar(20), primary key id)
create table tournament( id varchar(20), primary key id)
create table pt( p varchar(20), t varchar(20), foreign key (p) REFERENCES player (id), foreign key (t) REFERENCES tournament (id))
PT
Sonic, A
Sonic, B
GEO-REPLICATION
6
Tournament
A
B
Player
Sonic
Pacman
Mario
Tournament
A
B
Player
Sonic
Pacman
Mario
DC2
DC1
PT
Sonic, A
Sonic, B
PT
Sonic, A
Sonic, B
Pacman, A
enroll(Pacman, A):insert into PT values(‘Pacman’,’A’)
PT
Sonic, A
Sonic, B
GEO-REPLICATION
7
Tournament
A
B
Player
Sonic
Pacman
Mario
Tournament
A
B
Player
Sonic
Pacman
Mario
DC2
DC1
PT
Sonic, A
Sonic, B
PT
Sonic, A
Sonic, B
Pacman, A
enroll(Mario, A):insert into PT values(‘Mario’,’A’)
PT
Sonic, A
Sonic, B
Mario, A
PT
Sonic, A
Sonic, B
Pacman, A
PT
Sonic, A
Sonic, B
Mario, A
GEO-REPLICATION
8
Tournament
A
B
Player
Sonic
Pacman
Mario
Tournament
A
B
Player
Sonic
Pacman
Mario
DC2
DC1
removeTournament(A):delete from tournament where id = ’A’
delete from PT where t = ’A’
PT
Sonic, A
Sonic, B
Pacman, A
PT
Sonic, A
Sonic, B
Mario, A
GEO-REPLICATION
9
Tournament
A
B
Player
Sonic
Pacman
Mario
Tournament
A
B
Player
Sonic
Pacman
Mario
DC2
DC1
SYNC
PT
Sonic, A
Sonic, B
Pacman, A
Mario, A
PT
Sonic, A
Sonic, B
Pacman, A
Mario, A
GEO-REPLICATION
10
Tournament
A
B
Player
Sonic
Pacman
Mario
Tournament
A
B
Player
Sonic
Pacman
Mario
DC2
DC1
SYNC
Mario is enrolled in tournament that was
concurrently removed.Referential integrity
violation.
OUTLINE
• Context / problem• First take: Sieve• Second take: SQL IPA• Final remarks
11
RedBlue ConsistencyBuilds replicated systems that are fast and correct
RedBlue ConsistencyBuilds replicated systems that are fast and correct
Blue ops: local, fast, weakly consistent
RedBlue ConsistencyBuilds replicated systems that are fast and correct
State convergence
Invariant preservation
Blue ops: local, fast, weakly consistent
RedBlue ConsistencyBuilds replicated systems that are fast and correct
State convergence
Invariant preservation
Blue ops: local, fast, weakly consistent
Red ops: global, slow, strongly consistent
Choosing between Blue or Red
operation u
commutative?
Red
No
Ensuring state convergence
Choosing between Blue or Red
operation u
commutative?
breaks invariants?
Red Blue
No
Yes
Yes
No
Ensuring state convergence
Ensuring invariant
preservation
Choosing between Blue or Red
operation u
commutative?
breaks invariants?
Red Blue
No
Yes
Yes
No
Ensuring state convergence
Ensuring invariant
preservation
Good performance obtained if blue ops dominate op space
SIEVE
Transforming
Operation stream
Slow,Strongly consistent
Fast,Weakly consistent
Commutative shadow operations
Classifying
SIEVE
Transforming
Operation stream
Commutative shadow operations
SIEVE
Transforming
Operation stream
Commutative shadow operations
Challenges:• Making arbitrary side effects commute• Minimizing human intervention
SIEVE
Transforming
Operation stream
Commutative shadow operations
Challenges:• Making arbitrary side effects commute• Minimizing human intervention
CRDT Annotation Example
@AUSET CREATE TABLE BankAccount(id INT(11) NOT NULL,
@NUMDELTA balance INT(11) default 0,@LWW name char(60) default NULL,
PRIMARY KEY (id)) ENGINE=InnoDB
CRDT Annotation Example
@AUSET CREATE TABLE BankAccount(id INT(11) NOT NULL,
@NUMDELTA balance INT(11) default 0,@LWW name char(60) default NULL,
PRIMARY KEY (id)) ENGINE=InnoDB
SIEVE
Transforming
Operation stream
Slow,Strongly consistent
Fast,Weakly consistent
Commutative shadow operations
Classifying
SIEVE
Slow,Strongly consistent
Fast,Weakly consistent
Commutative shadow operations
Classifying
SIEVE
Slow,Strongly consistent
Fast,Weakly consistent
Commutative shadow operations
Classifying
Challenge:• How to classify accurately and efficiently?
SIEVE
Slow,Strongly consistent
Fast,Weakly consistent
Commutative shadow operations
Classifying
Challenge:• How to classify accurately and efficiently?
OUTLINE
• Context / problem• First take: Sieve• Second take: SQL IPA• Final remarks
29
Limitations of Sieve
• Operations that may violate the invariant need to be red/coordinated => slow– Acquiring reservation/token (Indigo/CISE)
• Static analysis of complete application(s)– Changes in applications require rerunning the
analysis process
30
Limitations of Sieve
• Operations that may violate the invariant need to be blue/coordinated => slow– Acquiring reservation/token (Indigo/CISE)
• Static analysis of complete application(s)– Changes in applications require rerunning the
analysis process
31
Goal: maintain invariants while avoiding coordination
Tournament
A
B
PT
Sonic, A
Sonic, B
PT
Sonic, A
Sonic, B
PT
Sonic, A
Sonic, B
GEO-REPLICATION
32
Tournament
A
B
Player
Sonic
Pacman
Mario
Tournament
A
B
Player
Sonic
Pacman
Mario
DC2
DC1
removeTournament(A):delete from tournament where id = ’A’
delete from PT where t = ’A’
PT
Sonic, A
Sonic, B
PT
Sonic, A
Sonic, B
PT
Sonic, A
Sonic, B
Mario, A
GEO-REPLICATION
33
Tournament
A
B
Player
Sonic
Pacman
Mario
Tournament
A
B
Player
Sonic
Pacman
Mario
DC2
DC1
enroll(Mario, A):insert into PT values(‘Mario’,’A’)
PT
Sonic, A
Sonic, B
PT
Sonic, A
Sonic, B
PT
Sonic, A
Sonic, B
Mario, A
GEO-REPLICATION
34
Tournament
A
B
Player
Sonic
Pacman
Mario
Tournament
A
B
Player
Sonic
Pacman
Mario
DC2
DC1
enroll(Mario, A):insert into PT values(‘Mario’,’A’)touch tournament where id = ‘A’
PT
Sonic, A
Sonic, B
PT
Sonic, A
Sonic, B
PT
Sonic, A
Sonic, B
Mario, A
GEO-REPLICATION
35
Tournament
A
B
Player
Sonic
Pacman
Mario
Tournament
A
B
Player
Sonic
Pacman
Mario
DC2
DC1
enroll(Mario, A):insert into PT values(‘Mario’,’A’)touch tournament where id = ‘A’
touch cascade PT where t=’A’
PT
Sonic, A
Sonic, B
PT
Sonic, A
Sonic, B
PT
Sonic, A
Sonic, B
Mario, A
GEO-REPLICATION
36
Tournament
A
B
Player
Sonic
Pacman
Mario
Tournament
A
B
Player
Sonic
Pacman
Mario
DC2
DC1
enroll(Mario, A):insert into PT values(‘Mario’,’A’)touch tournament where id = ‘A’
touch cascade PT where t=’A’
removeTournament(A):delete from tournament where id = ’A’
delete cascade from PT where t = ’A’
PT
Sonic, A
Sonic, B
PT
Sonic, A
Sonic, B
PT
Sonic, A
Sonic, B
Mario, A
GEO-REPLICATION
37
Tournament
A
B
Player
Sonic
Pacman
Mario
Tournament
A
B
Player
Sonic
Pacman
Mario
DC2
DC1
SYNC
Rules add-wins:1. || => 2. || =>
PT
Sonic, A
Sonic, B
PT
Sonic, A
Sonic, B
PT
Sonic, A
Sonic, B
Mario, A
GEO-REPLICATION
38
Tournament
A
B
Player
Sonic
Pacman
Mario
Tournament
A
B
Player
Sonic
Pacman
Mario
DC2
DC1
SYNC
Rules add-wins:1. || => 2. || =>
Other invariants
• Primary key (uniqueness)– Split keyspace
• Check constraint– E.g. stock int CHECK (stock >= 0)– Solved using bounded counter (escrow)
39
Limitations of Sieve
• Operations that may violate the invariant need to be blue/coordinated => slow– Acquiring reservation/token (Indigo/CISE)
• Static analysis of complete application(s)– Changes in applications require rerunning the
analysis process
40
Goal: “modify” operations in runtime.Use schema definition.
PT
Sonic, A
Sonic, B
PT
Sonic, A
Sonic, B
PT
Sonic, A
Sonic, B
Mario, A
GEO-REPLICATION
41
Tournament
A
B
Player
Sonic
Pacman
Mario
Tournament
A
B
Player
Sonic
Pacman
Mario
DC2
DC1
enroll(Mario, A):insert into PT values(‘Mario’,’A’)
create table player( id varchar(20), primary key id)
create table tournament( id varchar(20), primary key id)
create table pt( p varchar(20), t varchar(20), foreign key (p) REFERENCES player (id), foreign key (t) REFERENCES tournament (id))
PT
Sonic, A
Sonic, B
PT
Sonic, A
Sonic, B
PT
Sonic, A
Sonic, B
Mario, A
GEO-REPLICATION
42
Tournament
A
B
Player
Sonic
Pacman
Mario
Tournament
A
B
Player
Sonic
Pacman
Mario
DC2
DC1
enroll(Mario, A):insert into PT values(‘Mario’,’A’)touch tournament where id = ‘A’touch player where id = ‘Mario’
create table player( id varchar(20), primary key id)
create table tournament( id varchar(20), primary key id)
create table pt( p varchar(20), t varchar(20), AW foreign key (p) REFERENCES player (id), AW foreign key (t) REFERENCES tournament (id))
OUTLINE
• Context / problem• First take: Sieve• Second take: SQL IPA• Final remarks
43
Status
• Implementing prototype on top of Antidote database
• Runtime solution equivalent to static solution implemented in IPA
44
Impact of additional updates
0 10 20 30 40
1 2 4 8 16 32 64
Spee
d-up
Number of updated Keys
IPA/Strong
45
TOURNAMENT
46
0 50
100 150 200 250 300 350
0 50 100 150 200 250 300 350 400
Late
ncy
[ms]
Throughput [TP/s]
StrongIndigo
IPACausal
TOURNAMENT: OPERATIONS LATENCY
47
0 50
100 150 200 250 300
BeginFinish
Remove
DoMatch
EnrollDisenroll
Status
Late
ncy
[ms] Indigo
IPACausal
Final remarks
• SQL schema allows to define constraints• First approach– Coordinate on operations that may break invariants
• Second approach–Maintain invariants without coordination (or
minimizing coordination)
48
QUESTIONS?
Valter Balegas – NOVA LINCS, FCT-UNL - Putting Consistency Back Into Eventual Consistency @ Eurosys'15 49