June 9, 2005 at 12:30 am
Hi guys,
I'm working in a vb6 project with a data environment and I need a way to handle nested transactions with it.
I know that with DE's you can't directly creat a tran when another one is running. I've tried a few options but I didn't make it work.
¿Could anyone help me?
thx a lot.
regards,
Corcho.
June 10, 2005 at 4:23 am
Hi,
not sure I understand the question. If your DB provider supports nested transactions, so will ADO. I have done it in VB6 with SQL Server2000.
What I have observed is that SQLOLEDB allows ONLY one connection in scope of a transaction (Q272358) but I am not sure that applies to you.
Is that of any help?
June 10, 2005 at 4:40 am
SQL Server does not support nested transactions as you might think, example:
BEGIN TRANS T1 WITH MARK 'My Bad Transaction'
-- data changes
BEGIN TRANS T2
-- data changes
IF 1=1
ROLLBACK TRANS T2
ELSE
COMMIT TRANS T2
COMMIT TRANS T1
Will actually roll back the T2 Transaction and the T1 Transaction
Here is one cheat that I tried:
BEGIN TRANS T1 WITH MARK 'My Better Transaction'
-- data changes
SAVE TRANS S1
BEGIN TRANS T2
-- data changes
IF 1=1
ROLLBACK TRANS S1
ELSE
COMMIT TRANS T2
COMMIT TRANS T1
The trouble lies in the need to track the @@TRANCOUNT value, if your transactions are performed by the application. This is not a problem if Transations are always handled from within the T-SQL routine.
Plus by default OLE DB and ODBC connections are in autocommit transaction mode, which means that there is an automatic BEGIN TRANS before you ADO_Connection.Execute your 1st SQL and when it is finished there will be an automatic COMMIT TRANS, or ROLLBACK TRANS if there is an error. This also means that you have no control over the nesting. SET IMPLICIT_TRANSACTIONS OFF changes this to Implicit transaction mode.
I tried to implement individual ADO_Connection.Execute Statements and tracking the @@TRANCOUNT value at the application level, which worked as long as no one tried to perform:
SET IMPLICIT_TRANSACTIONS ON
BEGIN TRANS T1 WITH MARK 'My Failed Transaction'
-- data changes
SAVE TRANS S1
COMMIT TRANS T1
BEGIN TRANS T2
-- data changes
SAVE TRANS S2
BEGIN TRANS T3
-- data changes
IF 1=1
ROLLBACK TRANS S1
ELSE
COMMIT TRANS T3
COMMIT TRANS T2
IF 1=1
ROLLBACK TRANS S2
ELSE
COMMIT TRANS T1
SET IMPLICIT_TRANSACTIONS OFF
Which of course they did, so the ROLLBACK TRANS S1 failed.
I never figured out how to handle this kind of nesting at the App level, so gave up use of nested transaction except within Stored Procedures, and even then the autocommit transaction mode will still mess with you.
To recap the real issue, ROLLBACK TRANS always affects the outer most transaction level (except for ROLLBACK TRANS save_point), which provides for workaround for "flat" nesting. You are on your own for any other types of nested Transactions. Also check out the Transaction affects caused by the very loosly defined "batch".
Good Luck.
Andy
June 10, 2005 at 7:25 pm
Thx both, It looks that someone in my team got a solution to my problem. Thx anyway.
Corcho.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply