FIX: Creating Indexes in Parallel May Fail with Deadlock on Sysindexes (295373)
The information in this article applies to:
- Microsoft SQL Server 2000 (all editions)
This article was previously published under Q295373
BUG #: 236561 (SHILOH_BUGS)
SYMPTOMS
Creating multiple indexes at the same time on a table may fail with a deadlock on sysindexes. One of the CREATE INDEX statements will fail with the following error:
Server: Msg 1205, Level 13, State 3, Line 1
Your transaction (process ID #8) was deadlocked with another process and has been chosen as the deadlock victim. Rerun your transaction.
Trace flag 1204 can be used to capture deadlock information to the SQL Server errorlog. You can identify a deadlock on sysindexes by a KEY value of xx:2:1 (where xx is the database ID) in the errorlog:
Deadlock encountered .... Printing deadlock information
2000-10-02 11:51:39.82 spid4 Wait-for graph
2000-10-02 11:51:39.82 spid4
2000-10-02 11:51:39.82 spid4 Node:1
2000-10-02 11:51:39.82 spid4 KEY: 5:2:1 (60000b20631c) CleanCnt:1 Mode: X Flags: 0x0
2000-10-02 11:51:39.82 spid4 Grant List::
2000-10-02 11:51:39.82 spid4 Owner:0x19171080 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:56 ECID:0
2000-10-02 11:51:39.82 spid4 SPID: 56 ECID: 0 Statement Type: CREATE INDEX Line #: 5
...
2000-10-02 11:51:39.82 spid4 Requested By:
2000-10-02 11:51:39.82 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec:(0x19447a58) Value:0x191705c0 Cost:(0/0)
2000-10-02 11:51:39.82 spid4
2000-10-02 11:51:39.82 spid4 Node:2
2000-10-02 11:51:39.82 spid4 KEY: 5:2:1 (61004a117805) CleanCnt:1 Mode: X Flags: 0x0
2000-10-02 11:51:39.82 spid4 Grant List::
2000-10-02 11:51:39.82 spid4 Owner:0x1916f540 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:55 ECID:0
2000-10-02 11:51:39.82 spid4 SPID: 55 ECID: 0 Statement Type: CREATE INDEX Line #: 5
...
RESOLUTIONTo resolve this problem, obtain the latest service pack for SQL Server 2000. For additional information, click the following article number to view the article in the
Microsoft Knowledge Base:
290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack
WORKAROUND
Do not create multiple indexes on a table at the same time.
STATUSMicrosoft has confirmed that this is a problem in SQL Server 2000. This problem was first corrected in SQL Server 2000 Service Pack 1.
| Modification Type: | Major | Last Reviewed: | 11/6/2003 |
|---|
| Keywords: | kbBug kbfix kbSQLServ2000sp1fix KB295373 |
|---|
|