Docstoc

two phase commit

Document Sample
two phase commit Powered By Docstoc
					Bookmark Fixed font

Go to End

Doc ID: Note:13229.1 Subject: DISTRIBUTED DATABASE, TRANSACTIONS AND TWO PHASE COMMIT Type: BULLETIN Status: PUBLISHED Content Type: TEXT/PLAIN Creation Date: 09-MAY-1994 Last Revision Date: 21-MAY-2001 What is the Distributed Database Option? ---------------------------------------A distributed system is one in which both data and transaction processing are divided between one or more computers connected by a network, each computer playing a specific role in the system. This configuration has multiple databases, each of which is accessed directly by a single server and can be accessed indirectly by other instances through server/server cooperation. Distributed systems allow you to have data physically located at several sites, and each site can transparently access all of the data. Each node can be used for database processing, but the data is permanently partitioned among the nodes. Several smaller server machines can be cheaper and more flexible than one large, centrally located server. The key goals of a distributed database system are availability, accuracy, concurrency, and recoverability. The Client-Server Model and Distributed Systems ----------------------------------------------The client-server model is basic to distributed systems. It is a response to the limitations presented by the traditional mainframe client-host model, in which a single mainframe provides shared data access to many dumb terminals. The client-server model is also a response to the local area network (LAN) model, in which many isolated systems access a file server that provides no processing power. Client-server architecture provides integration of data and services and allows clients to be isolated from inherent complexities, such as communication protocols. The simplicity of the client-server architecture allows clients to make requests that are routed to the appropriate server. These requests are made in the form of transactions. Client transactions are often SQL or PL/SQL procedures and functions that access individual databases and services. Distributed Database Characteristics ------------------------------------

This article describes the twelve specifications for the ideal distributed database management system and how ORACLE conforms to these specifications. Oracle's distributed architecture, comprising SQL*Net, Open Gateway and the Oracle Server, provides an effective solution to the challenge of sharing data in a networked environment. The Oracle Server's distributed architecture provides effective data sharing in a networked environment using both client-server and distributed database architectures. In a client-server environment, communication takes place between two processes that reside on different machines. The client executes the application or application tool and sends requests to the server for data. The received data is processed at the client machine. This is known as distributed processing. The ideal distributed system should look like a non-distributed system. Twelve specifications for the ideal distributed database were developed by C.J. Date. The Oracle Server supports most of the ideal distributed features. 1. Local Autonomy The data is owned and managed locally. Local operations remain purely local. One site (node) in the distributed system does not depend on another site to function successfully. 2. No reliance on a central site All sites are treated as equals. 3. Continuous Operation Incorporating a new site has no effect on existing applications and does not disrupt service. 4. Location Independence Users can retrieve and update data independent of the site. 5. Fragmentation Independence Users can store parts of a table at different locations. and vertical partitioning of data is possible. Both horizontal Each site has its own data dictionary.

6. Replication Independence Stored copies of data can be located at multiple sites. Read-only snapshots (v7.0) and updatable snapshots (v7.1 and beyond) provide read-only and updatable copies of tables, respectively. Symmetric Replication using triggers make readable and writable replication possible. 7. Distributed Query Processing Users can query a database residing on another node. executed at the node where the data is located. 8. Distributed Transaction Management A transaction can update, insert or delete data from multiple databases. The two-phase commit mechanism in Oracle ensures the integrity of distributed transactions. Row level locking ensures a high level of data concurrency. 9. Hardware Independence Oracle7 runs on all major hardware platforms. 10. Operating System Independence A specific operating system is not required. variety of operating systems. 11. Network Independence The Oracle Server's SQL*Net supports most popular networking software. Network independence allows communication across homogeneous and heterogenous networks. Oracle's MultiProtocol Interchange enables applications to communicate with databases across multiple network protocols. 12. DBMS Independence DBMS Independence is the ability to integrate different databases. Gateway supports connections to non-Oracle databases. Open Oracle7 runs under a The query is

DISTRIBURTED TRANSACTIONS AND THE TWO PHASE COMMIT ================================================== Two phase commit only comes in play during a commit of a distributed transaction. The whole purpose is to maintain the integrity of the "global" database. In other words, two phase commit guarantees that

everything will either commit or rollback. TRANSACTION TYPES: LOCAL TRANSACTION node. REMOTE TRANSACTION DISTRIBUTED TRANSACTION comes into play. TERMS: CLIENTS database (C) SERVERS distributed (S) transaction server in a distributed transaction. are nodes that are directly referenced in a transaction, or is requested to participate in a because another node requires data from it. GLOBAL COORDINATOR (GC) LOCAL COORDINATOR (LC) COMMIT POINT SITE (CPS) critical site that can not afford collisions in case of an in-doubt transaction. SCN SCN number for each transaction. COMMIT_POINT_STRENGTH is the init.ora parameter that determines the (CPstr) COMMIT POINT SITE. is the system commit number. is essentially an internal database clock. This is a monotonically increasing an unique is the node in which the distributed transaction originates. is the node that references data on other nodes to complete its part in the distributed transaction. is the site with the highest commit point strength "init.ora" parameter. It is usually the most are nodes that references information from another contains one or more statements which ALL reference the same remote node. contains statements that modify data in two or more distinct nodes. * The only place where 2-PHASE COMMIT contains ONLY statements on the local

When you attempt to commit a distributed transaction, you will enter ORACLE'S TWO PHASE COMMIT MECHANISM. TWO-PHASE COMMIT: PREPARE PHASE: 1) Commit point site is determined. 2) Global coordinator asks all participating nodes (except commit point site) to promise to COMMIT or ROLLBACK the transaction regardless of failure). This information is propagated by the local coordinators. The servers have to be prepared before the local or global coordinators (except the commit point site). responsible for asking dependant nodes to prepare. POSSIBLE RESPONSES FROM NODES: * PREPARED * ABORT * READ-ONLY NODES Locks obtained during the distributed transaction will continue to be held. Redo is flushed to the local redo logs. 3) Each node will pass back the SCN for his node. 4) Global Coordinator determines the max SCN. The local coordinator is

After all the nodes have prepared successfully, we enter the commit phase. All transactions except those found in the commit point site are "indoubt" until the commit point phase completes successfully. COMMIT PHASE: 1) Global coordinator sends the max SCN to the commit point site and asks it to commit. 2) Commit Point Site will try to commit or everything is

rolled back. The locks are released in the commit point site first. 3) If committed/rolled back, the Commit Point Site will inform the Global Coordinator which will commit/rolled back at that time. 4) The information will propogate down to its clients/local coordinators and they will commit/roll back and propagate the information down to their servers until there are no more servers. (note: READ ONLY nodes do not participate in 2-phase commit.) EXAMPLE: UPDATE EMP SET SAL= SAL * 1.10 WHERE DEPTNO=10; UPDATE EMP@HAWAII SET SAL = SAL*1.20 WHERE DEPNO=20; BEFORE UPDATE TRIGGER Fires and updates dept@newyork. another trigger fires and inserts dept_audit@paranoid. after update trigger fires and inserts into the global audit table, emp_audit@headquarters INSERT INTO VACATION_TIME SELECT * FROM DAYS_OFF@HOME COMMIT; (TWO-PHASE COMMIT BEGINS HERE)

select days_off

update emp

_______________ | | | HOME | |_____________| | | _______________ | | | LOCAL | |_____________|

SERVER /*READ-ONLY*/ SCN=20000 CPstr=250 GC/LC/CLIENT SCN=100 CPstr=125

update emp emp_audit

/ / ____________ | |

\ \ _____________ | |

insert

SERVER/LC/CLIENT SCN=250 CPstr=95

| HAWAII | | HQ | |___________| |____________| / / / -----------| | inserts dept_audit | PARANOID | SERVER |__________| SCN=50 CPstr=205

SERVER SCN =1000 CPstr=10

PREPARE PHASE: 1) COMMIT POINT SITE IS PARANOID The global coordinator will already know what the commit point strength of each node prior to the commit. Read-only nodes are not included. 2) All nodes except for PARANOID is asked to prepare. 3) HAWAII, the local coordinator, is responsible to ask her dependent nodes to prepare before she prepares. this case, PARANOID is a commit point site; thus, it is ignored. 4) The highest SCN is sent to LOCAL node via the local coordinators. The highest SCN is 1000. 5) All nodes which PREPARED will flush entries of the transaction to the redo logs if not already done. If any of the nodes send an "ABORT" message back, then the transaction is rolled back at this time. Any failure after the PREPARE phase will result with "in-doubt" transactions. COMMIT PHASE: 1) PARANOID IS ASKED TO COMMIT OR ROLLBACK BY THE LOCAL (GC). 2) PARANOID commits at a SCN greater than 1000. a) Redo is flushed. b) Locks are released. c) outcome is relayed back to the LOCAL node (GC). Assume success: 3) AFter receiving the information, GC will commit at the same SCN and pass the information to its dependents. a) commit flushed to redo logs. b) data locks are released. c) GC will pass the information to HAWAII and HQ. (1) They, in turn, will commit and HAWAII will pass the information to PARANOID. In

If all is successful, every statement will commit with the same SCN and then RECO will delete the entries from "dba_2pc_pending" and "dba_2pc_neighbors" tables. Afterwards, the nodes will "forget" the transaction. TWO-PHASE COMMIT QUIZ/ANSWERS ============================= 1) What is the difference between remote transaction and distributed transaction? REMOTE TRANSACTION contains one or more statements which ALL reference the same remote node. DISTRIBUTED TRANSACTION contains statements that modify data in two or more distinct nodes. * The only place where 2-PHASE COMMIT comes into play. 2) When does two-phase commit come in play? Two phase commit only comes in play during a commit of a distributed transaction. The whole purpose is to maintain the integrity of the "global" database. In other words, two phase commit guarantees that everything will either commit or rollback. 3) Define distributed database. An environment that includes multiple servers where users manipulate data as if there is one "global database". With the widening use of heterogeneous hardware environments and multiple database servers, it has become essential to maintain the integrity of this "global database". Of course, the distributed concept is usually transparent to the end user and the application programmers. Another defintion could be: A distributed database is a database that is not stored in its entirety at a single physical location. Rather, a distributed database is a database that is stored across a network of locations that are connected via communication links. A distributed database consists of a collection of sites or nodes connected together into a communication network. 4) When is a transaction considered "in-doubt"? After all the nodes have prepared successfully, we enter the commit phase. All transactions except those found in the commit point site are "indoubt" until the commit point phase completes successfully.

5) Define the following terms: COORDINATOR,

CLIENTS, DATABASE SERVERS, GLOBAL LOCAL COORDINATOR, COMMIT POINT SITE

CLIENTS are nodes that references information from another database (C) server in a distributed transaction. SERVERS are nodes that are directly referenced in a distributed (S) transaction, or is requested to participate in a transaction because another node requires data from it. GLOBAL COORDINATOR is the node in which the distributed transaction (GC) originates. LOCAL COORDINATOR is the node that references data on other nodes to (LC) transaction. strength (CPS) critical site that can not afford collisions in case of an in-doubt transaction. SCN is the system commit number. SCN is essentially an internal database clock. This is a monotonically increasing an unique number for each transaction. COMMIT_POINT_STRENGTH is the init.ora parameter that determines the (CPstr) COMMIT POINT SITE. 6) Can the global coordinator be the commit point site as well? Yes, the only prerequisites are that the node is part of the distributed transaction (not read-only) and it has the highest commit point strength parameter. 7) What distinguishes the commit point site? COMMIT POINT SITE is the site with the highest commit point strength (CPS) critical site that can not afford collisions in case of an "init.ora" parameter. It is usually the most init.ora parameter. It is usually the most complete its part in the distributed

COMMIT POINT SITE is the site with the highest commit point

in-doubt transaction. 8) What does RECO do? RECO PROCESS * gets information from the "DBA_2PC_PENDING" and "DBA_2PC_NEIGHBORS" views. * executes as the global or local coordinator * tries to communicate wit other nodes in the in-doubt transaction If it cannot connect, it will continue to try at exponential intervals. * resolves in-doubt transactions if the connection is up. * uses the same DBLINK as the transaction * removes resolved transactions from the pending transaction table. 9) What happens during the PREPARE phase? possible? PREPARE PHASE: 1) Commit point site is determined. 2) Global coordinator asks all participating nodes (except commit point site) to promise to COMMIT or ROLLBACK the transaction regardless of failure). This information is propagated by the local coordinators. The servers have to be prepared before the local or global coordinators (except the commit point site). responsible for asking dependant nodes to prepare. POSSIBLE RESPONSES FROM NODES: * PREPARED * ABORT * READ-ONLY NODES Locks obtained during the distributed transaction will continue to be held. Redo is flushed to the local redo logs. 3) Each node will pass back the SCN for his node. 4) Global Coordinator determines the max SCN. The local coordinator is Please be as detailed as

After all the nodes have prepared successfully, we enter the commit phase. All transactions except those found in the commit point site are "indoubt" until the commit point phase completes successfully. 10) What happens during the COMMIT phase? possible? COMMIT PHASE: 1) Global coordinator sends the max SCN to the commit point site and asks it to commit. 2) Commit Point Site will try to commit or everything is rolled back. The locks are released in the commit point site first. 3) If committed/rolled back, the Commit Point Site will inform the Global Coordinator which will commit/rolled back at that time. 4) The information will propogate down to its clients/local coordinators and they will commit/roll back and propagate the information down to their servers until there are no more servers. (NOTE: READ ONLY nodes do not participate in 2-phase commit.) 11) What is SCN and how is it significant in two-phase commit? SCN is the system commit number. SCN is essentially an internal database clock. This is a monotonically increasing an unique number for each transaction. The entire distributed transaction will be committed at the same SCN. This will allow some type of consistency during distributed recovery. 12) Which two views are helpful during MANUAL distributed recovery? They are "dba_2pc_pending" and "dba_2pc_neighbors". 13) When does RECO remove the entries from those views? Please be as detailed as

If all is successful, every statement will commit with the same SCN and then RECO will delete the entries from "dba_2pc_pending" and "dba_2pc_neighbors" tables. Afterwards, the nodes will "forget" the transaction. 14) List some cases where you may do MANUAL RECOVERY? A dba may do some manual recovery if there was a network or system or database crash and the "in-doubt" transactions are holding locks that are crucial to other applications. 15) How should you pick your COMMIT POINT SITE? COMMIT POINT SITE is the site with the highest commit point strength (CPS) "init.ora" parameter. It is usually the most critical site that can not afford collisions in case of a doubted transaction. You need to coordinate with the other dba adminstrators to make that decision. .

------------------------------------------------------------------------------Copyright (c) 1995,2000 Oracle Corporation. All Rights Reserved. Legal Notices and Terms of Use.


				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:81
posted:8/29/2009
language:English
pages:12