Sql Transaction doesn't release lock after commit

Question

I have run a script similar to the one below in a SSMS query window. After the script run successfully, some locks remained on affected tables. When I try to close the window, a messagebox is showed asking me if I want to commit or cancel the transaction.

After I choose one of the options, the locks are released. What can cause this behaviour?

begin tran
delete from tableA
delete from tableB
insert into tableB
insert into tableA
commit tran 

I'm connected to a remote Sql Server 2014 and running localy SSMS 2014

Thanks!

Answer 1

The following example illustrates how locks are not released as a result of an unfinished open transaction: 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

View the locks that are held by executing the following command:

sp_lock

You see that locks are held for the authors table.

From the same server process id (SPID), execute the next batch:

Begin Tran
Update titleauthor set au_ord = 0
Commit Tran - Completed transaction.

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

Reference: Incomplete transaction may hold large number of locks and cause blocking