Availability, Consistency, and Horizontally Scalable Data Management (SF Bay Area ACM)

B7dc26518988058faa50712248c80bd3?s=47 pbailis
February 19, 2014

Availability, Consistency, and Horizontally Scalable Data Management (SF Bay Area ACM)

19 February 2014 SF Bay Area ACM Meetup
http://www.meetup.com/SF-Bay-ACM/events/158437632/

Video available at: http://www.youtube.com/watch?v=JVEwJyTIjcE

B7dc26518988058faa50712248c80bd3?s=128

pbailis

February 19, 2014
Tweet

Transcript

  1. Data Management Availability, Consistency, Peter Bailis! UC Berkeley, AMPLab! @pbailis

    with Alan Fekete, Mike Franklin, Ali Ghodsi, Ion Stoica, Joe Hellerstein and Horizontally Scalable
  2. DISTRIBUTED DATABASES THE END OF THE END OF SCALABLE AND

    CORRECT Peter Bailis! UC Berkeley, AMPLab! @pbailis with Alan Fekete, Mike Franklin, Ali Ghodsi, Ion Stoica, Joe Hellerstein
  3. A portrait of big services

  4. A portrait of big services

  5. A portrait of big services

  6. A portrait of big services

  7. A portrait of big services

  8. A portrait of big services

  9. A portrait of big services

  10. A portrait of big services

  11. stateless! horizontally scalable A portrait of big services

  12. stateless! horizontally scalable concurrent,! stateful! durable A portrait of big

    services
  13. None
  14. Users care about the correctness of their applications

  15. Users care about the correctness of their applications “usernames should

    be unique”
  16. Users care about the correctness of their applications “usernames should

    be unique” “each patient should have a attending doctor”
  17. Users care about the correctness of their applications “usernames should

    be unique” “each patient should have a attending doctor” “account balances should be positive”
  18. None
  19. Classic answer: use ACID transactions

  20. Classic answer: use ACID transactions Equivalent Serial Execution

  21. Classic answer: use ACID transactions Equivalent Serial Execution

  22. Classic answer: use ACID transactions Equivalent Serial Execution isolation provides

    correctness
  23. Classic answer: use ACID transactions Equivalent Serial Execution isolation provides

    correctness ACID
  24. Classic answer: use ACID transactions Equivalent Serial Execution isolation provides

    correctness ACID ACID
  25. Classic answer: use ACID transactions Equivalent Serial Execution isolation provides

    correctness ACID ACID
  26. Under the hood: ACID (conflict serializability) reasons about low-level read/write

    traces
  27. Under the hood: ACID (conflict serializability) reasons about low-level read/write

    traces
  28. Under the hood: ACID (conflict serializability) reasons about low-level read/write

    traces
  29. Under the hood: ACID (conflict serializability) reasons about low-level read/write

    traces
  30. Under the hood: ACID (conflict serializability) reasons about low-level read/write

    traces
  31. For any two operations to the same data item, if

    at least one is a write, operations might conflict T2 T1 T3 ww(c) rw(a) rw(c) rw(b) T3 T1 T2 Under the hood: ACID (conflict serializability) reasons about low-level read/write traces
  32. For any two operations to the same data item, if

    at least one is a write, operations might conflict T2 T1 T3 ww(c) rw(a) rw(c) rw(b) T3 T1 T2 Under the hood: ACID (conflict serializability) reasons about low-level read/write traces END RESULT:! A MYOPIC APPROACH TO CORRECTNESS
  33. END RESULT:! A MYOPIC APPROACH TO CORRECTNESS

  34. END RESULT:! A MYOPIC APPROACH TO CORRECTNESS COST:! SERIALIZABILITY REQUIRES

    COORDINATION
  35. END RESULT:! A MYOPIC APPROACH TO CORRECTNESS COST:! SERIALIZABILITY REQUIRES

    COORDINATION synchronous coordination =
  36. END RESULT:! A MYOPIC APPROACH TO CORRECTNESS COST:! SERIALIZABILITY REQUIRES

    COORDINATION synchronous coordination = stalls during network partitions =
  37. END RESULT:! A MYOPIC APPROACH TO CORRECTNESS COST:! SERIALIZABILITY REQUIRES

    COORDINATION synchronous coordination = stalls during network partitions = RTT latency during operations =
  38. END RESULT:! A MYOPIC APPROACH TO CORRECTNESS COST:! SERIALIZABILITY REQUIRES

    COORDINATION synchronous coordination = stalls during network partitions = RTT latency during operations = possible stall during concurrent access
  39. THE ACID SCALABILITY WALL

  40. 2 4 6 8 10 12 14 16 18 20

    Number of Servers in 2PC 0 200 400 600 800 1000 1200 Maximum Throughput (txns/s) LOCAL! DATACENTER max 1200 txn/s THE ACID SCALABILITY WALL
  41. +OR +CA +IR +SP +TO +SI +SY Participating Datacenters (+VA)

    2 4 6 8 10 12 Maximum Throughput (txn/s) 2 4 6 8 10 12 14 16 18 20 Number of Servers in 2PC 0 200 400 600 800 1000 1200 Maximum Throughput (txns/s) LOCAL! DATACENTER MULTI-! DATACENTER! max 1200 txn/s max 12 txn/s THE ACID SCALABILITY WALL
  42. +OR +CA +IR +SP +TO +SI +SY Participating Datacenters (+VA)

    2 4 6 8 10 12 Maximum Throughput (txn/s) 2 4 6 8 10 12 14 16 18 20 Number of Servers in 2PC 0 200 400 600 800 1000 1200 Maximum Throughput (txns/s) LOCAL! DATACENTER MULTI-! DATACENTER! max 1200 txn/s max 12 txn/s THE ACID SCALABILITY WALL decentralized (optimized) 2PC SERIALIZABILITY REQUIRES COORDINATION decentralized (optimized) 2PC
  43. None
  44. HANA

  45. do not support! SSI/serializability HANA

  46. do not support! SSI/serializability HANA Actian Ingres YES Aerospike NO

    Persistit NO Clustrix NO Greenplum YES IBM DB2 YES IBM Informix YES MySQL YES MemSQL NO MS SQL Server YES NuoDB NO Oracle 11G NO Oracle BDB YES Oracle BDB JE YES Postgres 9.2.2 YES SAP Hana NO ScaleDB NO VoltDB YES 8/18 databases! surveyed did not 15/18 used! weaker models! by default “Highly Available Transactions: Virtues and Limitations,” VLDB 2014
  47. do not support! SSI/serializability HANA Actian Ingres YES Aerospike NO

    Persistit NO Clustrix NO Greenplum YES IBM DB2 YES IBM Informix YES MySQL YES MemSQL NO MS SQL Server YES NuoDB NO Oracle 11G NO Oracle BDB YES Oracle BDB JE YES Postgres 9.2.2 YES SAP Hana NO ScaleDB NO VoltDB YES 8/18 databases! surveyed did not 15/18 used! weaker models! by default “Highly Available Transactions: Virtues and Limitations,” VLDB 2014
  48. None
  49. synchronous coordination =! stalls during network partitions = RTT latency

    during operations = possible stall during concurrent access
  50. no (or asynchronous) coordination = synchronous coordination =! stalls during

    network partitions = RTT latency during operations = possible stall during concurrent access
  51. no (or asynchronous) coordination = Gilbert and Lynch “High Availability”

    = synchronous coordination =! stalls during network partitions = RTT latency during operations = possible stall during concurrent access
  52. no (or asynchronous) coordination = Gilbert and Lynch “High Availability”

    = low latency (no RTT) = synchronous coordination =! stalls during network partitions = RTT latency during operations = possible stall during concurrent access
  53. no (or asynchronous) coordination = Gilbert and Lynch “High Availability”

    = low latency (no RTT) = indefinite horizontal scaling synchronous coordination =! stalls during network partitions = RTT latency during operations = possible stall during concurrent access
  54. no (or asynchronous) coordination = Gilbert and Lynch “High Availability”

    = low latency (no RTT) = indefinite horizontal scaling (even for a single record; true scalability) synchronous coordination =! stalls during network partitions = RTT latency during operations = possible stall during concurrent access
  55. no (or asynchronous) coordination = Gilbert and Lynch “High Availability”

    = low latency (no RTT) = indefinite horizontal scaling (even for a single record; true scalability) synchronous coordination =! stalls during network partitions = RTT latency during operations = possible stall during concurrent access
  56. no (or asynchronous) coordination = Gilbert and Lynch “High Availability”

    = low latency (no RTT) = indefinite horizontal scaling (even for a single record; true scalability) benefits also apply to concurrent access in single-node systems synchronous coordination =! stalls during network partitions = RTT latency during operations = possible stall during concurrent access
  57. no (or asynchronous) coordination = Gilbert and Lynch “High Availability”

    = low latency (no RTT) = indefinite horizontal scaling (even for a single record; true scalability) benefits also apply to concurrent access in single-node systems synchronous coordination =! stalls during network partitions = RTT latency during operations = possible stall during concurrent access BUT OFTEN GIVE UP CORRECTNESS!!
  58. CORRECTNESS vs. SCALABILITY

  59. CORRECTNESS vs. SCALABILITY SERIALIZABILITY

  60. CORRECTNESS vs. SCALABILITY SERIALIZABILITY Our insight: serializability is sufficient for

    correctness! ! ! ! ! but is not necessary! ! ! !
  61. CORRECTNESS vs. SCALABILITY SERIALIZABILITY Our insight: serializability is sufficient for

    correctness! ! ! ! ! but is not necessary! ! ! !
  62. CORRECTNESS vs. SCALABILITY SERIALIZABILITY Our solution: coordination avoidance Our insight:

    serializability is sufficient for correctness! ! ! ! ! but is not necessary! ! ! !
  63. CORRECTNESS vs. SCALABILITY SERIALIZABILITY Our solution: coordination avoidance Our insight:

    serializability is sufficient for correctness! ! ! ! ! but is not necessary! ! ! ! Only coordinate when necessary
  64. CORRECTNESS vs. SCALABILITY SERIALIZABILITY Our solution: coordination avoidance CORRECTNESS and

    SCALABILITY Our insight: serializability is sufficient for correctness! ! ! ! ! but is not necessary! ! ! ! Only coordinate when necessary
  65. CORRECTNESS vs. SCALABILITY SERIALIZABILITY Our solution: coordination avoidance CORRECTNESS and

    SCALABILITY Our insight: serializability is sufficient for correctness! ! ! ! ! but is not necessary! ! ! ! Only coordinate when necessary
  66. CORRECTNESS vs. SCALABILITY SERIALIZABILITY Our solution: coordination avoidance CORRECTNESS and

    SCALABILITY Our insight: serializability is sufficient for correctness! ! ! ! ! but is not necessary! ! ! ! Only coordinate when necessary Ask applications for invariants
  67. CORRECTNESS vs. SCALABILITY SERIALIZABILITY Our solution: coordination avoidance CORRECTNESS and

    SCALABILITY Our insight: serializability is sufficient for correctness! ! ! ! ! but is not necessary! ! ! ! Only coordinate when necessary Ask applications for invariants Invariants determine:! ! When is coordination needed?! ! How much coordination is required?! !
  68. Ask applications for invariants

  69. Invariant:! user IDs are unique Ask applications for invariants

  70. Invariant:! user IDs are unique Ask applications for invariants

  71. Invariant:! user IDs are unique Ask applications for invariants

  72. Invariant:! user IDs are unique Ask applications for invariants

  73. Invariant:! user IDs are unique Ask applications for invariants

  74. None
  75. Invariant: each employee is in a department Operations: add employees

  76. Invariant: each employee is in a department Operations: add employees

    Anomaly (to avoid):
  77. Invariant: each employee is in a department Operations: add employees

    l_emp = employees.find(id=“louise”) ! Anomaly (to avoid):
  78. Invariant: each employee is in a department Operations: add employees

    l_emp = employees.find(id=“louise”) ! Anomaly (to avoid):
  79. Invariant: each employee is in a department Operations: add employees

    l_emp = employees.find(id=“louise”) ! l_dept = dept.find(l_emp.dept) ! Anomaly (to avoid):
  80. Invariant: each employee is in a department Operations: add employees

    l_emp = employees.find(id=“louise”) ! l_dept = dept.find(l_emp.dept) ! ENORECORD Anomaly (to avoid):
  81. Invariant: each employee is in a department Operations: add employees

  82. Invariant: each employee is in a department Operations: add employees

  83. Invariant: each employee is in a department Operations: add employees

  84. employees = {} dept = {{“ops”:1}, {“dev”:2}} Invariant: each employee

    is in a department Operations: add employees
  85. employees = {} dept = {{“ops”:1}, {“dev”:2}} Invariant: each employee

    is in a department Operations: add employees
  86. employees = {} dept = {{“ops”:1}, {“dev”:2}} Invariant: each employee

    is in a department Operations: add employees d1 = dept.find(“ops”) employees.add({“Harry”:d1})
  87. employees = {} dept = {{“ops”:1}, {“dev”:2}} Invariant: each employee

    is in a department Operations: add employees d1 = dept.find(“ops”) employees.add({“Harry”:d1})
  88. employees = {} dept = {{“ops”:1}, {“dev”:2}} Invariant: each employee

    is in a department Operations: add employees d2 = dept.find(“dev”) employees.add({“Sue”:d2}) d1 = dept.find(“ops”) employees.add({“Harry”:d1})
  89. employees = {} dept = {{“ops”:1}, {“dev”:2}} Invariant: each employee

    is in a department Operations: add employees d2 = dept.find(“dev”) employees.add({“Sue”:d2}) d1 = dept.find(“ops”) employees.add({“Harry”:d1})
  90. employees = {{“Harry”:1}, {“Sue”:2}} dept = {{“ops”:1}, {“dev”:2}} employees =

    {} dept = {{“ops”:1}, {“dev”:2}} Invariant: each employee is in a department Operations: add employees d2 = dept.find(“dev”) employees.add({“Sue”:d2}) d1 = dept.find(“ops”) employees.add({“Harry”:d1})
  91. employees = {{“Harry”:1}, {“Sue”:2}} dept = {{“ops”:1}, {“dev”:2}} employees =

    {} dept = {{“ops”:1}, {“dev”:2}} Invariant: each employee is in a department Operations: add employees d2 = dept.find(“dev”) employees.add({“Sue”:d2}) d1 = dept.find(“ops”) employees.add({“Harry”:d1}) Invariant holds!
  92. None
  93. Invariant: only one ops on staff at a time Operations:

    change staffing
  94. Anomaly (to avoid): Invariant: only one ops on staff at

    a time Operations: change staffing
  95. on_duty = employees.find(staffed=”T”) ! Anomaly (to avoid): Invariant: only one

    ops on staff at a time Operations: change staffing
  96. on_duty = employees.find(staffed=”T”) ! Anomaly (to avoid): Invariant: only one

    ops on staff at a time Operations: change staffing
  97. on_duty = employees.find(staffed=”T”) ! assert(len(on_duty) == 1) ! Anomaly (to

    avoid): Invariant: only one ops on staff at a time Operations: change staffing
  98. on_duty = employees.find(staffed=”T”) ! assert(len(on_duty) == 1) ! ASSERTION FAILS

    Anomaly (to avoid): Invariant: only one ops on staff at a time Operations: change staffing
  99. Invariant: only one ops on staff at a time Operations:

    change staffing
  100. staff = {“Laura”:T, “Harry”:F, “Gary”:F} Invariant: only one ops on

    staff at a time Operations: change staffing
  101. staff = {“Laura”:T, “Harry”:F, “Gary”:F} Invariant: only one ops on

    staff at a time Operations: change staffing
  102. staff = {“Laura”:T, “Harry”:F, “Gary”:F} staff.set({“Laura”:F}, {“Harry”:T}) Invariant: only one

    ops on staff at a time Operations: change staffing
  103. staff = {“Laura”:T, “Harry”:F, “Gary”:F} staff.set({“Laura”:F}, {“Harry”:T}) Invariant: only one

    ops on staff at a time Operations: change staffing
  104. staff = {“Laura”:T, “Harry”:F, “Gary”:F} staff.set({“Laura”:F}, “Gary”:T}) staff.set({“Laura”:F}, {“Harry”:T}) Invariant:

    only one ops on staff at a time Operations: change staffing
  105. staff = {“Laura”:T, “Harry”:F, “Gary”:F} staff.set({“Laura”:F}, “Gary”:T}) staff.set({“Laura”:F}, {“Harry”:T}) Invariant:

    only one ops on staff at a time Operations: change staffing
  106. staff = {“Laura”:T, “Harry”:F, “Gary”:F} staff.set({“Laura”:F}, “Gary”:T}) staff.set({“Laura”:F}, {“Harry”:T}) Invariant:

    only one ops on staff at a time Operations: change staffing
  107. staff = {“Laura”:T, “Harry”:F, “Gary”:F} staff.set({“Laura”:F}, “Gary”:T}) staff.set({“Laura”:F}, {“Harry”:T}) Invariant

    violated! staff = {“Laura”:F, “Harry”:T, “Gary”:T} Invariant: only one ops on staff at a time Operations: change staffing
  108. None
  109. None
  110. None
  111. None
  112. None
  113. None
  114. None
  115. SAFETY correctness always guaranteed

  116. SAFETY correctness always guaranteed LIVENESS database states agree (converge)

  117. I-confluence is necessary and sufficient for simultaneously maintaining application-level consistency,

    availability, convergence, and coordination-freedom Invariant confluence: formal characterization of safe, coordination-free execution
  118. To maintain consistency... I-confluence is necessary and sufficient for simultaneously

    maintaining application-level consistency, availability, convergence, and coordination-freedom Invariant confluence: formal characterization of safe, coordination-free execution
  119. Sufficient? Necessary? App-Level? Conflict Serializability Yes No No Invariant Confluence

    Yes Yes Yes State-based Commutativity Yes* No Depends To maintain consistency... I-confluence is necessary and sufficient for simultaneously maintaining application-level consistency, availability, convergence, and coordination-freedom Invariant confluence: formal characterization of safe, coordination-free execution
  120. Sufficient? Necessary? App-Level? Conflict Serializability Yes No No Invariant Confluence

    Yes Yes Yes State-based Commutativity Yes* No Depends To maintain consistency... I-confluence is necessary and sufficient for simultaneously maintaining application-level consistency, availability, convergence, and coordination-freedom Invariant confluence: formal characterization of safe, coordination-free execution
  121. Sufficient? Necessary? App-Level? Conflict Serializability Yes No No Invariant Confluence

    Yes Yes Yes State-based Commutativity Yes* No Depends To maintain consistency... I-confluence is necessary and sufficient for simultaneously maintaining application-level consistency, availability, convergence, and coordination-freedom Invariant confluence: formal characterization of safe, coordination-free execution
  122. Sufficient? Necessary? App-Level? Conflict Serializability Yes No No Invariant Confluence

    Yes Yes Yes State-based Commutativity Yes* No Depends To maintain consistency... I-confluence is necessary and sufficient for simultaneously maintaining application-level consistency, availability, convergence, and coordination-freedom Invariant confluence: formal characterization of safe, coordination-free execution
  123. Sufficient? Necessary? App-Level? Conflict Serializability Yes No No Invariant Confluence

    Yes Yes Yes State-based Commutativity Yes* No Depends To maintain consistency... I-confluence is necessary and sufficient for simultaneously maintaining application-level consistency, availability, convergence, and coordination-freedom Invariant confluence: formal characterization of safe, coordination-free execution
  124. Sufficient? Necessary? App-Level? Conflict Serializability Yes No No Invariant Confluence

    Yes Yes Yes State-based Commutativity Yes* No Depends To maintain consistency... I-confluence is necessary and sufficient for simultaneously maintaining application-level consistency, availability, convergence, and coordination-freedom Invariant confluence: formal characterization of safe, coordination-free execution
  125. Sufficient? Necessary? App-Level? Conflict Serializability Yes No No Invariant Confluence

    Yes Yes Yes State-based Commutativity Yes* No Depends To maintain consistency... I-confluence is necessary and sufficient for simultaneously maintaining application-level consistency, availability, convergence, and coordination-freedom Invariant confluence: formal characterization of safe, coordination-free execution
  126. Sufficient? Necessary? App-Level? Conflict Serializability Yes No No Invariant Confluence

    Yes Yes Yes State-based Commutativity Yes* No Depends To maintain consistency... I-confluence is necessary and sufficient for simultaneously maintaining application-level consistency, availability, convergence, and coordination-freedom Invariant confluence: formal characterization of safe, coordination-free execution
  127. Formal framework for reasoning about application coordination requirements

  128. Formal framework for reasoning about application coordination requirements Coordination depends

    on combination of:! - expressiveness of operations! - strength of invariants
  129. Formal framework for reasoning about application coordination requirements Coordination depends

    on combination of:! - expressiveness of operations! - strength of invariants STRENGTH OF INVARIANTS EXPRESSIVENESS OF OPERATIONS *Okay, so this is simplified, and there isn’t really a linear order on either axis (rather, it’s more about equivalence classes), but humor me here...
  130. Formal framework for reasoning about application coordination requirements Coordination depends

    on combination of:! - expressiveness of operations! - strength of invariants STRENGTH OF INVARIANTS EXPRESSIVENESS OF OPERATIONS *Okay, so this is simplified, and there isn’t really a linear order on either axis (rather, it’s more about equivalence classes), but humor me here... COORDINATION! REQUIRED! COORDINATION-FREE
  131. Formal framework for reasoning about application coordination requirements Coordination depends

    on combination of:! - expressiveness of operations! - strength of invariants STRENGTH OF INVARIANTS EXPRESSIVENESS OF OPERATIONS *Okay, so this is simplified, and there isn’t really a linear order on either axis (rather, it’s more about equivalence classes), but humor me here... COORDINATION! REQUIRED! COORDINATION-FREE
  132. Formal framework for reasoning about application coordination requirements Coordination depends

    on combination of:! - expressiveness of operations! - strength of invariants STRENGTH OF INVARIANTS EXPRESSIVENESS OF OPERATIONS *Okay, so this is simplified, and there isn’t really a linear order on either axis (rather, it’s more about equivalence classes), but humor me here... COORDINATION! REQUIRED! COORDINATION-FREE
  133. Formal framework for reasoning about application coordination requirements Coordination depends

    on combination of:! - expressiveness of operations! - strength of invariants STRENGTH OF INVARIANTS EXPRESSIVENESS OF OPERATIONS *Okay, so this is simplified, and there isn’t really a linear order on either axis (rather, it’s more about equivalence classes), but humor me here... COORDINATION! REQUIRED! COORDINATION-FREE
  134. Can apply the I-confluence test to! standard SQL for program

    analysis Invariant Operation C.F. ? Equality, Inequality Any ??? Generate unique ID Any ??? Specify unique ID Insert ??? >! Increment ??? >! Decrement ??? < Decrement ??? < Increment ??? Foreign Key Insert ??? Foreign Key Delete ??? Secondary Indexing Any ??? Materialized Views Any ??? AUTO_INCREMENT Insert ???
  135. Can apply the I-confluence test to! standard SQL for program

    analysis Invariant Operation C.F. ? Equality, Inequality Any ??? Generate unique ID Any ??? Specify unique ID Insert ??? >! Increment ??? >! Decrement ??? < Decrement ??? < Increment ??? Foreign Key Insert ??? Foreign Key Delete ??? Secondary Indexing Any ??? Materialized Views Any ??? AUTO_INCREMENT Insert ???
  136. Constraint: record IDs are unique

  137. Constraint: record IDs are unique DECLARE TABLE users (! ID

    int UNIQUE,! FirstName string,! LastName string )
  138. Constraint: record IDs are unique DECLARE TABLE users (! ID

    int UNIQUE,! FirstName string,! LastName string ) Anomaly! (to avoid):
  139. Constraint: record IDs are unique DECLARE TABLE users (! ID

    int UNIQUE,! FirstName string,! LastName string ) Anomaly! (to avoid):
  140. Constraint: record IDs are unique DECLARE TABLE users (! ID

    int UNIQUE,! FirstName string,! LastName string ) Anomaly! (to avoid):
  141. Constraint: record IDs are unique DECLARE TABLE users (! ID

    int UNIQUE,! FirstName string,! LastName string )
  142. Operation: insert record with specific ID INSERT INTO users (ID,

    firstname, lastname)! VALUES (1, “Leslie”, “Lamport”) Constraint: record IDs are unique DECLARE TABLE users (! ID int UNIQUE,! FirstName string,! LastName string )
  143. NOT C-FREE Operation: insert record with specific ID INSERT INTO

    users (ID, firstname, lastname)! VALUES (1, “Leslie”, “Lamport”) Constraint: record IDs are unique DECLARE TABLE users (! ID int UNIQUE,! FirstName string,! LastName string )
  144. Operation: insert record INSERT INTO users (firstname, lastname)! VALUES (“Leslie”,

    “Lamport”) NOT C-FREE Operation: insert record with specific ID INSERT INTO users (ID, firstname, lastname)! VALUES (1, “Leslie”, “Lamport”) Constraint: record IDs are unique DECLARE TABLE users (! ID int UNIQUE,! FirstName string,! LastName string )
  145. let the DB decide the ID; use node ID or

    UUID C-FREE! Operation: insert record INSERT INTO users (firstname, lastname)! VALUES (“Leslie”, “Lamport”) NOT C-FREE Operation: insert record with specific ID INSERT INTO users (ID, firstname, lastname)! VALUES (1, “Leslie”, “Lamport”) Constraint: record IDs are unique DECLARE TABLE users (! ID int UNIQUE,! FirstName string,! LastName string )
  146. Foreign key constraints

  147. Foreign key constraints DECLARE TABLE users (! U_ID int UNIQUE,!

    D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) )
  148. Foreign key constraints DECLARE TABLE users (! U_ID int UNIQUE,!

    D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) ) DECLARE TABLE department (! D_ID int UNIQUE,! DeptName string )
  149. Foreign key constraints DECLARE TABLE users (! U_ID int UNIQUE,!

    D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) ) DECLARE TABLE department (! D_ID int UNIQUE,! DeptName string )
  150. Foreign key constraints DECLARE TABLE users (! U_ID int UNIQUE,!

    D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) ) DECLARE TABLE department (! D_ID int UNIQUE,! DeptName string ) NEW_D_ID = INSERT INTO department VALUES (“awesome division”);! INSERT INTO users (D_ID, UserName) VALUES (NEW_D_ID, “lamport”);!
  151. Foreign key constraints DECLARE TABLE users (! U_ID int UNIQUE,!

    D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) ) DECLARE TABLE department (! D_ID int UNIQUE,! DeptName string ) NEW_D_ID = INSERT INTO department VALUES (“awesome division”);! INSERT INTO users (D_ID, UserName) VALUES (NEW_D_ID, “lamport”);!
  152. Foreign key constraints DECLARE TABLE users (! U_ID int UNIQUE,!

    D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) ) DECLARE TABLE department (! D_ID int UNIQUE,! DeptName string ) NEW_D_ID = INSERT INTO department VALUES (“awesome division”);! INSERT INTO users (D_ID, UserName) VALUES (NEW_D_ID, “lamport”);! Anomaly (to avoid):
  153. Foreign key constraints DECLARE TABLE users (! U_ID int UNIQUE,!

    D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) ) DECLARE TABLE department (! D_ID int UNIQUE,! DeptName string ) NEW_D_ID = INSERT INTO department VALUES (“awesome division”);! INSERT INTO users (D_ID, UserName) VALUES (NEW_D_ID, “lamport”);! Anomaly (to avoid): “lamport” has no department! read lamport record; lookup lamport.D_ID returns NULL
  154. Foreign key constraints DECLARE TABLE users (! U_ID int UNIQUE,!

    D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) ) DECLARE TABLE department (! D_ID int UNIQUE,! DeptName string ) NEW_D_ID = INSERT INTO department VALUES (“awesome division”);! INSERT INTO users (D_ID, UserName) VALUES (NEW_D_ID, “lamport”);! Anomaly (to avoid): “lamport” has no department! read lamport record; lookup lamport.D_ID returns NULL I-confluence insight:! cannot be violated by inserts!
  155. Foreign key constraints DECLARE TABLE users (! U_ID int UNIQUE,!

    D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) ) DECLARE TABLE department (! D_ID int UNIQUE,! DeptName string ) NEW_D_ID = INSERT INTO department VALUES (“awesome division”);! INSERT INTO users (D_ID, UserName) VALUES (NEW_D_ID, “lamport”);! Anomaly (to avoid): “lamport” has no department! read lamport record; lookup lamport.D_ID returns NULL I-confluence insight:! cannot be violated by inserts! …but be careful about implementation! many ways to use coordination to enforce coordination-free semantics
  156. Foreign key constraints DECLARE TABLE users (! U_ID int UNIQUE,!

    D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) ) DECLARE TABLE department (! D_ID int UNIQUE,! DeptName string ) NEW_D_ID = INSERT INTO department VALUES (“awesome division”);! INSERT INTO users (D_ID, UserName) VALUES (NEW_D_ID, “lamport”);!
  157. users shard department shard Foreign key constraints DECLARE TABLE users

    (! U_ID int UNIQUE,! D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) ) DECLARE TABLE department (! D_ID int UNIQUE,! DeptName string ) NEW_D_ID = INSERT INTO department VALUES (“awesome division”);! INSERT INTO users (D_ID, UserName) VALUES (NEW_D_ID, “lamport”);!
  158. users shard department shard Foreign key constraints DECLARE TABLE users

    (! U_ID int UNIQUE,! D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) ) DECLARE TABLE department (! D_ID int UNIQUE,! DeptName string ) NEW_D_ID = INSERT INTO department VALUES (“awesome division”);! INSERT INTO users (D_ID, UserName) VALUES (NEW_D_ID, “lamport”);!
  159. users shard department shard Foreign key constraints DECLARE TABLE users

    (! U_ID int UNIQUE,! D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) ) DECLARE TABLE department (! D_ID int UNIQUE,! DeptName string ) NEW_D_ID = INSERT INTO department VALUES (“awesome division”);! INSERT INTO users (D_ID, UserName) VALUES (NEW_D_ID, “lamport”);! Visible to all readers Visible to all readers
  160. users shard department shard Foreign key constraints DECLARE TABLE users

    (! U_ID int UNIQUE,! D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) ) DECLARE TABLE department (! D_ID int UNIQUE,! DeptName string ) NEW_D_ID = INSERT INTO department VALUES (“awesome division”);! INSERT INTO users (D_ID, UserName) VALUES (NEW_D_ID, “lamport”);! Visible to all readers Visible to all readers Not yet visible to all readers Not yet visible to all readers
  161. users shard department shard Foreign key constraints DECLARE TABLE users

    (! U_ID int UNIQUE,! D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) ) DECLARE TABLE department (! D_ID int UNIQUE,! DeptName string ) NEW_D_ID = INSERT INTO department VALUES (“awesome division”);! INSERT INTO users (D_ID, UserName) VALUES (NEW_D_ID, “lamport”);! Visible to all readers Visible to all readers Not yet visible to all readers Not yet visible to all readers
  162. users shard department shard (342, “awesome division”) Foreign key constraints

    DECLARE TABLE users (! U_ID int UNIQUE,! D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) ) DECLARE TABLE department (! D_ID int UNIQUE,! DeptName string ) NEW_D_ID = INSERT INTO department VALUES (“awesome division”);! INSERT INTO users (D_ID, UserName) VALUES (NEW_D_ID, “lamport”);! Visible to all readers Visible to all readers Not yet visible to all readers Not yet visible to all readers
  163. users shard department shard (342, “awesome division”) Foreign key constraints

    DECLARE TABLE users (! U_ID int UNIQUE,! D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) ) DECLARE TABLE department (! D_ID int UNIQUE,! DeptName string ) NEW_D_ID = INSERT INTO department VALUES (“awesome division”);! INSERT INTO users (D_ID, UserName) VALUES (NEW_D_ID, “lamport”);! Visible to all readers Visible to all readers Not yet visible to all readers Not yet visible to all readers
  164. users shard department shard (342, “awesome division”) Foreign key constraints

    DECLARE TABLE users (! U_ID int UNIQUE,! D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) ) DECLARE TABLE department (! D_ID int UNIQUE,! DeptName string ) NEW_D_ID = INSERT INTO department VALUES (“awesome division”);! INSERT INTO users (D_ID, UserName) VALUES (NEW_D_ID, “lamport”);! Visible to all readers Visible to all readers Not yet visible to all readers Not yet visible to all readers (???, 342, “lamport”)
  165. users shard department shard (342, “awesome division”) Foreign key constraints

    DECLARE TABLE users (! U_ID int UNIQUE,! D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) ) DECLARE TABLE department (! D_ID int UNIQUE,! DeptName string ) NEW_D_ID = INSERT INTO department VALUES (“awesome division”);! INSERT INTO users (D_ID, UserName) VALUES (NEW_D_ID, “lamport”);! Visible to all readers Visible to all readers Not yet visible to all readers Not yet visible to all readers (402, 342, “lamport”)
  166. users shard department shard (342, “awesome division”) Foreign key constraints

    DECLARE TABLE users (! U_ID int UNIQUE,! D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) ) DECLARE TABLE department (! D_ID int UNIQUE,! DeptName string ) NEW_D_ID = INSERT INTO department VALUES (“awesome division”);! INSERT INTO users (D_ID, UserName) VALUES (NEW_D_ID, “lamport”);! Visible to all readers Visible to all readers Not yet visible to all readers Not yet visible to all readers (402, 342, “lamport”)
  167. users shard department shard (342, “awesome division”) Foreign key constraints

    DECLARE TABLE users (! U_ID int UNIQUE,! D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) ) DECLARE TABLE department (! D_ID int UNIQUE,! DeptName string ) NEW_D_ID = INSERT INTO department VALUES (“awesome division”);! INSERT INTO users (D_ID, UserName) VALUES (NEW_D_ID, “lamport”);! Visible to all readers Visible to all readers Not yet visible to all readers Not yet visible to all readers (402, 342, “lamport”)
  168. users shard department shard (342, “awesome division”) Foreign key constraints

    DECLARE TABLE users (! U_ID int UNIQUE,! D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) ) DECLARE TABLE department (! D_ID int UNIQUE,! DeptName string ) NEW_D_ID = INSERT INTO department VALUES (“awesome division”);! INSERT INTO users (D_ID, UserName) VALUES (NEW_D_ID, “lamport”);! Visible to all readers Visible to all readers Not yet visible to all readers Not yet visible to all readers (402, 342, “lamport”)
  169. users shard department shard (342, “awesome division”) Foreign key constraints

    DECLARE TABLE users (! U_ID int UNIQUE,! D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) ) DECLARE TABLE department (! D_ID int UNIQUE,! DeptName string ) NEW_D_ID = INSERT INTO department VALUES (“awesome division”);! INSERT INTO users (D_ID, UserName) VALUES (NEW_D_ID, “lamport”);! Visible to all readers Visible to all readers Not yet visible to all readers Not yet visible to all readers (402, 342, “lamport”)
  170. users shard department shard (342, “awesome division”) Foreign key constraints

    DECLARE TABLE users (! U_ID int UNIQUE,! D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) ) DECLARE TABLE department (! D_ID int UNIQUE,! DeptName string ) NEW_D_ID = INSERT INTO department VALUES (“awesome division”);! INSERT INTO users (D_ID, UserName) VALUES (NEW_D_ID, “lamport”);! Visible to all readers Visible to all readers Not yet visible to all readers Not yet visible to all readers (402, 342, “lamport”) 2 RTT writes (prepare and make visible)! Between 1-2 RTTs for reads! Basic idea: store metadata to record sibling writes
  171. users shard department shard (342, “awesome division”) Foreign key constraints

    DECLARE TABLE users (! U_ID int UNIQUE,! D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) ) DECLARE TABLE department (! D_ID int UNIQUE,! DeptName string ) NEW_D_ID = INSERT INTO department VALUES (“awesome division”);! INSERT INTO users (D_ID, UserName) VALUES (NEW_D_ID, “lamport”);! Visible to all readers Visible to all readers Not yet visible to all readers Not yet visible to all readers (402, 342, “lamport”) 2 RTT writes (prepare and make visible)! Between 1-2 RTTs for reads! Basic idea: store metadata to record sibling writes Read Atomic Multi-Partition ! Transactions, SIGMOD 2014
  172. Invariant Operation C.F. ? Equality, Inequality Any ??? Generate unique

    ID Any ??? Specify unique ID Insert ??? >! Increment ??? >! Decrement ??? < Decrement ??? < Increment ??? Foreign Key Insert ??? Foreign Key Delete ??? Secondary Indexing Any ??? Materialized Views Any ??? AUTO_INCREMENT Insert ??? Can apply the I-confluence test to! standard SQL for program analysis
  173. Invariant Operation C.F. ? Equality, Inequality Any Y Generate unique

    ID Any Y Specify unique ID Insert N >! Increment Y >! Decrement N < Decrement Y < Increment N Foreign Key Insert Y Foreign Key Delete Y* Secondary Indexing Any Y Materialized Views Any Y! AUTO_INCREMENT Insert N Can apply the I-confluence test to! standard SQL for program analysis
  174. Eventual consistency Invariant Operation C.F. ? Equality, Inequality Any Y

    Generate unique ID Any Y Specify unique ID Insert N >! Increment Y >! Decrement N < Decrement Y < Increment N Foreign Key Insert Y Foreign Key Delete Y* Secondary Indexing Any Y Materialized Views Any Y! AUTO_INCREMENT Insert N Can apply the I-confluence test to! standard SQL for program analysis
  175. Eventual consistency Invariant Operation C.F. ? Equality, Inequality Any Y

    Generate unique ID Any Y Specify unique ID Insert N >! Increment Y >! Decrement N < Decrement Y < Increment N Foreign Key Insert Y Foreign Key Delete Y* Secondary Indexing Any Y Materialized Views Any Y! AUTO_INCREMENT Insert N Abstract data types Can apply the I-confluence test to! standard SQL for program analysis
  176. Eventual consistency Invariant Operation C.F. ? Equality, Inequality Any Y

    Generate unique ID Any Y Specify unique ID Insert N >! Increment Y >! Decrement N < Decrement Y < Increment N Foreign Key Insert Y Foreign Key Delete Y* Secondary Indexing Any Y Materialized Views Any Y! AUTO_INCREMENT Insert N RAMP Transaction Abstract data types Can apply the I-confluence test to! standard SQL for program analysis
  177. Remainder: Cannot avoid coordination Eventual consistency Invariant Operation C.F. ?

    Equality, Inequality Any Y Generate unique ID Any Y Specify unique ID Insert N >! Increment Y >! Decrement N < Decrement Y < Increment N Foreign Key Insert Y Foreign Key Delete Y* Secondary Indexing Any Y Materialized Views Any Y! AUTO_INCREMENT Insert N RAMP Transaction Abstract data types Can apply the I-confluence test to! standard SQL for program analysis
  178. Standard SQL with extensions and ! analysis!

  179. CREATE TABLE Orders! (! O_ID int AUTO_INCREMENT,! C_ID int,! O_QTY

    int,! DATE datetime NOT NULL! ! PRIMARY KEY (OrderID),! FOREIGN KEY (CustomerID) REFERENCES Customers(C_ID),! CONSTRAINT [O_QTY > 0]! ) CREATE PROCEDURE CreateOrder(@C_ID int, @O_QTY int)! AS! INSERT INTO Orders (C_ID, O_QTY, DATE) VALUES! (C_ID, O_QTY, NOW());! GO Standard SQL with extensions and ! analysis!
  180. > WARNING: Orders.O_ID requires coordination!! INSERT found in CreateOrder! >

    WARNING: CreateOrder requires remote check for @C_ID! CREATE TABLE Orders! (! O_ID int AUTO_INCREMENT,! C_ID int,! O_QTY int,! DATE datetime NOT NULL! ! PRIMARY KEY (OrderID),! FOREIGN KEY (CustomerID) REFERENCES Customers(C_ID),! CONSTRAINT [O_QTY > 0]! ) CREATE PROCEDURE CreateOrder(@C_ID int, @O_QTY int)! AS! INSERT INTO Orders (C_ID, O_QTY, DATE) VALUES! (C_ID, O_QTY, NOW());! GO Standard SQL with extensions and ! analysis!
  181. None
  182. DDL with invariants DDL with invariants CREATE TABLE Orders (

    O_ID int AUTO_INCREMENT, C_ID int, O_QTY int, DATE datetime NOT NULL PRIMARY KEY (OrderID), FOREIGN KEY (CustomerID) REFERENCES Customers(C_ID), CONSTRAINT [O_QTY > 0] )
  183. DDL with invariants DDL with invariants CREATE TABLE Orders (

    O_ID int AUTO_INCREMENT, C_ID int, O_QTY int, DATE datetime NOT NULL PRIMARY KEY (OrderID), FOREIGN KEY (CustomerID) REFERENCES Customers(C_ID), CONSTRAINT [O_QTY > 0] ) I-confluence! analysis
  184. DDL with invariants DDL with invariants CREATE TABLE Orders (

    O_ID int AUTO_INCREMENT, C_ID int, O_QTY int, DATE datetime NOT NULL PRIMARY KEY (OrderID), FOREIGN KEY (CustomerID) REFERENCES Customers(C_ID), CONSTRAINT [O_QTY > 0] ) I-confluence! analysis COORDINATION COST
  185. DDL with invariants DDL with invariants CREATE TABLE Orders (

    O_ID int AUTO_INCREMENT, C_ID int, O_QTY int, DATE datetime NOT NULL PRIMARY KEY (OrderID), FOREIGN KEY (CustomerID) REFERENCES Customers(C_ID), CONSTRAINT [O_QTY > 0] ) I-confluence! analysis COORDINATION COST
  186. I-confluence in real programs?

  187. I-confluence in real programs? Benchmark I-confluent invariants TPC-C! 10 of

    12 TPC-E 4 of 4 AuctionMark all but 1 SEATS all but 1 JPAB all TATP all
  188. I-confluence in real programs? Benchmark I-confluent invariants TPC-C! 10 of

    12 TPC-E 4 of 4 AuctionMark all but 1 SEATS all but 1 JPAB all TATP all TRADITIONAL! OLTP! APPLICATIONS! ARE ACHIEVABLE! WITHOUT! (MUCH)! COORDINATION
  189. I-confluence in real programs? Benchmark I-confluent invariants TPC-C! 10 of

    12 TPC-E 4 of 4 AuctionMark all but 1 SEATS all but 1 JPAB all TATP all Still requires coordination-avoiding query plans!! • Appropriate merge (e.g., counter datatype) • Atomic multi-put (e.g., RAMP) • Nested atomic transactions (e.g., New-Order ID assignment) TRADITIONAL! OLTP! APPLICATIONS! ARE ACHIEVABLE! WITHOUT! (MUCH)! COORDINATION
  190. DDL with invariants DDL with invariants CREATE TABLE Orders (

    O_ID int AUTO_INCREMENT, C_ID int, O_QTY int, DATE datetime NOT NULL PRIMARY KEY (OrderID), FOREIGN KEY (CustomerID) REFERENCES Customers(C_ID), CONSTRAINT [O_QTY > 0] ) I-confluence! analysis COORDINATION COST
  191. DDL with invariants DDL with invariants CREATE TABLE Orders (

    O_ID int AUTO_INCREMENT, C_ID int, O_QTY int, DATE datetime NOT NULL PRIMARY KEY (OrderID), FOREIGN KEY (CustomerID) REFERENCES Customers(C_ID), CONSTRAINT [O_QTY > 0] ) I-confluence! analysis COORDINATION COST
  192. DDL with invariants DDL with invariants CREATE TABLE Orders (

    O_ID int AUTO_INCREMENT, C_ID int, O_QTY int, DATE datetime NOT NULL PRIMARY KEY (OrderID), FOREIGN KEY (CustomerID) REFERENCES Customers(C_ID), CONSTRAINT [O_QTY > 0] ) Query! planner I-confluence! analysis COORDINATION COST
  193. DDL with invariants DDL with invariants CREATE TABLE Orders (

    O_ID int AUTO_INCREMENT, C_ID int, O_QTY int, DATE datetime NOT NULL PRIMARY KEY (OrderID), FOREIGN KEY (CustomerID) REFERENCES Customers(C_ID), CONSTRAINT [O_QTY > 0] ) Query! planner Application queries I-confluence! analysis COORDINATION COST
  194. DDL with invariants DDL with invariants CREATE TABLE Orders (

    O_ID int AUTO_INCREMENT, C_ID int, O_QTY int, DATE datetime NOT NULL PRIMARY KEY (OrderID), FOREIGN KEY (CustomerID) REFERENCES Customers(C_ID), CONSTRAINT [O_QTY > 0] ) Query! planner Application queries Query! executor I-confluence! analysis COORDINATION COST
  195. DDL with invariants DDL with invariants CREATE TABLE Orders (

    O_ID int AUTO_INCREMENT, C_ID int, O_QTY int, DATE datetime NOT NULL PRIMARY KEY (OrderID), FOREIGN KEY (CustomerID) REFERENCES Customers(C_ID), CONSTRAINT [O_QTY > 0] ) Query! planner Storage! manager Application queries Query! executor I-confluence! analysis COORDINATION COST
  196. DDL with invariants DDL with invariants CREATE TABLE Orders (

    O_ID int AUTO_INCREMENT, C_ID int, O_QTY int, DATE datetime NOT NULL PRIMARY KEY (OrderID), FOREIGN KEY (CustomerID) REFERENCES Customers(C_ID), CONSTRAINT [O_QTY > 0] ) Query! planner Storage! manager Lock! manager Application queries Query! executor I-confluence! analysis COORDINATION COST
  197. DDL with invariants DDL with invariants CREATE TABLE Orders (

    O_ID int AUTO_INCREMENT, C_ID int, O_QTY int, DATE datetime NOT NULL PRIMARY KEY (OrderID), FOREIGN KEY (CustomerID) REFERENCES Customers(C_ID), CONSTRAINT [O_QTY > 0] ) Query! planner Storage! manager Lock! manager Application queries Query! executor I-confluence! analysis STATISTICS COORDINATION COST
  198. COORDINATION COST DDL with invariants DDL with invariants CREATE TABLE

    Orders ( O_ID int AUTO_INCREMENT, C_ID int, O_QTY int, DATE datetime NOT NULL PRIMARY KEY (OrderID), FOREIGN KEY (CustomerID) REFERENCES Customers(C_ID), CONSTRAINT [O_QTY > 0] ) Query! planner Storage! manager Application queries Query! executor I-confluence! analysis STATISTICS Lock! manager
  199. COORDINATION COST DDL with invariants DDL with invariants CREATE TABLE

    Orders ( O_ID int AUTO_INCREMENT, C_ID int, O_QTY int, DATE datetime NOT NULL PRIMARY KEY (OrderID), FOREIGN KEY (CustomerID) REFERENCES Customers(C_ID), CONSTRAINT [O_QTY > 0] ) Query! planner Storage! manager Application queries Query! executor I-confluence! analysis STATISTICS Lock! manager
  200. COORDINATION COST DDL with invariants DDL with invariants CREATE TABLE

    Orders ( O_ID int AUTO_INCREMENT, C_ID int, O_QTY int, DATE datetime NOT NULL PRIMARY KEY (OrderID), FOREIGN KEY (CustomerID) REFERENCES Customers(C_ID), CONSTRAINT [O_QTY > 0] ) Query! planner Storage! manager Application queries Query! executor I-confluence! analysis STATISTICS Lock! manager ONGOING! WORK
  201. COORDINATION COST DDL with invariants DDL with invariants CREATE TABLE

    Orders ( O_ID int AUTO_INCREMENT, C_ID int, O_QTY int, DATE datetime NOT NULL PRIMARY KEY (OrderID), FOREIGN KEY (CustomerID) REFERENCES Customers(C_ID), CONSTRAINT [O_QTY > 0] ) STATIC! PLAN! (manual) Storage! manager Application queries Query! executor I-confluence! analysis STATISTICS Lock! manager
  202. COORDINATION COST DDL with invariants DDL with invariants CREATE TABLE

    Orders ( O_ID int AUTO_INCREMENT, C_ID int, O_QTY int, DATE datetime NOT NULL PRIMARY KEY (OrderID), FOREIGN KEY (CustomerID) REFERENCES Customers(C_ID), CONSTRAINT [O_QTY > 0] ) STATIC! PLAN! (manual) Storage! manager Application queries Query! executor I-confluence! analysis Lock! manager
  203. COORDINATION COST DDL with invariants DDL with invariants CREATE TABLE

    Orders ( O_ID int AUTO_INCREMENT, C_ID int, O_QTY int, DATE datetime NOT NULL PRIMARY KEY (OrderID), FOREIGN KEY (CustomerID) REFERENCES Customers(C_ID), CONSTRAINT [O_QTY > 0] ) STATIC! PLAN! (manual) Storage! manager Application queries Query! executor I-confluence! analysis Lock! manager
  204. None
  205. TPC-C New-Order

  206. TPC-C New-Order warehouse district orders neworders

  207. TPC-C New-Order Pre-materialized aggregates (e.g., W_YTD=SUM(orders for warehouse)) warehouse district

    orders neworders
  208. TPC-C New-Order Pre-materialized aggregates (e.g., W_YTD=SUM(orders for warehouse)) warehouse district

    orders neworders insert! 100
  209. TPC-C New-Order Pre-materialized aggregates (e.g., W_YTD=SUM(orders for warehouse)) warehouse district

    orders neworders +100 insert! 100
  210. TPC-C New-Order Pre-materialized aggregates (e.g., W_YTD=SUM(orders for warehouse)) RAMP transaction

    on counter CRDT warehouse district orders neworders +100 insert! 100
  211. TPC-C New-Order Pre-materialized aggregates (e.g., W_YTD=SUM(orders for warehouse)) RAMP transaction

    on counter CRDT warehouse district orders neworders
  212. TPC-C New-Order Foreign key insert (e.g., NewOrder, Orders tables) Pre-materialized

    aggregates (e.g., W_YTD=SUM(orders for warehouse)) RAMP transaction on counter CRDT warehouse district orders neworders
  213. TPC-C New-Order Foreign key insert (e.g., NewOrder, Orders tables) Pre-materialized

    aggregates (e.g., W_YTD=SUM(orders for warehouse)) RAMP transaction on counter CRDT insert! O_ID warehouse district orders neworders
  214. TPC-C New-Order Foreign key insert (e.g., NewOrder, Orders tables) Pre-materialized

    aggregates (e.g., W_YTD=SUM(orders for warehouse)) RAMP transaction on counter CRDT insert! O_ID warehouse district orders neworders insert! O_ID
  215. TPC-C New-Order Foreign key insert (e.g., NewOrder, Orders tables) Pre-materialized

    aggregates (e.g., W_YTD=SUM(orders for warehouse)) RAMP transaction on counter CRDT RAMP transaction across tables insert! O_ID warehouse district orders neworders insert! O_ID
  216. TPC-C New-Order Foreign key insert (e.g., NewOrder, Orders tables) Pre-materialized

    aggregates (e.g., W_YTD=SUM(orders for warehouse)) Sequence number ID assignment (i.e., D_NEXT_O_ID) RAMP transaction on counter CRDT RAMP transaction across tables insert! O_ID warehouse district orders neworders insert! O_ID
  217. TPC-C New-Order Foreign key insert (e.g., NewOrder, Orders tables) Pre-materialized

    aggregates (e.g., W_YTD=SUM(orders for warehouse)) Sequence number ID assignment (i.e., D_NEXT_O_ID) RAMP transaction on counter CRDT RAMP transaction across tables insert! O_ID warehouse district orders neworders insert! O_ID assign! new! O_ID
  218. TPC-C New-Order Foreign key insert (e.g., NewOrder, Orders tables) Pre-materialized

    aggregates (e.g., W_YTD=SUM(orders for warehouse)) Sequence number ID assignment (i.e., D_NEXT_O_ID) RAMP transaction on counter CRDT RAMP transaction across tables insert! O_ID warehouse district orders neworders insert! O_ID assign! new! O_ID
  219. TPC-C New-Order Foreign key insert (e.g., NewOrder, Orders tables) Pre-materialized

    aggregates (e.g., W_YTD=SUM(orders for warehouse)) Sequence number ID assignment (i.e., D_NEXT_O_ID) RAMP transaction on counter CRDT RAMP transaction across tables insert! O_ID warehouse district orders neworders insert! O_ID deferred atomic incrementAndGet() on commit! assign! new! O_ID
  220. TPC-C New-Order Foreign key insert (e.g., NewOrder, Orders tables) Pre-materialized

    aggregates (e.g., W_YTD=SUM(orders for warehouse)) Sequence number ID assignment (i.e., D_NEXT_O_ID) RAMP transaction on counter CRDT RAMP transaction across tables insert! O_ID warehouse district orders neworders insert! O_ID deferred atomic incrementAndGet() on commit! assign! new! O_ID tmp ID
  221. TPC-C New-Order Foreign key insert (e.g., NewOrder, Orders tables) Pre-materialized

    aggregates (e.g., W_YTD=SUM(orders for warehouse)) Sequence number ID assignment (i.e., D_NEXT_O_ID) RAMP transaction on counter CRDT RAMP transaction across tables rewrite FK references to point to temp unique ID! create local index from temp unique ID to sequence ID insert! O_ID warehouse district orders neworders insert! O_ID deferred atomic incrementAndGet() on commit! assign! new! O_ID tmp ID
  222. TPC-C New-Order Foreign key insert (e.g., NewOrder, Orders tables) Pre-materialized

    aggregates (e.g., W_YTD=SUM(orders for warehouse)) Sequence number ID assignment (i.e., D_NEXT_O_ID) RAMP transaction on counter CRDT RAMP transaction across tables rewrite FK references to point to temp unique ID! create local index from temp unique ID to sequence ID insert! O_ID warehouse district orders neworders insert! O_ID deferred atomic incrementAndGet() on commit! assign! new! O_ID tmp ID
  223. TPC-C New-Order Foreign key insert (e.g., NewOrder, Orders tables) Pre-materialized

    aggregates (e.g., W_YTD=SUM(orders for warehouse)) Sequence number ID assignment (i.e., D_NEXT_O_ID) RAMP transaction on counter CRDT RAMP transaction across tables rewrite FK references to point to temp unique ID! create local index from temp unique ID to sequence ID insert! O_ID warehouse district orders neworders insert! O_ID deferred atomic incrementAndGet() on commit! assign! new! O_ID tmp ID
  224. TPC-C New-Order Foreign key insert (e.g., NewOrder, Orders tables) Pre-materialized

    aggregates (e.g., W_YTD=SUM(orders for warehouse)) Sequence number ID assignment (i.e., D_NEXT_O_ID) RAMP transaction on counter CRDT RAMP transaction across tables rewrite FK references to point to temp unique ID! create local index from temp unique ID to sequence ID insert! O_ID warehouse district orders neworders insert! O_ID deferred atomic incrementAndGet() on commit! assign! new! O_ID tmp ID
  225. TPC-C New-Order Foreign key insert (e.g., NewOrder, Orders tables) Pre-materialized

    aggregates (e.g., W_YTD=SUM(orders for warehouse)) Sequence number ID assignment (i.e., D_NEXT_O_ID) RAMP transaction on counter CRDT RAMP transaction across tables rewrite FK references to point to temp unique ID! create local index from temp unique ID to sequence ID insert! O_ID warehouse district orders neworders insert! O_ID deferred atomic incrementAndGet() on commit! assign! new! O_ID tmp ID O NLY SYNCH CO O RDINATIO N! REQ UIRED
  226. TPCC Combine fkeys with sequence number insert on commit...

  227. TPCC Combine fkeys with sequence number insert on commit... 500K

    txns/s
  228. None
  229. None
  230. None
  231. None
  232. Linear Scaling via Coordination Avoidance Coordination need not be a

    bottleneck (if implemented in a coordination-free manner): UC Berkeley database prototype, 100 EC2 CC2.8xlarge instances (thank you AWS folks! currently poor single-node performance, but unimportant if you can scale out [for the time being]), linearizable masters, only blocking coordination: incrementAndGet for “district next order ID” key, CPU-bound on in-memory data; ~2500 lines Java; 120 clients/warehouse, 5 warehouses/machine, no THINK TIME (i.e., more contention than stock configuration)
  233. Linear Scaling via Coordination Avoidance Coordination need not be a

    bottleneck (if implemented in a coordination-free manner): UC Berkeley database prototype, 100 EC2 CC2.8xlarge instances (thank you AWS folks! currently poor single-node performance, but unimportant if you can scale out [for the time being]), linearizable masters, only blocking coordination: incrementAndGet for “district next order ID” key, CPU-bound on in-memory data; ~2500 lines Java; 120 clients/warehouse, 5 warehouses/machine, no THINK TIME (i.e., more contention than stock configuration)
  234. Traditional database systems suffer from! coordination bottlenecks By understanding application

    requirements,! we can avoid coordination unless necessary We can build systems that actually scale! while providing correct behavior
  235. Traditional database systems suffer from! coordination bottlenecks By understanding application

    requirements,! we can avoid coordination unless necessary We can build systems that actually scale! while providing correct behavior Thanks!! ! pbailis@cs.berkeley.edu! @pbailis! http://bailis.org/ http://amplab.cs.berkeley.edu/!
  236. Traditional database systems suffer from! coordination bottlenecks By understanding application

    requirements,! we can avoid coordination unless necessary We can build systems that actually scale! while providing correct behavior Thanks!! ! pbailis@cs.berkeley.edu! @pbailis! http://bailis.org/ http://amplab.cs.berkeley.edu/! based on “Coordination-Avoiding Database Systems,”! Bailis, Fekete, Franklin, Ghodsi, Hellerstein, Stoica! arXiv:1402.2237 http://arxiv.org/abs/1402.2237
  237. http://pbs.cs.berkeley.edu/#demo