I put a breakpoint in my code to pause the execution before transaction is commited or rolled back. Then I'd like to see the current state of the database, but when I set in ssms the transaction isolation level to read uncommited and run the query against the tables affected by the paused transaction it gets locked and waits until transaction is finished.
Why does this happen, and is it possible to disable locking?
My crystal ball told me that the transaction that you'd paused had made schema modifications.
Such modifications take out [Schema Modification locks] (Sch-M)(https://technet.microsoft.com/en-us/library/ms175519%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396):
This means the Sch-M lock blocks all outside operations until the lock is released.
And this includes even being able to compile your read uncommitted using query because:
The Database Engine uses schema stability (Sch-S) locks when compiling and executing queries
Which makes some sense - schema modifications could include adding or removing columns so other queries can't know what the current layout of data on disk/in memory actually means.
Even for your case where all you've done is disabled constraints, the optimizer would usually make use of constraint information when planning a query - e.g. whether a check constraint can actually be trusted or not.