How to lock transactions that cannot be ended autonomously

Question

An abnormal table lock was detected in the production environment. Tracking revealed a transaction that lasted for several hours, which was conspicuous in a production setting where transactions typically last no more than 30 seconds. Monitoring through Dynamic Management Views (DMVs) showed that its SQL text was constantly changing, indicating its activity, but the locks it held were only increasing without being released. It was later found to be caused by an explicit transaction in a stored procedure that was not properly closed.

Please execute the following SQL in order to reproduce the issue:

/*Create test objects, ensuring that the session isolation level is set to the default Read Committed before execution*/
if object_id('TestTable') is not null drop table TestTable
create table TestTable(id int)
insert into TestTable values(1)
go
if object_id('TestProc') is not null drop proc TestProc
go
create proc TestProc
as
begin
begin tran
if 1=1 return
commit
end
go
/*Execute the following SQL manually in Session 1 first:*/
exec TestProc
/*Manually execute the following SQL in Session 1 again:*/
update TestTable set id = id+1
/*Finally, execute in Session 2:*/
select * from TestTable

After executing the final SQL in Session 2, you will find a blockage, which comes from Session 1. I have summarized this phenomenon as a transaction that cannot be ended autonomously.

The issue occurred in "TestProc", and I naively thought that locking it would help pinpoint the problem. The DMV queries I know only show "update TestTable set id = id+1", but that is not helpful.

I have asked this question before, but perhaps due to unclear expression, no one commented. This time, I have used a new translation tool to reorganize my language and ask for help. I believe I have explained the situation clearly. Finally, I hope everyone can help come up with ideas, any thoughts and approaches can be shared. Thank you very much.

Answer 1

Unfortunately, error handling is not SQL Server's best game.

To some extent, you can protect yourself by using SET XACT_ABORT ON. With this setting most errors causes execution to be aborted and open transactions to be rolled back. But there are exceptions, and one such example is error 266, which is the error you get when you exit a stored procedure with a different trancount from when you entered.

It is important to have TRY-CATCH in your SQL code as well as good error handling in your client.

And of course, you should avoid making mistakes like these in the first place.