rollback transaction for single phase commit

[vamshidhar nimbagiri]

I have requirement that i need to insert into two databases i.e., sql and DB2.
But our code not using two phase commit.
The requirement:
i am inserting into sql-database table first by creating one transaction and doing commit after that, and next creating another transaction to inert into DB2-database table and doing commit.
Problem is: if it fails in second transaction its not doing rollback for sql-database table, its doing rollback for DB2-database table only.

Plz any one can help me in this


vamshidhar
scjp
[ December 13, 2005: Message edited by: vamshidhar nimbagiri ]

[Jeanne Boyarsky]

Vamshidhar,
Welcome to JavaRanch!

Is there any reason you can't use an XA (2 phase commit) driver? That would be a lot easier than simulating one.

If not, the key is to do all the updates to both database before committing either one. There is still a small window for failure - between the two commits - but at least it is smaller.

[vamshidhar nimbagiri]

Hi Jeanne,
Thanks for your quick reply.
The problem is the existing code is done long before and we dont want to include 2-phase commit and ejbs as of now.

i thought of 2 solutions will you tell me weather this is correct:
1.Do you have any idea that DBlink work in SQL server.
2. Can i use savepoints for SQL server and rollback it once it fails.

or If you have any other solution plz tell me.

Thanks,
vamshi

[vamshidhar nimbagiri]

Hi can u tell me weather i can do setSavepoint and rollback on UserTransaction interface.


Thanks

[Jeanne Boyarsky]

Vamshi,
A savepoint will have the same problem. Once you have committed, you can no longer roll back to a savepoint. The idea with savepoints is that they provide an interim rollback point.

2 phase commit is just the driver. if you are using a DataSource, this won't affect your actual Java code.

I don't know anything about dbLink so I can't comment on whether or not that will help.

[vamshidhar nimbagiri]

Thanks Jeanne,

I thought if we setsavepoint the prior to the savepoint can be rollbacked even if we do commit.

But do any body have solution for this problem.

Thanks in Advance,
vamshi

[vamshidhar nimbagiri]

Hi All,

Do any body knows about DBlink? if yes..
Plz send me some links related that or some notes how to use it?

Thanks in Advance
vamshi

[Jeanne Boyarsky]

Vamshi,
I recommend posting a new thread in this forum that includes the word dblink in the subject. People who know about dblink (of which I am not one) are more likely to see it that way.

[vamshidhar nimbagiri]

Thanks Jeanne