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 150 ms SYNC DC2 DC1 20 ms 20 ms 3
GEO-REPLICATION DC2 DC1 4
GEO-REPLICATION DC2 DC1 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)) Player PT Tournament Player PT Tournament Sonic Sonic, A A Sonic Sonic, A A Pacman Sonic, B B Pacman Sonic, B B Mario Mario 5
GEO-REPLICATION DC2 DC1 enroll(Pacman, A): insert into PT values(‘Pacman’,’A’) Player PT PT Tournament Player PT Tournament Sonic Sonic, A Sonic, A A Sonic Sonic, A A Pacman Sonic, B Sonic, B B Pacman Sonic, B B Mario Pacman, A Mario 6
GEO-REPLICATION DC2 DC1 enroll(Mario, A): insert into PT values(‘Mario’,’A’) PT Player PT PT Tournament Player PT Tournament Sonic, A Sonic Sonic, A Sonic, A A Sonic Sonic, A A Sonic, B Pacman Sonic, B Sonic, B B Pacman Sonic, B B Mario, A Mario Pacman, A Mario 7
GEO-REPLICATION DC2 DC1 removeTournament(A): delete from tournament where id = ’A’ delete from PT where t = ’A’ PT Player PT Tournament Player Tournament Sonic, A Sonic Sonic, A A Sonic A Sonic, B Pacman Sonic, B B Pacman B Mario, A Mario Pacman, A Mario 8
GEO-REPLICATION SYNC DC2 DC1 PT Player PT Tournament Player Tournament Sonic, A Sonic Sonic, A A Sonic A Sonic, B Pacman Sonic, B B Pacman B Mario, A Mario Pacman, A Mario 9
GEO-REPLICATION SYNC DC2 DC1 Mario is enrolled in tournament that was concurrently removed. Referential integrity violation. PT Player PT Tournament Player Tournament Sonic, A Sonic Sonic, A A Sonic A Sonic, B Pacman Sonic, B B Pacman B Pacman, A Mario Pacman, A Mario Mario, A Mario, A 10
OUTLINE • Context / problem • First take: Sieve • Second take: SQL IPA • Final remarks 11
RedBlue Consistency Builds replicated systems that are fast and correct
RedBlue Consistency Builds replicated systems that are fast and correct Blue ops: local, fast, weakly consistent
RedBlue Consistency Builds replicated systems that are fast and correct State convergence Blue ops: local, fast, weakly consistent Invariant preservation
RedBlue Consistency Builds replicated systems that are fast and correct State convergence Blue ops: local, fast, weakly consistent Red ops: global, slow, strongly consistent Invariant preservation
Choosing between Blue or Red operation u Ensuring state convergence No commutative ? Red
Choosing between Blue or Red operation u Ensuring state convergence Ensuring No commutative invariant ? preservation Yes Yes No breaks Red Blue invariants?
Choosing between Blue or Red Good performance obtained if blue ops dominate op space operation u Ensuring state convergence Ensuring No commutative invariant ? preservation Yes Yes No breaks Red Blue invariants?
SIEVE Operation stream Transforming Commutative shadow operations Classifying Slow, Fast, Strongly consistent Weakly consistent
SIEVE Operation stream Transforming Commutative shadow operations
SIEVE Operation stream Transforming Commutative shadow operations Challenges: • Making arbitrary side effects commute • Minimizing human intervention
SIEVE Operation stream Transforming 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 Operation stream Transforming Commutative shadow operations Classifying Slow, Fast, Strongly consistent Weakly consistent
SIEVE Commutative shadow operations Classifying Slow, Fast, Strongly consistent Weakly consistent
SIEVE Challenge: • How to classify accurately and efficiently? Commutative shadow operations Classifying Slow, Fast, Strongly consistent Weakly consistent
SIEVE Challenge: • How to classify accurately and efficiently? Commutative shadow operations Classifying Slow, Fast, Strongly consistent Weakly consistent
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) Goal : maintain invariants while avoiding • Static analysis of complete application(s) coordination – Changes in applications require rerunning the analysis process 31
GEO-REPLICATION DC2 DC1 removeTournament(A): delete from tournament where id = ’A’ delete from PT where t = ’A’ PT Tournament PT Player PT Tournament Player Tournament Sonic, A A Sonic, A Sonic Sonic, A A Sonic A Sonic, B B Sonic, B Pacman Sonic, B B Pacman B Mario Mario 32
GEO-REPLICATION DC2 DC1 enroll(Mario, A): insert into PT values(‘Mario’,’A’) PT PT Player PT Tournament Player Tournament Sonic, A Sonic, A Sonic Sonic, A A Sonic A Sonic, B Sonic, B Pacman Sonic, B B Pacman B Mario, A Mario Mario 33
GEO-REPLICATION DC2 DC1 enroll(Mario, A): insert into PT values(‘Mario’,’A’) touch tournament where id = ‘A’ PT PT Player PT Tournament Player Tournament Sonic, A Sonic, A Sonic Sonic, A A Sonic A Sonic, B Sonic, B Pacman Sonic, B B Pacman B Mario, A Mario Mario 34
GEO-REPLICATION DC2 DC1 enroll(Mario, A): insert into PT values(‘Mario’,’A’) touch tournament where id = ‘A’ touch cascade PT where t=’A’ PT PT Player PT Tournament Player Tournament Sonic, A Sonic, A Sonic Sonic, A A Sonic A Sonic, B Sonic, B Pacman Sonic, B B Pacman B Mario, A Mario Mario 35
GEO-REPLICATION DC2 DC1 enroll(Mario, A): insert into PT values(‘Mario’,’A’) removeTournament(A): touch tournament where id = ‘A’ delete from tournament where id = ’A’ touch cascade PT where t=’A’ delete cascade from PT where t = ’A’ PT PT Player PT Tournament Player Tournament Sonic, A Sonic, A Sonic Sonic, A A Sonic A Sonic, B Sonic, B Pacman Sonic, B B Pacman B Mario, A Mario Mario 36
GEO-REPLICATION SYNC DC2 DC1 Rules add-wins : 1. || => 2. || => PT PT Player PT Tournament Player Tournament Sonic, A Sonic, A Sonic Sonic, A A Sonic A Sonic, B Sonic, B Pacman Sonic, B B Pacman B Mario, A Mario Mario 37
GEO-REPLICATION SYNC DC2 DC1 Rules add-wins : 1. || => 2. || => PT PT Player PT Tournament Player Tournament Sonic, A Sonic, A Sonic Sonic, A A Sonic A Sonic, B Sonic, B Pacman Sonic, B B Pacman B Mario, A Mario Mario 38
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 Goal : “modify” operations in runtime. to be blue/coordinated => slow Use schema definition. – Acquiring reservation/token (Indigo/CISE) • Static analysis of complete application(s) – Changes in applications require rerunning the analysis process 40
create table player( id varchar(20), primary key id) GEO-REPLICATION 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)) DC2 DC1 enroll(Mario, A): insert into PT values(‘Mario’,’A’) PT PT Player PT Tournament Player Tournament Sonic, A Sonic, A Sonic Sonic, A A Sonic A Sonic, B Sonic, B Pacman Sonic, B B Pacman B Mario, A Mario Mario 41
Recommend
More recommend
