SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 alter index rebuild deadlock
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sim2012
Starting Member

3 Posts

Posted - 11/12/2012 :  22:03:13  Show Profile  Reply with Quote
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 ?

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

srimami
Posting Yak Master

160 Posts

Posted - 11/20/2012 :  09:21:47  Show Profile  Reply with Quote
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
Go to Top of Page

sim2012
Starting Member

3 Posts

Posted - 11/29/2012 :  02:53:29  Show Profile  Reply with Quote
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
Go to Top of Page

johnson_ef
Starting Member

India
16 Posts

Posted - 11/30/2012 :  04:30:02  Show Profile  Reply with Quote
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
Go to Top of Page

sim2012
Starting Member

3 Posts

Posted - 12/04/2012 :  02:50:32  Show Profile  Reply with Quote
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.
Go to Top of Page

johnson_ef
Starting Member

India
16 Posts

Posted - 12/06/2012 :  07:04:34  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000