Is a SQL Server transaction holding a lock on resources when the client aborts the operation?
I receive a regular question “What does the SQL Server sleeping state mean , and are resources locked?”
Let’s investigate .
Activity Monitor lists information about SQL Server processes. A session in the sleeping state means a client connection without an active query.
But , If a client :
1)creates a session ,
2)submits a long running transaction
3)No commit or rollback is executed
4)Client application states a query timeout of 60 seconds and the transaction hasn’t completed (or the connection is broken )
5)The SQL server session will go into a sleeping state. And maintain locks
To test , the steps above do the following –
On the SQL Server . Assuming there is a “testable”
CREATE PROCEDURE MYSPSLEEPING
AS
BEGIN TRAN
INSERT INTO testtable (ID,avalue) VALUES(1 ,'myvalue')
WAITFOR DELAY '0:5:10'--
ROLLBACK
Connect via SQLCMD – on the DOS command window
SQLCMD -U testlogin -P testloginpw -S MYSERVER\INSTANCE1 -q "exec MYSPSLEEPING"
In the Activity monitor – a new Process is created and locks are established .
After a few seconds , or before the WAITFOR DELAY limit , terminate the operation from the command window .
The process is now in SLEEPING state
And the locks remain :
In this case I have simulated a connection issue , but I see this problem often with application design query timeout
Issue a DBCC OPENTRAN on the database -
Oldest active transaction:
SPID (server process ID): 60
UID (user ID) : -1
Name : user_transaction
LSN : (14174:2928:2)
Start time : May 18 2011 9:43:49:047AM
SID : 0x49cbb5d2cd807a419b45c2795ef32db2
Speak to the application design team – requesting details about application query timeouts.
Source:Jack Vamvas (http://www.sqlserver-dba.com)
