Post reply

Replace trigger due to deadlock - but with what?

  • December 15, 2008 at 6:04 am

    #198994

    Hi all

    I have taken over the responsibility of a database. There keeps occurring deadlocks, because of the way the previous developer has designed some business rules.

    What i would like your meaning about is how do i do this in the proper way.

    The case is this (simplified a bit - but none the less valid for the discussion):

    We have some tasks. A task can be delayed and wait for another task to complete. And then of course a task can be completed.

    What we would like to occur, is that when a task is completed, then all tasks that are delayed with the completed task as reason, should be "undelayed".

    In the current implementation, we have a table Tasks, which has a trigger on insert/update that calls a stored procedure which handles the logic of undelaying.

    And this is where the deadlock occurs, because in a trigger for the table Tasks, we might want to update other rows in Tasks.

    So my question is: How can i ensure the same without using the deadlocking trigger?

    I would really much like it to be the database itself that handles the logic to enforce this. So no matter if i update by using SSMS or going through a web application or web service, it would be handled consistently.

    The trigger does in fact this, but carries a small sideeffect by deadlocking... 🙂

    I hope some of you guys has been here before, and can help me with a good solution.

    Best regards

    /Anders

  • December 15, 2008 at 6:40 am

    #912420

    If depends on what the trigger actually does.

    May we see the code?

    N 56°04'39.16"
    E 12°55'05.25"

  • December 15, 2008 at 6:50 am

    #912426

    It sounds like you can do this with a trigger or with a stored procedure. While I'm not a fan of triggers, if you need one, you need one. It sounds like the trigger might be referring to the tables in a different order than which they are normally accessed, or even, in a different order than the procedure that is calling the trigger accesses them. The classic deadlock scenario is where session A requests table X & Y and then escelates it's locks in the same order while session B requests tables Y & X and escelates it's locks in the reverse order.

    I'd verify that first, because even if you switch the processing of the data to procedures, the procedures will need to access the data in the same order.

    On the other hand, it could be something else entirely. when you say that processes are "delayed" does that mean that connections to the database are held open while the process is in the delayed state? If so, that's pretty likely to be the cause of the deadlocks. You really don't want to hold transactions open from the client.

    More details will help identify the core issue and therefor the solution.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • December 15, 2008 at 6:51 am

    #912428

    Post the deadlock graph please?

    To get deadlock graph, turn traceflag 1222 on. Deadlock graph will then be written into the error log everytime a deadlock occurs.

    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

  • December 16, 2008 at 4:24 am

    #913036

    Hi

    By tasks i didn't mean anything process-like. A task is just an object in our domain model. It could just as well had been a car, with a property cabin light and door state. If door state is changed to open, the light should be set to on. And when the door closes the light should be set off.

    Does that make it clearer?

    Anyway, i have turned trace flag 1222 on, and are awaiting the next deadlock. When it occurs i will post the deadlock graph here.

    Maybe i can see something from the graph myself, haven't ever looked at one of those before.

    Anyway, I will post when i know more. As it is with most deadlocks, it doesn't appear always, and can thus not say anything about when it will happen next...

    /Anders

  • December 18, 2008 at 1:25 am

    #914340

    Hi

    I have now experienced a few deadlocks catched by my own logging mechanism since i turned on trace 1222. I did that with

    DBCC TRACEON(1222)

    But i can't find anything in the log. What am i doing wrong?

    To explain my own logging mechanism in short.

    All insert /update stored procedures has a try-catch pattern.

    In the catch block, i exec a SP in which i insert a row in my log table, and then "re-raise" the exception with a new raiserror statement using the values from ERROR_MESSAGE(), ERROR_SEVERITY(), etc as parameters.

    Can this cause the deadlock not to be written in log?

    /Anders

  • December 18, 2008 at 5:13 am

    #914412

    DBCC TRACEON (1222,-1)

    The -1 turns it on globally. Without that the traceflag is only enabled for a single session, the one that turned it on. Since deadlocks are detected by system processes, the user process cannot pick up deadlocks, and hence no graph will be written.

    Also bear in mind that a restart of SQL will reset any traceflags to default settings.

    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

  • December 18, 2008 at 6:26 am

    #914465

    Thanks. We learn every day... 🙂

    Hopefully i get some results on this side of Christmas, but going on holiday tomorrow, so propably i won't post any results this year.. 😉

    /Anders

  • December 18, 2008 at 6:29 am

    #914467

    Good luck. I assume you're not getting paged on the deadlock events? I would be, so I'd sure as heck get a resolution in place prior to Christmas.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • January 12, 2009 at 1:14 am

    #925126

    Hi all,

    Now i actually caught a deadlock - they don't occur very often any more since i have made a change in a specific trigger, where they did occur most.

    How do i obtain a nice view on the deadlock graph? And how do you guys want it to be formatted when i post it here?

    /Anders

  • January 12, 2009 at 2:11 am

    #925134

    Just post it exactly as it was written into the errorlog. You can cut off the log datatime if you like.

    Easiest way is to open the errorlog in a text editor (it's just a text file) and copy-paste.

    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

  • January 13, 2009 at 5:30 am

    #925769

    Ok, here it comes:

    I have replaced database name, machine names, table and procedure names, but that shouldn't impact on the essence.

    I hope this gives meaning to you. 🙂

    Date,Source,Severity,Message

    01/09/2009 10:55:12,spid15s,Unknown,waiter id=processf15588 mode=IX requestType=wait

    01/09/2009 10:55:12,spid15s,Unknown,waiter-list

    01/09/2009 10:55:12,spid15s,Unknown,owner id=processc8fd68 mode=SIX

    01/09/2009 10:55:12,spid15s,Unknown,owner-list

    01/09/2009 10:55:12,spid15s,Unknown,pagelock fileid=1 pageid=226585 dbid=5 objectname=MYDATABASE.dbo.TABLE_A id=lockc62de700 mode=SIX associatedObjectId=72057596754264064

    01/09/2009 10:55:12,spid15s,Unknown,waiter id=processc8fd68 mode=S requestType=convert

    01/09/2009 10:55:12,spid15s,Unknown,waiter-list

    01/09/2009 10:55:12,spid15s,Unknown,owner id=processf15588 mode=IX

    01/09/2009 10:55:12,spid15s,Unknown,owner-list

    01/09/2009 10:55:12,spid15s,Unknown,pagelock fileid=1 pageid=225607 dbid=5 objectname=MYDATABASE.dbo.TABLE_F id=lockbb81c380 mode=IX associatedObjectId=72057596754657280

    01/09/2009 10:55:12,spid15s,Unknown,resource-list

    01/09/2009 10:55:12,spid15s,Unknown,Proc [Database Id = 5 Object Id = 930818378]

    01/09/2009 10:55:12,spid15s,Unknown,inputbuf

    01/09/2009 10:55:12,spid15s,Unknown,-- Inserting generic fields if any

    01/09/2009 10:55:12,spid15s,Unknown,-- 2c.

    01/09/2009 10:55:12,spid15s,Unknown,@Comments=@Comments

    01/09/2009 10:55:12,spid15s,Unknown,@IssueAssetGenerics

    01/09/2009 10:55:12,spid15s,Unknown,@Components

    01/09/2009 10:55:12,spid15s,Unknown,@Platforms

    01/09/2009 10:55:12,spid15s,Unknown,@Versions

    01/09/2009 10:55:12,spid15s,Unknown,@TargetReleaseVersionSiteID

    01/09/2009 10:55:12,spid15s,Unknown,@TargetReleaseVersionID

    01/09/2009 10:55:12,spid15s,Unknown,@TargetReleaseLabelSiteID

    01/09/2009 10:55:12,spid15s,Unknown,@TargetReleaseLabelID

    01/09/2009 10:55:12,spid15s,Unknown,@Rank

    01/09/2009 10:55:12,spid15s,Unknown,@PrioritySiteID

    01/09/2009 10:55:12,spid15s,Unknown,@PriorityID

    01/09/2009 10:55:12,spid15s,Unknown,@BatchSiteID

    01/09/2009 10:55:12,spid15s,Unknown,@BatchID

    01/09/2009 10:55:12,spid15s,Unknown,@AssetSiteID

    01/09/2009 10:55:12,spid15s,Unknown,@AssetID

    01/09/2009 10:55:12,spid15s,Unknown,@IssueAssetTemplateSiteID

    01/09/2009 10:55:12,spid15s,Unknown,@IssueAssetTemplateID

    01/09/2009 10:55:12,spid15s,Unknown,@SiteID

    01/09/2009 10:55:12,spid15s,Unknown,@IssueID

    01/09/2009 10:55:12,spid15s,Unknown,exec dbo.PROC_1

    01/09/2009 10:55:12,spid15s,Unknown,frame procname=MYDATABASE.dbo.PROC_2 line=287 stmtstart=18526 stmtend=19616 sqlhandle=0x030005004a297b37a0f68000469b00000100000000000000

    01/09/2009 10:55:12,spid15s,Unknown,--

    01/09/2009 10:55:12,spid15s,Unknown,IsDefaultState=1

    01/09/2009 10:55:12,spid15s,Unknown,and

    01/09/2009 10:55:12,spid15s,Unknown,IssueAssetTemplateSiteID=@IssueAssetTemplateSiteID

    01/09/2009 10:55:12,spid15s,Unknown,and

    01/09/2009 10:55:12,spid15s,Unknown,IssueAssetTemplateID=@IssueAssetTemplateID

    01/09/2009 10:55:12,spid15s,Unknown,where

    01/09/2009 10:55:12,spid15s,Unknown,ISSUE_IssueAssetTemplateWorkflowStates

    01/09/2009 10:55:12,spid15s,Unknown,from

    01/09/2009 10:55:12,spid15s,Unknown,OptionalFieldCollectionSiteID

    01/09/2009 10:55:12,spid15s,Unknown,OptionalFieldCollectionID

    01/09/2009 10:55:12,spid15s,Unknown,MandatoryFieldCollectionSiteID

    01/09/2009 10:55:12,spid15s,Unknown,MandatoryFieldCollectionID

    01/09/2009 10:55:12,spid15s,Unknown,WorkflowStateSiteID

    01/09/2009 10:55:12,spid15s,Unknown,WorkflowStateID

    01/09/2009 10:55:12,spid15s,Unknown,@SiteID

    01/09/2009 10:55:12,spid15s,Unknown,@IssueAssetID

    01/09/2009 10:55:12,spid15s,Unknown,@SiteID

    01/09/2009 10:55:12,spid15s,Unknown,select

    01/09/2009 10:55:12,spid15s,Unknown,OptionalFieldCollectionSiteID)

    01/09/2009 10:55:12,spid15s,Unknown,OptionalFieldCollectionID

    01/09/2009 10:55:12,spid15s,Unknown,MandatoryFieldCollectionSiteID

    01/09/2009 10:55:12,spid15s,Unknown,MandatoryFieldCollectionID

    01/09/2009 10:55:12,spid15s,Unknown,WorkflowStateSiteID

    01/09/2009 10:55:12,spid15s,Unknown,WorkflowStateID

    01/09/2009 10:55:12,spid15s,Unknown,IssueAssetSiteID

    01/09/2009 10:55:12,spid15s,Unknown,IssueAssetID

    01/09/2009 10:55:12,spid15s,Unknown,SiteID

    01/09/2009 10:55:12,spid15s,Unknown,insert into dbo.TABLE_A (

    01/09/2009 10:55:12,spid15s,Unknown,frame procname=MYDATABASE.dbo.PROC_1 line=435 stmtstart=26886 stmtend=28400 sqlhandle=0x03000500110587366cf68000469b00000100000000000000

    01/09/2009 10:55:12,spid15s,Unknown,executionStack

    01/09/2009 10:55:12,spid15s,Unknown,process id=processf15588 taskpriority=0 logused=6608 waitresource=PAGE: 5:1:226585 waittime=8171 ownerId=497761914 transactionname=user_transaction lasttranstarted=2009-01-09T10:55:04.373 XDES=0xa4cba700 lockMode=IX schedulerid=2 kpid=4216 status=suspended spid=54 sbid=0 ecid=0 priority=0 transcount=3 lastbatchstarted=2009-01-09T10:55:04.373 lastbatchcompleted=2009-01-09T10:55:04.373 clientapp=.Net SqlClient Data Provider hostname=CLIENTMACHINE hostpid=500 loginname=DOMAINUSER1 isolationlevel=read committed (2) xactid=497761914 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056

    01/09/2009 10:55:12,spid15s,Unknown,Proc [Database Id = 5 Object Id = 930818378]

    01/09/2009 10:55:12,spid15s,Unknown,inputbuf

    01/09/2009 10:55:12,spid15s,Unknown,-- Inserting generic fields if any

    01/09/2009 10:55:12,spid15s,Unknown,-- 2c.

    01/09/2009 10:55:12,spid15s,Unknown,@Comments=@Comments

    01/09/2009 10:55:12,spid15s,Unknown,@IssueAssetGenerics

    01/09/2009 10:55:12,spid15s,Unknown,@Components

    01/09/2009 10:55:12,spid15s,Unknown,@Platforms

    01/09/2009 10:55:12,spid15s,Unknown,@Versions

    01/09/2009 10:55:12,spid15s,Unknown,@TargetReleaseVersionSiteID

    01/09/2009 10:55:12,spid15s,Unknown,@TargetReleaseVersionID

    01/09/2009 10:55:12,spid15s,Unknown,@TargetReleaseLabelSiteID

    01/09/2009 10:55:12,spid15s,Unknown,@TargetReleaseLabelID

    01/09/2009 10:55:12,spid15s,Unknown,@Rank

    01/09/2009 10:55:12,spid15s,Unknown,@PrioritySiteID

    01/09/2009 10:55:12,spid15s,Unknown,@PriorityID

    01/09/2009 10:55:12,spid15s,Unknown,@BatchSiteID

    01/09/2009 10:55:12,spid15s,Unknown,@BatchID

    01/09/2009 10:55:12,spid15s,Unknown,@AssetSiteID

    01/09/2009 10:55:12,spid15s,Unknown,@AssetID

    01/09/2009 10:55:12,spid15s,Unknown,@IssueAssetTemplateSiteID

    01/09/2009 10:55:12,spid15s,Unknown,@IssueAssetTemplateID

    01/09/2009 10:55:12,spid15s,Unknown,@SiteID

    01/09/2009 10:55:12,spid15s,Unknown,@IssueID

    01/09/2009 10:55:12,spid15s,Unknown,exec dbo.PROC_1

    01/09/2009 10:55:12,spid15s,Unknown,frame procname=MYDATABASE.dbo.PROC_2 line=287 stmtstart=18526 stmtend=19616 sqlhandle=0x030005004a297b37a0f68000469b00000100000000000000

    01/09/2009 10:55:12,spid15s,Unknown,-- If this is a move-to-other-asset operation delete the old asset

    01/09/2009 10:55:12,spid15s,Unknown,-- 9.

    01/09/2009 10:55:12,spid15s,Unknown,-- ==========================================================

    01/09/2009 10:55:12,spid15s,Unknown,-- ==========================================================

    01/09/2009 10:55:12,spid15s,Unknown,@Comments = @Comments

    01/09/2009 10:55:12,spid15s,Unknown,@BatchSiteID = @BatchSiteID

    01/09/2009 10:55:12,spid15s,Unknown,@BatchID = @BatchID

    01/09/2009 10:55:12,spid15s,Unknown,@DueTime = null

    01/09/2009 10:55:12,spid15s,Unknown,@WorkflowStateSiteID = @WorkflowStateSiteID

    01/09/2009 10:55:12,spid15s,Unknown,@WorkflowStateID = @WorkflowStateID

    01/09/2009 10:55:12,spid15s,Unknown,@EmployeeSiteID = @AssignedEmployeeSiteID

    01/09/2009 10:55:12,spid15s,Unknown,@EmployeeID = @AssignedEmployeeID

    01/09/2009 10:55:12,spid15s,Unknown,@IssueAssetSiteID = @SiteID

    01/09/2009 10:55:12,spid15s,Unknown,@IssueAssetID = @IssueAssetID

    01/09/2009 10:55:12,spid15s,Unknown,@SiteID = @SiteID

    01/09/2009 10:55:12,spid15s,Unknown,exec dbo.PROC_3

    01/09/2009 10:55:12,spid15s,Unknown,frame procname=MYDATABASE.dbo.PROC_1 line=532 stmtstart=32878 stmtend=34178 sqlhandle=0x03000500110587366cf68000469b00000100000000000000

    01/09/2009 10:55:12,spid15s,Unknown,-- Logging:

    01/09/2009 10:55:12,spid15s,Unknown,-- ----------------------------

    01/09/2009 10:55:12,spid15s,Unknown,@PersonSiteID)

    01/09/2009 10:55:12,spid15s,Unknown,@PersonID

    01/09/2009 10:55:12,spid15s,Unknown,@DueTime

    01/09/2009 10:55:12,spid15s,Unknown,@EmployeeSiteID

    01/09/2009 10:55:12,spid15s,Unknown,@EmployeeID

    01/09/2009 10:55:12,spid15s,Unknown,@WorkflowStateSiteID

    01/09/2009 10:55:12,spid15s,Unknown,@WorkflowStateID

    01/09/2009 10:55:12,spid15s,Unknown,@IssueAssetSiteID

    01/09/2009 10:55:12,spid15s,Unknown,@IssueAssetID

    01/09/2009 10:55:12,spid15s,Unknown,@SiteID

    01/09/2009 10:55:12,spid15s,Unknown,values (

    01/09/2009 10:55:12,spid15s,Unknown,AssignerSiteID)

    01/09/2009 10:55:12,spid15s,Unknown,AssignerID

    01/09/2009 10:55:12,spid15s,Unknown,DueTime

    01/09/2009 10:55:12,spid15s,Unknown,EmployeeSiteID

    01/09/2009 10:55:12,spid15s,Unknown,EmployeeID

    01/09/2009 10:55:12,spid15s,Unknown,WorkflowStateSiteID

    01/09/2009 10:55:12,spid15s,Unknown,WorkflowStateID

    01/09/2009 10:55:12,spid15s,Unknown,IssueAssetSiteID

    01/09/2009 10:55:12,spid15s,Unknown,IssueAssetID

    01/09/2009 10:55:12,spid15s,Unknown,SiteID

    01/09/2009 10:55:12,spid15s,Unknown,insert into TABLE_B (

    01/09/2009 10:55:12,spid15s,Unknown,frame procname=MYDATABASE.dbo.PROC_3 line=23 stmtstart=976 stmtend=1876 sqlhandle=0x030005009e76092c73f28000469b00000100000000000000

    01/09/2009 10:55:12,spid15s,Unknown,exec dbo.PROC_4

    01/09/2009 10:55:12,spid15s,Unknown,frame procname=MYDATABASE.dbo.PROC_5 line=6 stmtstart=220 stmtend=302 sqlhandle=0x030005003c8e7533988bf100e79900000000000000000000

    01/09/2009 10:55:12,spid15s,Unknown,-- Finding Wait-For IssueAsset conditions that are met:

    01/09/2009 10:55:12,spid15s,Unknown,-- ==========================================================

    01/09/2009 10:55:12,spid15s,Unknown,-- ==========================================================

    01/09/2009 10:55:12,spid15s,Unknown,ws.FlowOrder >= wc.FlowOrder

    01/09/2009 10:55:12,spid15s,Unknown,where

    01/09/2009 10:55:12,spid15s,Unknown,dbo.TABLE_C wc on c.WorkflowStateID = wc.ID and c.WorkflowStateSiteID = wc.SiteID

    01/09/2009 10:55:12,spid15s,Unknown,inner join

    01/09/2009 10:55:12,spid15s,Unknown,dbo.TABLE_D c on c.IssueID = s.IssueID and c.IssueSiteID = s.IssueSiteID

    01/09/2009 10:55:12,spid15s,Unknown,inner join

    01/09/2009 10:55:12,spid15s,Unknown,dbo.TABLE_C ws on s.WorkflowStateID = ws.ID and s.WorkflowStateSiteID = ws.SiteID

    01/09/2009 10:55:12,spid15s,Unknown,inner join

    01/09/2009 10:55:12,spid15s,Unknown,dbo.TABLEVALFUNC1() s

    01/09/2009 10:55:12,spid15s,Unknown,from

    01/09/2009 10:55:12,spid15s,Unknown,c.ID c.WorkflowStateSiteID

    01/09/2009 10:55:12,spid15s,Unknown,select

    01/09/2009 10:55:12,spid15s,Unknown,@DoneWaitForConditions (ID WorkflowStateSiteID)

    01/09/2009 10:55:12,spid15s,Unknown,insert into

    01/09/2009 10:55:12,spid15s,Unknown,frame procname=MYDATABASE.dbo.PROC_4 line=16 stmtstart=1042 stmtend=2938 sqlhandle=0x030005002d74b63249f58000469b00000100000000000000

    01/09/2009 10:55:12,spid15s,Unknown,i.ID as Iss

    01/09/2009 10:55:12,spid15s,Unknown,select

    01/09/2009 10:55:12,spid15s,Unknown,union

    01/09/2009 10:55:12,spid15s,Unknown,TABLE_C ws on ls.FlowOrder = ws.FlowOrder

    01/09/2009 10:55:12,spid15s,Unknown,inner join

    01/09/2009 10:55:12,spid15s,Unknown,LowestStates ls

    01/09/2009 10:55:12,spid15s,Unknown,from

    01/09/2009 10:55:12,spid15s,Unknown,ws.StateName as WorkflowDisplayName

    01/09/2009 10:55:12,spid15s,Unknown,ws.IdentityName as WorkflowIdentityName

    01/09/2009 10:55:12,spid15s,Unknown,ws.SiteID as WorkflowStateSiteID

    01/09/2009 10:55:12,spid15s,Unknown,ws.ID as WorkflowStateID

    01/09/2009 10:55:12,spid15s,Unknown,ls.IssueSiteID

    01/09/2009 10:55:12,spid15s,Unknown,ls.IssueID

    01/09/2009 10:55:12,spid15s,Unknown,select

    01/09/2009 10:55:12,spid15s,Unknown,WorkflowDisplayName)

    01/09/2009 10:55:12,spid15s,Unknown,WorkflowIdentityName

    01/09/2009 10:55:12,spid15s,Unknown,WorkflowStateSiteID

    01/09/2009 10:55:12,spid15s,Unknown,WorkflowStateID

    01/09/2009 10:55:12,spid15s,Unknown,IssueSiteID

    01/09/2009 10:55:12,spid15s,Unknown,IssueID

    01/09/2009 10:55:12,spid15s,Unknown,insert into @CurrentStates(

    01/09/2009 10:55:12,spid15s,Unknown,i.[Key])

    01/09/2009 10:55:12,spid15s,Unknown,ia.IssueSiteID

    01/09/2009 10:55:12,spid15s,Unknown,ia.IssueID

    01/09/2009 10:55:12,spid15s,Unknown,group by

    01/09/2009 10:55:12,spid15s,Unknown,a.Completed = 0

    01/09/2009 10:55:12,spid15s,Unknown,where

    01/09/2009 10:55:12,spid15s,Unknown,TABLE_C w on w.ID = a.WorkflowStateID and w.SiteID = a.WorkflowStateSiteID

    01/09/2009 10:55:12,spid15s,Unknown,inner join

    01/09/2009 10:55:12,spid15s,Unknown,TABLE_E i on ia.IssueID = i.ID and ia.IssueSiteID = i.SiteID

    01/09/2009 10:55:12,spid15s,Unknown,inner join

    01/09/2009 10:55:12,spid15s,Unknown,TABLE_F ia on ia.ID = a.IssueAssetID and ia.SiteID = a.IssueAssetSiteID

    01/09/2009 10:55:12,spid15s,Unknown,inner join

    01/09/2009 10:55:12,spid15s,Unknown,TABLE_B a

    01/09/2009 10:55:12,spid15s,Unknown,from

    01/09/2009 10:55:12,spid15s,Unknown,min(w.FlowOrder)

    01/09/2009 10:55:12,spid15s,Unknown,i.[Key]

    01/09/2009 10:55:12,spid15s,Unknown,ia.IssueSiteID

    01/09/2009 10:55:12,spid15s,Unknown,ia.IssueID

    01/09/2009 10:55:12,spid15s,Unknown,(select

    01/09/2009 10:55:12,spid15s,Unknown,with LowestStates (IssueID FlowOrder) as

    01/09/2009 10:55:12,spid15s,Unknown,frame procname=MYDATABASE.dbo.TABLEVALFUNC1 line=14 stmtstart=528 stmtend=3698 sqlhandle=0x03000500b8590e17b63bc000c59a00000000000000000000

    01/09/2009 10:55:12,spid15s,Unknown,executionStack

    01/09/2009 10:55:12,spid15s,Unknown,process id=processc8fd68 taskpriority=0 logused=10740 waitresource=PAGE: 5:1:225607 waittime=4953 ownerId=497761708 transactionname=user_transaction lasttranstarted=2009-01-09T10:55:03.843 XDES=0xca0a34a0 lockMode=S schedulerid=1 kpid=3700 status=suspended spid=62 sbid=0 ecid=0 priority=0 transcount=3 lastbatchstarted=2009-01-09T10:55:03.843 lastbatchcompleted=2009-01-09T10:55:03.843 clientapp=.Net SqlClient Data Provider hostname=CLIENTMACHINE2 hostpid=3348 loginname=DOMAINUSER2 isolationlevel=read committed (2) xactid=497761708 currentdb=5 lockTimeout=4294967295 clientoption1=671219744 clientoption2=128056

    01/09/2009 10:55:12,spid15s,Unknown,process-list

    01/09/2009 10:55:12,spid15s,Unknown,deadlock victim=processf15588

    01/09/2009 10:55:12,spid15s,Unknown,deadlock-list

  • January 14, 2009 at 9:26 am

    #926678

    In the future, if you post pieces from the error log, please find and open the error log in a text editor. The log reader within management studio puts the newer rows at the top and, as a result, that deadlock graph is upside down (starts at the bottom of the page and ends at the top) and it a little more difficult to read than normal.

    I'll look at it later.

    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

  • January 15, 2009 at 1:50 am

    #927117

    Ok, will do that.

    I just exported from the log viewer.

    I'll look forward to hear from you when you've had the time to look at it.

    Do you want me to post in the right order?

    /Anders

  • January 15, 2009 at 3:39 am

    #927155

    I see you point now....

    Here is another one, which i would actually rather like you to see on. The first one may be caused by some manual run procedures from another guy. It appears so after i have looked a bit at it.

    But here comes a graph, in correct order and indents.

    2009-01-15 11:10:35.00 spid7s deadlock-list

    2009-01-15 11:10:35.00 spid7s deadlock victim=processf14c58

    2009-01-15 11:10:35.00 spid7s process-list

    2009-01-15 11:10:35.00 spid7s process id=processc8f198 taskpriority=0 logused=1120 waitresource=KEY: 5:72057596752822272 (f40080bbf0db) waittime=4375 ownerId=513956241 transactionname=user_transaction lasttranstarted=2009-01-15T11:10:30.520 XDES=0xac49ad30 lockMode=S schedulerid=1 kpid=6004 status=suspended spid=63 sbid=0 ecid=0 priority=0 transcount=1 lastbatchstarted=2009-01-15T11:10:30.520 lastbatchcompleted=2009-01-15T11:10:30.520 clientapp=.Net SqlClient Data Provider hostname=ETI-SRV38 hostpid=1956 loginname=DOMAINUSER2 isolationlevel=read committed (2) xactid=513956241 currentdb=5 lockTimeout=4294967295 clientoption1=671219744 clientoption2=128056

    2009-01-15 11:10:35.00 spid7s executionStack

    2009-01-15 11:10:35.00 spid7s frame procname=DATABASE.dbo.Proc1 line=7 stmtstart=284 stmtend=654 sqlhandle=0x03000500036a8132988bf100e79900000000000000000000

    2009-01-15 11:10:35.00 spid7s set @MultiAssignments = (select count(*)

    2009-01-15 11:10:35.00 spid7s from dbo.TABLEA

    2009-01-15 11:10:35.00 spid7s where Completed=0

    2009-01-15 11:10:35.00 spid7s group by IssueAssetID, IssueAssetSiteID

    2009-01-15 11:10:35.00 spid7s having count(*) > 1)

    2009-01-15 11:10:35.00 spid7s frame procname=DATABASE.dbo.PROC2 line=109 stmtstart=7052 stmtend=7564 sqlhandle=0x030005006280551116f58000469b00000100000000000000

    2009-01-15 11:10:35.00 spid7s update

    2009-01-15 11:10:35.00 spid7s dbo.TABLEA

    2009-01-15 11:10:35.00 spid7s set

    2009-01-15 11:10:35.00 spid7s DateModified=getdate(),

    2009-01-15 11:10:35.00 spid7s Completed=1

    2009-01-15 11:10:35.00 spid7s where

    2009-01-15 11:10:35.00 spid7s IssueAssetID=@IssueAssetID

    2009-01-15 11:10:35.00 spid7s and IssueAssetSiteID=@IssueAssetSiteID

    2009-01-15 11:10:35.00 spid7s and Completed=0

    2009-01-15 11:10:35.00 spid7s -- =======================================================

    2009-01-15 11:10:35.00 spid7s inputbuf

    2009-01-15 11:10:35.00 spid7s Proc [Database Id = 5 Object Id = 290816098]

    2009-01-15 11:10:35.00 spid7s process id=processf14c58 taskpriority=0 logused=948 waitresource=PAGE: 5:1:225741 waittime=4250 ownerId=513956007 transactionname=user_transaction lasttranstarted=2009-01-15T11:10:30.163 XDES=0xe2359710 lockMode=S schedulerid=2 kpid=4980 status=suspended spid=76 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2009-01-15T11:10:30.163 lastbatchcompleted=2009-01-15T11:10:30.163 clientapp=.Net SqlClient Data Provider hostname=ETI-SRV38 hostpid=1956 loginname=DOMAINUSER1 isolationlevel=read committed (2) xactid=513956007 currentdb=5 lockTimeout=4294967295 clientoption1=671219744 clientoption2=128056

    2009-01-15 11:10:35.00 spid7s executionStack

    2009-01-15 11:10:35.00 spid7s frame procname=DATABASE.dbo.PROC3 line=15 stmtstart=562 stmtend=3048 sqlhandle=0x030005005fae95779134c000c59a00000000000000000000

    2009-01-15 11:10:35.00 spid7s insert into @CurrentStates(

    2009-01-15 11:10:35.00 spid7s IssueAssetID,

    2009-01-15 11:10:35.00 spid7s IssueAssetSiteID,

    2009-01-15 11:10:35.00 spid7s WorkflowStateID,

    2009-01-15 11:10:35.00 spid7s WorkflowStateSiteID,

    2009-01-15 11:10:35.00 spid7s WorkflowIdentityName,

    2009-01-15 11:10:35.00 spid7s WorkflowDisplayName)

    2009-01-15 11:10:35.00 spid7s select

    2009-01-15 11:10:35.00 spid7s a.IssueAssetID,

    2009-01-15 11:10:35.00 spid7s a.IssueAssetSiteID,

    2009-01-15 11:10:35.00 spid7s w.ID as WorkflowStateID,

    2009-01-15 11:10:35.00 spid7s w.SiteID as WorkflowStateSiteID,

    2009-01-15 11:10:35.00 spid7s w.IdentityName as WorkflowStateIdentityName,

    2009-01-15 11:10:35.00 spid7s w.StateName as WorkflowStateDisplayName

    2009-01-15 11:10:35.00 spid7s from

    2009-01-15 11:10:35.00 spid7s TABLEB

    2009-01-15 11:10:35.00 spid7s inner join

    2009-01-15 11:10:35.00 spid7s TABLEC w on a.WorkflowStateID = w.ID and a.WorkflowStateSiteID = w.SiteID

    2009-01-15 11:10:35.00 spid7s where

    2009-01-15 11:10:35.00 spid7s a.Completed = 0

    2009-01-15 11:10:35.00 spid7s group by

    2009-01-15 11:10:35.00 spid7s a.IssueAssetID,

    2009-01-15 11:10:35.00 spid7s a.IssueAssetSiteID,

    2009-01-15 11:10:35.00 spid7s w.ID,

    2009-01-15 11:10:35.00 spid7s w.SiteID,

    2009-01-15 11:10:35.00 spid7s w.IdentityName,

    2009-01-15 11:10:35.00 spid7s w.StateName

    2009-01-15 11:10:35.00 spid7s union

    2009-01-15 11:10:35.00 spid7s select

    2009-01-15 11:10:35.00 spid7s a.IssueAssetID,

    2009-01-15 11:10:35.00 spid7s a.IssueAssetSiteID,

    2009-01-15 11:10:35.00 spid7s w.ID as WorkflowStateID,

    2009-01-15 11:10:35.00 spid7s w.SiteID as WorkflowStateSiteID,

    2009-01-15 11:10:35.00 spid7s w.IdentityName as WorkflowStateIdentityName,

    2009-01-15 11:10:35.00 spid7s w.StateName as WorkflowStateDisplayName

    2009-01-15 11:10:35.00 spid7s from

    2009-01-15 11:10:35.00 spid7s TABLEB inner join TABLED i on a.IssueAssetID = i.ID and a.IssueAssetSiteID = i.SiteID,

    2009-01-15 11:10:35.00 spid7s TABLEC w

    2009-01-15 11:10:35.00 spid7s where

    2009-01-15 11:10:35.00 spid7s w.Identi

    2009-01-15 11:10:35.00 spid7s frame procname=DATABASE.dbo.PROC4 line=38 stmtstart=2940 stmtend=4876 sqlhandle=0x030005002d74b63249f58000469b00000100000000000000

    2009-01-15 11:10:35.00 spid7s insert into

    2009-01-15 11:10:35.00 spid7s @DoneWaitForConditions (ID, SiteID, AssignmentID, AssignmentSiteID, IssueID, IssueSiteID, IssueAssetID, IssueAssetSiteID, WorkflowStateID, WorkflowStateSiteID)

    2009-01-15 11:10:35.00 spid7s select

    2009-01-15 11:10:35.00 spid7s c.ID, c.SiteID, c.AssignmentID, c.AssignmentSiteID, null, null, c.IssueAssetID, c.IssueAssetSiteID, c.WorkflowStateID, c.WorkflowStateSiteID

    2009-01-15 11:10:35.00 spid7s from

    2009-01-15 11:10:35.00 spid7s dbo.PROC3() ia

    2009-01-15 11:10:35.00 spid7s inner join

    2009-01-15 11:10:35.00 spid7s ISSUE_WaitForConditions c on ia.IssueAssetID = c.IssueAssetID and ia.IssueAssetSiteID = c.IssueAssetSiteID

    2009-01-15 11:10:35.00 spid7s inner join

    2009-01-15 11:10:35.00 spid7s TABLEC wcur on ia.WorkflowStateID = wcur.ID and ia.WorkflowStateSiteID = wcur.SiteID

    2009-01-15 11:10:35.00 spid7s inner join

    2009-01-15 11:10:35.00 spid7s TABLEC wcon on c.WorkflowStateID = wcon.ID and c.WorkflowStateSiteID = wcon.SiteID

    2009-01-15 11:10:35.00 spid7s where

    2009-01-15 11:10:35.00 spid7s wcur.FlowOrder >= wcon.FlowOrder

    2009-01-15 11:10:35.00 spid7s -- ==========================================================

    2009-01-15 11:10:35.00 spid7s -- ==========================================================

    2009-01-15 11:10:35.00 spid7s -- Deleting found conditions

    2009-01-15 11:10:35.02 spid7s frame procname=DATABASE.dbo.PROC5 line=6 stmtstart=220 stmtend=302 sqlhandle=0x030005003c8e7533988bf100e79900000000000000000000

    2009-01-15 11:10:35.02 spid7s exec dbo.PROC4

    2009-01-15 11:10:35.02 spid7s frame procname=DATABASE.dbo.PROC6line=49 stmtstart=2704 stmtend=3344 sqlhandle=0x03000500d79afd2cd88af100e79900000100000000000000

    2009-01-15 11:10:35.02 spid7s update

    2009-01-15 11:10:35.02 spid7s dbo.TABLEA

    2009-01-15 11:10:35.02 spid7s set

    2009-01-15 11:10:35.02 spid7s Completed=1,

    2009-01-15 11:10:35.02 spid7s DateModified=getdate()

    2009-01-15 11:10:35.02 spid7s where

    2009-01-15 11:10:35.02 spid7s ID=@CurrentAssignmentID

    2009-01-15 11:10:35.02 spid7s and SiteID=@CurrentAssignmentSiteID

    2009-01-15 11:10:35.02 spid7s -- ================================================

    2009-01-15 11:10:35.02 spid7s -- ================================================

    2009-01-15 11:10:35.02 spid7s -- Creating the new assignment

    2009-01-15 11:10:35.02 spid7s inputbuf

    2009-01-15 11:10:35.02 spid7s Proc [Database Id = 5 Object Id = 754817751]

    2009-01-15 11:10:35.02 spid7s resource-list

    2009-01-15 11:10:35.02 spid7s keylock hobtid=72057596752822272 dbid=5 objectname=DATABASE.dbo.TABLEA indexname=IX_ISSUE_Assignments_Completed_Issue id=lockd91c6380 mode=X associatedObjectId=72057596752822272

    2009-01-15 11:10:35.02 spid7s owner-list

    2009-01-15 11:10:35.02 spid7s owner id=processf14c58 mode=X

    2009-01-15 11:10:35.02 spid7s waiter-list

    2009-01-15 11:10:35.02 spid7s waiter id=processc8f198 mode=S requestType=wait

    2009-01-15 11:10:35.02 spid7s pagelock fileid=1 pageid=225741 dbid=5 objectname=DATABASE.dbo.TABLED id=lockd942a580 mode=IX associatedObjectId=72057596754722816

    2009-01-15 11:10:35.02 spid7s owner-list

    2009-01-15 11:10:35.02 spid7s owner id=processc8f198 mode=IX

    2009-01-15 11:10:35.02 spid7s waiter-list

    2009-01-15 11:10:35.02 spid7s waiter id=processf14c58 mode=S requestType=wait

Viewing 15 posts - 1 through 14 (of 14 total)

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