Handling nested transactions in Sql Server : Do rolling back out transaction roll back the inner committed transaction too ?????? big question...

-
SQL Server supports nesting transactions, however, writing nested transactions is usually not a good idea. The problem with nested transactions is that ROLLBACK and COMMIT statements affect multiple open transactions. Nested transactions usually occur when one stored procedure begins a transaction and then calls other procedures also using transactions before commiting the initial transaction. However, a single stored procedure could also have multiple transactions open at the same time.

Despite the common belief that nested transaction problems can be mitigated by using named transactions, ROLLBACK and COMMIT statements don't behave as you would expect from the first glance. ROLLBACK TRANSACTION only maintains the name of the outermost transaction ; if you try rolling back any transaction except the outermost one, your statement will fail. The COMMIT TRAN statement, on the other hand will not fail; however, it will only commit the inner transaction, without releasing the exclusive locks until the outermost transaction is explicitly committed or rolled back. If the outermost transaction is rolled back, then all inner transactions are also rolled back, regardless of whether or not you previously committed the inner transactions.

The transaction nesting level can be examined by querying the global variable @@trancount. In the following example, we'll look at how nested transaction commands behave. This example is extended from the Error Checking with Transactions section:

Comments

Post a Comment

Popular posts from this blog

Authorize.net Integration eCommerce Payment Gateway ( Direct Post Method New! )

-
https://developer.authorize.net/integration/fifteenminutes/csharp These steps assume that your web server is on the public internet and can be accessed via a domain name or IP address. Step 1:Sign up for a Test Account. Sign up for a test account to obtain an API Login ID and Transaction Key. These keys will authenticate requests to the payment gateway. Step 2: Download the Authorize.Net C# SDK and include it in your project Download the SDK Create a new ASP.NET MVC application and add a reference to the AuthorizeNET.dll from the SDK Step 3: Modify web.config. Add the AuthorizeNET.Helpers namespace to the web.config, under system.web/pages: ... Step 4: Create a checkout form that posts directly to Authorize.Net Add the following code to the Home/Index View - this will create a form which will submit a test transaction to Authorize.Net (with hardcoded values). This code uses the SDKs CheckoutFormBuilder to create the form for you - just include your API login and Transa
Read more

Get Organised with OneNote

-
One note is greate tool to get organised and in todays world of information explosion. you may need any set of information weather financial, personal, or expenses or your assets your insurence etc. onenote is the best way and easiest way to manage this info. only thing to do is create templates for eash of these stuffs and you can add info as and when required. you can also, attach the scanned documents with the records in onenotes. For example, monthly phone bill. Below is good link which explains how to utilise onenote and how to find better templates for your use. Click Here
Read more

Test your Sql Query Online

-
Hi Guys i found a good tool to test simple Sql query online the link is as below. This time i used this to test the pivot functionality. The Pivot Sql Function converts the row wise category data into column wise metrix/pivot kind of data. http://www.sqlfiddle.com/#!3/b228e/3 another good example is below http://sqlfiddle.com/#!6/2ba07/131
Read more