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

Question

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.

Answer 1

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.

Answer 2

Some More

David's answer is correct, but to add a few more things that may be important...

You can generally infer from an execution plan and query that:

• Seeks will start with key locks
• Scans will start with page locks
• Local locking hints will usually override other settings (rowlock may not always be honored though)
• Foreign Keys with cascading actions will take serializable locks
• Indexed view maintenance will take serializable locks when the view definition spans multiple tables

Some things can only be determined at runtime, and some only under concurrency. For example, lock escalation may be attempted, but may not always be successful due to competing lock incompatibility on the object. There may also be some local factors that change locking behavior, like changing index options around allowing row and page locks, etc.

To monitor locks, you can use:

• sp_WhoIsActive @get_locks = 1;
• SELECT * FROM dbo.WhatsUpLocks(@@SPID) AS wul;

You can monitor locks taken by using an Extended Event session like this one from a previous answer of mine: Offline Index Rebuild on a Partitioned Table.

But fair warning, you will need to alter it to be compatible with Azure SQLDB. You'll need to change references to SERVER to DATABASE, and you'll need to choose between storing the file in Blob Storage or just using the Ring Buffer instead. You will also need to hit slightly different views when attempting to query the session data if you choose to use that over the GUI.