InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 26 May 2013 02:07:49 GMT20http://www.sqlservercentral.com/Forums/Topic1352470-360-1.aspxHi All,Just to finish this off, thanks for all the help again.I figured out that it was a missing clustered index and defragmentation (or fragmentation, whichever way you look at it, i think specifically data fragmentation), that was the culprit.From the graph, my theory was that 2 ad hoc INSERT processes were trying to insert into the table. For some reason, since it was a heap, it was trying to escalate it's lock (i'm assuming at the page level), to a table level lock. Since the two processes occurred within milliseconds of each other, it each grabbed a page, and were both trying to escalate to a table-level lock.....hence, DEADLOCK.With the clustered index, i was hoping that it grabbed a page-level lock (or something more granular than a table-level lock), and the lock was held quicker b/c SQL knew exactly where to insert the row. and thus prevent a deadlock..Hopefully that reasoning made sense..from a technical persepctive.Tue, 04 Sep 2012 08:21:46 GMTmsandico 57892http://www.sqlservercentral.com/Forums/Topic1352470-360-1.aspxHi All,Just to finish this off, thanks for all the help again.I figured out that it was a missing clustered index and defragmentation (or fragmentation, whichever way you look at it, i think specifically data fragmentation), that was the culprit.From the graph, my theory was that 2 ad hoc INSERT processes were trying to insert into the table. For some reason, since it was a heap, it was trying to escalate it's lock (i'm assuming at the page level), to a table level lock. Since the two processes occurred within milliseconds of each other, it each grabbed a page, and were both trying to escalate to a table-level lock.....hence, DEADLOCK.With the clustered index, i was hoping that it grabbed a page-level lock (or something more granular than a table-level lock), and the lock was held quicker b/c SQL knew exactly where to insert the row. and thus prevent a deadlock..Hopefully that reasoning made sense..from a technical persepctive.Tue, 04 Sep 2012 07:40:38 GMTmsandico 57892http://www.sqlservercentral.com/Forums/Topic1352470-360-1.aspx[quote][b]msandico 57892 (8/30/2012)[/b][hr]we never REBUILD because of the requirement of the system to stay UP all the time[/quote]REBUILD supports the ONLINE option. I presume you have Enterprise Edition and are using the ONLINE option already when adding indexes.[quote]we REORGANIZE weekly but on a heap table, i believe the non-clustered indexes will still be fragmented[/quote]ALTER INDEX ... REORGANIZE allows you to specify ALL indexes or individual ones. Both ALTER INDEX ALL and ALTER TABLE REBUILD affect the heap and all non-clustered indexes, as far as I recall.[quote]I also thought about giving a row locking hint (PAGLOCK) not (TABLOCK), because in the graph isn't it trying to lock the table to do the changes? My theory is that if the INSERT and UPDATE does a PAGLOCK then maybe a deadlock won't occur because the processes are locking at the page level rather than the table level..thus reducing the chance of wanting each other's resources...does that even make sense?[/quote]That's the broad theory of it, yes.[quote]I barely have access to the code as the app that's sending the queries is developed by a third-party..but i have rare access to them if absolutley needed..just wondering if this can be fixed at the server side before going to client side..[/quote]Not that I know of. We can't use a plan guide to specify a lock-granularity hint for the table using the TABLE HINT syntax because it affects the semantic of the query (you get error 8722 if you try this).Thu, 30 Aug 2012 21:54:26 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1352470-360-1.aspx[quote][b]SQL Kiwi (8/30/2012)[/b][hr]You're getting a deadlock between one process that is updating a large object (text) column, and another that is inserting a new row (which will also include the text column, naturally). My strong suspicion is that the deadlock is occurring during management of the text pages. As you may know, text data is stored off row, in separate pages dedicated for large object use. There's a lot of potential detail here, since exactly how the large object data is stored depends on its size (smaller ones may share a page with other rows from the same partition, for example) and changes in size can result in things moving around in complex ways.I would expect that making the heap a clustered table could only help in general, but it will not solve all of your problems, and perhaps not the deadlock issue either. It is probably going to be too hard to nail down the precise cause of your deadlock, but I would definitely want to have an index to help the UPDATE query:Another thing I might try is to add a WITH (PAGLOCK) or WITH (TABLOCK) hint to the INSERT query, assuming the code is accessible to you. This second suggestion is more out of curiosity to see if it helps rather than a serious long-term solution, though.[/quote]Thanks for this. Couple other items of interest:- we never REBUILD because of the requirement of the system to stay UP all the time- we REORGANIZE weekly but on a heap table, i believe the non-clustered indexes will still be fragmentedWhat i can do is try to add the non-clustered index and a potential clustered index on the table (on DataID). Then i'll test again.I also thought about giving a row locking hint (PAGLOCK) not (TABLOCK), because i the graph isn't it trying to lock the table to do the changes? My theory is that if the INSERT and UPDATE does a PAGLOCK then maybe a deadlock won't occur because the processes are locking at the page level rather than the table level..thus reducing the chance of wanting each other's resources...does that even make sense?I barely have access to the code as the app that's sending the queries is developed by a third-party..but i have rare access to them if absolutley needed..just wondering if this can be fixed at the server side before going to client side..Thu, 30 Aug 2012 21:16:51 GMTmsandico 57892http://www.sqlservercentral.com/Forums/Topic1352470-360-1.aspxYou're getting a deadlock between one process that is updating a large object (text) column, and another that is inserting a new row (which will also include the text column, naturally). My strong suspicion is that the deadlock is occurring during management of the text pages. As you may know, text data is stored off row, in separate pages dedicated for large object use. There's a lot of potential detail here, since exactly how the large object data is stored depends on its size (smaller ones may share a page with other rows from the same partition, for example) and changes in size can result in things moving around in complex ways.If you are looking for an exciting life (!) one very good approach is to use the old LOB types (text, ntext and image) in a heap structure that encounters lots of concurrent INSERT/UPDATE/DELETE activity, especially if the old-style LOB columns change size frequently. For extra credit, you can have twenty-odd non-clustered indexes, a very large number of table columns, and some wide data types (e.g. Name nvarchar(56) = up to 112 bytes + overhead per row) :-)I prefer a quiet life myself, where things just work. The design is not helping you here, and you may not be performing the right maintenance regularly to remove ghost and forwarded records, compact LOB space, and generally tidy things up:[code="sql"]ALTER TABLE dbo.Heap REBUILD;ALTER INDEX ALL ON dbo.Heap REORGANIZE WITH (LOB_COMPACTION = ON);[/code]I would expect that making the heap a clustered table could only help in general, but it will not solve all of your problems, and perhaps not the deadlock issue either. It is probably going to be too hard to nail down the precise cause of your deadlock, but I would definitely want to have an index to help the UPDATE query:[code="sql"]CREATE UNIQUE NONCLUSTERED INDEX uq1 ON dbo.Heap (Name, SubType)[/code]I hesitate to suggest that as the clustered index, since the Name column is potentially quite wide.Another thing I might try is to add a WITH (PAGLOCK) or WITH (TABLOCK) hint to the INSERT query, assuming the code is accessible to you. This second suggestion is more out of curiosity to see if it helps rather than a serious long-term solution, though.Thu, 30 Aug 2012 20:59:27 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1352470-360-1.aspx[quote][b]Jo Pattyn (8/30/2012)[/b][hr]Is the table involved in a foreign key relationship?[/quote]NopeThu, 30 Aug 2012 20:19:38 GMTmsandico 57892http://www.sqlservercentral.com/Forums/Topic1352470-360-1.aspxIf you have the deadlock graph in XML format, use my script ([url=http://blog.waynesheffield.com/wayne/code-library/shred-deadlock-graph/][u]http://blog.waynesheffield.com/wayne/code-library/shred-deadlock-graph/[/u][/url]) to shred it and see what all is going on. If this doesn't help, post the XML and I'll see what I can do with it.Thu, 30 Aug 2012 19:29:37 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1352470-360-1.aspxI'm on my phone, so I may not be reading this right, but it looks like both spids have a Tran count of 2, so could there be other tsql in play here?Thu, 30 Aug 2012 16:00:01 GMTSpringTownDBAhttp://www.sqlservercentral.com/Forums/Topic1352470-360-1.aspxIs the table involved in a foreign key relationship?Thu, 30 Aug 2012 15:32:39 GMTJo Pattynhttp://www.sqlservercentral.com/Forums/Topic1352470-360-1.aspxYeah, sorry I'm not directly helping here. Your assumptions and reading of the graph are accurate. The queries are both conflicting on an IX - X conversion for the same resource (21575115, said table). However, I know of no reason a Heap would conflict on that, the IX is just an optimization piece and shouldn't be required.The Update itself is tight and affects very little.Hrm. Either I'm missing something obvious or my knowledge isn't up to the task. Calling in the calvary. :-)Thu, 30 Aug 2012 15:18:50 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1352470-360-1.aspxHi, thanks for the response.The query you gave above did return 'TableA' , and yep i double-checked that 3 times previously too lol.I am certain there are no indexes and is indeed a heap table.There is one constraint on a column, DEFAULT of newid() on a guid columnThu, 30 Aug 2012 15:09:44 GMTmsandico 57892http://www.sqlservercentral.com/Forums/Topic1352470-360-1.aspxThe Intent Exclusive locks trying to convert to exclusive aren't your direct issue here. They'll attempt to escalate at 5000 rows but it's not required, it's just an optimization to keep lock usage down to reasonable volumes.Can you confirm that you get TableA back as the object in the following query, please? By preference, please simply post the results, but I realize you've modified things slightly to obfuscate certain things... well, unless you actually HAVE a table called TableA.SELECT * FROM sys.sysobjects where [id] = 21575115Also please reconfirm TableA has no indexes whatsoever, also check for unique constraints and anything else that might be hanging around. By rights, an Update and an Insert should not collide in a non-indexed heap in any way. Inserts will tail insert and updates will work against existing data pages. Even splits shouldn't affect it. Something is odd here.Thu, 30 Aug 2012 14:42:09 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1352470-360-1.aspxHi All,I know this is a general cry for help for my specific deadlock but hoping you can help understand the graph, or let me know what i'm missing.SQL Server 2008 R21 tables involved: Table ATableA - no indexes! Ok, the reason for this is i wanted to check if indexes were a cause of deadlocks, because originally it had 21 non-clustered indexes, and no clustered index! So i made it a heap table and still received the same deadlocks.But now i'm thinking that a clustered index may help resolve the deadlock issue... *Maybe*Here's the graph:deadlock-list deadlock victim=process4911948 process-list process id=process4911948 taskpriority=0 logused=0 waitresource=OBJECT: 5:21575115:0 waittime=4476 ownerId=118226314 transactionname=implicit_transaction lasttranstarted=2012-08-30T15:26:20.823 XDES=0x3eab42c60 lockMode=X schedulerid=6 kpid=500 status=suspended spid=95 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2012-08-30T15:26:20.867 lastbatchcompleted=2012-08-30T15:26:20.840 clientapp=Livelink Enterprise Server hostname=SANLLINK2-STG hostpid=6472 loginname=livelink isolationlevel=read committed (2) xactid=118226314 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128058 executionStack frame procname=adhoc line=1 stmtstart=72 sqlhandle=0x02000000548063157079ca7ed9f4302a5e5655a6f5ccbb5cupdate TableA set ExtendedData = @P1 where Name = @P2 and SubType = @P3 frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000unknown inputbuf(@P1 ntext,@P2 nvarchar(56),@P3 int)update TableA set ExtendedData = @P1 where Name = @P2 and SubType = @P3 process id=process49454c8 taskpriority=0 logused=2448 waitresource=OBJECT: 5:21575115:0 waittime=4476 ownerId=118226298 transactionname=implicit_transaction lasttranstarted=2012-08-30T15:26:20.763 XDES=0x5c7ab5970 lockMode=X schedulerid=8 kpid=2400 status=suspended spid=92 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2012-08-30T15:26:20.820 lastbatchcompleted=2012-08-30T15:26:20.817 clientapp=Livelink Enterprise Server hostname=SANLLINK2-STG hostpid=6472 loginname=livelink isolationlevel=read committed (2) xactid=118226298 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128058 executionStack frame procname=adhoc line=1 stmtstart=1070 sqlhandle=0x020000002e5b8b37b467ec8f8d31844d950625041254ec2einsert into TableA ( OwnerID,ParentID,DataID,Name,OriginOwnerID,OriginDataID,UserID,GroupID,UPermissions,GPermissions,WPermissions,SPermissions,ACLCount,PermID,DataType,Reserved,SubType,CreatedBy,CreateDate,ModifyDate,MaxVers,ReservedBy,ReservedDate,VersionNum,DComment,DCategory,ExAtt1,ExAtt2,Ordering,Major,Minor,ReleaseRef,ChildCount,AssignedTo,DateAssigned,DateEffective,DateDue,DateExpiration,DateStarted,DateCompleted,Status,Priority,GIF,ExtendedData,Catalog,CacheExpiration,Deleted,ModifiedBy ) values (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@P13,@P14,@P15,@P16,@P17,@P18,@P19,@P20,@P21,@P22,@P23,@P24,@P25,@P26,@P27,@P28,@P29,@P30,@P31,@P32,@P33,@P34,@P35,@P36,@P37,@P38,@P39,@P40,@P41,@P42,@P43,@P44,@P45,@P46,@P47,@P48) frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000unknown inputbuf(@P1 int,@P2 int,@P3 int,@P4 nvarchar(24),@P5 int,@P6 int,@P7 int,@P8 int,@P9 int,@P10 int,@P11 int,@P12 int,@P13 int,@P14 char(1),@P15 char(1),@P16 int,@P17 int,@P18 int,@P19 datetime,@P20 datetime,@P21 int,@P22 int,@P23 char(1),@P24 int,@P25 nvarchar(1),@P26 char(1),@P27 char(1),@P28 char(1),@P29 char(1),@P30 char(1),@P31 char(1),@P32 char(1),@P33 int,@P34 char(1),@P35 char(1),@P36 char(1),@P37 char(1),@P38 char(1),@P39 char(1),@P40 char(1),@P41 char(1),@P42 char(1),@P43 char(1),@P44 char(1),@P45 int,@P46 int,@P47 int,@P48 int)insert into TableA ( OwnerID,ParentID,DataID,Name,OriginOwnerID,OriginDataID,UserID,GroupID,UPermissions,GPermissions,WPermissions,SPermissions,ACLCount,PermID,DataType,Reserved,SubType,CreatedBy,CreateDate,ModifyDate,MaxVers,ReservedBy,ReservedDate,VersionNum,DComment,DCategory,ExAtt1,ExAtt2,Ordering,Major,Minor,ReleaseRef,ChildCount,AssignedTo,DateAssigned,DateEffective,DateDue,DateExpiration,DateStarted,DateCompleted,Status,Priority,GIF,ExtendedData,Catalog,CacheExpiration,Deleted, resource-list objectlock lockPartition=0 objid=21575115 subresource=FULL dbid=5 objectname=dbA.dbo.TableA id=lock1fd639c80 mode=IX associatedObjectId=21575115 owner-list owner id=process49454c8 mode=IX waiter-list waiter id=process4911948 mode=X requestType=convert objectlock lockPartition=0 objid=21575115 subresource=FULL dbid=5 objectname=dbA.dbo.TableA id=lock1fd639c80 mode=IX associatedObjectId=21575115 owner-list owner id=process4911948 mode=IX waiter-list waiter id=process49454c8 mode=X requestType=convertAm i reading this correctly?Process 49454c8 is holding an IX lock on TableA Process 4911948 is also holding an IX lock on TableABoth want to convert to an X lock to do their INSERT or UPDATE. But neither can convert b/c the other process is holding an IX lock...so deadlock occurs?Is that what it's saying?Thanks in advance!Thu, 30 Aug 2012 13:44:15 GMTmsandico 57892