Re: Autonomous Transactions

devjnr@xxxxxxxxx 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/
.

Relevant Pages

  • Re: Locking and Delay in a Bottleneck
    ... that should avoid the gap issue on rollback, ... SQL Server MVP ... the saving transaction, this journal voucher is having a header table ... number in a table whereupon you commit. ...
    (microsoft.public.sqlserver.programming)
  • Re: Trigger does not seem to fire from front end or enterprise manager
    ... there is no need to even do COMMIT in a trigger. ... > the transaction will be committed as the statement completes. ... > against the inserted and deleted tables inside your trigger code. ... run a profiler trace and you will see what SQL EM submits. ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL Server Analysis Services Query in a SQL Server job step
    ... My guess is that each request (begin transaction, execute processing, ... Try out the MSDN Forums for Analysis Services at: ... I have a step (SQL Server Analysis Services Query) inside of a 2005 SQL ... begin, commit, rollback transactions all within the code. ...
    (microsoft.public.sqlserver.olap)
  • Re: Behavior of Connection.commit()
    ... Does it say "commit" and "rollback" and other SQL commands that affect the transaction state are not allowed? ... How sure can the driver ever be about the state of the transaction? ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: Really wiered problem. ALT+TAB closes database connection. Please help.
    ... Apart from checking the SQL Server timeouts, you should verify that you are ... not permitting the user to perform a search _during_ a transaction, ... I have my application that uses ADO to connect to SQL 2000 database. ... The main app, ...
    (microsoft.public.vb.general.discussion)