ORA-01591: Lock pending Distributed Transaction Processing Solution, ora-01591 Transaction Processing

ORA-01591: Lock pending Distributed Transaction Processing Solution, ora-01591 Transaction Processing

On-site report has a function can not go on, background log error: java. SQL. SQLException: ORA-01591: Lock is pending Distributed Transaction Processing 657.7.39336 hold.

Solution:

Rollback force '2017. 123 ';-- Execution may be slow

Query DBA_2PC_PENDING,

Select * from DBA_2PC_PENDING s where s. local_tran_id = '2017. 123 ';

657.7.39336 SP4GD. a6dfea73.657.7.39336Forced rollbackNo 5:28:05 10:44:33 5:28:05 oracle UNKNOWN SCDB02 LCA_ZC 14456764049772
Or
Delete from sys. pending_trans $ where local_tran_id = '2017. 123 ';
Delete from sys. pending_sessions $ where local_tran_id = '2017. 123 ';
Delete from sys. pending_sub_sessions $ where local_tran_id = '2017. 123 ';
Commit;
Commit force '2017. 100'
Exec dbms_transaction.purge_lost_db_entry ('2017. 657 ');

DBA_2PC_PENDING Describes distributed transactions awaiting recovery. describes the distributed transaction to be restored.

LOCAL_TRAN_ID String of form: n. n. n; n is a number
GLOBAL_TRAN_ID Globally unique transaction ID
STATE Collecting, prepared, committed, forced commit, or forced rollback
Mixed yes indicates part of the transaction committed and part rolled back
Advice c for commit, R for rollback, else NULL
TRAN_COMMENT Text for commit work comment text
FAIL_TIME Value of SYSDATE when the row was inserted (transaction or system recovery)
FORCE_TIME Time of manual force demo-( null if not forced locally)
RETRY_TIME Time automatic recovery (RECO) last tried to recover the transaction
OS _USER Operating system-specific name for the end-user
OS _TERMINAL Operating system-specific name for the end-user terminal
HOST Name of the host machine for the end-user
DB_USER Oracle user name of the end-user at the topmost database
COMMIT # Global commit number for committed transactions

What does this error mean?

[Oracle @ standby ~] $ Oerr ora 1, 01591
01591,000 00, "lock held by in-doubt distributed transaction % s"
// * Cause: Trying to access resource that is locked by a dead two-phase commit
// Transaction that is in prepared state.
// * Action: DBA shocould query the pending_trans $ and related tables, and attempt
// To repair network connection (s) to coordinator and commit point.
// If timely repair is not possible, DBA shocould contact DBA at commit
// Point if known or end user for correct outcome, or use heuristic
// Default if given to issue a heuristic commit or abort command
// Finalize the local portion of the distributed transaction.

Two-phase commit (2 PC)
The two-phase commit protocol ensures strong data consistency. Many Distributed Relational Data Management systems use this Protocol to complete distributed transactions. It is a distributed algorithm that coordinates all distributed atomic Transaction participants and decides to commit or cancel (rollback. It is also an algorithm for solving consistency problems. This algorithm can solve many temporary system faults (including processes, network nodes, communications, and other faults) and is widely used. However, it cannot solve all faults through configuration. In some cases, it still requires human intervention to solve the problem.
As the name implies, two-phase commit is divided into the following two phases:
1) Prepare Phase (preparation node)
2) Commit Phase (submission Phase)
1) Prepare Phase
In the request phase, the Coordinator notifies the transaction participant to prepare to submit or cancel the transaction and then enters the voting process. During the voting, the participants will inform the coordinator of their decision: consent (successful execution of the local job of the transaction participant) or cancellation (local job execution failure ).

To complete the quasi-preparation phase, other database nodes except the commit point site follow these steps:
Each node checks whether it is referenced by other nodes. If yes, it notifies these nodes to Prepare for submission (entering the Prepare stage ).
Each node checks its own transaction. if it finds that the local transaction has not modified the data (read-only), It skips the subsequent steps and returns a read only to the global coordinator.
If the transaction needs to modify the data, allocate the corresponding resources for the transaction to ensure the normal modification.
After all the preceding operations are successful, the prepared information is returned to the global coordinator. Otherwise, the information about failure is returned.
2) Commit Phase
At this stage, the Coordinator will make a decision based on the voting results of the first stage: Submit or cancel. If and only when all participants agree to submit the Transaction Coordinator notifies all participants to submit the transaction, otherwise the Coordinator will notify all participants to cancel the transaction. After receiving a message from the Coordinator, the participant performs a response operation.
 
In the submission phase, follow these steps:
The Global Coordinator notifies commit point site for submission.
The commit point site is submitted, and the global coordinator is notified after submission.
The Global Coordinator notifies other nodes for submission.
Other nodes submit local transactions and release the locks and resources.
Other nodes notify the Global Coordinator that the submission is complete.
3) End Stage
The Global Coordinator notifies commit point site that all nodes have been submitted.
The commit point site Database releases all transaction-related resources and notifies the global coordinator.
The Global Coordinator releases its own resources.
Distributed Transaction ends
Generally, the two-phase commit mechanism works well. When a participant goes down during a transaction and restarts, you can ask other participants or coordinators, to know whether the transaction has been committed. Of course, the premise is that each participant writes logs in advance during each step of operation.


The only dilemma that cannot be solved by a two-phase commit is that when the Coordinator sends a commit message, the only participant who receives the command goes down, at this time, the transaction is in an unknown state. No one knows whether the transaction has been committed or not. This requires the intervention of the database administrator to prevent the database from entering an inconsistent state. Of course, if one premise is that all node or network exceptions will eventually be restored, the problem will not exist, and the coordinators and participants will eventually restart, other nodes will eventually receive the commit information. This is also in line with the CAP theory. Http://blog.itpub.net/48010/viewspace-1016050/

The following describes distributed transactions.

A distributed transaction, in simple terms, refers to a transaction being executed locally and remotely. The local transaction needs to wait for confirmation that the remote transaction is complete before proceeding to the next local operation. For example, if a row of records in the remote database is updated through dblink, if the network is abnormal during execution or other events cause the local database to be unable to know the execution status of the remote database, in this case, an in doublt error occurs. In this case, dba intervention is required, and the process must be performed in multiple situations.

The Two-Phase Commit mechanism of distributed transactions goes through three phases:

1. prepare phase:

1.1 determine which database is a commit point site. (Note: the database with the highest commit_point_strength value in the parameter file is the commit point site)

1.2 The Global Coordinator requires that all vertices (except the commit point site) be prepared for commit or rollback. In this case, the table of the distributed transaction is locked.

1.3 all distributed transaction nodes inform the global coordinator of its scn.

1.4 The Global Coordinator takes the largest scn of each point as the scn of the distributed transaction.

So far, all the points have completed the preparation work, and we start to enter the commit phase stage. At this time, the transactions at all points except the commit point site are in doubt State until the commit phase stage ends.

2. commit phase:
2.1 Global Coordinator uploads the maximum scn to the commit point site and requires its commit.
2.2 commit point: Try commit or rollback. Release the Distributed Transaction lock.
2.3 commit point indicates that the Global Coordinator has been commit.
2.4 Global Coordinator notifies all vertices of distributed transactions for commit.

3. forget phase:
3.1 The participating points notify the commit point site that they have completed the commit, and the commit point site will be able to forget the transaction (forget.
3.2 commit point site clears distributed transaction information on a remote database.
3.3 commit point site notifies Global Coordinator to clear local distributed transaction information.
3.4 Global Coordinator clear distributed transaction information