January 19, 2022 at 2:26 pm
I am investigating a Query Timeout failure in our overnight batch. This is a simplified version of what has happening:
The process that failed was trying to insert a row into a table (table_a):
Insert table_a
(col1,col2)
VALUES
('Bah','Humbug')
However, at the same time there was another process running which updated a different (table_b) but had a join to table_a
update b
SET Col1 = 'Foo'
From dbo.table_b b
JOIN dbo.table_a a
on b.col2 = a.col2;
This update took 5 minutes (it is updating millions of rows).
The question is whether the first query trying to insert a row into table_a will be blocked by the long running update query? We don't explicitly set the transaction isolation level so I am assuming that it is READ COMMITTED.
January 19, 2022 at 3:38 pm
It certainly can. It sounds like the update took a table lock on table_a to handle the big update.
January 20, 2022 at 5:15 pm
Do you have an index on table_a that covers the UPDATE query? That could help prevent some contention.
You could also try to "help" SQL by explicitly "telling" SQL about the range of keys to be UPDATEd (useful only if there is an index that can be used for the UPDATE).
update b
SET Col1 = 'Foo'
From dbo.table_b b
JOIN dbo.table_a a
on b.col2 = a.col2
and a.col2 BETWEEN (SELECT MIN(col2) FROM dbo.table_b) AND (SELECT MAX(col2) FROM dbo.table_b);
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply