alter index rebuild deadlock - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

alter index rebuild deadlock

Author

Topic

sim2012
Starting Member

3 Posts

Posted - 2012-11-12 : 22:03:13

hello, my server version is Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64).
I've an index rebuild maintenance plan to rebuild index of selected large tables on weekly basis during maintenance window.
Recently the server log showed deadlock message while the job was running.

11/04/2012 01:09:36,spid21s,Unknown,deadlock-list
11/04/2012 01:09:36,spid21s,Unknown,deadlock victim=process4f2fb88
11/04/2012 01:09:36,spid21s,Unknown,process-list
...
11/04/2012 01:09:36,spid21s,Unknown,resource-list
...
11/04/2012 01:09:36,spid62,Unknown,TID: 7: Online index builder(clustered index) was chosen as deadlock victim<c/> handling deadlock internally
11/04/2012 01:09:41,spid62,Unknown,TID: 8: Online index builder(clustered index) was chosen as deadlock victim<c/> handling deadlock internally
...
...
11/04/2012 01:09:41,spid23s,Unknown,deadlock-list

The "Alter index rebuild" command was deadlock itself during processing one large table.
What is the meaning of the message "handling deadlock internally" ?
Note both sql agent job and maintenance plan status were returned successfully.
I was wondering if the deadlock will be handled itself and the index rebuildind was automatically retried by the server.
Note there is no retry parameter is set on the sql agent job.

I've came acrossed below link. So is it suggested to turn maxdop=1 to prevent the cause of deadlock itself ?

[url]https://connect.microsoft.com/SQLServer/feedback/details/755384/index-maintenance-task-in-maintainace-plan-cannot-be-modified-to-set-the-maxdop-parameter[/url]

srimami
Posting Yak Master

160 Posts

Posted - 2012-11-20 : 09:21:47

Please refer to the link http://msdn.microsoft.com/en-us/library/ms178104.aspx for more information on Deadlock. Also, be it windows Operating System or Database the deadlocks are handled internally

sim2012
Starting Member

3 Posts

Posted - 2012-11-29 : 02:53:29

Thanks for the link. Actually the command "ALTER INDEX" generated multiple threads and caused deadlock itself due to the page lock contention.
And I noticed the transactonname is OnlineIndexInsertTxn.

I've found below wordings in this link http://www.google.com/url?sa=t&rct=j&q=OnlineIndexInsertTxn+sql+server+2005&source=web&cd=1&ved=0CCwQFjAA&url=http%3A%2F%2Fdownload.microsoft.com%2Fdownload%2F8%2F5%2Fe%2F85eea4fa-b3bb-4426-97d0-7f7151b2011c%2Fonlineindex.doc&ei=ZRC3UMSkFu2tiQeuwYG4Aw&usg=AFQjCNFHmJTklrGH0BAHHwg8KCKVlNkRxw

"Deadlocks between the index builder transaction that is holding the batch transaction locks and DML statements are possible, but are handled internally so that neither the DML operation nor the index builder transaction should terminate during the build phase due to a deadlock."

So I wonder if the index builder transaction retries whenever deadlock is encountered.

11/04/2012 01:09:36,spid21s,Unknown,process id=process4f2fb88 taskpriority=10 logused=240 waitresource=PAGE: 5:1:4492187 waittime=1162 ownerId=15018248 transactionname=OnlineIndexInsertTxn lasttranstarted=2012-11-04T01:09:34.990 XDES=0x1807483b0 lockMode=IX schedulerid=1 kpid=1812 status=suspended spid=62 sbid=0 ecid=6 priority=0 trancount=0 lastbatchstarted=2012-11-04T01:09:34.977 lastbatchcompleted=2012-11-04T01:09:34.977 clientapp=.Net SqlClient Data Provider hostname=SQL01 hostpid=2824 isolationlevel=serializable (4) xactid=15018240 currentdb=5 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
11/04/2012 01:09:36,spid21s,Unknown,executionStack
11/04/2012 01:09:36,spid21s,Unknown,frame procname=adhoc line=1 sqlhandle=0x02000000cb515637fd9d3c251909605d36efb3cbb2808136
11/04/2012 01:09:36,spid21s,Unknown,insert [dbo].[table1] select * from [dbo].[table1]
11/04/2012 01:09:36,spid21s,Unknown,frame procname=adhoc line=1 sqlhandle=0x0100050093328506203f461c030000000000000000000000
11/04/2012 01:09:36,spid21s,Unknown,ALTER INDEX [pk1] ON [dbo].[table1] REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF<c/> STATISTICS_NORECOMPUTE = OFF<c/> ALLOW_ROW_LOCKS = ON<c/> ALLOW_PAGE_LOCKS = ON<c/> ONLINE = ON<c/> SORT_IN_TEMPDB = OFF )
11/04/2012 01:09:36,spid21s,Unknown,inputbuf

11/04/2012 01:09:36,spid21s,Unknown,process id=process6fecbc8 taskpriority=10 logused=8384 waitresource=PAGE: 5:1:4492187 waittime=1158 ownerId=15018250 transactionname=OnlineIndexInsertTxn lasttranstarted=2012-11-04T01:09:34.990 XDES=0x80063430 lockMode=IX schedulerid=4 kpid=4212 status=suspended spid=62 sbid=0 ecid=8 priority=0 trancount=0 lastbatchstarted=2012-11-04T01:09:34.977 lastbatchcompleted=2012-11-04T01:09:34.977 clientapp=.Net SqlClient Data Provider hostname=SQL01 hostpid=2824 isolationlevel=serializable (4) xactid=15018240 currentdb=5 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
11/04/2012 01:09:36,spid21s,Unknown,executionStack
11/04/2012 01:09:36,spid21s,Unknown,frame procname=adhoc line=1 sqlhandle=0x02000000cb515637fd9d3c251909605d36efb3cbb2808136
11/04/2012 01:09:36,spid21s,Unknown,insert [dbo].[table1] select * from [dbo].[table1]
11/04/2012 01:09:36,spid21s,Unknown,frame procname=adhoc line=1 sqlhandle=0x0100050093328506203f461c030000000000000000000000
11/04/2012 01:09:36,spid21s,Unknown,ALTER INDEX [pk1] ON [dbo].[table1] REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF<c/> STATISTICS_NORECOMPUTE = OFF<c/> ALLOW_ROW_LOCKS = ON<c/> ALLOW_PAGE_LOCKS = ON<c/> ONLINE = ON<c/> SORT_IN_TEMPDB = OFF )
11/04/2012 01:09:36,spid21s,Unknown,inputbuf

11/04/2012 01:09:36,spid21s,Unknown,process id=process6fd3288 taskpriority=10 logused=107104 waitresource=PAGE: 5:1:4492288 waittime=1157 ownerId=15018247 transactionname=OnlineIndexInsertTxn lasttranstarted=2012-11-04T01:09:34.990 XDES=0x2eb0c63b0 lockMode=IX schedulerid=3 kpid=4768 status=suspended spid=62 sbid=0 ecid=5 priority=0 trancount=0 lastbatchstarted=2012-11-04T01:09:34.977 lastbatchcompleted=2012-11-04T01:09:34.977 clientapp=.Net SqlClient Data Provider hostname=SQL01 hostpid=2824 isolationlevel=serializable (4) xactid=15018240 currentdb=5 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
11/04/2012 01:09:36,spid21s,Unknown,executionStack
11/04/2012 01:09:36,spid21s,Unknown,frame procname=adhoc line=1 sqlhandle=0x02000000cb515637fd9d3c251909605d36efb3cbb2808136
11/04/2012 01:09:36,spid21s,Unknown,insert [dbo].[table1] select * from [dbo].[table1]
11/04/2012 01:09:36,spid21s,Unknown,frame procname=adhoc line=1 sqlhandle=0x0100050093328506203f461c030000000000000000000000
11/04/2012 01:09:36,spid21s,Unknown,ALTER INDEX [pk1] ON [dbo].[table1] REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF<c/> STATISTICS_NORECOMPUTE = OFF<c/> ALLOW_ROW_LOCKS = ON<c/> ALLOW_PAGE_LOCKS = ON<c/> ONLINE = ON<c/> SORT_IN_TEMPDB = OFF )
11/04/2012 01:09:36,spid21s,Unknown,inputbuf

johnson_ef
Starting Member

16 Posts

Posted - 2012-11-30 : 04:30:02

Are you trying Index rebuild 'Online' or 'Offline'?

If you do it 'Online', I think you won't get this deadlock error. But if you do it offline and you didn't restrict the DB access for Transactions, it may occur.
reason for this is, Since DB should be consistent, any other transaction other than Index creation will be prohibited. Ideally, DBA will make sure to prevent this prior to start Index rebuild.
-Johnson

-Johnson

sim2012
Starting Member

3 Posts

Posted - 2012-12-04 : 02:50:32

I used the online option for rebuild index task in maintenance plan. I also think that it wouldn't get me the deadlock as the index rebuilding was done on Sunday and there was no user activity.
But it seems to me that the index rebuilding transaction would cause deadlock itself unless I set maxdop to 0.

johnson_ef
Starting Member

16 Posts

Posted - 2012-12-06 : 07:04:34

Can you narrow down the issue more? You can pull out the script for the Index rebuild when you setup maintenance plan. If you select the whole DB, it will take out all the Indexes, pull out the script and try one after another, this will help you to understand when\where you get deadlock.

-Johnson

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources