Deadlocking on parallel create indexes?

[kdd21]

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)...

 

[ptheriault]

To work around the problem you can set the MAXDOP 1 option on your create index statement.



- Paul

- If at first you don't succeed, find out if the loser gets anything.