Oracle9i Database Administrator's Guide Release 2 (9.2) Part Number A96521-01 |
|
This chapter describes what distributed transactions are and how Oracle maintains their integrity. The following topics are contained in this chapter:
A distributed transaction includes one or more statements that, individually or as a group, update data on two or more distinct nodes of a distributed database. For example, assume the database configuration depicted in Figure 31-1:
The following distributed transaction executed by scott
updates the local sales
database, the remote hq
database, and the remote maint
database:
UPDATE scott.dept@hq.us.acme.com SET loc = 'REDWOOD SHORES' WHERE deptno = 10; UPDATE scott.emp SET deptno = 11 WHERE deptno = 10; UPDATE scott.bldg@maint.us.acme.com SET room = 1225 WHERE room = 1163; COMMIT;
Note: If all statements of a transaction reference only a single remote node, then the transaction is remote, not distributed. |
There are two types of permissible operations in distributed transactions:
The following list describes DML and DDL operations supported in a distributed transaction:
CREATE TABLE AS SELECT
DELETE
INSERT
(default and direct load)LOCK TABLE
SELECT
SELECT FOR UPDATE
You can execute DML and DDL statements in parallel, and INSERT
direct load statements serially, but note the following restrictions:
SELECT
statements.INSERT
, UPDATE
, or DELETE
statement is remote, then execution is serial rather than parallel.INSERT
.SELECT
in the transaction, no DML is parallelized.The following list describes supported transaction control statements:
COMMIT
ROLLBACK
SAVEPOINT
See Also:
Oracle9i SQL Reference for more information about these SQL statements |
As the statements in a distributed transaction are issued, Oracle defines a session tree of all nodes participating in the transaction. A session tree is a hierarchical model that describes the relationships among sessions and their roles. Figure 31-2 illustrates a session tree:
All nodes participating in the session tree of a distributed transaction assume one or more of the following roles:
Role | Description |
---|---|
Client |
A node that references information in a database belonging to a different node. |
Database server |
A node that receives a request for information from another node. |
Global coordinator |
The node that originates the distributed transaction. |
Local coordinator |
A node that is forced to reference data on other nodes to complete its part of the transaction. |
Commit point site |
The node that commits or rolls back the transaction as instructed by the global coordinator. |
The role a node plays in a distributed transaction is determined by:
A node acts as a client when it references information from another node's database. The referenced node is a database server. In Figure 31-2, the node sales
is a client of the nodes that host the warehouse
and finance
databases.
A database server is a node that hosts a database from which a client requests data.
In Figure 31-2, an application at the sales
node initiates a distributed transaction that accesses data from the warehouse
and finance
nodes. Therefore, sales.acme.com
has the role of client node, and warehouse
and finance
are both database servers. In this example, sales
is a database server and a client because the application also modifies data in the sales
database.
A node that must reference data on other nodes to complete its part in the distributed transaction is called a local coordinator. In Figure 31-2, sales
is a local coordinator because it coordinates the nodes it directly references: warehouse
and finance.
The node sales
also happens to be the global coordinator because it coordinates all the nodes involved in the transaction.
A local coordinator is responsible for coordinating the transaction among the nodes it communicates directly with by:
The node where the distributed transaction originates is called the global coordinator. The database application issuing the distributed transaction is directly connected to the node acting as the global coordinator. For example, in Figure 31-2, the transaction issued at the node sales
references information from the database servers warehouse
and finance
. Therefore, sales.acme.com
is the global coordinator of this distributed transaction.
The global coordinator becomes the parent or root of the session tree. The global coordinator performs the following operations during a distributed transaction:
The job of the commit point site is to initiate a commit or roll back operation as instructed by the global coordinator. The system administrator always designates one node to be the commit point site in the session tree by assigning all nodes a commit point strength. The node selected as commit point site should be the node that stores the most critical data.
Figure 31-3 illustrates an example of distributed system, with sales
serving as the commit point site:
The commit point site is distinct from all other nodes involved in a distributed transaction in these ways:
A distributed transaction is considered committed after all non-commit point sites are prepared, and the transaction has been actually committed at the commit point site. The online redo log at the commit point site is updated as soon as the distributed transaction is committed at this node.
Because the commit point log contains a record of the commit, the transaction is considered committed even though some participating nodes may still be only in the prepared state and the transaction not yet actually committed at these nodes. In the same way, a distributed transaction is considered not committed if the commit has not been logged at the commit point site.
Every database server must be assigned a commit point strength. If a database server is referenced in a distributed transaction, the value of its commit point strength determines which role it plays in the two-phase commit. Specifically, the commit point strength determines whether a given node is the commit point site in the distributed transaction and thus commits before all of the other nodes. This value is specified using the initialization parameter COMMIT_POINT_STRENGTH
. This section explains how Oracle determines the commit point site.
The commit point site, which is determined at the beginning of the prepare phase, is selected only from the nodes participating in the transaction. The following sequence of events occurs:
Figure 31-4 shows in a sample session tree the commit point strengths of each node (in parentheses) and shows the node chosen as the commit point site:
The following conditions apply when determining the commit point site:
ROLLBACK
statement to all nodes and ends the processing of the distributed transaction.As Figure 31-4 illustrates, the commit point site and the global coordinator can be different nodes of the session tree. The commit point strength of each node is communicated to the coordinators when the initial connections are made. The coordinators retain the commit point strengths of each node they are in direct communication with so that commit point sites can be efficiently selected during two-phase commits. Therefore, it is not necessary for the commit point strength to be exchanged between a coordinator and a node each time a commit occurs.
See Also:
|
Unlike a transaction on a local database, a distributed transaction involves altering data on multiple databases. Consequently, distributed transaction processing is more complicated, because Oracle must coordinate the committing or rolling back of the changes in a transaction as a self-contained unit. In other words, the entire transaction commits, or the entire transaction rolls back.
Oracle ensures the integrity of data in a distributed transaction using the two-phase commit mechanism. In the prepare phase, the initiating node in the transaction asks the other participating nodes to promise to commit or roll back the transaction. During the commit phase, the initiating node asks all participating nodes to commit the transaction. If this outcome is not possible, then all nodes are asked to roll back.
All participating nodes in a distributed transaction should perform the same action: they should either all commit or all perform a rollback of the transaction. Oracle automatically controls and monitors the commit or rollback of a distributed transaction and maintains the integrity of the global database (the collection of databases participating in the transaction) using the two-phase commit mechanism. This mechanism is completely transparent, requiring no programming on the part of the user or application developer.
The commit mechanism has the following distinct phases, which Oracle performs automatically whenever a user commits a distributed transaction:
Phase | Description |
---|---|
Prepare phase |
The initiating node, called the global coordinator, asks participating nodes other than the commit point site to promise to commit or roll back the transaction, even if there is a failure. If any node cannot prepare, the transaction is rolled back. |
Commit phase |
If all participants respond to the coordinator that they are prepared, then the coordinator asks the commit point site to commit. After it commits, the coordinator asks all other nodes to commit the transaction. |
Forget phase |
The global coordinator forgets about the transaction. |
This section contains the following topics:
The first phase in committing a distributed transaction is the prepare phase. In this phase, Oracle does not actually commit or roll back the transaction. Instead, all nodes referenced in a distributed transaction (except the commit point site, described in the "Commit Point Site" ) are told to prepare to commit. By preparing, a node:
When a node responds to the global coordinator that it is prepared to commit, the prepared node promises to either commit or roll back the transaction later--but does not make a unilateral decision on whether to commit or roll back the transaction. The promise means that if an instance failure occurs at this point, the node can use the redo records in the online log to recover the database back to the prepare phase.
Note: Queries that start after a node has prepared cannot access the associated locked data until all phases complete. The time is insignificant unless a failure occurs (see "Deciding How to Handle In-Doubt Transactions" ). |
When a node is told to prepare, it can respond in the following ways:
Response | Meaning |
---|---|
Prepared |
Data on the node has been modified by a statement in the distributed transaction, and the node has successfully prepared. |
Read-only |
No data on the node has been, or can be, modified (only queried), so no preparation is necessary. |
Abort |
The node cannot successfully prepare. |
When a node has successfully prepared, it issues a prepared message. The message indicates that the node has records of the changes in the online log, so it is prepared either to commit or perform a rollback. The message also guarantees that locks held for the transaction can survive a failure.
When a node is asked to prepare, and the SQL statements affecting the database do not change the node's data, the node responds with a read-only message. The message indicates that the node will not participate in the commit phase.
There are three cases in which all or part of a distributed transaction is read-only:
Case | Conditions | Consequence |
---|---|---|
Partially read-only |
Any of the following occurs:
|
The read-only nodes recognize their status when asked to prepare. They give their local coordinators a read-only response. Thus, the commit phase completes faster because Oracle eliminates read-only nodes from subsequent processing. |
Completely read-only with prepare phase |
All of following occur:
|
All nodes recognize that they are read-only during prepare phase, so no commit phase is required. The global coordinator, not knowing whether all nodes are read-only, must still perform the prepare phase. |
Completely read-only without two-phase commit |
All of following occur:
|
Only queries are allowed in the transaction, so global coordinator does not have to perform two-phase commit. Changes by other transactions do not degrade global transaction-level read consistency because of global SCN coordination among nodes. The transaction does not use rollback segments. |
Note that if a distributed transaction is set to read-only, then it does not use rollback segments. If many users connect to the database and their transactions are not set to READ ONLY
, then they allocate rollback space even if they are only performing queries.
When a node cannot successfully prepare, it performs the following actions:
These actions then propagate to the other nodes involved in the distributed transaction so that they can roll back the transaction and guarantee the integrity of the data in the global database. This response enforces the primary rule of a distributed transaction: all nodes involved in the transaction either all commit or all roll back the transaction at the same logical time.
To complete the prepare phase, each node excluding the commit point site performs the following steps:
These actions guarantee that the node can subsequently commit or roll back the transaction on the node. The prepared nodes then wait until a COMMIT
or ROLLBACK
request is received from the global coordinator.
After the nodes are prepared, the distributed transaction is said to be in-doubt (see "In-Doubt Transactions" ). It retains in-doubt status until all changes are either committed or rolled back.
The second phase in committing a distributed transaction is the commit phase. Before this phase occurs, all nodes other than the commit point site referenced in the distributed transaction have guaranteed that they are prepared, that is, they have the necessary resources to commit the transaction.
The commit phase consists of the following steps:
When the commit phase is complete, the data on all nodes of the distributed system is consistent.
Each committed transaction has an associated system change number (SCN) to uniquely identify the changes made by the SQL statements within that transaction. The SCN functions as an internal Oracle timestamp that uniquely identifies a committed version of the database.
In a distributed system, the SCNs of communicating nodes are coordinated when all of the following actions occur:
Among other benefits, the coordination of SCNs among the nodes of a distributed system ensures global read-consistency at both the statement and transaction level. If necessary, global time-based recovery can also be completed.
During the prepare phase, Oracle determines the highest SCN at all nodes involved in the transaction. The transaction then commits with the high SCN at the commit point site. The commit SCN is then sent to all prepared nodes with the commit decision.
See Also:
"Managing Read Consistency" for information about managing time lag issues in read consistency |
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:
The two-phase commit mechanism ensures that all nodes either commit or perform a rollback together. What happens if any of the three phases fails because of a system or network error? The transaction becomes in-doubt.
Distributed transactions can become in-doubt in the following ways:
The RECO process automatically resolves in-doubt transactions when the machine, network, or software problem is resolved. Until RECO can resolve the transaction, the data is locked for both reads and writes. Oracle blocks reads because it cannot determine which version of the data to display for a query.
This section contains the following topics:
In the majority of cases, Oracle resolves the in-doubt transaction automatically. Assume that there are two nodes, local
and remote
, in the following scenarios. The local node is the commit point site. User scott
connects to local
and executes and commits a distributed transaction that updates local
and remote
.
Figure 31-5 illustrates the sequence of events when there is a failure during the prepare phase of a distributed transaction:
The following steps occur:
Scott
connects to local
and executes a distributed transaction.remote
database crashes before issuing the prepare response back to local
.Figure 31-6 illustrates the sequence of events when there is a failure during the commit phase of a distributed transaction:
The following steps occur:
Scott
connects to local
and executes a distributed transaction.remote
saying that it will commit.remote
asking it to commit.remote
database receives the commit message, but cannot respond because of a network failure.
See Also:
"Deciding How to Handle In-Doubt Transactions" for a description of failure situations and how Oracle resolves intervening failures during two-phase commit |
You should only need to resolve an in-doubt transaction in the following cases:
Resolution of in-doubt transactions can be complicated. The procedure requires that you do the following:
DBA_2PC_PENDING
and DBA_2PC_NEIGHBORS
views to determine whether the databases involved in the transaction have committed.COMMIT FORCE
statement or a rollback using the ROLLBACK FORCE
statement.
See Also:
The following sections explain how to resolve in-doubt transactions:
|
A system change number (SCN) is an internal timestamp for a committed version of the database. The Oracle database server uses the SCN clock value to guarantee transaction consistency. For example, when a user commits a transaction, Oracle records an SCN for this commit in the online redo log.
Oracle uses SCNs to coordinate distributed transactions among different databases. For example, Oracle uses SCNs in the following way:
SCNs are important for distributed transactions because they function as a synchronized commit timestamp of a transaction--even if the transaction fails. If a transaction becomes in-doubt, an administrator can use this SCN to coordinate changes made to the global database. The global SCN for the transaction commit can also be used to identify the transaction later, for example, in distributed recovery.
In this scenario, a company has separate Oracle database servers, sales.acme.com
and warehouse.acme.com
. As users insert sales records into the sales
database, associated records are being updated at the warehouse
database.
This case study of distributed processing illustrates:
At the Sales department, a salesperson uses SQL*Plus to enter a sales order and then commit it. The application issues a number of SQL statements to enter the order into the sales
database and update the inventory in the warehouse
database:
CONNECT scott/tiger@sales.acme.com ...; INSERT INTO orders ...; UPDATE inventory@warehouse.acme.com ...; INSERT INTO orders ...; UPDATE inventory@warehouse.acme.com ...; COMMIT;
These SQL statements are part of a single distributed transaction, guaranteeing that all issued SQL statements succeed or fail as a unit. Treating the statements as a unit prevents the possibility of an order being placed and then inventory not being updated to reflect the order. In effect, the transaction guarantees the consistency of data in the global database.
As each of the SQL statements in the transaction executes, the session tree is defined, as shown in Figure 31-7.
Note the following aspects of the transaction:
sales
database initiates the transaction. Therefore, sales.acme.com
is the global coordinator for the distributed transaction.sales
database and updates the inventory at the warehouse. Therefore, the nodes sales.acme.com
and warehouse.acme.com
are both database servers.sales.acme.com
updates the inventory, it is a client of warehouse.acme.com
.This stage completes the definition of the session tree for this distributed transaction. Each node in the tree has acquired the necessary data locks to execute the SQL statements that reference local data. These locks remain even after the SQL statements have been executed until the two-phase commit is completed.
Oracle determines the commit point site immediately following the COMMIT
statement. sales.acme.com
, the global coordinator, is determined to be the commit point site, as shown in Figure 31-8.
See Also:
"Commit Point Strength" for more information about how the commit point site is determined |
The prepare stage involves the following steps:
warehouse.acme.com
is the only node asked to prepare.warehouse.acme.com
tries to prepare. If a node can guarantee that it can commit the locally dependent part of the transaction and can record the commit information in its local redo log, then the node can successfully prepare. In this example, only warehouse.acme.com
receives a prepare message because sales.acme.com
is the commit point site.warehouse.acme.com
responds to sales.acme.com
with a prepared message.As each node prepares, it sends a message back to the node that asked it to prepare. Depending on the responses, one of the following can happen:
The committing of the transaction by the commit point site involves the following steps:
sales.acme.com
, receiving acknowledgment that warehouse.acme.com
is prepared, instructs the commit point site to commit the transaction.Even if warehouse.acme.com
has not yet committed, the outcome of this transaction is predetermined. In other words, the transaction will be committed at all nodes even if a given node's ability to commit is delayed.
This stage involves the following steps:
The committing of the transaction by all the nodes in the transaction involves the following steps:
In Figure 31-10, sales.acme.com
, which is both the commit point site and the global coordinator, has already committed the transaction locally. sales
now instructs warehouse.acme.com
to commit the transaction.
The completion of the commit of the transaction occurs in the following steps:
After the completion of the COMMIT
phase, the distributed transaction is itself complete. The steps described above are accomplished automatically and in a fraction of a second.
|
Copyright © 2001, 2002 Oracle Corporation. All Rights Reserved. |
|
玻璃钢生产厂家玻璃钢雕塑经销商佳木斯市玻璃钢雕塑玻璃钢蔬菜雕塑哪家靠谱福建主题商场美陈价格广州卡通雕塑玻璃钢内江玻璃钢仿铜雕塑价格玻璃钢雕塑摆件有哪些玻璃钢西瓜雕塑造价冀州玻璃钢仿铜雕塑双城玻璃钢雕塑厂家小品系列玻璃钢雕塑定做上海玻璃钢花盆多少钱唐山玻璃钢人物雕塑定制玻璃钢园林雕塑有哪些水生植物白玻璃钢花盆路边玻璃钢花盆图片价格广州玻璃钢人物雕塑代理商天津大型现代人物玻璃钢雕塑通道商场美陈多少钱仿铜玻璃钢雕塑制作过程宛城玻璃钢雕塑定制玻璃钢环境雕塑邵阳玻璃钢群马雕塑工业玻璃钢雕塑摆件哪里有小象雕塑玻璃钢滁州玻璃钢仿真植物水果雕塑浙江 玻璃钢雕塑玻璃钢雕塑用的是玻璃纤维吗佛山人物玻璃钢卡通雕塑河南玻璃钢仿铜雕塑厂家供应香港通过《维护国家安全条例》两大学生合买彩票中奖一人不认账让美丽中国“从细节出发”19岁小伙救下5人后溺亡 多方发声单亲妈妈陷入热恋 14岁儿子报警汪小菲曝离婚始末遭遇山火的松茸之乡雅江山火三名扑火人员牺牲系谣言何赛飞追着代拍打萧美琴窜访捷克 外交部回应卫健委通报少年有偿捐血浆16次猝死手机成瘾是影响睡眠质量重要因素高校汽车撞人致3死16伤 司机系学生315晚会后胖东来又人满为患了小米汽车超级工厂正式揭幕中国拥有亿元资产的家庭达13.3万户周杰伦一审败诉网易男孩8年未见母亲被告知被遗忘许家印被限制高消费饲养员用铁锨驱打大熊猫被辞退男子被猫抓伤后确诊“猫抓病”特朗普无法缴纳4.54亿美元罚金倪萍分享减重40斤方法联合利华开始重组张家界的山上“长”满了韩国人?张立群任西安交通大学校长杨倩无缘巴黎奥运“重生之我在北大当嫡校长”黑马情侣提车了专访95后高颜值猪保姆考生莫言也上北大硕士复试名单了网友洛杉矶偶遇贾玲专家建议不必谈骨泥色变沉迷短剧的人就像掉进了杀猪盘奥巴马现身唐宁街 黑色着装引猜测七年后宇文玥被薅头发捞上岸事业单位女子向同事水杯投不明物质凯特王妃现身!外出购物视频曝光河南驻马店通报西平中学跳楼事件王树国卸任西安交大校长 师生送别恒大被罚41.75亿到底怎么缴男子被流浪猫绊倒 投喂者赔24万房客欠租失踪 房东直发愁西双版纳热带植物园回应蜉蝣大爆发钱人豪晒法院裁定实锤抄袭外国人感慨凌晨的中国很安全胖东来员工每周单休无小长假白宫:哈马斯三号人物被杀测试车高速逃费 小米:已补缴老人退休金被冒领16年 金额超20万