Re: Autonomous Transactions

From: Serge Rielau <srielau_at_ca.ibm.com>
Date: Mon, 28 Aug 2006 10:06:36 -0400
Message-ID: <4lgbjbF1ourtU1@individual.net>

devjnr_at_gmail.com wrote:

>> In Oracle (and DB2 FWIW) transactions are always global.
>> If you want to open a new nested scope to be rolled back individually
>> you use a save point.

> You mean that I always have to explicitly commit or rollback sql
> commands before the use of a proc that should have commit or rollback
> inside itself?

Not exactly.
I'm taking a few guesses here based on your background: You don't have much SQL in your app.
Instead all of your SQL in buries in procedures which you execute. So in your MS SQL Server code a transaction equals one procedure execution.

Now what happens is that at the beginning of each procedure you start a transaction and at the end you either commit or rollback whatever the procedure does. All your TSQL procedure look the same that way. A nested procedure will start a new (nested transaction) and commit/rollback it's work.

Now. Mapping that to a DBMS with save points and global transactions you need to map all those begin transaction and commit/rollback statements to save points because save point can be nested and they operate exactly like your nested TSQL transactions.

There is one hitch that you have to consider: What do you do with the outermost procedure. The outermost procedure represents the interface to your app and you app does NOT contain any COMMIT/ROLLBACK. You have three choices:
1. Find out that you are in the outermost procedure and use transactions instead of save points in that case. This will match exactly T-SQL semantics.
2. You have a defined set of procedures which are only called by the app and you use commit/rollback there and save points everywhere else 3. You issue commit and rollback from the application.

Personally I prefer the third option. I think the app should always have the last say.

Not sure I explained it better this time. Maybe an Oracle expert can whip up some examples.

Cheers
Serge

-- 
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ondemandbusiness/conf2006/

Received on Mon Aug 28 2006 - 09:06:36 CDT