今天碰到的一個問題
[@more@] Managing Distributed TransactionsThis chapter describes how to manage and troubleshoot distributed transactions. The following topics are included in this chapter:
- Specifying the Commit Point Strength of a Node
- Naming Transactions
- Viewing Information About Distributed Transactions
- Deciding How to Handle In-Doubt Transactions
- Manually Overriding In-Doubt Transactions
- Purging Pending Rows from the Data Dictionary
- Manually Committing an In-Doubt Transaction: Example
- Data Access Failures Due To Locks
- Simulating Distributed Transaction Failure
- Managing Read Consistency
Specifying the Commit Point Strength of a Node
The database with the highest commit point strength determines which node commits first in a distributed transaction. When specifying a commit point strength for each node, ensure that the most critical server will be nonblocking if a failure occurs during a prepare or commit phase. The COMMIT_POINT_STRENGTH
initialization parameter determines a node's commit point strength.
The default value is operating system-dependent. The range of values is any integer from 0 to 255. For example, to set the commit point strength of a database to 200, include the following line in that database's initialization parameter file:
COMMIT_POINT_STRENGTH = 200
The commit point strength is only used to determine the commit point site in a distributed transaction.
When setting the commit point strength for a database, note the following considerations:
- Because the commit point site stores information about the status of the transaction, the commit point site should not be a node that is frequently unreliable or unavailable in case other nodes need information about the transaction's status.
- Set the commit point strength for a database relative to the amount of critical shared data in the database. For example, a database on a mainframe computer usually shares more data among users than a database on a PC. Therefore, set the commit point strength of the mainframe to a higher value than the PC.
See Also: "Commit Point Site" for a conceptual overview of commit points
Naming Transactions
Starting with Oracle9i you can name a transaction. This is useful for identifying a specific distributed transaction and replaces the use of the COMMIT COMMENT
statement for this purpose.
To name a transaction, use the SET TRANSACTION ... NAME
statement. For example:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE NAME 'update inventory checkpoint 0';
This example shows that the user started a new transaction with isolation level equal to SERIALIZABLE
and named it 'update inventory checkpoint 0'
.
For distributed transactions, the name is sent to participating sites when a transaction is committed. If a COMMIT COMMENT
exists, it is ignored when a transaction name exists.
The transaction name is displayed in the NAME
column of the V$TRANSACTION
view, and is placed in the TRAN_COMMENT
column of the view when the transaction is committed.
Viewing Information About Distributed Transactions
The data dictionary of each database stores information about all open distributed transactions. You can use data dictionary tables and views to gain information about the transactions. This section contains the following topics:
- Determining the ID Number and Status of Prepared Transactions
- Tracing the Session Tree of In-Doubt Transactions
Determining the ID Number and Status of Prepared Transactions
The following view shows the database links that have been defined at the local database and stored in the data dictionary:
View | Purpose |
---|---|
| Lists all in-doubt distributed transactions. The view is empty until populated by an in-doubt transaction. After the transaction is resolved, the view is purged. |
Use this view to determine the global commit number for a particular transaction ID. You can use this global commit number when manually resolving an in-doubt transaction.
The following table shows the most relevant columns (for a description of all the columns in the view, see Oracle9i Database Reference):
Table 32-1 DBA_2PC_PENDING
Execute the following script, named pending_txn_script
, to query pertinent information in DBA_2PC_PENDING
(sample output included):
COL LOCAL_TRAN_ID FORMAT A13 COL GLOBAL_TRAN_ID FORMAT A30 COL STATE FORMAT A8 COL MIXED FORMAT A3 COL HOST FORMAT A10 COL COMMIT# FORMAT A10 SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID, STATE, MIXED, HOST, COMMIT# FROM DBA_2PC_PENDING / SQL> @pending_txn_script LOCAL_TRAN_ID GLOBAL_TRAN_ID STATE MIX HOST COMMIT# ------------- ------------------------------ -------- --- ---------- ---------- 1.15.870 HQ.ACME.COM.ef192da4.1.15.870 commit no dlsun183 115499
This output indicates that local transaction 1.15.870
has been committed on this node, but it may be pending on one or more other nodes. Because LOCAL_TRAN_ID
and the local part of GLOBAL_TRAN_ID
are the same, the node is the global coordinator of the transaction.
Tracing the Session Tree of In-Doubt Transactions
The following view shows which in-doubt transactions are incoming from a remote client and which are outgoing to a remote server:
When a transaction is in-doubt, you may need to determine which nodes performed which roles in the session tree. Use to this view to determine:
- All the incoming and outgoing connections for a given transaction
- Whether the node is the commit point site in a given transaction
- Whether the node is a global coordinator in a given transaction (because its local transaction ID and global transaction ID are the same)
The following table shows the most relevant columns (for an account of all the columns in the view, see Oracle9i Database Reference):
Table 32-2 DBA_2PC_NEIGHBORS
Execute the following script, named neighbors_script
, to query pertinent information in DBA_2PC_PENDING
(sample output included):
COL LOCAL_TRAN_ID FORMAT A13 COL IN_OUT FORMAT A6 COL DATABASE FORMAT A25 COL DBUSER_OWNER FORMAT A15 COL INTERFACE FORMAT A3 SELECT LOCAL_TRAN_ID, IN_OUT, DATABASE, DBUSER_OWNER, INTERFACE FROM DBA_2PC_NEIGHBORS / SQL> CONNECT SYS/password@hq.acme.com SQL> @neighbors_script LOCAL_TRAN_ID IN_OUT DATABASE DBUSER_OWNER INT ------------- ------ ------------------------- --------------- --- 1.15.870 out SALES.ACME.COM SYS C
This output indicates that the local node sent an outgoing request to remote server sales
to commit transaction 1.15.870
. If sales
committed the transaction but no other node did, then you know that sales
is the commit point site--because the commit point site always commits first.
Deciding How to Handle In-Doubt Transactions
A transaction is in-doubt when there is a failure during any aspect of the two-phase commit. Distributed transactions become in-doubt in the following ways:
- A server machine running Oracle software crashes
- A network connection between two or more Oracle databases involved in distributed processing is disconnected
- An unhandled software error occurs
See Also: "In-Doubt Transactions" for a conceptual overview of in-doubt transactions
You can manually force the commit or rollback of a local, in-doubt distributed transaction. Because this operation can generate consistency problems, perform it only when specific conditions exist.
This section contains the following topics:
- Discovering Problems with a Two-Phase Commit
- Determining Whether to Perform a Manual Override
- Analyzing the Transaction Data
Discovering Problems with a Two-Phase Commit
The user application that commits a distributed transaction is informed of a problem by one of the following error messages:
ORA-02050: transaction ID rolled back, some remote dbs may be in-doubt ORA-02051: transaction ID committed, some remote dbs may be in-doubt ORA-02054: transaction ID in-doubt
A robust application should save information about a transaction if it receives any of the above errors. This information can be used later if manual distributed transaction recovery is desired.
No action is required by the administrator of any node that has one or more in-doubt distributed transactions due to a network or system failure. The automatic recovery features of Oracle transparently complete any in-doubt transaction so that the same outcome occurs on all nodes of a session tree (that is, all commit or all roll back) after the network or system failure is resolved.
In extended outages, however, you can force the commit or rollback of a transaction to release any locked data. Applications must account for such possibilities.
Determining Whether to Perform a Manual Override
Override a specific in-doubt transaction manually only when one of the following situations exists:
- The in-doubt transaction locks data that is required by other transactions. This situation occurs when the
ORA-01591
error message interferes with user transactions. - An in-doubt transaction prevents the extents of a rollback segment from being used by other transactions. The first portion of an in-doubt distributed transaction's local transaction ID corresponds to the ID of the rollback segment, as listed by the data dictionary views
DBA_2PC_PENDING
andDBA_ROLLBACK_SEGS
. - The failure preventing the two-phase commit phases to complete cannot be corrected in an acceptable time period. Examples of such cases include a telecommunication network that has been damaged or a damaged database that requires a long recovery time.
Normally, you should make a decision to locally force an in-doubt distributed transaction in consultation with administrators at other locations. A wrong decision can lead to database inconsistencies that can be difficult to trace and that you must manually correct.
If the conditions above do not apply, always allow the automatic recovery features of Oracle to complete the transaction. If any of the above criteria are met, however, consider a local override of the in-doubt transaction.
Analyzing the Transaction Data
If you decide to force the transaction to complete, analyze available information with the following goals in mind.
Find a Node That Committed or Rolled Back
Use the DBA_2PC_PENDING
view to find a node that has either committed or rolled back the transaction. If you can find a node that has already resolved the transaction, then you can follow the action taken at that node.
Look For Transaction Comments
See if any information is given in the TRAN_COMMENT
column of DBA_2PC_PENDING
for the distributed transaction. Comments are included in the COMMENT
clause of the COMMIT
statement, or if transaction naming is used, the transaction name is placed in the TRAN_COMMENT
field when the transaction is committed.
For example, an in-doubt distributed transaction's comment can indicate the origin of the transaction and what type of transaction it is:
COMMIT COMMENT 'Finance/Accts_pay/Trans_type 10B';
The SET TRANSACTION ... NAME
statement could also have been used (and is preferable) to provide this information in a transaction name.
Look For Transaction Advice
See if any information is given in the ADVICE
column of DBA_2PC_PENDING
for the distributed transaction. An application can prescribe advice about whether to force the commit or force the rollback of separate parts of a distributed transaction with the ADVISE
clause of the ALTER SESSION
statement.
The advice sent during the prepare phase to each node is the advice in effect at the time the most recent DML statement executed at that database in the current transaction.
For example, consider a distributed transaction that moves an employee record from the emp
table at one node to the emp
table at another node. The transaction can protect the record--even when administrators independently force the in-doubt transaction at each node--by including the following sequence of SQL statements:
ALTER SESSION ADVISE COMMIT; INSERT INTO emp@hq ... ; /*advice to commit at HQ */ ALTER SESSION ADVISE ROLLBACK; DELETE FROM emp@sales ... ; /*advice to roll back at SALES*/ ALTER SESSION ADVISE NOTHING;
If you manually force the in-doubt transaction following the given advice, the worst that can happen is that each node has a copy of the employee record; the record cannot disappear.
Manually Overriding In-Doubt Transactions
Use the COMMIT
or ROLLBACK
statement with the FORCE
option and a text string that indicates either the local or global transaction ID of the in-doubt transaction to commit.
This section contains the following topics:
Manually Committing an In-Doubt Transaction
Before attempting to commit the transaction, ensure that you have the proper privileges. Note the following requirements:
If the transaction was committed by... | Then you must have this privilege... |
---|---|
You |
|
Another user |
|
Committing Using Only the Transaction ID
The following SQL statement commits an in-doubt transaction:
COMMIT FORCE 'transaction_id';
The variable transaction_id is the identifier of the transaction as specified in either the LOCAL_TRAN_ID
or GLOBAL_TRAN_ID
columns of the DBA_2PC_PENDING
data dictionary view.
For example, assume that you query DBA_2PC_PENDING
and determine that LOCAL_TRAN_ID
for a distributed transaction is 1:45.13
.
You then issue the following SQL statement to force the commit of this in-doubt transaction:
COMMIT FORCE '1.45.13';
Committing Using an SCN
Optionally, you can specify the SCN for the transaction when forcing a transaction to commit. This feature allows you to commit an in-doubt transaction with the SCN assigned when it was committed at other nodes.
Consequently, you maintain the synchronized commit time of the distributed transaction even if there is a failure. Specify an SCN only when you can determine the SCN of the same transaction already committed at another node.
For example, assume you want to manually commit a transaction with the following global transaction ID:
SALES.ACME.COM.55d1c563.1.93.29
First, query the DBA_2PC_PENDING
view of a remote database also involved with the transaction in question. Note the SCN used for the commit of the transaction at that node. Specify the SCN when committing the transaction at the local node. For example, if the SCN is 829381993
, issue:
COMMIT FORCE 'SALES.ACME.COM.55d1c563.1.93.29', 829381993;
See Also: Oracle9i SQL Reference for more information about using the |
Manually Rolling Back an In-Doubt Transaction
Before attempting to roll back the in-doubt distributed transaction, ensure that you have the proper privileges. Note the following requirements:
If the transaction was committed by... | Then you must have this privilege... |
---|---|
You |
|
Another user |
|
The following SQL statement rolls back an in-doubt transaction:
ROLLBACK FORCE 'transaction_id';
The variable transaction_id is the identifier of the transaction as specified in either the LOCAL_TRAN_ID
or GLOBAL_TRAN_ID
columns of the DBA_2PC_PENDING
data dictionary view.
For example, to roll back the in-doubt transaction with the local transaction ID of 2.9.4
, use the following statement:
ROLLBACK FORCE '2.9.4';
See Also: Oracle9i SQL Reference for more information about using the |
Purging Pending Rows from the Data Dictionary
Before RECO recovers an in-doubt transaction, the transaction appears in DBA_2PC_PENDING.STATE
as COLLECTING
, COMMITTED
, or PREPARED
. If you force an in-doubt transaction using COMMIT FORCE
or ROLLBACK FORCE
, then the states FORCED COMMIT
or FORCED ROLLBACK
may appear.
Automatic recovery normally deletes entries in these states. The only exception is when recovery discovers a forced transaction that is in a state inconsistent with other sites in the transaction. In this case, the entry can be left in the table and the MIXED
column in DBA_2PC_PENDING
has a value of YES
. These entries can be cleaned up with the DBMS_TRANSACTION.PURGE_MIXED
procedure.
If automatic recovery is not possible because a remote database has been permanently lost, then recovery cannot identify the re-created database because it receives a new database ID when it is re-created. In this case, you must use the PURGE_LOST_DB_ENTRY
procedure in the DBMS_TRANSACTION
package to clean up the entries. The entries do not hold up database resources, so there is no urgency in cleaning them up.
Executing the PURGE_LOST_DB_ENTRY Procedure
To manually remove an entry from the data dictionary, use the following syntax (where trans_id is the identifier for the transaction):
DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('trans_id');
For example, to purge pending distributed transaction 1.44.99
, enter the following statement in SQL*Plus:
EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('1.44.99');
Execute this procedure only if significant reconfiguration has occurred so that automatic recovery cannot resolve the transaction. Examples include:
- Total loss of the remote database
- Reconfiguration in software resulting in loss of two-phase commit capability
- Loss of information from an external transaction coordinator such as a TPMonitor
See Also: Oracle9i Supplied PL/SQL Packages and Types Reference for more information about the
DBMS_TRANSACTION
package
Determining When to Use DBMS_TRANSACTION
The following tables indicates what the various states indicate about the distributed transaction what the administrator's action should be:
See Also: Oracle9i Supplied PL/SQL Packages and Types Reference for more information about the |
Manually Committing an In-Doubt Transaction: Example
Figure 32-1, illustrates a failure during the commit of a distributed transaction. In this failure case, the prepare phase completes. During the commit phase, however, the commit point site's commit confirmation never reaches the global coordinator, even though the commit point site committed the transaction. Inventory data is locked and cannot be accessed because the in-doubt transaction is critical to other transactions. Further, the locks must be held until the in-doubt transaction either commits or rolls back.
Figure 32-1 Example of an In-Doubt Distributed Transaction
Text description of the illustration admin037.gif
You can manually force the local portion of the in-doubt transaction by following the steps detailed in the following sections:
Step 3: Query DBA_2PC_NEIGHBORS on Local Node
Step 4: Querying Data Dictionary Views on All Nodes
Step 5: Commit the In-Doubt Transaction
Step 1: Record User Feedback
The users of the local database system that conflict with the locks of the in-doubt transaction receive the following error message:
ORA-01591: lock held by in-doubt distributed transaction 1.21.17
In this case, 1.21.17
is the local transaction ID of the in-doubt distributed transaction. You should request and record this ID number from users that report problems to identify which in-doubt transactions should be forced.
Step 2: Query DBA_2PC_PENDING
After connecting with SQL*Plus to warehouse
, query the local DBA_2PC_PENDING
data dictionary view to gain information about the in-doubt transaction:
CONNECT SYS/password@warehouse.acme.com SELECT * FROM DBA_2PC_PENDING WHERE LOCAL_TRAN_ID = '1.21.17';
Oracle returns the following information:
Column Name Value ---------------------- -------------------------------------- LOCAL_TRAN_ID 1.21.17 GLOBAL_TRAN_ID SALES.ACME.COM.55d1c563.1.93.29 STATE prepared MIXED no ADVICE TRAN_COMMENT Sales/New Order/Trans_type 10B FAIL_TIME 31-MAY-91 FORCE_TIME RETRY_TIME 31-MAY-91 OS_USER SWILLIAMS OS_TERMINAL TWA139: HOST system1 DB_USER SWILLIAMS COMMIT#
Determining the Global Transaction ID
The global transaction ID is the common transaction ID that is the same on every node for a distributed transaction. It is of the form:
global_database_name.hhhhhhhh.local_transaction_id
where:
- global_database_name is the database name of the global coordinator.
- hhhhhhhh is the internal database identifier of the global coordinator (in hexadecimal).
- local_transaction_id is the corresponding local transaction ID assigned on the global coordinator.
Note that the last portion of the global transaction ID and the local transaction ID match at the global coordinator. In the example, you can tell that warehouse
is not the global coordinator because these numbers do not match:
LOCAL_TRAN_ID 1.21.17 GLOBAL_TRAN_ID ... 1.93.29
Determining the State of the Transaction
The transaction on this node is in a prepared state:
STATE prepared
Therefore, warehouse
waits for its coordinator to send either a commit or a rollback request.
Looking For Comments or Advice
The transaction's comment or advice can include information about this transaction. If so, use this comment to your advantage. In this example, the origin and transaction type is in the transaction's comment:
TRAN_COMMENT Sales/New Order/Trans_type 10B
It could also be provided as a transaction name with a SET TRANSACTION ... NAME
statement.
This information can reveal something that helps you decide whether to commit or rollback the local portion of the transaction. If useful comments do not accompany an in-doubt transaction, you must complete some extra administrative work to trace the session tree and find a node that has resolved the transaction.
Step 3: Query DBA_2PC_NEIGHBORS on Local Node
The purpose of this step is to climb the session tree so that you find coordinators, eventually reaching the global coordinator. Along the way, you may find a coordinator that has resolved the transaction. If not, you can eventually work your way to the commit point site, which will always have resolved the in-doubt transaction. To trace the session tree, query the DBA_2PC_NEIGHBORS
view on each node.
In this case, you query this view on the warehouse
database:
CONNECT SYS/password@warehouse.acme.com SELECT * FROM DBA_2PC_NEIGHBORS WHERE LOCAL_TRAN_ID = '1.21.17' ORDER BY SESS#, IN_OUT; Column Name Value ---------------------- -------------------------------------- LOCAL_TRAN_ID 1.21.17 IN_OUT in DATABASE SALES.ACME.COM DBUSER_OWNER SWILLIAMS INTERFACE N DBID 000003F4 SESS# 1 BRANCH 0100
Obtaining Database Role and Database Link Information
The DBA_2PC_NEIGHBORS
view provides information about connections associated with an in-doubt transaction. Information for each connection is different, based on whether the connection is inbound (IN_OUT = in
) or outbound (IN_OUT = out
):
In this example, the IN_OUT
column reveals that the warehouse
database is a server for the sales
client, as specified in the DATABASE column:
IN_OUT in DATABASE SALES.ACME.COM
The connection to warehouse
was established through a database link from the swilliams
account, as shown by the DBUSER_OWNER
column:
DBUSER_OWNER SWILLIAMS
Determining the Commit Point Site
Additionally, the INTERFACE
column tells whether the local node or a subordinate node is the commit point site:
INTERFACE N
Neither warehouse
nor any of its descendants is the commit point site, as shown by the INTERFACE
column.
Step 4: Querying Data Dictionary Views on All Nodes
At this point, you can contact the administrator at the located nodes and ask each person to repeat Steps 2 and 3 using the global transaction ID.
Note: If you can directly connect to these nodes with another network, you can repeat Steps 2 and 3 yourself. |
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/202861/viewspace-807203/,如需转载,请注明出处,否则将追究法律责任。
<%=items[i].content%>
<%if(items[i].items.items.length) { %><%=items[i].items.items[j].username%> 回复 <%=items[i].items.items[j].tousername%>: <%=items[i].items.items[j].content%>
转载于:http://blog.itpub.net/202861/viewspace-807203/