Will insert process be blocked?

  • 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

    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.


  • It certainly can. It sounds like the update took a table lock on table_a to handle the big update.

  • 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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply