1

we have troubles regarding querying from a dynamic view in SQL Server. We basically check for an existing object lock on a table (CHANGELOG) from sys.dm_tran_locks with the @@spid in a stored procedure which does a few other things too.

In some cases, this leads to blocking of random pages, tables rids etc.

In the "SentryOne" Tool, we have blocking situations looking like this enter image description here

As there is no actual way to find out all locks from @@spid, how can it be possible that a simple select from a dynamic view actually blocks other tables? We execute the select in READ UNCOMMITED

The query that is displayed in line 170:

if exists (select NULL from sys.dm_tran_locks l with (nolock)
        where   l.resource_associated_entity_id = object_id('[dbo].[CHANGELOG]')    and
            l.request_session_id = @@spid and
            l.resource_type = 'OBJECT')

Does anyone have an idea how to avoid these blocks? Or are we missing some point and the select is not even the real reason for the block?

6
  • 2
    you are running multi statement transaction and tables are locked by previous commands Commented Nov 26, 2020 at 9:58
  • Thanks, but why is the statement with the select from dmv in the reason for the block?
    – Mac-Chiato
    Commented Nov 26, 2020 at 10:33
  • 2
    it's not the reason, it's just the last statement in a session which holds the locks. Please see this answer - dba.stackexchange.com/questions/199842/… Commented Nov 26, 2020 at 11:19
  • 1
    Note: I can not mark a comment as answer.
    – Mac-Chiato
    Commented Nov 26, 2020 at 12:08
  • 1
    I suppose admins will remove this question anyway :) Commented Nov 26, 2020 at 12:25

0

Browse other questions tagged or ask your own question.