Identify applications with locks

Question

In my environment there is a database Sybase ASE 15, that I need to replace with a Microsoft SQL Server 2000-r2. Many users access this database via some applications, that sometimes "forget" to commit a transaction, then idle indefinitely keeping the table lock. This has a terrible effect: all other applications queue to obtain a lock on the tables affected, and are effectively stuck. In Sybase I use a query that tells me which user is causing the problem; I can then either kill the task, or even go to him/her and find and correct the problem in the application. This is the Sybase query:

select l.spid, SysLogin=s.name,SysObject=o.name, dbname
  from master..syslocks l, master..sysprocesses p, SIAM..sysobjects o, master..syslogins s
 where o.type='U'
   and p.spid=l.spid
   and l.id=o.id
   and p.suid=s.suid

The output produced looks like this:

81  john    authors     maindb
88  mary    authors     maindb
88  mary    books       maindb

Unfortunately, syslocks does not exist in Mssql, at least not in the version I am using. How do I convert the query to work on Mssql? Of course, if there is an alternative way of achieving the same result that would be great.

Answer 1

You might get it to work using only system procedure sp_lock. Although it's deprecated and it will be removed in a future version, it still works on SQL 2008 R2 and SQL 2012.

USE master;
GO
EXEC sp_lock;
GO
EXEC sp_lock 53; -- 53 is the spid for some specific user session;
GO

Another way is to use the DMV sys.dm_tran_locks.

Select *
from sys.dm_tran_locks dl
join sys.sysprocesses sp on dl.request_session_id = sp.spid

I think that a more clearer way is to use Adam Machanic's procedure WhoIsActive which has a specific parameter for showing locks for a session.

Use them all and choose whatever suits you best, but the WhoIsActive procedure provides more info than just locks.

Answer 2

You're looking for transactions that have been left open. Try "DBCC OPENTRAN('db_name') WITH TABLERESULTS, NO_INFOMSGS" to get the currently open transactions, including when they were started. That would be a good starting point. There are probably some fancier DMV's you could use - but DBCC OPENTRAN works across a whole bunch of different versions (SQL Server 2000+).

Answer 3

Take 2, the newer DMV based solution.

SELECT des.session_id, OBJECT_NAME(resource_associated_entity_id) [Entity], des.login_name, db_name(der.database_id) [database] FROM sys.dm_exec_requests der JOIN sys.dm_exec_sessions des ON des.session_id = der.session_id JOIN sys.dm_tran_locks dtl ON des.session_id = request_session_id WHERE command NOT IN ('BACKUP LOG', 'BACKUP DATABASE', 'DBCC') AND resource_type <> 'DATABASE' AND open_transaction_count > 0 AND start_time > dateadd(minute, -2, getdate())

Lists the data you want for open transactions that have current locks. There is a whole bunch more information you can get out of it, but this is close to what you get in SYBASE. I've excluded non-DML commands and database locks (of which you'll get one per connection).