| Distributed Database, Transactions and Two Phase Commit [ID 13229.1] | |||
|
|
|||
| Modified 04-JAN-2010 Type BULLETIN Status PUBLISHED | |||
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. Site Autonomy
Site autonomy means that each server participating in a distributed database is
administered independently from all other databases.
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. Each site has its own data dictionary.
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. Both horizontal and
vertical partitioning of data is possible.
6. Replication Independence
Stored copies of data can be located at multiple sites. Read-only snapshots
and updatable snapshots 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. The query is 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
Oracle runs on all major hardware platforms.
10. Operating System Independence
A specific operating system is not required. Oracle runs under a 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. Open
Gateway supports connections to non-Oracle databases.
13. Distributed Database Security
The database supports all of the security features that are available with a
non-distributed database environment for distributed database systems, including:
Password authentication for users and roles
Some types of external authentication for users and roles including:
Kerberos version 5 for connected user links
DCE for connected user links
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 contains ONLY statements on the local node.
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.
TERMS:
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) complete its part in the distributed transaction.
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 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.
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). The local coordinator is 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.
After all the nodes have prepared successfully, we enter the commit phase.
All transactions except those found in the commit point site are "in-doubt"
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 | HOME | SERVER /*READ-ONLY*/
|_____________| SCN=20000
| CPstr=250
|
_______________
| | GC/LC/CLIENT
update emp | LOCAL | SCN=100
|_____________| CPstr=125
/ \
/ \
____________ _____________
update emp | | | | insert emp_audit
SERVER/LC/CLIENT | HAWAII | | HQ | SERVER
SCN=250 |___________| |____________| SCN =1000
CPstr=95 / CPstr=10
/
/
------------
| | inserts dept_audit
| PARANOID | SERVER
|__________| SCN=50
CPstr=205
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. In 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.
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.
Forget Phase
1) After the participating nodes notify the commit point site
that they have committed, the commit point site can forget
about the transaction. The following steps occur:
2) After receiving notice from the global coordinator that all
nodes have committed, the commit point site erases status
information about this transaction.
3)The commit point site informs the global coordinator that
it has erased the status information.
4)The global coordinator erases its own information about 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 "in-doubt"
until the commit point phase completes successfully.
5) Define the following terms: CLIENTS, DATABASE SERVERS, GLOBAL COORDINATOR,
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) complete its part in the distributed transaction.
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 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) "init.ora" parameter. It is usually the most critical
site that can not afford collisions in case of an
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? Please be as detailed as 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). The local coordinator is 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.
After all the nodes have prepared successfully, we enter the commit phase.
All transactions except those found in the commit point site are "in-doubt"
until the commit point phase completes successfully.
10) What happens during the COMMIT phase? Please be as detailed as 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?
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.
References:
============
+ Note 100664.1 How to Troubleshoot Distributed Transactions
+ Note 1012842.102 ORA-2019 ORA-2058 ORA-2068 ORA-2050:FAILED DISTRIBUTED TRANSACTIONS
+ 10.2 Database Administrator's Guide Chapter 29 Distributed Database Concepts
+Oracle® Database Administrator's Guide 11g Release 1 (11.1) Part Number B28310-03
Check Chapter Distributed Database AdministrationChapter Two-Phase Commit Mechanism
+ Oracle® Database Reference 11g Release 1 (11.1) Part Number B28320-01
Check DBA_2PC_PENDING and dba_2pc_neighbors
| Related
Products
Keywords
|
