Nested Transaction in SQL Server 2012

Nested transaction is nothing but, you can make another transaction without even committing/Rollback previous transaction. With Every begin the value of @@Trancount increases by one and this is counter balanced by commit statement. As shown in image below. But in case of rollback it directly falls back to zero.

 

Lets experiment this;

Assume i have a table “Columns_for_Transaction_Example” having some values in it

Begin Nested Transaction:

here the value of Trancount will become 1. And new value would be like this as shown below

Let’s begin another transaction:

 

now Trancount will change to 2

 

lets check the value in table after the update, as  we can see offset value will become 0.

so now lets commit one by one , As shown in transaction tree figure trancount will decrease one by one ,can’t reduce to zero directly.

similarly if we do rollback,Trancount decreases directly to zero As shown below.

How SQL Transactions Affect if not committed or rolled back

We have discussed little about SQL transactions in my previous post.Today lets discuss behavior of transactions in different scenarios.Imagine a scenario where transaction has begun but neither committed yet ,nor rolled back.

let’s consider a table for example which is taken from system table:

/*Create a table Columns_for_Transaction_Example */

Select * into COLUMNS_FOR_TRANSACTION_EXAMPLE from sys.syscolumns

 

/*Fetch table #Columns_for_Transaction_Example for particular Id */

select object_name(id) Names,id,xtype,length,offset

from Columns_for_Transaction_Example where id=2089058478

This would be the result:

Let’s check the TRANCOUNT now:

That shows that there is no pending transaction, or all the transactions are committed.

Let’s come back to the scenario we were discussing about.How this scenario is going to affect Transaction in this database.Lets begin the transaction.

 

In this transaction named “MyTransaction” which deletes the values from a table having id=2089058478. On execution I found like 4 rows are affected.

Let’s check the Trancount:

This shows there is one uncommitted transaction in this database.

 

Let’s see what happens if I query something from this table from another session(Another query window).

You can see my snapshot which says it executing for past 9 minutes, Hence you cannot make any transaction on this table until the transaction is committed or rolled back.

What if you query that table with query hint [nowait] ? You get message like table is locked. This means the pages are protected from reading uncommitted data.

 

So now, we can either rollback the changes or we can commit if we think its fine to do so. As soon as we rollback or commit the Trancount reduces to “0”.If we rollback, then changes made will be reverted back. If we commit then the changes will be committed to the page.

Rollback Transaction Mytransaction;

COMMIT TRANSACTION Mytransaction;

Transactions in SQL Server 2012

 

Well i am learning transaction these days, So thought of sharing my earnings with all. well this is very useful when we need to have good control on transaction. Or in case of failure/error we can handle the transaction like roll back the changes . During learning process i referred couple of blogs and MSDN as well.

Transactions:

Basically as simple it is, transaction is any task performed in SQL Server by our command given. To be precise smallest unit of task called transaction. For example, reading data from table, inserting data to the table we say its transaction.

When we use select statement, we read data. It’s transaction. We update table, again we enter data to the specified table its transaction. Similarly we give any command from SSMS, read data or write information to memory/Table.

Transactions are having standard property called ACID:

• Atomicity: Guaranty the task, for its success or failure.
• Consistent: Ensures that the database properly changes states upon a successfully committed transaction.
• Isolation: Ensures that the database properly changes states upon a successfully committed transaction.
• Durability: Ensures that the result or effect of a committed transaction persists in case of a system failure.

Sometimes we need to control over the transactions we made, thus SQL provides some commands to us, to control the transaction for DML (Select, Insert and Update). These commands are:

• Begin Transaction
• Commit Transaction
• Rollback Transaction
• Save Transaction

 

This becomes Important to talk about Global Variable @@TRANCOUNT when we are discussing about Transactions. This is the variable which keeps the value ,or number of times Transaction happened which is not yet committed. If there is no Transaction then value in this variable would be zero.

We can discuss about this in Next blog.