| TABLE gets in doubt locked in TWO PHASE COMMIT CASE (very challenging problem). [message #206741] |
Fri, 01 December 2006 05:36  |
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
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
