Incomplete transaction may hold large number of locks and cause blocking

Article translations Article translations
Article ID: 295108 - View products that this article applies to.
This article was previously published under Q295108
Expand all | Collapse all

On This Page

SUMMARY

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.

MORE INFORMATION

The following example illustrates how locks are not released as a result of an unfinished open transaction:
  1. Open the SQL Server Query Analyzer and run the following batch but cancel the transaction before it completes:
    Begin Tran
    Update authors set state = 'CA'
    waitfor delay "00:02:00" --Cancel the command
    Commit Tran
    					
  2. View the locks that are held by executing the following command:
    sp_lock
    					
    You see that locks are held for the authors table.

  3. From the same server process id (SPID), execute the next batch:
    Begin Tran
    Update titleauthor set au_ord = 0
    Commit Tran - Completed transaction.
    					
  4. View the locks that are held by executing the following command:
    sp_lock
    					
    You see that although the last transaction is completed, locks are held on both the authors and titleauthors tables. The reason is that the first transaction did not complete and when the second transaction was executed from the same connection, it was treated as a nested transaction.

    You can view the transaction count by checking the @@trancount global variable by issuing the following statement:
    select @@trancount
    					
    This query returns 1, which indicates that one transaction is outstanding.

    Any further transactions that are executed from this connection are treated as nested. Locks continue to accumulate and are not released until a ROLLBACK is executed, which rollbacks to the outer most transaction or to a savepoint.
In continuing with the example, you can see how a rollback may cause a completed transaction to be negated by executing the following transaction from the same connection:
Begin Tran
Update titles set royalty = 0
Rollback
				
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:
If @@trancount > 0 rollback
				

REFERENCES

For 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:
251004 How to monitor SQL Server 7.0 blocking
271509 How to monitor blocking in SQL Server 2005 and in SQL Server 2000

Properties

Article ID: 295108 - Last Review: December 23, 2005 - Revision: 3.5
APPLIES TO
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 6.5 Standard Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Workgroup Edition
Keywords: 
kbinfo KB295108

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com