Whopper of a Deadlock, but why are there so many blank input buffers?

Question

Whopper of a Deadlock, but why are there so many blank input buffers?

Lowell

SSC Guru

Points: 323518
More actions
June 24, 2015 at 6:53 am

#301968

i've got a whopper of a deadlock that i'd love to get some peer review on.
the actual xdl with a renamed extension is located here: EDIT: YOU MUST OPEN IN SSMS2012 OR GREATER TO SEE IT CORRECTLY
Deadlocks.xdl.xml
i have a job that executes whenever an alert for a deadlock occurs, and it sends me an email, with some information i parsed out of the deadlock graph.
I'll post my personal analysis and my proposed fixes after the original post.
so my questions are really, why are so many input buffers blank? I suspect they are child updates of indexes from one of the participants doing an update, and therefor no buffer?
in this case, most of the input buggers are blank, except for two items that , because of the style of the query, i know were auto generated form LinqToSQL.
The following Input buffers could be read directly from the XML, and were the available commands that were involved in the deadlock.
AllInputBuffers
> (@p0 bigint,@p1 bigint,@p2 int,@p3 nvarchar(4000),@p4 nvarchar(4000),@p5 varchar(8000),@p6 varchar(8000),@p7 datetime,@p8 int,@p9 bigint,@p10 smallint,@p11 int,@p12 nvarchar(4000),@p13 datetime,@p14 nvarchar(4000),@p15 datetime,@p16 nvarchar(4000),@p17 datetime,@p18 smallint,@p19 bigint,@p20 bigint,@p21 int,@p22 datetime,@p23 nvarchar(4000),@p24 datetime,@p25 nvarchar(4000),@p26 nvarchar(4000),@p27 datetime,@p28 nvarchar(4000),@p29 datetime,@p30 smallint,@p31 bigint)UPDATE [dbo].[EDLogDetail] SET [UpdatedBy] = @p26, [UpdatedDate] = @p27, [CheckOutUser] = @p28, [CheckOutDate] = @p29, [CurStatusID] = @p30, [VisitInfoID] = @p31 WHERE ([EDLogDetailID] = @p0) AND ([EDLogID] = @p1) AND ([EDLogRecoveryID] IS NULL) AND ([FacilityID] = @p2) AND ([HospAcctno] = @p3) AND ([MRNumber] = @p4) AND ([PatFname] = @p5) AND ([PatLName] = @p6) AND ([DateOfService] = @p7) AND ([ProviderID] = @p8) AND ([VoidsID] IS NULL) AND ([isDeficience] IS NULL) AND (NOT ([isInHouse] = 1)) AND ([ResolvedDate] IS NULL) AND ([ResolvedBy] IS N <
> (@p0 int,@p1 nvarchar(4000),@p2 nvarchar(4000),@p3 nvarchar(4000),@p4 nvarchar(4000),@p5 int,@p6 int,@p7 int,@p8 int,@p9 int,@p10 int,@p11 int,@p12 int,@p13 int,@p14 int,@p15 int,@p16 int,@p17 int,@p18 int,@p19 int,@p20 int,@p21 int,@p22 int,@p23 int,@p24 int,@p25 int,@p26 int,@p27 int,@p28 int,@p29 int,@p30 int,@p31 int,@p32 int,@p33 int,@p34 int,@p35 int,@p36 int,@p37 int,@p38 int,@p39 int,@p40 int,@p41 int,@p42 int,@p43 int,@p44 int,@p45 int,@p46 int,@p47 int,@p48 int,@p49 int,@p50 int,@p51 int,@p52 int,@p53 int,@p54 int,@p55 int,@p56 int,@p57 int,@p58 int,@p59 int,@p60 int,@p61 int,@p62 int,@p63 int,@p64 int,@p65 int,@p66 int,@p67 int,@p68 int,@p69 int,@p70 int,@p71 int,@p72 int,@p73 int,@p74 int,@p75 int,@p76 int,@p77 int,@p78 int,@p79 int,@p80 int,@p81 int,@p82 int,@p83 int,@p84 int,@p85 int,@p86 int,@p87 int,@p88 int,@p89 int,@p90 int,@p91 int,@p92 int,@p93 int,@p94 int,@p95 int,@p96 int,@p97 int,@p98 int,@p99 int,@p100 int,@p101 int,@p102 bigint,@p103 bigint,@p104 nvarchar(4000),@p105 nvarchar(4000)) <
{no input buffer}
{no input buffer}
{no input buffer}
{no input buffer}
{no input buffer}
{no input buffer}
{no input buffer}
{no input buffer}
{no input buffer}
{no input buffer}
{no input buffer}
{no input buffer}
{no input buffer}
{no input buffer}
{no input buffer}
{no input buffer}
{no input buffer}
{no input buffer}
{no input buffer}
{no input buffer}
{no input buffer}
{no input buffer}
{no input buffer}
{no input buffer}
{no input buffer}
{no input buffer}
{no input buffer}
{no input buffer}
{no input buffer}
{no input buffer}
{no input buffer}
{no input buffer}
{no input buffer}
{no input buffer}
the visualization is here:
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 3 posts - 1 through 2 (of 2 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

ok so my analysis of WHY it occurred is as follows, see if you agree with me:

LinqToSQL wants to try and maintain state of data, so instead of just doing an UPDATE WHERE PrimaryKey = 42, it queries the PK, as well as all the columns that it saw when it did the data snapshot: this ends up doing a big old table scan, instead of a nice index seek on the PK.

what is the significance of the spiderwebs pointing to [Exchange Event]? what is an exchange event in a deadlock graph?

anyway, so the query ends up being like this:

UPDATE [dbo].[EDLogDetail]

SET [UpdatedBy] = @p26,

[UpdatedDate] = @p27,

[CheckOutUser] = @p28,

[CheckOutDate] = @p29,

[CurStatusID] = @p30,

[VisitInfoID] = @p31

WHERE ([EDLogDetailID] = @p0)

AND ([EDLogID] = @p1)

AND ([EDLogRecoveryID] IS NULL)

AND ([FacilityID] = @p2)

AND ([HospAcctno] = @p3)

AND ([MRNumber] = @p4)

AND ([PatFname] = @p5)

AND ([PatLName] = @p6)

AND ([DateOfService] = @p7)

AND ([ProviderID] = @p8)

AND ([VoidsID] IS NULL)

AND ([isDeficience] IS NULL)

AND (NOT ([isInHouse] = 1))

AND ([ResolvedDate] IS NULL)

AND ([ResolvedBy] IS N (40 more columns later)<

instead of assuming last update wins, which would be

simply

UPDATE [dbo].[EDLogDetail]

SET [UpdatedBy] = @p26,

[UpdatedDate] = @p27,

[CheckOutUser] = @p28,

[CheckOutDate] = @p29,

[CurStatusID] = @p30,

[VisitInfoID] = @p31

WHERE ([EDLogDetailID] = @p0)

the fix in this one instance is to have the codebase call a stored procedure with the parameters....so fine, but my question is really, am i either interpreting the xdl right, and is there a reason i'm not aware of for why i expect inputbuffers for every item involved in the deadlock.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Lowell (6/24/2015)
what is the significance of the spiderwebs pointing to [Exchange Event]? what is an exchange event in a deadlock graph?

A parallelism operation.

That's also why there are so many empty input buffers and so many nodes. It looks like both processes are running in parallel and hence each thread gets mentioned in the deadlock graph.

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