Orphaned Locks - Idle Session - and XACT_ABORT

Sometimes you might see a situation where a session is holding locks, blocking other sessions for an extended period of time, but does not have an active request and appears to be Idle.  This can happen in a few different scenarios, but here we’ll outline one scenario and how being sure to use XACT_ABORT can help alleviate this situation. 

For more information on XACT_ABORT, refer to the following:

SET XACT_ABORT (Transact-SQL)
https://msdn.microsoft.com/en-us/library/ms188792.aspx

Below is an example showing orphaned locks and using XACT_ABORT.

First, create a table and load some data:

 create table ##test (id int)
go

insert into ##test values (1);
insert into ##test values (2);
insert into ##test values (3);
insert into ##test values (4);
insert into ##test values (5);

Then create a stored procedure that will have enough of a delay to cause a Command Timeout in .NET:

 create proc usp_AddNumber
(
    @id int,
    @newid int
)
as
begin tran
    update ##test set id = @newid where id = @id;
    waitfor delay '00:01:00.000';
commit tran
go

The following simple .NET code will call the stored procedure and fail due to a Command Timeout because of the WAITFOR exceeding the timeout allowed by the SqlCommand object (default is 30 seconds):

         private void btn_xact_btn_Click(object sender, EventArgs e)
        {
            SqlConnectionStringBuilder cnString = new SqlConnectionStringBuilder();

            cnString.InitialCatalog = "tempdb";
            cnString.IntegratedSecurity = true;
            cnString.DataSource = ".";

            using (SqlConnection cn = new SqlConnection(cnString.ToString()))
            {
                cn.Open();
                SqlCommand cmd = new SqlCommand("usp_AddNumber", cn);
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter p1 = cmd.Parameters.Add("id", SqlDbType.Int);
                p1.Value = 4;

                SqlParameter p2 = cmd.Parameters.Add("newid", SqlDbType.Int);
                p2.Value = 3;

                Int32 RowsAffected = cmd.ExecuteNonQuery();
            }
        }

BEFORE the timeout, we see the locks taken:

AFTER the timeout hits, we see the _same_ locks still in place:

If we search for a request for SPID 55 – the session ID holding the locks, we see nothing:

 select * from sys.dm_exec_requests where session_id = 55
  

When the timeout hits and the client sends a cancel or disconnects, we see the Attention event in SQL Server:

For more information on an Attention event, refer to MSDN here:

Attention Event Class

https://msdn.microsoft.com/en-US/library/ms190979(v=sql.110).aspx

One solution may be to terminate the process/application if that is feasible (but may not be if this is a middleware component or application server).  If you see the session id but it has no active request, then killing the session (kill 55) can also release these locks.  If I stop my .NET application – then my locks are gone.  I can also kill the connection.  However be aware that killing the connection (or hard terminating the application) may cause SQL Server to go into a lengthy rollback if the transaction has done a large amount of work.

If you want to know what application did this – which may or may not be useful based on the application architecture (if it is using middleware, etc..) - then we could:

 select s.host_name, c.client_net_address, s.host_process_id from sys.dm_exec_sessions s
    inner join sys.dm_exec_connections c
    on s.session_id = c.session_id
    where s.session_id = 55

You could then go to system ASKJ-W520, find process id 5748 and see what it is doing or was doing (again it could be a mid-tier web or application server and may not be useful).

So, if we change the stored procedure to:

 alter proc usp_AddNumber
(
    @id int,
    newid int
)
as
    set xact_abort on
    begin tran
        update ##test set id = @newid where id = @id;
        waitfor delay '00:01:00.000';
    commit tran
go

Then, when we hit the timeout, we have no leftover locks:

So this may be as simple as killing the connection and identifying the application to see what was done (though in middleware this may not be very helpful).  Again, remember – killing it may incur a lengthy rollback process. 

If this specific issue is causing you problems, you should start by figuring out why the processing is taking so long to see if you can shorten it through tuning or breaking it into smaller transactions.  Otherwise, you can consider increasing the timeout on the Command object.

- Jay