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
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.
There are several options, and you must find the link between the tables.
Check for hidden references:
- Foreign Keys
- 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
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 🙂