Database Replication in WAN

1. Database Replication in WAN Yi Lin McGill University Distributed Information Systems

2. What? Why? How? Without Replication With Replication Toronto Montreal Ottawa Toronto Montreal Ottawa … … How? Montreal Toronto Montreal Ottawa Benefits: Performance, Scalability, Failover

3. How? Replication Protocols based on Group Communication • Group Communication: Each group member can multicast messages to all members which will receive the messages in specified order such as • FIFO: messages of one sender are received in the order as they were sent. • TOTAL: if two members receive messages m1 and m2, both receive them in the same order. m3 m3 m1 m1 m1 m1 m1 m1 m2 m2 m2 m2 m2 m2 FIFO FIFO, not TOTAL FIFO, TOTAL

4. Existing Replication Protocol for LAN • Basic idea: • 1. Upon receiving a read request from the client, submitit to execute immediately • 2. Upon receiving a update request from the client: multicast the request to all sites in TOTAL order • 3. Upon receiving an update request in TOTAL order: put the necessary lock requests in a locktable. • 4. submit the transactions for execution once locks are granted

5. Example: 2 txns accessing X in 2 sites multicast in TOTAL order T1 T2 Locktable X: T1 X: T1 X: T1, T2 X: T1, T2 T1 Resp X:T2 X:T2 T2 Resp Site A Site B

6. Replication Protocol Variations T1 T1 T1 T2 T2 T2 T1 exe T1 exe T1 exe T1 exe T1 Resp T2 exe T2 exe T2 exe Apply ws1 Apply ws1 T2 Resp T2 exe Apply ws2 Apply ws2 T2 Resp T2 Resp

7. Lesson 1 learned from experiments in Planetlab(WAN) Message delivery time accounts for 70-80% of txn response time whereas execution time only 5-10% Solution: a. Symmetric protocol preferred in WAN b. Propose a fast TOTAL order algorithm (Local Token) Waiting in locktable time Txn Exe time Resp time Msg delivery time

8. LAN prefers primary copy WAN prefers Symmetric

9. Local Token TOTAL order • In each site there is one FIFO queue for one sender. • All queues compose a ring (same ring configuration in each site). • At any time one queue has a token. • Upon receiving msg, append it in the corresponding queue. • Upon holding token, deliver first msg (blocked if no msg) and pass the token to the next queue. Comparing Local Token with other TOTAL order algorithm (4 planetlab sites )

10. Lesson 2 learned from experiments • Locking granularity in locktable has significant influence on performance. • Table Level locking • In middleware level, since tuples to be accessed by txns are possibly unknown (if primary key is not provided), the reasonable locking mechanism is locking the tables to be accessed. • Solution: • Optimistic delivery • Pseudo-tuple level locking

11. Optimistic Delivery • In WAN a msg may be physically received much earlier than when its TOTAL order is determined. • We can optimistically start execution (appending txn to locktable) upon receiving the msg instead of upon msg’s TOTAL order is determined. • However txn will not be committed until its TOTAL order is determined. If there is no difference between optimistic delivery and TOTAL order delivery, or mismatch happens with non-conflicting txns, execution overlaps with determining the TOTAL order. Otherwise, txn is aborted and rollbacked. Time Total Order Txn Execution Response Time • Regular delivery Optimistic-delivery TOTAL-delivery Optimistic Txn Execution Total Order Response Time (b) With Optimistic delivery

12. Pseudo-Tuple Level locking DBMS serializable isolation level provides: T1 Executed x If two concurrent txns access the same data, the later txn will be blocked by the first txn and aborted upon the first txn’s commit. blocked T2 x T1 commits Table-level locking may lead to unnecessary blocking. We can take advantage of serializable isolation level to detect if two txns conflict at tuple level by optimistically applying both txns and checking if second txn will be blocked by the previous txn. T2 abort time This approach in conjunction with optimistic delivery may be able to detect tuple level conflict before txn TOTAL order is determined.