When is a nested transaction required?

Question

Even a transaction is nested, it will not be updated until the transaction itself is committed. So, what is the meaning of a nested transaction and what specific situation does this function require?

Answer 1

See links

http://msdn.microsoft.com/en-us/library/ms189336.aspx

http://beyondrelational.com/blogs/diligentdba/archive/2011/04/22/nested-transactions-and-savepoints.aspx

http://www.codeproject.com/KB/database/sqlservertransactions.aspx

Answer 2

For example, suppose a situation like this:

Class A { /// props save(); } class B { A a{get;set}; // other props save(); } 

now when you want to save B, you first save A suppose that while saving A you have any utility calls to check, etc., this is the situation when saving B (some checks), so you need to roll back if B may not be verified, also you should rollback when you cannot confirm A, so you had to invest one (actually Separation of anxiety , you can mix all things and have spaghetti code without nested transactions).

Answer 3

Nested transactions are not called.

The only transaction that SQL considers is the outermost one. This is the one that committed or rollback. Nested transactions are syntactically valid, that's all. Suppose you call a procedure from within a transaction, and this procedure has transactions in itself, the syntax allows you to nest transactions, however the only one that has any effect is the most external.

edit: Link here: http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(2630)-nested-transactions-are-real.aspx