ORA-01591: lock held by in-doubt distributed transaction:
This error is encountered by many DBA's and cause problem by locking the distributed transaction process, and not letting the query go through, because the Two - Phase Commit Mecanism got an error somewhere.
Lets see what is a distributed transaction first:
What Are Distributed Transactions?
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.Note: If all statements of a transaction reference only a single remote node, then the transaction is remote, not distributed
Lets assume that we have a scenerio like the below image.
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;
There are two types of permissible operations in distributed transactions:
The following list describes DML and DDL operations supported in a distributed transaction:
The following list describes supported transaction control statements:
The distributed transaction process is verified by the Two Phase Commit Mechanism.
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.
In-Doubt Transactions
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:
- 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
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.
Manual Resolution of In-Doubt Transactions
You should only need to resolve an in-doubt transaction in the following cases:
- The in-doubt transaction has locks on critical data or rollback segments.
- The cause of the machine, network, or software failure cannot be repaired quickly.
Resolution of in-doubt transactions can be complicated. The procedure requires that you do the following:
- Identify the transaction identification number for the in-doubt transaction.
- Query the
DBA_2PC_PENDING
andDBA_2PC_NEIGHBORS
views to determine whether the databases involved in the transaction have committed. - If necessary, force a commit using the
COMMIT FORCE
statement or a rollback using theROLLBACK FORCE
statement.
Here are some codes to help you through the process:
This one brings in-doubt transactions:
select * from
DBA_2PC_PENDING where state='prepared'
This one prepares the rollback script for the transactions:
select 'rollback
force '''||local_tran_id||''';' from DBA_2PC_PENDING where state='prepared'
At last run the rollback with the transaction id:
rollback force
'29.34.42726';
It worked for me.
ReplyDeleteThanks
worked very well
ReplyDeleteWow. It helped.
ReplyDeleteThanks a lot.
Thanks a lot, it helped for me as well.:) and got an opportunity to learn a new and interesting topic.
ReplyDeleteIt worked for me, thanks a lot
ReplyDeleteIt helped me, Thanks a lot!!
ReplyDeleteIt worked for me too. Thanks
ReplyDeleteIron Man's New Iron Man's New Iron Man's New
ReplyDeleteIron Man's New Iron titanium white fennec Man's titanium rod New Iron Man's New mens titanium watches Iron titanium oxide formula Man's New microtouch titanium trim as seen on tv Iron Man's New Iron Man's New Iron Man's New Iron Man's New Iron Man's
f738g0tjakq380 real dolls,dual stimulator,sex chair,penis sleeves,dildos,cheap sex toys,G-Spot Vibrators,horse dildo,sex toys s638u3gtolb690
ReplyDelete