Alter Index statement causes unrecoverable deadlock

  • I just ran into an interesting little problem.

    SQL Server 2005 SP2 (9.0.3054) x64 8CPU (2 quad cores) 32GB memory (28GB allocated to SQL Server).

    We have one index that when we try to rebuild the index using ONLINE=ON - the process will cause an unrecoverable deadlock. The interesting thing is that the process is deadlocking on itself.

    My questions is: has anyone ever seen anything like this? Is this a known issue that could be corrected by upgrading to a later CU?

    Any other ideas?

    Thanks

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Hmm - haven't heard of this and it shouldn't happen. It can deadlock with user updates but then it should be chosen as the deadlock victim. I'd call Product Support as this seems like a bug.

    Thanks

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • By unrecoverable, do you mean the deadlock detector's not picking it up? Does having traceflag 1222 on result in a deadlock graph in the error log?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks - that is what I was afraid of. I guess we are going to open a ticket with Microsoft.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • GilaMonster (12/15/2008)


    By unrecoverable, do you mean the deadlock detector's not picking it up? Does having traceflag 1222 on result in a deadlock graph in the error log?

    By unrecoverable, I mean the deadlock detector is picking it up and giving me a stack dump. Specifically stating that this deadlock cannot be recovered.

    Date 12/15/2008 12:18:14 PM

    Log SQL Server (Archive #1 - 12/15/2008 12:18:00 PM)

    Source spid11s

    Message

    Deadlock monitor failed to resolve this deadlock.

    Server may require restart to recover from this condition

    Reproducible on a copy of the live database - by issuing an ALTER INDEX on the clustered index for that table. When I use ONLINE=ON, the process immediately deadlocks. Without it - the index rebuild happens in less than a second.

    The next thing I am going to try is forcing MAXDOP to 1 for this process and see what happens.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Ok...... Never seen that before.

    If it's stack dumping, you need to call customer support.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • For anybody who is interested, setting MAXDOP to 1 allowed the process to complete successfully, even with ONLINE=ON.

    So, it definitely looks like an issue with parallelism on this index. I so hope it is only this index and not any others. 🙂

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • GilaMonster (12/15/2008)


    Ok...... Never seen that before.

    If it's stack dumping, you need to call customer support.

    Yeah, that is what I figured was going to happen. For the short term, I will just modify my re-indexing procedure to not reindex this table/index online. I really don't like hard-coding in my procedures, but that is better than the alternative.

    Thanks.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • I thought I would come back here and give you all an update. I was able to actually reproduce the same issue on a newly created table. I created the table exactly the same as the original table, then loaded the new table with data from the first table.

    Ran the alter index online (parallel process) on the new table and it deadlocked.

    Here is how the table was created:

    CREATE TABLE dbo.Deadlock_Table (

    ID numeric(16,0) IDENTITY(1,1) NOT NULL,

    VisitID numeric(16,0) NOT NULL,

    ProviderID numeric(16,0) NOT NULL

    )

    GO

    CREATE CLUSTERED INDEX IX_Deadlock_Table ON dbo.Deadlock_Table

    ( VisitID ASC,

    ProviderID ASC

    )WITH (PAD_INDEX = off

    ,STATISTICS_NORECOMPUTE = off

    , SORT_IN_TEMPDB = off

    , IGNORE_DUP_KEY = off

    , DROP_EXISTING = off

    , ONLINE = off

    , ALLOW_ROW_LOCKS = On

    , ALLOW_PAGE_LOCKS = On

    , FILLFACTOR = 95

    )

    GO

    As you will notice, we have an IDENTITY created - but not indexed. A clustered index - but it is not unique, no primary key (is it really a table?). I have already talked to the vendor about this - still waiting to hear back from them on why the table was created this way.

    With the above table and almost 1.25 million rows - running the alter index online in parallel will cause the deadlock. I changed the clustered index to a non-clustered index and could not reproduce the problem. Added the clustered index on the identity column and made it the primary key - no problem.

    But, leave it setup as above - deadlocks every time.

    I have Microsoft looking at this issue, just to see if we can figure out exactly what is causing it. For now, I am avoiding the issue by not re-indexing this table online. I hope to have some changes from the vendor on the table structure that will also avoid the problem.

    It is still very strange that this table will deadlock like this.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • And one final update - adding a new non-clustered index on the ID column and making it the primary key fixed the problem. I could not reproduce the deadlock.

    Removed the above index - tried to rebuild the clustered index and it deadlocked again.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • interesting, in testing have you let it run for hours just to see if it ever stops or finishes?

  • We first noticed the issue on a Sunday morning, when we found that our nightly maintenance from Friday night was still running.

    So, yeah - it ran for a long time :w00t:

    It would never have completed because of the deadlock.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 12 posts - 1 through 12 (of 12 total)

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