Skip to Main Content
  • Questions
  • a procedure to rollback in-doubt transactions.

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Colin .

Asked: May 02, 2000 - 1:21 pm UTC

Last updated: June 03, 2013 - 2:49 pm UTC

Version:

Viewed 10K+ times! This question is

You Asked

I am trying to create a script which will find any
In-Doubt transactions and then roll them back. I know what they
are and this methodology is OK.
However I cannot insert the variable correctly into the Rollback
or Purge commands.
The print statement work correctly. Please help!!

Here is my Script....

SET serveroutput on
VARIABLE my_trans_id1 VARCHAR2(22);
DECLARE
v_trans_id2 VARCHAR2(22);
BEGIN
SELECT local_tran_id
INTO v_trans_id2
FROM dba_2pc_pending;
DBMS_OUTPUT.PUT_LINE ('My Variable in the PL/SQL Block is: '||
(v_trans_id2));
:my_trans_id1 := v_trans_id2;
END;
/
print my_trans_id1;
ROLLBACK FORCE 'my_trans_id1';
EXECUTE dbms_tranaction.purge_lost_db_entry(my_trans_id1);




and Tom said...

Well, you cannot use bind variables in DDL at all and my_trans_id1 would be
a bind variable. Here i would suggest using dbms_sql and dynamic sql --
especially since you may have >1 transaction to process (and youre select
into will start failing). I'm thinking something like:


declare
cursor_name pls_integer default dbms_sql.open_cursor;
ignore pls_integer;

procedure execute_immediate( p_sql in varchar2 )
is
BEGIN
dbms_sql.parse(cursor_name, p_sql, dbms_sql.native);
ignore := dbms_sql.execute(cursor_name);
dbms_sql.close_cursor(cursor_name);
END;

begin
for x in ( select local_tran_id from dba_2pc_pending )
loop
dbms_output.put_line( 'My variable is ' || x.local_tran_id );
execute_immediate( 'rollback force ''' || x.local_tran_id || '''' );
dbms_transaction.purge_lost_db_entry(x.local_tran_id);
end loop;
end;
/


if you really want to do it as separate commands, you'll need to use macro
variables -- not bind variables. It might look like:

column local_tran_id new_value TRANS_ID
select local_tran_id from dba_2pc_pending;
rollback force '&TRANS_ID';
exec dbms_transaction.purge_lost_db_entry(&TRANS_ID);



Again -- that'll fail unless you don't have exactly 1 row in dba_2pc_pending
when you run this (you could add a where ronum = 1 to the query to let it
get one row at a time and just keeping running it until there is no more
data)



Rating

  (15 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

where will this occur

Thank you, September 29, 2001 - 8:29 pm UTC

Give me a scenario where I will have in doubt transaction and which I have to rollback dynamically.

I have never encountered this kind of a situation , but believe this is very useful.

Kindly illustrate with a scenario

Tom Kyte
September 29, 2001 - 8:41 pm UTC

The 2 phase commit protocol goes through 2 stages.

Before the commit you make updates to many sources -- say three databases. One of the databases is the coordinator.

When you issue COMMIT, the coordinator takes over. It will go out to each of the sites and ask them "are you ready to commit". They will answer YES or NO. This is the voting or PREPARE stage. After they VOTE yes, they must be prepared to commit.

When the coordinator collects the votes, it will determine if everyone said "YES". If they did -- the coordinator will commit itself and broadcast to the other participants "COMMIT" (else it broadcasts rollback).

It is during the period of time between the VOTING (prepare) phase and the broadcast that you may have a distributed in doubt transaction. Say SITE1 was the coordinator and SITE2 and SITE3 the participants. If after SITE2 voted "YES" to commit -- SITE1 crashed (or the network failed or whatever) -- SITE2 would be left hanging -- it would not know whether to commit or rollback the transaction. Data would be locked, rows would be "in doubt" as to their value. If the outage was long in duration -- the DBA at SITE2 could phone the other DBA at SITE1 and ask them "how did this transaction end up". The DBA at SITE1 could tell them it committed or rolled back and the DBA at SITE2 could force the in doubt distributed transaction.

In the normal course of operation -- this will not happen.

A reader, September 15, 2003 - 4:53 pm UTC

Tom,

Can i force a commit in another user session if its not a distributed database (local database).

Thanks..





Tom Kyte
September 15, 2003 - 8:00 pm UTC

nope, you can force a "rollback" (kill em), but not a commit

(you have NO idea if they are ready to commit or not)

A reader, September 15, 2003 - 10:35 pm UTC

Thanks Tom

How to rollback ?

Tony, September 16, 2003 - 9:24 am UTC

Rollback segment status is 'Partialy Active' even after restarting database. I think there are some pending transactions in the rollback segment. What should I do to do rollback the transaction?


In-doubt transactions locking up the database

Arun Gupta, October 10, 2003 - 1:21 pm UTC

Tom,
We have a heterogeneous system with one Oracle and one non-Oracle database. A third party TPM initiates the 2pc transactions. Once the transactions enter the prepare phase something happens and the TPM can neither commit or rollback. We are investigating this issue. The problem is that on the Oracle side, we observe that till we manually clear this in-doubt transaction, all the tables, even the ones which do not participate in 2pc transaction, are locked for reads. Is this expected behaviour? The administrator's guide says as:
=======================================================
Prepare Phase
The first phase in committing a distributed transaction is the prepare phase. In this phase, Oracle does not actually commit or roll back the transaction. Instead, all nodes referenced in a distributed transaction (except the commit point site, described in the "Commit Point Site") are told to prepare to commit. By preparing, a node:

Records information in the online redo logs so that it can subsequently either commit or roll back the transaction, regardless of intervening failures

Places a distributed lock on modified tables, which prevents reads
========================================================
The last line says that only the modified tables are locked. I have read lot of articles on Metalink, but cannot understand what is going on.
Thanks.

Please ignore the question

Arun Gupta, October 10, 2003 - 2:04 pm UTC

Tom,
Please ignore my post. It was a typical case of communication distortion when going through a noisy channel.
Thanks.

Help on in-doubt trans

V, April 07, 2006 - 4:07 pm UTC

I have Cloned a database from 1 server to another changing the dB name on the new instance. I notice I have in-doubt transactions now on the new instance. How can I kill them?

WHen I try to commit or rollback I get a ORA-2058 error

In-doubt transactions, 10.2.0.3

Martin Vajsar, July 21, 2008 - 1:49 am UTC

Tom,
in a project I'm working right now we need to perform distributed transactions. It seems that this can be done either via some middle-tier framework, which can manage distributed transactions on its own, or via database links, which is solution that I would prefer for various reasons (ability to manipulate data in pure SQL being the most important one to me). The database links would be between different 10.2.0.3 Oracle databases and in one case between Oracle DB and MS SQL Server. (SQL Server would be the remote DB)

Customer's DBAs argue against database links with two principal arguments:

a) database links can cause unresolved in-doubt transactions in case the connection between databases gets broken,

b) when using database links, username and password travel in plain text over the network.

Could you please tell me whether the following is true:

1) if the connection between middle-tier framework and one (or both) of the databases gets broken in the middle of the two-phase commit, the situation is basically the same as if the database link gets broken - that is, results in hanging in-doubt transaction,

2) when I modify data over DB link in the remote database only in single transaction (eg. exporting data to other DB), this will not result in distributed transaction,

3) is it really impossible to encrypt the password when initiating the DB link (the customer uses Enterprise edition).

If you would have any other comments on this situation, I'd be grateful.
Tom Kyte
July 22, 2008 - 10:19 am UTC

a) and so will the middle tier, if it does not, then middle tier is fundamentally BROKEN (think about it, two phase commit (2pc) is 2pc - whether you do it here, there or anywhere. If after all or some of the sites vote to commit - one of them becomes unavailable after voting but before hearing the overall outcome - that site is in the "unresolved state". Ask the DBA's whether they would like to

1) have a nice database set of stuff to deal with to fix the problem, to resolve the in doubt transactions

2) have to hope that the developers wrote enough logging code in the middle tier for someone to a) figure out something went wrong, b) figure out what went wrong, c) fix it


I opt myself for "give me the database, it is all logged, documented and fixable in the database - I know what I'll see when a failure occurs - in the middle tier - I have no idea - and remember - it is ALL DATA, we protect DATA, that is our job"



the passwords in current releases of the database are stored encrypted.

re: In-doubt transactions, 10.2.0.3

Stew Ashton, August 02, 2008 - 10:01 am UTC


"when using database links, username and password travel in plain text over the network."

I would bet anything that usernames and passwords travel over the network the same way, whether the client is a middle tier or another database. Why would any database supplier build specific login software just for database links?

Transaction does no exist

javed, February 03, 2011 - 4:06 am UTC

The results from two queries are as follows:
SQL> exec p ( ' select * from dba_2pc_pending where LOCAL_TRAN_ID = ''5.45.15590''');

LOCAL_TRAN_ID :5.45.15590
GLOBAL_TRAN_ID :PROD.WORLD.355ec77f.5.45.15590
STATE :commited
MIXED :no
ADVICE :
TRAN_COMMENT :
FAIL_TIME :2/3/2011 11:41:24 AM
FORCE_TIME :
RETRY_TIME :2/3/2011 12:43:58 PM
OS_USER :system
OS_TERMINAL :location01
HOST :location01
DB_USER :LOC01
COMMIT# :1995534655

PL/SQL procedure successfully completed.

SQL>
SQL> exec p ( ' select * from sys.pending_trans$ where LOCAL_TRAN_ID = ''5.45.15590''');

LOCAL_TRAN_ID :5.45.15590
GLOBAL_TRAN_FMT :306206
GLOBAL_ORACLE_ID :PROD.WORLD.355ec77f.5.45.15590
GLOBAL_FOREIGN_ID :
TRAN_COMMENT :
STATE :commited
STATUS :P
HEURISTIC_DFLT :
SESSION_VECTOR :00000001
RECO_VECTOR :00000001
TYPE# :0
FAIL_TIME :2/3/2011 11:41:24 AM
HEURISTIC_TIME :
RECO_TIME :2/3/2011 1:53:20 PM
TOP_DB_USER :LOC01
TOP_OS_USER :SYSTEM
TOP_OS_HOST :location01
TOP_OS_TERMINAL :location01
GLOBAL_COMMIT# :1995534655
SPARE1 :
SPARE2 :
SPARE3 :
SPARE4 :

PL/SQL procedure successfully completed.

I have some questions:
1)The error in the alert log says the transaction roll back. but the query shows the commit#. Does this mean it already committed?
2)This transaction is still in the sys.pending_trans$, does this mean
this transaction is still pending?

Tom Kyte
February 03, 2011 - 3:28 pm UTC

this is saying that it committed locally but it never heard "globally" if all other nodes finished. It had prepared - it committed, but it doesn't know if all of the other sites committed.


see
http://docs.oracle.com/docs/cd/E11882_01/server.112/e17120/ds_txnman007.htm#ADMIN12269

it walks through the steps you want to take.

Locks held by in-doubt txn

Gareth, December 14, 2011 - 10:12 am UTC

Hi Tom

Is it possible for a locked txn (which is in-doubt) to block inserts into the same table? How could I recreate this scenario in a test situation?

Thanks
Gareth
Tom Kyte
December 15, 2011 - 7:41 am UTC

Data block held?

Gareth, December 15, 2011 - 8:06 am UTC

Thanks for that, the scenario I've been trying to emulate has a function based index (non-unique).

From the documentation, I see that

> 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 theory, could a 'locked resource' mentioned above mean... a data block? And hence cause inserts to wait on the resolution of an in-doubt transaction...?

Just as an aside, and why I ask is that I see 'enq: TX - row lock contention' on INSERTs into the said table (which has no PK)...
Tom Kyte
December 16, 2011 - 6:19 am UTC

yes, it could be a data block. but it doesn't seem you have any failed/in doubt transactions - if it resolves itself quickly. why are you looking down that path?


is this a normal b*tree index?

distributed transaction

Gareth, December 16, 2011 - 7:05 am UTC

>yes, it could be a data block. but it doesn't seem you have any failed/in doubt transactions - if it resolves itself quickly. why are you looking down that path?

A little background (sorry to drip feed previously but am conscious this is a followup)

* we have a 'local' table and another table over a dblink
* the 'local' table is populated with 'new' rows by an application throughout the day (i.e processed_flag = 'N')
* a periodic PL/SQL job updates 'new' rows with a processed flag of 'Y' and the data is inserted into the remote table - once inserted then the daemon job commits (i.e. a distributed transaction).

The failed/in-doubt transaction occurred when the remote system failed. The application's inserts into the 'local' table appeared to be blocked... just wondering how this could be possible?

>is this a normal b*tree index?

It's a function based index on a single column, it is b*tree (not bitmap), basically to identify already processed rows:
DECODE(proc, 'Y', NULL, 'N')


Tom Kyte
December 16, 2011 - 5:41 pm UTC

yes, this could be a problem with the in-doubt transaction. You've got a small index, you have updated an N to a Y - which will modify that index block - which we might be trying to modify during an insert of an N record.

COMMIT POINT SITE

Gareth, January 03, 2012 - 6:48 am UTC

Following up from above, would we avoid blocking 'application' INSERTS if the 'local' database had a higher COMMIT_POINT_STRENGTH than the 'remote' (they're equal at the moment)?

A reader, June 02, 2013 - 1:35 pm UTC

Hi Tom ,I m little new to DBA group but in my production environment .in-doubt not cleared after force roll-back and dbms-purge package .On regular basis ,we facing issue this issue .Is there any parameter which impact force roll or force commit to indoubt traction .
Tom Kyte
June 03, 2013 - 2:49 pm UTC

if you are frequently having to force these - that means your network is pretty horrific - or your databases crash often.

there are no "parameters" affecting the frequency of this - this is more related to the overall stability of the environment.

so - is your network that bad?
how often are you doing a distributed commit? (and why?)

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library