In SQL Server / Azure SQL is there a way to see the locks a query produced or wants to produce?



  • I'm trying to investigate a lock situation. I know how to see the current locks on DB by querying sys.dm_tran_locks but what I want is to take a specific SQL Query and having a kind of 'analysis' on it to calculate what locks it will generate. Like an execution plan for locks?

    It can also be a way where I run a query and afterwards I see what locks were generated, for me it's the same. I just need a way to log/see logs generated since I currently can only see 'real-time' locks being held.

    I specifically need to know as many details of those locks as possible: type, mode, object it's holding, etc.

    I'm on Azure SQL Database.



  • The easy way is to start a transaction, run the query and then examine sys.dm_tran_locks. But this will only show you locks that are held after the query, typically U or X locks, or S locks in REPEATABLE READ or SERIALIZABLE. eg

    if @@trancount > 0 rollback
    

    begin transaction

    --run some queries

    select *
    from sys.dm_tran_locks
    where request_session_id = @@spid

    rollback

    To see the complete history of lock acquisition and release for a statement use XEvents or Profiler to capture the lock:acquired and lock:released events for a session. Note that this trace is extremely verbose so don't attempt to capture it for a whole instance, or on a production server at all.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2