Home » RDBMS Server » Backup & Recovery » TABLE gets in doubt locked in TWO PHASE COMMIT CASE (very challenging problem).
TABLE gets in doubt locked in TWO PHASE COMMIT CASE (very challenging problem). [message #206741] Fri, 01 December 2006 05:36 Go to next message
rajyouradmirer
Messages: 1
Registered: December 2006
Junior Member
Hi,
I am facing a problem in our telecom software application. Below, I am describing the implementations and the problem facing:

1.) For our application, we have two oracle 9i databases (DB). Tables on both DBs are same.
2.) In a Stored Procedure on Primary Database, I have written one sql statement to update one record in a table and immediately after this update statement, I am writing another update statement for the same row and same table but to update on Secondary DB (accessing through @peer from primary DB stored procedure).

3.) The above situation works fine in a normal case. But, if let us suppose, somehow just before final commit on the secondary DB, secondary DB has gone down.

4.) Now our problem is, in this case this table on the primary DB gets locked. In fact, it is not allowing to read the data also.

What, we have understood from our analysis is:

1.) This is the case of updaing the data on two DBs and in this case oracle is following the TWO PHASE COMMIT mechanism.
After getting the final go ahead to commit the data on both the DBs, as soon as commit about to happens on secondary DB, this DB goes down and no final commit happend. So, primary DB is waiting for the secondary DB to comeback. But, due the secondary DB went down, commit never happens and primary DB has put the lock on this table. More interesting thing is it does not allow to read the data from this primary DB table.

I would like to know, how to overcome this problem?? Can anything be done to modify the data in the oracle table where it keeps the commit or transaction related informations???
ANy suggestions are welcome. Thanx in advance.

Regards
Rajan
Re: TABLE gets in doubt locked in TWO PHASE COMMIT CASE (very challenging problem). [message #207613 is a reply to message #206741] Wed, 06 December 2006 04:04 Go to previous messageGo to next message
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
Senior Member
I would suggest going as simple as possible at first. The more you make your triggers do them more you might have this problem?

And we did wright the insert, update, delete statements.

How about postponing and sending the updates out every hour or day?
Re: TABLE gets in doubt locked in TWO PHASE COMMIT CASE (very challenging problem). [message #208649 is a reply to message #206741] Mon, 11 December 2006 13:45 Go to previous message
manish mendiratta
Messages: 110
Registered: May 2002
Senior Member
Yea make it simple. You can create another table populate after you update this table and send information to remote DB after 1 min.
Previous Topic: script to take online backup of database
Next Topic: dbms_scheduler problem
Goto Forum:
  


Current Time: Sun Dec 04 14:35:37 CST 2016

Total time taken to generate the page: 0.08808 seconds