I know everyone will be thinking why am i posting about BEGIN TRANSACTION and COMMIT TRANSACTION which are the very basic and common thing that every one is known about it and why and where we we need to use this statement in sql statements/queries  or in our code. But  sometimes, we make such kind of small mistake with this BEGIN and COMMIT statement that make a cause of deadlock in our application and when it happens no one thinks that this could be the reason of the dead lock. The same problem i faced sometimes back.

So the issue was, i have one store procedure which has some logic and on the basis of that some data Insert/Delete into the table. So in that store procedure there was a BEGIN transaction statement at the beginning but there was no COMMIT transaction statement at the end so whenever any statement executes on that table the resources for that connection never frees because there was no COMMIT transaction and due to this for the particular table resources blocked (means the table is locked to do any operations) and whenever you execute a query against this table the execution time will be infinite or you will get some kind of MSSQL error message for this. So I just wanted to tell this small mistake can make a big issue. Every time make sure in your code or SQL statement that if you are using BEGIN TRANSACTION then you must use the COMMIT TRANSACTION at the end which makes all data modifications performed since the start of the transaction a permanent part of the database and frees the resources held by the connection.

If i am missing something please leave your comments and make this more knowledgable for everyone.

Related posts:

1. Insert value using Table Value Functions (TVF) Hello All, In my last article we discuss about Table...
2. Recompiling Stored Procedures in Sql Server In this post , we are going to discuss 1....
3. Real Simple Transactions C# The .NET Framework provides support for managing transactions from code...
4. How to search specific word or text in Database object ? Today i am posting very basic but very important topic...
5. Clearing the SQL Cache when Testing your TSQL in SSMS SQL Server uses an intelligent caching system to enhance performance....