Nested Transaction in SQL Server 2000

[Fursten]

Hi,

Can someone tell me if SQL Server 2000 suports nested transactions? I read somewhere that SQL Server doesn´t support nested transaction so ADO.NET doesn´t too (against a SQL Server DataSource). Anyideia? Is there somewhere where I can read about that?

Thank you

 

[LakshmiKiran]

Hope this article helps..

SQL 2K allows nested transactions.

For e.g.

BEGIN TRAN 1 do something BEGIN TRAN 2 do something COMMIT TRAN 2 do something
COMMIT TRAN 1

It says that all transactions become permanenet only when the outermost transaction
is committed. In the above example till COMMIT TRAN 1 is committed, COMMIT TRAN 2
will not be committed to the database. Or to put it in other words; if between COMMIT
TRAN 2 and COMMIT TRAN 1 some error occure, TRAN 2 will be rolled back.

Now the question: From application point of view I don't any reason why I should have
nested transactions. what does it buy me. One level of transaction is enuf.

May be nested transaction has some resource benefits. Can anyone explain.

Now the question: From application point of view I don't any reason why I should
have nested transactions. what does it buy me. One level of transaction is enuf.

May be nested transaction has some resource benefits. Can anyone explain.

No, there are not any resource benefits, it's simply an issue of simplifying
programming.

Say that I write a stored procedures that adds a transaction to your account
statement. This procedure needs to update several tables:
* the account-statements table.
* the current-balances table.
* if the transaction is in the past, I must also update historic balances.
* the table that holds bookings for the general ledger. To achieve this I put all my
updates between BEGIN and COMMIT TRANSACTION.

Say now that you are writing a function that permits a user to move money from one
account to another. To carry out this, you call my stored procedure twice. However,
you can not tolerate that only one part of the transfer is carried out, so you start
a transaction before you make your calls to my procedure, and then you commit when
you are ready.

Had nested transactions not been permitted my procedure would have gone apeshit, when
it was called and there already was a transaction in progress. Or I would have had to
add tests to see if I was allowed to start my own transaction. And I had forgotten
that, you would not have been able to reuse my code.

http://www.microsoft.com/sql/techin.../2000/books.asp

and also this link..

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_md_06_66nq.asp