How an Update on table X can lock table Y?

Question

WE are in a situation where a select is being blocked by an update. but the tables are different.

UPDATE [dbo].[TABLE_1]
SET ...

SELECT [t1].[field]
FROM [dbo].[TABLE_1] AS [t1]
WHERE...

This simple process is blocking this one when they run at the same time:

SELECT "A lot of fields"
FROM TABLE_TOTALLY_DIFFERENT
INNER JOIN [another table totally different] 
ON...
WHERE...

That makes no sense to me.

I can see even by looking at the pageID that the table being locked in this situation is the TABLE_TOTALLY_DIFFERENT.

but they have nothing to do with them.

How can I seek and identify why they're being locked?

Answer 1

There are several options, and you must find the link between the tables.

Check for hidden references:

• Foreign Keys
• Triggers
• Scalar UDFs
  • In Computed columns
  • In Check constraints

The locks on TABLE_TOTALLY_DIFFERENT might have been taken in a different statement that's part of a larger transaction and the UPDATE TABLE_1 is just the latest statement that shows up in the blocked_process_report.

There is another option. You have an Indexed view that combines these unrelated tables

I blog about this specific case here https://straightforwardsql.com/posts/is-lock-in-rcsi-enabled-database/

Here's a code for how to check for that

SELECT
    v.object_id
    , SCHEMA_NAME(v.schema_id) AS schemaName
    , v.name AS viewName
    , i.name AS indexName
    , dsre.referenced_schema_name AS refSchema
    , dsre.referenced_entity_name AS refObject
    , dsre.referenced_minor_name AS refColumn
FROM sys.views AS v
JOIN sys.indexes AS i
    ON i.object_id = v.object_id
    AND i.index_id = 1 /* Clustered */
CROSS APPLY sys.dm_sql_referenced_entities
(
    CONCAT
    (
        OBJECT_SCHEMA_NAME(v.object_id)
        , '.'
        , v.name
    )
    , 'OBJECT'
) AS dsre
WHERE dsre.referenced_entity_name LIKE '%TABLE_TOTALLY_DIFFERENT%'