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):
However, at the same time there was another process running which updated a different (table_b) but had a join to table_a
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.