sqlsql-servert-sqllockingtransaction-isolation

How can I verify that my query is causing no locks?

Suppose that I'm having a bad day and my confidence just isn't with me. Suppose also that I've written a query with set transaction isolation level read uncommitted. After it runs for a few minutes, I get scared that I've messed something up and could be causing locks. By opening another query window, how can I verify that my query is definitely not causing locks?

Assume that I have full admin-level access everywhere. The classic procedures of Adam Mechanic's and Brent Ozar are already installed.

Solution

  • To see the locks held by session 67 (for example) you can use

    SELECT *
FROM sys.dm_tran_locks
WHERE request_session_id = 67

    And to see whether session 67 is causing blocking to other sessions you can use

    SELECT *
FROM sys.dm_os_waiting_tasks
WHERE blocking_session_id = 67 AND session_id <> blocking_session_id