Select the product you need help with
- Internet Explorer
- Windows Phone
- More products
Incomplete transaction may hold large number of locks and cause blocking
Article ID: 295108 - View products that this article applies to.
This article was previously published under Q295108
When a transaction is not completed either because a query times out or because the batch is cancelled in the middle of a transaction without issuing a COMMIT or ROLLBACK statement to complete the transaction, the transaction is left open and all the locks acquired during that transaction continue to be held. Subsequent transactions executed under the same connection are treated as nested transactions, so all the locks acquired in these completed transactions are not released. This problem repeats with all the transactions executed from the same connection until a ROLLBACK is executed. As a result, a large number of locks are held, users are blocked, and transactions are lost, which results in data that is different from what you expect.
The following example illustrates how locks are not released as a result of an unfinished open transaction:
The rollback rolls the batch back to the outermost transaction, even though there is a completed transaction (2) on titleauthors. The rollback on the completed transaction occurs because the completed transaction is treated as a nested transaction.
To avoid this kind of problem, check after each transaction to see if the transaction is complete by using the following statement:
REFERENCESFor a description of how to monitor blocking with SQL scripts in SQL Server 7.0 or SQL Server 2000, see the following articles in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/251004/ )How to monitor SQL Server 7.0 blocking
(http://support.microsoft.com/kb/271509/ )How to monitor blocking in SQL Server 2005 and in SQL Server 2000
Article ID: 295108 - Last Review: December 23, 2005 - Revision: 3.5