How an Update on table X can lock table Y?

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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%' 

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply