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;