- Open the SQL Server Query Analyzer and run the following batch but cancel the transaction before it completes:
Update authors set state = 'CA'
waitfor delay "00:02:00" --Cancel the command
- View the locks that are held by executing the following command:You see that locks are held for the authors table.
- From the same server process id (SPID), execute the next batch:
Update titleauthor set au_ord = 0
Commit Tran - Completed transaction.
- View the locks that are held by executing the following command: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: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.
Update titles set royalty = 0
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