Problem with transaction rollback
0 posts in topic
Flat View  Flat View
TOPIC ACTIONS:
 

Posted By:   Audrey_Ng
Posted On:   Sunday, August 3, 2003 11:52 PM

Hi! I have a servlet that has to update some DB tables, post a request to a remote server, and depending on the server's reponse, either commit or rollback the transaction. I am using Oracle9iAS as the servlet/JSP engine and Oracle9 DB. The flow is as follows: 1/ Lookup datasource(for a Central Database) from jndi context 2/ retrieve DB connection 3/ start transaction by setting autoCommit to false 4/ use a callableStatement to execute a stored procedure. The stored procedure updates some tables in the Central DB as well as in a remote database through a Database link. 5/ open an SSLSocket to the remote server and send the post request 6/ depending on t   More>>

Hi!


I have a servlet that has to update some DB tables, post a request to a remote server, and depending on the server's reponse, either commit or rollback the transaction. I am using Oracle9iAS as the servlet/JSP engine and Oracle9 DB.


The flow is as follows:

1/ Lookup datasource(for a Central Database) from jndi context

2/ retrieve DB connection

3/ start transaction by setting autoCommit to false

4/ use a callableStatement to execute a stored procedure. The stored procedure updates some tables in the Central DB as well as in a remote database through a Database link.

5/ open an SSLSocket to the remote server and send the post request

6/ depending on the response, commit or rollack transaction.


The problem is that only the updates in the central database are being rolled back, the ones in the remote database are still there.


Is it that I should use a non-emulated datasource instead of the Oracle 9ias default emulated DS. I have read the oracle docs on non-emulated DS, and it says I have to specify a DBlink to the remote DB in the datasource config file. What confuses me is that my servlet code know nothing about the remote database, it just calls the stored procedure that will then update the remote DB. Does it mean that I have to connect directly to the remote DB and perform the updates instead of having it in the stored procedure.



Thanks in advance for your help.

   <<Less
About | Sitemap | Contact