Deadlock investigation, help!

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » SQL Server 2005 » SQL Server 2005 Performance Tuning » Deadlock investigation, help!

Add to briefcase

13 posts, Page 1 of 2

1 2 »»

Deadlock investigation, help!

Expand / Collapse

Author

Message

Posted Thursday, August 30, 2012 1:44 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, May 13, 2013 9:21 AM
Points: 8, Visits: 70

Hi 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 R2

1 tables involved: Table A

TableA - 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=0x02000000548063157079ca7ed9f4302a5e5655a6f5ccbb5c
update TableA set ExtendedData = @P1 where Name = @P2 and SubType = @P3
frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
unknown
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=0x020000002e5b8b37b467ec8f8d31844d950625041254ec2e
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,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=0x000000000000000000000000000000000000000000000000
unknown
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=convert

Am i reading this correctly?

Process 49454c8 is holding an IX lock on TableA
Process 4911948 is also holding an IX lock on TableA

Both 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!

Post #1352470

Posted Thursday, August 30, 2012 2:42 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 1:29 PM
Points: 5,678, Visits: 6,128

The 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] = 21575115

Also 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.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables
Twitter: @AnyWayDBA

Post #1352498

Posted Thursday, August 30, 2012 3:09 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, May 13, 2013 9:21 AM
Points: 8, Visits: 70

Hi, 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 column

Post #1352515

Posted Thursday, August 30, 2012 3:18 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 1:29 PM
Points: 5,678, Visits: 6,128

Yeah, 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. Smile

Smile

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables
Twitter: @AnyWayDBA

Post #1352516

Posted Thursday, August 30, 2012 3:32 PM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 3:34 PM
Points: 1,331, Visits: 4,310

Is the table involved in a foreign key relationship?

Post #1352519

Posted Thursday, August 30, 2012 4:00 PM

Old Hand

Group: General Forum Members
Last Login: Today @ 9:19 AM
Points: 316, Visits: 1,484

I'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?

Post #1352522

Posted Thursday, August 30, 2012 7:29 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 9:12 AM
Points: 6,370, Visits: 8,235

If you have the deadlock graph in XML format, use my script (http://blog.waynesheffield.com/wayne/code-library/shred-deadlock-graph/) 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.

Wayne
Microsoft Certified Master: SQL Server 2008

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Post #1352551

Posted Thursday, August 30, 2012 8:19 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, May 13, 2013 9:21 AM
Points: 8, Visits: 70

Jo Pattyn (8/30/2012)

Is the table involved in a foreign key relationship?

Nope

Post #1352558

Posted Thursday, August 30, 2012 8:59 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 1:02 AM
Points: 10,989, Visits: 10,534

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.

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) Smile

Smile

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:

ALTER TABLE dbo.Heap REBUILD;
ALTER INDEX ALL ON dbo.Heap REORGANIZE WITH (LOB_COMPACTION = ON);

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:

CREATE UNIQUE NONCLUSTERED INDEX uq1 ON dbo.Heap (Name, SubType)

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.

Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi

Post #1352564

Posted Thursday, August 30, 2012 9:16 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, May 13, 2013 9:21 AM
Points: 8, Visits: 70

SQL Kiwi (8/30/2012)

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.

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 fragmented

What 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..

Post #1352566

« Prev Topic | Next Topic »

Add to briefcase

13 posts, Page 1 of 2

1 2 »»

Permissions

Expand / Collapse