Post reply

Blocking during bulk inserts

  • December 9, 2014 at 12:40 pm

    #295817

    Hey all,

    We are noticing a lot of blocking when performing bulk inserts.

    Sometimes the lead blocker(s) are blocking inserts into the same tables, but most of the time, it's insert to other tables. So, while bulk insert into table X is running, bulk insert into table Y is blocked from the insert into X.

    - We turned off transactions from the application performing the bulk inserts - no change.

    - We enabled table locking from the application - no change

    - We enabled lock_on_bulk_load on the tables - this seems to have helped; however still see some blocking.

    According to the lock_on_bulk article - When you specify table locking for a bulk import operation, a bulk update (BU) lock is taken on the table for the duration of the bulk-import operation, shouldn't we be seeing this BU lock? Instead, we see nothing but LCK_M_X (exclusive table locks). EDIT: Just found out that in order get a BU lock, no indexing can exist on the table ... sure be nice if that was in the article!

    Also, we saw the exclusive locks happening before we made these changes, meaning it wasn't using the row locks that it should be by default. Assuming we're missing something here with lock escalation. I am profiling just for lock escalation, and it never happens ...

    So I guess my pending question at this time is that why when inserting into table X do inserts into table Y get blocked?

    Any suggestions?

    Thanks

  • December 9, 2014 at 2:52 pm

    #1763662

    triggers? foreign key constraints? ok out of ideas!

  • December 9, 2014 at 2:53 pm

    #1763663

    No and no 🙁

  • December 9, 2014 at 10:52 pm

    #1763692

    Adam Bean (12/9/2014)

    - We turned off transactions from the application performing the bulk inserts - no change.

    What "application"???

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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