Where is dba 2pc pending

Distributed transactions become in-doubt in the following ways:. A network connection between two or more Oracle Databases involved in distributed processing is disconnected.

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. The user application that commits a distributed transaction is informed of a problem by one of the following error messages:.

A robust application should save information about a transaction if it receives any of the preceding 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 the database 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. Override a specific in-doubt transaction manually only when one of the following conditions exists:.

The in-doubt transaction locks data that is required by other transactions. This situation occurs when the ORA error message interferes with user transactions. An in-doubt transaction prevents the extents of a undo segment from being used by other transactions. 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 none of these conditions apply, always allow the automatic recovery features of the database to complete the transaction. If any of these conditions are met, however, consider a local override of the in-doubt transaction. If you decide to force the transaction to complete, analyze available information with the following goals in mind. If you can find a node that has already resolved the transaction, then you can follow the action taken at that node.

For example, the comment of an in-doubt distributed transaction can indicate the origin of the transaction and what type of transaction it is:.

NAME statement could also have been used and is preferable to provide this information in a transaction name. 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:.

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. Before attempting to commit the transaction, ensure that you have the proper privileges. Note the following requirements:. You then issue the following SQL statement to force the commit of this in-doubt transaction:. Optionally, you can specify the SCN for the transaction when forcing a transaction to commit.

This feature lets you 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.

For example, assume you want to manually commit a transaction with the following global transaction ID:. 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 , issue:. Before attempting to roll back the in-doubt distributed transaction, ensure that you have the proper privileges.

The following SQL statement rolls back an in-doubt transaction:. For example, to roll back the in-doubt transaction with the local transaction ID of 2. 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.

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. The entries do not hold up database resources, so there is no urgency in cleaning them up. For example, to purge pending distributed transaction 1. Execute this procedure only if significant reconfiguration has occurred so that automatic recovery cannot resolve the transaction. Examples include:.

The following tables indicates what the various states indicate about the distributed transaction what the administrator's action should be:. Figure , 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 confirmation of the commit point site 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. You can manually force the local portion of the in-doubt transaction by following the steps detailed in the following sections:.

The users of the local database system that conflict with the locks of the in-doubt transaction receive the following error message:. In this case, 1. You should request and record this ID number from users that report problems to identify which in-doubt transactions should be forced.

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:.

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:. Therefore, warehouse waits for its coordinator to send either a commit or a rollback request.

The transaction 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 comment:. 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.

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. 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.

For example, the following results are returned when Steps 2 and 3 are performed at sales and hq. Reformatted information corresponding to the rows for the warehouse connection appears below:.

Reformatted information corresponding to the rows for the hq connection appears below:. Two outbound connections are established from this node, but no inbound connections. At this point, you have found a node that resolved the transaction.

As the view reveals, it has been committed and assigned a commit ID number:. Therefore, you can force the in-doubt transaction to commit at your local database.

In this example, the origin and transaction type is in the transaction comment:. 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.

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.

Lists the local account for the database link connection that corresponds to the in-doubt transaction. 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. If you can directly connect to these nodes with another network, you can repeat Steps 2 and 3 yourself. For example, the following results are returned when Steps 2 and 3 are performed at sales and hq. Reformatted information corresponding to the rows for the warehouse connection appears below:.

Reformatted information corresponding to the rows for the hq connection appears below:. Two outbound connections are established from this node, but no inbound connections. At this point, you have found a node that resolved the transaction. As the view reveals, it has been committed and assigned a commit ID number:. Therefore, you can force the in-doubt transaction to commit at your local database. It is a good idea to contact any other administrators you know that could also benefit from your investigation.

You contact the administrator of the sales database, who manually commits the in-doubt transaction using the global ID:. As administrator of the warehouse database, you manually commit the in-doubt transaction using the global ID:. After you manually force a transaction to commit or roll back, the corresponding row in the pending transaction table remains. The state of the transaction is changed depending on how you forced the transaction. Every Oracle Database has a pending transaction table.

This is a special table that stores information about distributed transactions as they proceed through the two-phase commit phases.

You can make the wrong choice if a pending transaction is forced to commit or roll back. For example, the local administrator rolls back the transaction, but the other nodes commit it. The RECO Recoverer background process uses the information in the pending transaction table to finalize the status of in-doubt transactions.

You can also use the information in the pending transaction table to manually override the automatic recovery procedures for pending distributed transactions. All transactions automatically resolved by RECO are removed from the pending transaction table.

Additionally, all information about in-doubt transactions correctly resolved by an administrator as checked when RECO reestablishes communication are automatically removed from the pending transaction table. When you issue a SQL statement, the database attempts to lock the resources needed to successfully execute the statement. If the requested data is currently held by statements of other uncommitted transactions, however, and remains locked for a long time, a timeout occurs. A DML statement that requires locks on a remote database can be blocked if another transaction own locks on the requested data.

If these locks continue to block the requesting SQL statement, then the following sequence of events occurs:.

Because the transaction did not modify data, no actions are necessary as a result of the timeout. Applications should proceed as if a deadlock has been encountered. The user who executed the statement can try to reexecute the statement later.

If the lock persists, then the user should contact an administrator to report the problem. A query or DML statement that requires locks on a local database can be blocked indefinitely due to the locked resources of an in-doubt distributed transaction.

In this case, the database rolls back the SQL statement immediately. If the lock persists, the user should contact an administrator to report the problem, including the ID of the in-doubt distributed transaction. The chances of these situations occurring are rare considering the low probability of failures during the critical portions of the two-phase commit. Even if such a failure occurs, and assuming quick recovery from a network or system failure, problems are automatically resolved without manual intervention.

Thus, problems usually resolve before they can be detected by users or database administrators. You can force the failure of a distributed transaction to observe RECO automatically resolving the local portion of the transaction or to practice manually resolving in-doubt distributed transactions and observing the results.

To intentionally induce a failure during the two-phase commit phases of a distributed transaction, include the following comment in the COMMENT parameter:. For example, the following statement returns the following messages if the local commit point strength is greater than the remote commit point strength and both nodes are updated:.

If enabled, RECO automatically resolves the transaction. The RECO background process of an Oracle Database instance automatically resolves failures involving distributed transactions.

At exponentially growing time intervals, the RECO background process of a node attempts to recover the local portion of an in-doubt distributed transaction. RECO can use an existing connection or establish a new connection to other nodes involved in the failed transaction. When a connection is established, RECO automatically resolves all in-doubt transactions. Rows corresponding to any resolved in-doubt transactions are automatically removed from the pending transaction table of each database.

For example, you can temporarily disable RECO to force the failure of a two-phase commit and manually resolve the in-doubt transaction.

Alternatively, the following statement enables RECO so that in-doubt transactions are automatically resolved:. An important restriction exists in the Oracle Database implementation of distributed read consistency.

The problem arises because each system has its own SCN, which you can view as the database internal timestamp. The SCNs in a distributed transaction are synchronized at the end of each remote SQL statement and at the start and end of each transaction.

Between two nodes that have heavy traffic and especially distributed updates, the synchronization is frequent.

Nevertheless, no practical way exists to keep SCNs in a distributed system absolutely synchronized: a window always exists in which one node may have an SCN that is somewhat in the past with respect to the SCN of another node.

Because of the SCN gap, you can execute a query that uses a slightly old snapshot, so that the most recent changes to the remote database are not seen. In accordance with read consistency, a query can therefore retrieve consistent, but out-of-date data. Note that all data retrieved by the query will be from the old SCN, so that if a locally executed update transaction updates two tables at a remote node, then data selected from both tables in the next remote access contain data before the update.

For example, you can issue an update statement and then commit the update on the remote database. You can use the following techniques to ensure that the SCNs of the two systems are synchronized just before a query:.

Because SCNs are synchronized at the start of every remote transaction, commit or roll back the current transaction before issuing the remote query. When setting the commit point strength for a database, note the following considerations:.

See Also: " Commit Point Site " for a conceptual overview of commit points. Prepared The node has prepared and may or may not have acknowledged this to its local coordinator with a prepared message. Committed The node any type has committed the transaction, but other nodes involved in the transaction may not have done the same. Forced Commit A pending transaction can be forced to commit at the discretion of a database administrator.

Forced termination rollback A pending transaction can be forced to roll back at the discretion of a database administrator. HOST Name of the host system. Solution to Condition 2: What we need to do at this point is; recovering our transaction from being an orphan by inserting some dummy records into dictionay tables so the views… and then force a rollback or commit: You do not have to change the parameters in the insert command other than the transaction id. Why RECO cannot perform in some cases?

STUCK 1. Stuck transactions can be examined under the below conditions: 2. Tagged 2pc , oracle , transaction , troubleshooting.