Post reply

deadlock on parallel create indexes?

  • May 11, 2007 at 2:40 pm

    #116801

    Am working with a warehousing installation that nightly imports a massive amount of data. Before the import, indexes are dropped and afterwards, the indexes are regenerated. The "create index" steps are now being multithreaded-- distributed over several threads. However, I'm finding in cases when the create indexes are operating on the same source table, a thread may become a deadlock victim.

    This is on SQL Server 2000. Haven't tried it on 2005, as it's not that easy to try at the moment.

    So I gather that while it's indexing, since it can't afford to have the table change while indexing, it keeps it locked-- but in such a way that is blocking reads as well? I can't otherwise see how such a deadlock would occur. The threads are doing a create index on the same table but creating a different index, and generally using different columns, though there may be cases where one index is a composite field which would include a column also utilized in another index. But essentially, it's a single read-only operation from the contending table so I don't really see why a deadlock should occur.

    Any ideas what may be going on here? Options I might be able to use to work around the problem (other than going single-thread, preferrably)...

  • May 11, 2007 at 6:04 pm

    #706024

    Ys if you try to create index on the same columns via diff jobs or diff transactions there is a high chance od deadlock. create index creates a table level lock even if the index is only on a column. Its best so group indexes from same table NEXT to each other in a job so there is no contention between them.

    ******************
    Dinakar Nethi
    Life is short. Enjoy it.
    ******************

  • May 11, 2007 at 11:57 pm

    #706034

    I wouldn't think it would need a table level lock that would lock out read access, only write access as create index doesn't modify the table data itself, just generating an index on a column.

    Is this situation still true in SQL Server 2005?

    --

    Sync

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

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