Sql debugging query run at same time as debugging in management studio - locked data hangs the query

Question

Scenario: I'm debugging some stored procedures and have hit my breakpoint.

I now want to execute some queries so I can further debug the issue. How do I do that?

I have tried executing query from a new query window, but it seems the debugging experience has locked all of the tables.

The immediate window seemed to suffer from the same problem.

Answer 1

I have tried executing query from a new query window, but it seems the debugging experience has locked all of the tables

This is exactly what would be happening at this point of execution in the stored procedures you are debugging, so this is a 'good' thing to discover. The 'hang' occurs because your Proc holds locks on this data (e.g. as the result of an INSERT or UPDATE) and has not yet committed its transaction. Other connections attempting to read the uncommitted data on an isolation level such as READ COMMITTED (usually the default), will block until the PROC releases these locks through a commit, or rollback.

You can read past (most) locks on the other adhoc debugging query connections by setting a weaker isolation level on that connection, e.g.

-- Separate debugging query when the breakpoint is set on the actual code
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM TableLockedByProcBeingDebugged WHERE ...

OR with NOLOCK

SELECT * FROM TableLockedByProcBeingDebugged (NOLOCK) WHERE ...

This should allow you to read the transient state of the data.

And just to state the obvious:

• Use this only for debugging - as the isolation level suggests, READ UNCOMMITTED isn't yet committed, and can return ghost reads and even duplicate reads during a page split.
• If the PROC is escalating locks (e.g. from Row to the whole Table), you will need to address this, e.g. by updating fewer records at a time.