Finding out which locks that are acquired in a query on SQL Server?

Question

I have a SQL statement from my application. I'd like to know which locks that statement acquires; how can I do that with SQL server? The statement has been involved in a deadlock, which I'm trying to analyze; I cannot reproduce the deadlock. I'm running on MS SQL Server 2005.

Answer 1

run a trace in the profiler (pick the blank template), select the deadlock graph event, and on the new tab that appears (Events Extraction Settings), save each (check save deadlock XML events separately) in its own file. Open this file in an xml viewer and it will be easy to tell what is happening. Each process is contained, with a stack of procedure calls, etc. and all locks are in there too.

Let this trace run until the deadlock happens again, info is only recorded when a deadlock happens, so not much overhead. If it never happens again, good it is solved, if not you have captured all the info.

Answer 2

Hi,

I would suggest that you turn on the Deadlock Detection Trace Flags in the first instance, rather than running a Profiler Trace indefinitely.

This way, the event details will be logged to the SQL Server Error Log.

Review the following Books Online reference for details of the various trace flags. You need to use 1204 and/or 1222

http://msdn.microsoft.com/en-us/library/ms188396(SQL.90).aspx

Be sure to enable the trace flags with server scope and not just the current session. For example use:

DBCC TRACEON(1222,-1)

Answer 3

You can run the statement in a transaction, but not commit the transaction. Since the locks will be held until the transaction is committed, this gives you time to inspect the locks. (Not indefinitely, but like 5 minutes by default.)

Like:

BEGIN TRANSACTION
select * from table

Then open Management Studio and check the locks. They're in Management -> Activity Monitor -> Locks by Object or Locks by Process. After you're done, run:

COMMIT TRANSACTION

to free the locks.

Answer 4

You can run a profiler trace on your dev box for the queries and see exactly what locks are taken. This is typically a huge amount of data, but much of it will be patterns you can skim over. E.g. for read committed isolation, you will see a succession of locks being acquired and released as you do a table or index scan (each row must be locked before it is read, and it is immediately released after it is read).

What isolation do you run under? What kind of queries are deadlocking? Are you using explicit transactions that encompass multiple updates, or are they single statements deadlocking?

The most typical case for a deadlock is a transaction with the sequence (update table x, update table y), and a second transaction with the sequence (update table y, update table x). The common solution is to make sure you use the same update sequence across queries.

Let us know what kind of queries they are, there are different common issues for different types of transactions.

Answer 5

Here's a query that will show you all active locks, who's got them, and what object they are on. I pulled this from a technet article or something years and years ago. It works on SQL 2000 and 2005 (change sysobjects to sys.objects for 2005.) Uncomment the WHERE clause if you want to restrict it to just this databse, and just the "EXCLUSIVE" locks.

select 'Locks' as Locks,
    spid, nt_username, name, hostname, loginame, waittime, open_tran,
    convert(varchar ,getdate() - last_batch, 114) as TimeSinceLastCommand,
    case req_mode
    when  0 then 'Not granted'
    when  1 then 'Schema stability'
    when  2 then 'Schema modification'
    when  3 then 'Intent shared'
    when  4 then 'Shared intent update'
    when  5 then 'Intent shared shared'
    when  6 then 'Intent exclusive'
    when  7 then 'Shared Intent Exclusive'
    when  8 then 'Shared'
    when  9 then 'Update'
    when 10 then 'Intent insert NULL'
    when 11 then 'Intent shared exclusive'
    when 12 then 'Intent update'
    when 13 then 'Intent shared-update'
    when 14 then 'Exclusive'
    when 15 then 'Bulk operation'
    else str(req_mode) end as LockMode
from master..syslockinfo
    left join sysobjects so on so.id = rsc_objid
    left join master..sysprocesses sp on sp.spid = req_spid
--where rsc_dbid = (select db_id()) and ltrim(req_mode) in (6,7,11,14)

Answer 6

Troubleshooting deadlocks:

http://blogs.msdn.com/bartd/archive/2006/09/09/747119.aspx http://blogs.msdn.com/bartd/archive/2006/09/13/751343.aspx

Reproducing deadlocks:

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/01/01/reproducing-deadlocks-involving-only-one-table.aspx