ORA-01591: Lock-pending Distributed transaction processing solution

Live newspaper has a function to go down, the background log error: Java.sql.SQLException:ora-01591: Lock is pending distributed transaction processing 657.7.39336 hold.

Solution:

Rollback force ' 657.7.39336 '; --execution may be slower

When execution is complete, query dba_2pc_pending,

SELECT * from dba_2pc_pending s where s.local_tran_id= ' 657.7.39336 ';

657.7.39336 sp4gd.a6dfea73.657.7.39336forced rollback no 2015-6-17 5:28:05 2015-6-17 10:44 : 2015-6-17 5:28:05 Oracle UNKNOWN SCDB02 lca_zc 14456764049772
or
Delete from sys.pending_trans$ where local_tran_id = ' 657.7.39336 ';
Delete from sys.pending_sessions$ where local_tran_id = ' 657.7.39336 ';
Delete from sys.pending_sub_sessions$ where local_tran_id = ' 657.7.39336 ';
commit;
Commit force ' 657.7.39336 '
exec dbms_transaction.purge_lost_db_entry (' 657.7.39336 ');

DBA_2PC_PENDING describes distributed transactions awaiting recovery. Describes a distributed transaction awaiting recovery.

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 decision (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 do you mean by this mistake?

[[email protected] ~]$  oerr ora 01591
01591, 00000, "lock held by In-doubt distributed transaction%s"
//*cause:  trying to ACC ESS resource that's locked by a dead two-phase commits
//         transaction that's in prep Ared state.
//*ACTION:DBA should query the pending_trans$ and related tables, and attempt
//        &NBS P;to Repair network connection (s) to coordinator and commit point.
//         IF timely repair is not possible, DBA should contact DBA at commit
//  & nbsp      point If known or end user for correct outcome, or use heuristic
//        &NB Sp;default if given to issue a heuristic commit or abort command to
//         finalize the L ocal portion of the distributed transaction.

two phase commit (2PC)
The two-phase commit protocol ensures strong data consistency, and many distributed relational data management systems use this protocol to accomplish 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 to solve the consistency problem. This algorithm can solve a lot of temporary system failures (including process, network node, communication and other failures), is widely used. However, it cannot be configured to resolve all failures, and in some cases it requires an artificial engagement to solve the problem.
as the name implies, two-phase submissions are divided into the following two phases:
1) Prepare Phase (Preparation node)
2) Commit Phase (submission phase)
1) Prepare Phase
during the request phase, the coordinator notifies the transaction participants that they are ready to commit or cancel the transaction before entering the voting process. During the voting process, participants will inform the facilitator of their own decision: consent (the transaction Contributor local job execution succeeds) or cancel (local job execution failure).

In order to complete the quasi-preparation phase, other database nodes, except commit point site, follow these steps:
Each node checks whether it is referenced by other nodes and, if so, notifies the nodes to prepare for submission (into the prepare phase).
each node checks its own running transaction, and if it finds that the locally running transaction does not have the operation to modify the data (read-only), skip the next step and return a read only to the global coordinator.
if the transaction needs to modify the data, the transaction is assigned the appropriate resources to ensure that the modifications are working correctly.
when the above work is successful, the global coordinator is returned with ready-made information, and conversely, the failed information is returned.
2) Commit Phase
at this stage, the facilitator will make a decision based on the poll results of the first stage: Commit or Cancel. The coordinator notifies all participants to cancel the transaction when and only if all participants agree to submit the Transaction Coordinator to notify all participants to commit the transaction. The action that the contributor will perform after receiving a message from the coordinator.
 
The submission phase follows these steps:
the global coordinator notifies commit point site to commit.
commit Point site commits and notifies the global coordinator when it is complete.
the global coordinator notifies other nodes to commit.
other nodes commit local transactions and release locks and resources when they are finished.
The other node notifies the global coordinator that the submission is complete.
3) End Stage
The global Coordinator notifies the commit point site that all node commits are complete.
Commit the Point site database to release all resources related to the transaction, and then notify the global coordinator.
the global coordinator frees the resources that it holds.
Distributed Transaction End
In General, a two-phase commit mechanism can run well, and when a participant goes down during a transaction, after a reboot, it can be known that the transaction was committed without being asked by another participant or facilitator. Of course, the premise is that each participant will write the log in advance of each step of the operation.


The only dilemma that the two-phase commit cannot solve is when the coordinator goes down after the commit message is sent, and the only participant who receives the command goes down, this time the transaction is in an unknown state, and no one knows whether the transaction was committed or not committed. This requires the involvement of the database administrator to prevent the database from entering an inconsistent state. Of course, if there is a premise that all nodes or network anomalies will eventually recover, then this problem will not exist, the coordinator and the participants will eventually restart, the other nodes will eventually receive a commit message. This is also in line with cap theory. http://blog.itpub.net/48010/viewspace-1016050/

The following is a brief introduction to distributed transactions.

A distributed transaction, in short, refers to the local and remote execution of a transaction, and the local need to wait for confirmation of the remote transaction at the end of the next local operation. In the case of Dblink update a row of records of the remote database, if the network exception during the execution, or other events caused the local database to be unable to know the execution of the remote database, an in DOUBLT error occurs. DBA intervention is required and needs to be handled in a variety of situations.

The two-phase commit mechanism of a distributed transaction goes through 3 stages:

1.PREPARE PHASE:

1.1 Decide which database is the commit point site. (Note that the database with the high commit_point_strength value in the parameter file is the commit point site)

1.2 Global coordinator requires all points (except commit point site) to be ready for commit or rollback. At this point, the table for the distributed transaction is locked.

1.3 The node for all distributed transactions informs the global coordinator of its SCN.

1.4 The global coordinator takes the maximum SCN of each point as the SCN of the distributed transaction.

At this point, all the points are ready and we begin to enter the commit phase phase, at which point all transactions except the commit point site are in the doubt state until the commit phase phase is finished.

2.COMMIT PHASE:
2.1 Global Coordinator uploads the maximum SCN to commit point site, requiring its commit.
2.2 Commit point tries commit or rollback. Distributed transaction lock release.
2.3 Commit point notifies global coordinator that a commit has been made.
2.4 Global Coordinator notifies all points of a distributed transaction to commit.

3.FORGET PHASE:
3.1 Participating points notice that the commit point site they have completed commit,commit the dot site can forget (forget) this transaction.
3.2 Commit Point site clears the distributed transaction information on the remote database.
3.3 Commit Point site notifies global coordinator that local distributed transaction information can be cleared.
3.4 Global Coordinator clearing Distributed transaction information

ORA-01591: Lock-pending Distributed transaction processing solution