Chapter 22: Distributed Databases

Chapter 22: Distributed Databases

Database System Concepts • Chapter 1: Introduction • Part 1: Relational databases • Chapter 2: Relational Model • Chapter 3: SQL • Chapter 4: Advanced SQL • Chapter 5: Other Relational Languages • Part 2: Database Design • Chapter 6: Database Design and the E-R Model • Chapter 7: Relational Database Design • Chapter 8: Application Design and Development • Part 3: Object-based databases and XML • Chapter 9: Object-Based Databases • Chapter 10: XML • Part 4: Data storage and querying • Chapter 11: Storage and File Structure • Chapter 12: Indexing and Hashing • Chapter 13: Query Processing • Chapter 14: Query Optimization • Part 5: Transaction management • Chapter 15: Transactions • Chapter 16: Concurrency control • Chapter 17: Recovery System • Part 6: Data Mining and Information Retrieval • Chapter 18: Data Analysis and Mining • Chapter 19: Information Retreival • Part 7: Database system architecture • Chapter 20: Database-System Architecture • Chapter 21: Parallel Databases • Chapter 22: Distributed Databases • Part 8: Other topics • Chapter 23: Advanced Application Development • Chapter 24: Advanced Data Types and New Applications • Chapter 25: Advanced Transaction Processing • Part 9: Case studies • Chapter 26: PostgreSQL • Chapter 27: Oracle • Chapter 28: IBM DB2 • Chapter 29: Microsoft SQL Server • Online Appendices • Appendix A: Network Model • Appendix B: Hierarchical Model • Appendix C: Advanced RelationalDatabase Model

Part 7: Database system architecture(Chapters 20 through 22). • Chapter 20: Database-System Architecture • covers computer-system architecture, and describes the influence of the underlying computer system on the database system. We discuss centralized systems, client-server systems, parallel and distributed architectures, and network types in this chapter. • Chapter 21: Parallel Databases • explores a variety of parallelization techniques, including I/O parallelism, interquery and intraquery parallelism, and interoperation and intraoperation parallelism. The chapter also describes parallel-system design. • Chapter 22: Distributed Databases • covers distributed database systems, revisiting the issues of database design, transaction management, and query evaluation and optimization, in the context of distributed databases. The chapter also covers issues of system availability during failures and describes the LDAP directory system.

22.1 Heterogeneous and Homogeneous Databases 22.2 Distributed Data Storage 22.3 Distributed Transactions 22.4 Commit Protocols 22.5 Concurrency Control in Distributed Databases 22.6 Availability 22.7 Distributed Query Processing 22.8 Heterogeneous Distributed Databases 22.9 Directory Systems 22.10 Summary Chapter 22: Distributed Databases

A distributed database system consists of loosely coupled sites that share no physical component Database systems that run on each site are independent of each other Transactions may access data at one or more sites Distributed Database System

In a homogeneous distributed database All sites have identical softwares Are aware of each other and agree to cooperate in processing user requests. Each site surrenders part of its autonomy in terms of right to change schemas or software Appears to user as a single system In a heterogeneous distributed database Different sites may use different schemas and softwares Difference in schema is a major problem for query processing Difference in software is a major problem for transaction processing Sites may not be aware of each other and may provide only limited facilities for cooperation in transaction processing Distributed Database System (cont.)

Assume relational data model Replication System maintains multiple copies of data, stored in different sites, for faster retrieval and fault tolerance. Fragmentation Relation is partitioned into several fragments stored in distinct sites Replication and fragmentation can be combined Relation is partitioned into several fragments: system maintains several identical replicas of each such fragment. Distributed Data Storage

A relation or fragment of a relation is replicatedif it is stored redundantly in two or more sites. Full replicationof a relation is the case where the relation is stored at all sites. Fully redundant DB means every site contains a copy of the entire DB. Advantages of Replication Availability: failure of site containing relation r does not result in unavailability of r is replicas exist. Parallelism: queries on r may be processed by several nodes in parallel. Reduced data transfer: relation r is available locally at each site containing a replica of r. Disadvantages of Replication Increased cost of updates: each replica of relation r must be updated. Increased complexity of concurrency control: concurrent updates to distinct replicas may lead to inconsistent data unless special concurrency control mechanisms are implemented. One solution: choose one copy as primary copyand apply concurrency control operations on primary copy Data Replication

Division of relation r into fragments r1, r2, …, rnwhich contain sufficient information to reconstruct relation r. Horizontal fragmentation: each tuple of r is assigned to one or more fragments Vertical fragmentation: the schema for relation r is split into several smaller schemas All schemas must contain a common candidate key (or superkey) to ensure lossless join property. A special attribute, the tuple-id attribute may be added to each schema to serve as a candidate key. Example : relation account with following schema Account = (branch_name, account_number, balance ) Employee_Info = ( employee_id, name, destination, salary) Data Fragmentation

Horizontal Fragmentation of account Relation Account = (branch_name, account_number, balance ) account1 =branch_name=“Hillside” (account ) account_number branch_name balance Hillside Hillside Hillside A-305 A-226 A-155 500 336 62 account2 =branch_name=“Valleyview” (account ) account_number branch_name balance Valleyview Valleyview Valleyview Valleyview A-177 A-402 A-408 A-639 205 10000 1123 750

Vertical Fragmentation of employee_info Relation Employee_Info = ( branch_name, customer_name, account_number, balance) deposit1 =branch_name, customer_name, tuple_id (employee_info ) tuple_id branch_name customer_name Lowman Camp Camp Kahn Kahn Kahn Green Hillside Hillside Valleyview Valleyview Hillside Valleyview Valleyview 1 2 3 4 5 6 7 deposit2 =account_number, balance, tuple_id (employee_info ) account_number tuple_id balance A-305 A-226 A-177 A-402 A-155 A-408 A-639 500 336 205 10000 62 1123 750 1 2 3 4 5 6 7

Horizontal fragmentation: allows parallel processing on fragments of a relation allows a relation to be split so that tuples are located where they are most frequently accessed Vertical fragmentation: allows tuples to be split so that each part of the tuple is stored where it is most frequently accessed tuple-id attribute allows efficient joining of vertical fragments allows parallel processing on a relation Vertical and horizontal fragmentation can be mixed. Fragments may be successively fragmented to an arbitrary depth. Advantages of Fragmentation

Data transparency: Degree to which system user may remain unaware of the details of how and where the data items are stored in a distributed system Consider transparency issues in relation to: Fragmentation transparency Replication transparency Location transparency Naming of Data Items - Criteria 1. Every data item must have a system-wide unique name. 2. It should be possible to find the location of data items efficiently. 3. It should be possible to change the location of data items transparently. 4. Each site should be able to create new data items autonomously. Data Transparency

Central Name Server Scheme Structure: name server assigns all names each site maintains a record of local data items sites ask name server to locate non-local data items Advantages: satisfies naming criteria 1-3 Disadvantages: does not satisfy naming criterion 4 name server is a potential performance bottleneck name server is a single point of failure Naming Schemes

Site-Id Prefix Scheme each site prefixes its own site identifier to any name that it generates i.e., site 17.account. Fulfills having a unique identifier, and avoids problems associated with central control. However, fails to achieve network transparency. Solution: Create a set of aliasesfor data items; Store the mapping of aliases to the real names at each site. The user can be unaware of the physical location of a data item, and is unaffected if the data item is moved from one site to another. Naming Schemes (cont.)

Transaction may access data at several sites. Each site has a local transaction manager responsible for: Maintaining a log for recovery purposes Participating in coordinating the concurrent execution of the transactions executing at that site. Each site has a transaction coordinator, which is responsible for: Starting the execution of transactions that originate at the site. Distributing subtransactions at appropriate sites for execution. Coordinating the termination of each transaction that originates at the site, which may result in the transaction being committed at all sites or aborted at all sites. Distributed Transactions

Failures unique to distributed systems: Failure of a site Loss of massages Handled by network transmission control protocols such as TCP-IP Failure of a communication link Handled by network protocols, by routing messages via alternative links Network partition A network is said to be partitioned when it has been split into two or more subsystems that lack any connection between them Note: a subsystem may consist of a single node Network partitioning and site failures are generally indistinguishable. System Failure Modes

Commit protocols are used to ensure atomicity across sites a transaction which executes at multiple sites must either be committed at all the sites, or aborted at all the sites. not acceptable to have a transaction committed at one site and aborted at another The two-phase commit (2PC) protocol is widely used The three-phase commit (3PC) protocol is more complicated and more expensive, but avoids some drawbacks of two-phase commit protocol. This protocol is not used in practice. Commit Protocols

Assumesfail-stopmodel failed sites simply stop working, and do not cause any other harm, such as sending incorrect messages to other sites. Execution of the protocol is initiated by the coordinator after the last step of the transaction has been reached. The protocol involves all the local sites at which the transaction executed Let T be a transaction initiated at site Si, and let the transaction coordinator at Sibe Ci Two Phase Commit Protocol (2PC)

Coordinator asks all participants to prepare to commit transaction Ti. Ci adds the records <prepare T> to the log and forces log to stable storage sends prepare T messages to all sites at which T executed Upon receiving message, transaction manager at site determines if it can commit the transaction if not, add a record <no T> to the log and send abort T message to Ci if the transaction can be committed, then: add the record <ready T> to the log force all records for T to stable storage send readyT message to Ci Phase 1 of 2PC: Obtaining a Decision

T can be committed if Cireceived a ready T message from all the participating sites: otherwise T must be aborted. Coordinator adds a decision record, <commit T> or <abort T>, to the log and forces record onto stable storage. Once the record stable storage it is irrevocable (even if failures occur) Coordinator sends a message to each participant informing it of the decision (commit or abort) Participants take appropriate action locally. Phase 2 of 2PC: Recording the Decision

2PC – Finite State Machine Prepare-commit Local-abort INIT INIT Execute-done Prepare-commit Prepare-commit Ready-commit READY WAIT Global-abort ACK Local-abort Global-abort Global-commit ACK Ready-commit Global-commit ABORT COMMIT ABORT COMMIT participant coordinator

2PC – Commit Assume the coordinator received a global transaction Tg, which is composed of local transaction T1 and T2. Site A Site B Site C Participant 1CoordinatorParticipant 2 Execute Execute Execution Execution Done Done Prepare to Commit Prepare to Commit Ready to Commit Ready to Commit Commit Commit Execute Commit Execute Commit Ack Ack INIT T1 T2 INIT INIT READY WAIT READY COMMIT COMMIT COMMIT Tg Commit

2PC – Abort Assume the coordinator received a global transaction Tg, which is composed of local transaction T1 and T2. Site A Site B Site C Participant 1CoordinatorParticipant 2 Execute Execute Execution Execution Done Done Prepare to Commit Prepare to Commit Abort Ready to Commit Abort Abort Undo Execution Undo Execution Ack Ack INIT T1 T2 INIT INIT ABORT WAIT READY ABORT ABORT Tg Abort

When site Sirecovers, it examines its log to determine the fate of transactions active at the time of the failure. Log contain <commit T> record: site executes redo (T) Log contains <abort T> record: site executes undo (T) Log contains <ready T> record: site must consult Ci to determine the fate of T. If T committed, redo (T) If T aborted, undo (T) The log contains no control records concerning T replies that Sk failed before responding to the prepare T message from Ci since the failure of Sk precludes the sending of such a response C1 must abort T Sk must execute undo (T) Handling of Failures in 2PC - Site Failure

Handling Site Failure in 2PC (1) CASE 1: Participant 2 dies during commit execution Participant 2 knew the commit before it dies.  During recovery, Participant 2 must redo T2. Site A Site B Site C Participant 1CoordinatorParticipant 2 Execute Execute Execution Execution Done Done Prepare to Commit Prepare to Commit Ready to Commit Ready to Commit Commit Commit Execute Commit Execute Commit Ack INIT T1 T2 INIT INIT READY WAIT READY COMMIT COMMIT DIE Tg Commit

Handling Site Failure in 2PC (2) CASE 2: Participant 2 dies during undo execution Participant 2 knew the abort before it dies.  During recovery, Participant 2 must undo T2. Site A Site B Site C • Participant 1CoordinatorParticipant 2 • Execute Execute • Execution Execution • Done • Done • Prepare to Commit Prepare to Commit • Abort Ready to Commit • Abort Abort • Undo Execution Undo Execution • Ack INIT T1 T2 INIT INIT ABORT WAIT READY ABORT ABORT DIE Tg Abort

Handling Site Failure in 2PC (3) CASE 3: Participant 2 dies during ready state, before receiving commit/abort message Participant 2 don’t know the fate of the transaction.  During recovery, Participant 2 must consult the coordinator. In this case, redo T2. (redo T2 if Tg is commited, undo T2 if Tg is aborted.) Site A Site B Site C Participant 1CoordinatorParticipant 2 Execute Execute Execution Execution Done Done Prepare to Commit Prepare to Commit Ready to Commit Ready to Commit Commit Commit Execute Commit Ack INIT T1 T2 INIT INIT READY WAIT READY DIE COMMIT COMMIT Tg Commit

Handling Site Failure in 2PC (4) CASE 4: Participant 2 dies before it responds to the prepare to commit message Tg cannot be commited because Participant 2 didn’t send a ready to commit message.  During recovery, Participant 2 must undo T2 without consulting with the coordinator Site A Site B Site C • Participant 1CoordinatorParticipant 2 • Execute Execute • Execution Execution • Done • Done • Prepare to Commit Prepare to Commit • Ready to Commit • Abort • Undo Execution • Ack INIT T1 T2 INIT INIT READY WAIT DIE ABORT ABORT Tg Abort

If coordinator fails while the commit protocol for T is executing then participating sites must decide on T’s fate: If an active site contains a <commit T> record in its log, then T must be committed. If an active site contains an <abort T> record in its log, then T must be aborted. If some active participating site does not contain a <ready T> record in its log, then the failed coordinator Ci cannot have decided to commit T. Can therefore abort T. If none of the above cases holds, then all active sites must have a <ready T> record in their logs, but no additional control records (such as <abort T> of <commit T>). In this case active sites must wait for Cito recover, to find decision. Blocking problem : active sites may have to wait for failed coordinator to recover. Handling of Failures in 2PC - Coordinator Failure

Handling Coordinator Failure in 2PC (1) CASE 1: Coordinator dies while it sends commit messages (i.e., an active site is in COMMIT state) When coordinator fails, Participant 2 asks Participant 1.  No recovery process is required: Participant 2 just commit T2. Site A Site B Site C Participant 1CoordinatorParticipant 2 Execute Execute Execution Execution Done Done Prepare to Commit Prepare to Commit Ready to Commit Ready to Commit Commit Execute Commit Ack INIT T1 T2 INIT INIT WAIT READY READY DIE COMMIT Tg Commit

Handling Coordinator Failure in 2PC (2) CASE 2: Coordinator dies while it sends abort messages (i.e., an active site is in ABORT state) When coordinator fails, Participant 2 asks Participant 1.  No recovery process is required: Participant 2 just abort T2. Site A Site B Site C • Participant 1CoordinatorParticipant 2 • Execute Execute • Execution Execution • Done • Done • Prepare to Commit Prepare to Commit • Abort Ready to Commit • Abort • Undo Execution • Ack INIT T1 T2 INIT INIT WAIT ABORT READY DIE ABORT Tg Abort

Handling Coordinator Failure in 2PC (3) CASE 3: Coordinator dies while it sends prepare to commit messages (i.e., some active sites are in INIT state) Tg cannot be commited because Participant 2 didn’t receive a prepare to commit message.  All participants must abort the transactions. Site A Site B Site C Participant 1CoordinatorParticipant 2 Execute Execute Execution Execution Done Done Prepare to Commit Ready to Commit INIT T1 T2 INIT INIT DIE READY Tg Abort

Handling Coordinator Failure in 2PC (4-1) CASE 4: Coordinator dies and all active sites are in READY state (some dead parts may be in INIT, READY, ABORT, or COMMIT state, or coordinator may not have received all votes before crash.) When coordinator fails, All participants don’t know the fate of the transaction.  Blocking – All participants cannot proceed until either the coordinator are available. Site A Site B Site C • Participant 1CoordinatorParticipant 2 • Execute Execute • Execution Execution • Done • Done • Prepare to Commit Prepare to Commit • Ready to Commit INIT T1 T2 INIT INIT DIE READY WAIT DIE Blocked Tg ?

Handling Coordinator Failure in 2PC (4-2) CASE 4: Coordinator dies and all active sites are in READY state (some dead parts may be in INIT, READY, ABORT, or COMMIT state, or coordinator may not have received all votes before crash.) When coordinator fails, All participants don’t know the fate of the transaction.  Blocking – All participants cannot proceed until either the coordinator are available. Site A Site B Site C • Participant 1CoordinatorParticipant 2 • Execute Execute • Execution Execution • Done • Done • Prepare to Commit Prepare to Commit • Ready to Commit INIT T1 T2 INIT INIT READY WAIT READY DIE DIE Blocked Tg ?

Handling Coordinator Failure in 2PC (4-3) CASE 4: Coordinator dies and all active sites are in READY state (some dead parts may be in INIT, READY, ABORT, or COMMIT state, or coordinator may not have received all votes before crash.) When coordinator fails, All participants don’t know the fate of the transaction.  Blocking – All participants cannot proceed until either the coordinator are available. Site A Site B Site C • Participant 1CoordinatorParticipant 2 • Execute Execute • Execution Execution • Done • Done • Prepare to Commit Prepare to Commit • Abort Ready to Commit • Abort • Undo Execution • Ack INIT T1 T2 INIT INIT ABORT WAIT READY DIE DIE Blocked ABORT Tg ?

Handling Coordinator Failure in 2PC (4-4) CASE 4: Coordinator dies and all active sites are in READY state (some dead parts may be in INIT, READY, ABORT, or COMMIT state, or coordinator may not have received all votes before crash.) When coordinator fails, All participants don’t know the fate of the transaction.  Blocking – All participants cannot proceed until either the coordinator are available. Site A Site B Site C • Participant 1CoordinatorParticipant 2 • Execute Execute • Execution Execution • Done • Done • Prepare to Commit Prepare to Commit • Ready to Commit Ready to Commit • Commit • Execute Commit • Ack INIT T1 T2 INIT INIT READY WAIT READY DIE DIE Blocked COMMIT Tg ?

If the coordinator and all its participants remain in one partition, the failure has no effect on the commit protocol. If the coordinator and its participants belong to several partitions: Sites that are not in the partition containing the coordinator think the coordinator has failed, and execute the protocol to deal with failure of the coordinator. No harm results, but sites may still have to wait for decision from coordinator. The coordinator and the sites are in the same partition as the coordinator think that the sites in the other partition have failed, and follow the usual commit protocol. Again, no harm results Site 4 Site 5 Handling of Failures in 2PC - Network Partition Coordinator Site 1 Site 3 C

In-doubttransactionshave a <ready T>, but neither a <commitT>, nor an <abortT> log record. The recovering site must determine the commit-abort status of such transactions by contacting other sites this can slow and potentially block recovery. Recovery algorithms can note lock information in the log. Instead of <ready T>, write out <readyT, L> L = list of locks held by T when the log is written (read locks can be omitted). For every in-doubt transaction T, all the locks noted in the <readyT, L> log record are reacquired. After lock reacquisition, transaction processing can resume the commit or rollback of in-doubt transactions is performed concurrently with the execution of new transactions. Recovery and Concurrency Control in 2PC Think more!!!

2PC vs. 3PC • 2PC may cause blocking when coordinator fails. • 3PC chooses “abort” instead of the blocking process • After aborting, the participants can resume the normal activity • Basic idea of 3PC: • By having PRECOMMIT state, there is no single state from which it is possible to make a transition directly to either a COMMIT or an ABORT state. • By having PRECOMMIT state, there is no state (i.e., blocking state) in which it is not possible to make a final decision, and from which a transition to a COMMIT state can be made. When coordinator dies and all active sites are in READY state Coordinator Site 3 2PC  Participants go to “blocking” 3PC  Participants go to “abort” state by having “precommit” state Site 1 INIT? READY? ABORT? COMMIT? Site 2 READY READY

Three Phase Commit Protocol (3PC) • Three phases • Phase 1 : • Coordinator check if T can commit, participants send their choice to coordinator • Phase 2 : Coordinator makes decision • If commit, send precommit message to participants • If abort, send abort message to participants • Phase 3 : • If commit, final commit decision is broadcast and everyone commits

3PC – Finite State Machine Prepare-commit Local-abort INIT INIT Execute-done Prepare-commit Prepare-commit Ready-precommit READY WAIT Global-abort ACK Local-abort Global-abort Global-precommit Ready-commit Ready-precommit Global-precommit ABORT PRECOMMIT ABORT PRECOMMIT Global-commit ACK Ready-commit Global-commit COMMIT COMMIT participant coordinator

3PC – Commit Assume the coordinator received a global transaction Tg, which is composed of local transaction T1 and T2. Site A Site B Site C Participant 1CoordinatorParticipant 2 Execute Execute Execution Execution Done Done Prepare to Commit Prepare to Commit Ready to Precommit Ready to Precommit Precommit Precommit Ready to Commit Ready to Commit Commit Commit Execute Commit Execute Commit Ack Ack INIT INIT INIT WAIT READY READY PRECOMMIT PRECOMMIT PRECOMMIT COMMIT COMMIT COMMIT Tg Commit

3PC – Abort Assume the coordinator received a global transaction Tg, which is composed of local transaction T1 and T2. Site A Site B Site C Participant 1CoordinatorParticipant 2 Execute Execute Execution Execution Done Done Prepare to Commit Prepare to Commit Abort Ready to Precommit Abort Abort Undo Execution Undo Execution Ack Ack INIT T1 T2 INIT INIT ABORT WAIT READY ABORT ABORT Tg Abort

When site Sirecovers, it examines its log to determine the fate of transactions active at the time of the failure. Log contain <commit T> record: site executes redo (T) Log contains <abort T> record: site executes undo (T) Log contains <ready T> or <precommit T> record: site must consult Ci to determine the fate of T. If T committed, redo (T) If T aborted, undo (T) The log contains no control records concerning T replies that Sk failed before responding to the prepare T message from Ci since the failure of Sk precludes the sending of such a response C1 must abort T Sk must execute undo (T) Handling of Failures in 3PC - Site Failure

Handling Site Failurein 3PC (1) CASE 1: Participant 2 dies during commit execution Participant 2 knew the commit before it dies  During recovery, Participant 2 must redo T2. Site C Site B Site A Participant 1CoordinatorParticipant 2 Execute Execute Execution Execution Done Done Prepare to Commit Prepare to Commit Ready to Precommit Ready to Precommit Precommit Precommit Ready to Commit Ready to Commit Commit Commit Execute Commit Execute Commit Ack INIT INIT INIT WAIT READY READY PRECOMMIT PRECOMMIT PRECOMMIT COMMIT DIE COMMIT Tg Commit

Handling Site Failure in 3PC (2) CASE 2: Participant 2 dies during undo execution Participant 2 knew the abort before it dies.  During recovery, Participant 2 must undo T2. Site A Site B Site C • Participant 1CoordinatorParticipant 2 • Execute Execute • Execution Execution • Done • Done • Prepare to Commit Prepare to Commit • Abort Ready to Precommit • Abort Abort • Undo Execution Undo Execution • Ack INIT T1 T2 INIT INIT ABORT WAIT READY ABORT ABORT DIE Tg Abort

Chapter 22: Distributed Databases