SQL Server - table seems to be blocked

Question

Using NHibernate, I have just attempted to save a graph of objects using NHibernate. The save failed due to a not-null constraint violation.

I am now finding that a table in the database corresponding to one of the objects in the graph now appears to be locked. I cannot query it. Whenever I try, it just sits there doing nothing until I cancel the query. This has been the case for about half an hour.

In the save code, the transaction is first committed and then disposed.

SELECT @@TRANCOUNT against the database gives 0.

Any ideas a) What's going on and b) How can I get the table back?

Thanks

David

Answer 1

If you have access to the database, try running sp_lock to see who has the table locked.

Edit: That will return records with several columns of information described here. One of those columns is spid. You can use sp_who (or sp_who2) to see who is logged in, and look for the spid holding the lock.

If you have "view server state" permission, you can also use sys.dm_tran_locks.

Answer 2

@@TRANCOUNT is scoped to the current connection.

Assuming you have sufficient permissions on the server.

select hostname,program_name, spid,text  from master.sys.sysprocesses 
outer apply sys.dm_exec_sql_text(sql_handle) s
WHERE open_tran>0

gives any connections with currently open transactions along with the last text executed by that connection. I'm using sysprocesses for the reasons here.

If the above query shows your culprit then you can get the spid and use kill <spidnumber> to unceremoniously roll it back.You might want to consider setting XACT_ABORT on for future queries so errors won't leave transactions open.

Answer 3

Are you sure you properly disposed of your session? That's usually the cause of such problems...

Answer 4

try running this:

SELECT
    r.session_id AS spid
        ,r.cpu_time,r.reads,r.writes,r.logical_reads 
        ,r.blocking_session_id AS BlockingSPID
        ,LEFT(OBJECT_NAME(st.objectid, st.dbid),50) AS ShortObjectName
        ,LEFT(DB_NAME(r.database_id),50) AS DatabaseName
        ,s.program_name
        ,s.login_name
        ,OBJECT_NAME(st.objectid, st.dbid) AS ObjectName
        ,SUBSTRING(st.text, (r.statement_start_offset/2)+1,( (CASE r.statement_end_offset
                                                                  WHEN -1 THEN DATALENGTH(st.text)
                                                                  ELSE r.statement_end_offset
                                                              END - r.statement_start_offset
                                                             )/2
                                                           ) + 1
                  ) AS SQLText
    FROM sys.dm_exec_requests                          r
        JOIN sys.dm_exec_sessions                      s ON r.session_id = s.session_id
        CROSS APPLY sys.dm_exec_sql_text (sql_handle) st
    WHERE r.session_id!=@@SPID

it will tell you who is blocking your query

Answer 5

Regardless of any other DB-specific behaviors, you should rollback the transaction immediately after an error. That shouldn't leave anything locked.