CODEWITHSUNDEEP
.netsqlado.netsqltransaction
Jonathan Allen
Jonathan Allen

Reputation: 70307

SqlTransaction and nested transactions

Is it possible to create nested transactions with a the SqlTransaction class? If so, what are the rules/limitations that I need to be aware of?

Upvotes: 1

Views: 1612

Answers (3)

Tomas Vavrda
Tomas Vavrda

Reputation: 101

You can use Save(string savePointName) method on the SqlTransaction class. This will create a save point to which you can rollback inside the transaction. So if part of your code fails, you will rollback to the previous save point and start over.

Example:

SqlTransaction tran = _transaction as SqlTransaction;
tran.Save(savePointName);

and when you fail you will do:

tran.Rollback(savePointName);

You can do this as many times you want. That will solve your problem of nested transactions.

Upvotes: 1

JNK
JNK

Reputation: 65157

Unfortunately SQL Server ignores commits on the inner transactions, as in this MSDN article.

You can nest transactions, but be aware the behavior may not be what you expect.

Nothing is committed until the outermost transaction commits.

So, in the following...

transaction A
Query A

transaction B
Query B

Commit B
Rollback A

The result of Query B is not actually committed to the database.

Upvotes: 3

Earlz
Earlz

Reputation: 63835

Yes it's possible to create nested transactions.

For instance:

transaction A
do action #1
transaction B
do action #2
transaction C
do action #3

rollback transaction B
transaction D
do action #4
commit transaction D
transaction E
do action #5

commit transaction A

with this sequence, only actions 1, 4, and 5 will have actually happened. Nested transactions have no limitations other than the limitations of a transaction by itself that I'm aware of. The only thing that must be understood is that nothing is actually committed until the "top level" (the top of the nest) transaction is committed.

Upvotes: 0

Related Questions