<resource-list> Deadlock graph interpretation
in [Programming SQL]
|
From: Dan Holmes on 29 Apr 2010 15:39 in the text of the deadlock graph i have this: <resource-list> <pagelock fileid="1" pageid="41452" dbid="30" objectname="RM_PA_Septa_74346.dbo.tblRunActual" id="lock38c95d00" mode="IX" associatedObjectId="72057601943470080"> <owner-list> <owner id="process8db978" mode="IX"/> <owner id="process9695b8" mode="IX"/> </owner-list> <waiter-list> <waiter id="process8db978" mode="S" requestType="convert"/> <waiter id="process9695b8" mode="S" requestType="convert"/> </waiter-list> </pagelock> </resource-list> Does that mean the processes both have an IX lock and want to convert to S or the other way around? The full graph file is attached if that helps. (it hasn't helped me so far.)
From: Kalen Delaney on 29 Apr 2010 16:30 Hi Dan I cannot open the attached file, so I am only looking at the info in your message. IX is the lock mode in the owner list, so they already have those locks. S is the mode of the waiter list elements, so that is what they are requesting. -- HTH Kalen ---------------------------------------- Kalen Delaney SQL Server MVP www.SQLServerInternals.com "Dan Holmes" <dan.holmes(a)routematch.com> wrote in message news:edsHrO95KHA.1924(a)TK2MSFTNGP06.phx.gbl... > in the text of the deadlock graph i have this: > > <resource-list> > <pagelock fileid="1" pageid="41452" dbid="30" > objectname="RM_PA_Septa_74346.dbo.tblRunActual" id="lock38c95d00" > mode="IX" associatedObjectId="72057601943470080"> > <owner-list> > <owner id="process8db978" mode="IX"/> > <owner id="process9695b8" mode="IX"/> > </owner-list> > <waiter-list> > <waiter id="process8db978" mode="S" requestType="convert"/> > <waiter id="process9695b8" mode="S" requestType="convert"/> > </waiter-list> > </pagelock> > </resource-list> > > Does that mean the processes both have an IX lock and want to convert to S > or the other way around? The full graph file > is attached if that helps. (it hasn't helped me so far.) >
From: Dan Holmes on 29 Apr 2010 17:24 On 4/29/2010 4:30 PM, Kalen Delaney wrote: > Hi Dan > > I cannot open the attached file, so I am only looking at the info in > your message. > > IX is the lock mode in the owner list, so they already have those locks. > S is the mode of the waiter list elements, so that is what they are > requesting. > So does that mean the task already had a IX lock before the SQL in the file deadlock graph? now that statement is requrested a S lock? And it can't convert the IX to a S. Hence the deadlock. Is that the process here? Here is the whole thing. BTW, great book (SQL 2008 Internals); i am still digesting. <deadlock-list> <deadlock victim="process9695b8"> <process-list> <process id="process8db978" taskpriority="0" logused="2496" waitresource="PAGE: 30:1:41452" waittime="953" ownerId="3955996" transactionname="user_transaction" lasttranstarted="2010-04-29T14:41:40.313" XDES="0x3a67d778" lockMode="S" schedulerid="1" kpid="4508" status="suspended" spid="57" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2010-04-29T14:41:40.313" lastbatchcompleted="2010-04-29T14:41:40.313" clientapp="R3Load.exe" hostname="DHOLMES-dholmes-20100429:133324" hostpid="2824" loginname="pmuser" isolationlevel="read committed (2)" xactid="3955996" currentdb="30" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056"> <executionStack> <frame procname="RM_PA_Septa_74346.dbo.spEnsureVehicleForDate2" line="619" stmtstart="40060" stmtend="45364" sqlhandle="0x03001e00cee4211f36a4f100679d00000100000000000000"> INSERT dbo.tblRunActual( RunID, VehicleID, StartDateTime, FirstPickupTime, LastDropoffTime, EndDateTime, Verified , AgencyID, Properties, RunDate, GarageActualID, DriverID ) OUTPUT inserted.id, inserted.Runid INTO @PostedRuns (Runactualid, RunID) SELECT r.RunID, r.VehicleID, r.StartDateTime, r.StartDateTime, r.EndDateTime, r.EndDateTime, 0, @AgencyID, @RUN_PROPERTIES_POSTED, dbo.DateReturnISO(r.RunDate), @nVehicleRunActualID, ( SELECT MIN(d.DriverID) FROM dbo.DriverScheduleByDate(@GarageDateAsINT) d --driver scheduled overlap with run time WHERE dbo.cvtDatetimeToTime24(r.StartDateTime) <= d.EndTime AND dbo.cvtDatetimeToTime24(r.EndDateTime) >= d.StartTime AND d.Active = 1 ) FROM RunsForDateTimeRangeAndVehicleIDs(@VehicleStartDateTime, @VehicleEndDateTime, @sVehicleID) r; --update driverID by driver scheduling info --for vehicle = giving vehicle and run startdatetime within vehicle run startdatetime </frame> </executionStack> <inputbuf> Proc [Database Id = 30 Object Id = 522314958] </inputbuf> </process> <process id="process9695b8" taskpriority="0" logused="2496" waitresource="PAGE: 30:1:41452" waittime="937" ownerId="3955680" transactionname="user_transaction" lasttranstarted="2010-04-29T14:41:40.140" XDES="0x52117b0" lockMode="S" schedulerid="2" kpid="760" status="suspended" spid="56" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2010-04-29T14:41:40.140" lastbatchcompleted="2010-04-29T14:41:40.140" clientapp="R3Load.exe" hostname="DHOLMES-dholmes-20100429:133213" hostpid="3484" loginname="pmuser" isolationlevel="read committed (2)" xactid="3955680" currentdb="30" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056"> <executionStack> <frame procname="RM_PA_Septa_74346.dbo.spEnsureVehicleForDate2" line="619" stmtstart="40060" stmtend="45364" sqlhandle="0x03001e00cee4211f36a4f100679d00000100000000000000"> INSERT dbo.tblRunActual( RunID, VehicleID, StartDateTime, FirstPickupTime, LastDropoffTime, EndDateTime, Verified , AgencyID, Properties, RunDate, GarageActualID, DriverID ) OUTPUT inserted.id, inserted.Runid INTO @PostedRuns (Runactualid, RunID) SELECT r.RunID, r.VehicleID, r.StartDateTime, r.StartDateTime, r.EndDateTime, r.EndDateTime, 0, @AgencyID, @RUN_PROPERTIES_POSTED, dbo.DateReturnISO(r.RunDate), @nVehicleRunActualID, ( SELECT MIN(d.DriverID) FROM dbo.DriverScheduleByDate(@GarageDateAsINT) d --driver scheduled overlap with run time WHERE dbo.cvtDatetimeToTime24(r.StartDateTime) <= d.EndTime AND dbo.cvtDatetimeToTime24(r.EndDateTime) >= d.StartTime AND d.Active = 1 ) FROM RunsForDateTimeRangeAndVehicleIDs(@VehicleStartDateTime, @VehicleEndDateTime, @sVehicleID) r; --update driverID by driver scheduling info --for vehicle = giving vehicle and run startdatetime within vehicle run startdatetime </frame> </executionStack> <inputbuf> Proc [Database Id = 30 Object Id = 522314958] </inputbuf> </process> </process-list> <resource-list> <pagelock fileid="1" pageid="41452" dbid="30" objectname="RM_PA_Septa_74346.dbo.tblRunActual" id="lock38c95d00" mode="IX" associatedObjectId="72057601943470080"> <owner-list> <owner id="process8db978" mode="IX"/> <owner id="process9695b8" mode="IX"/> </owner-list> <waiter-list> <waiter id="process8db978" mode="S" requestType="convert"/> <waiter id="process9695b8" mode="S" requestType="convert"/> </waiter-list> </pagelock> </resource-list> </deadlock> </deadlock-list>
From: Kalen Delaney on 29 Apr 2010 18:20 Dan It's hard to say when exactly the locks were acquired. But before the process got blocked, it had an IX lock. It then tried to convert to S, but it couldn't, because someone else had an IX lock. -- HTH Kalen ---------------------------------------- Kalen Delaney SQL Server MVP www.SQLServerInternals.com "Dan Holmes" <dan.holmes(a)routematch.com> wrote in message news:OCxKsJ#5KHA.3184(a)TK2MSFTNGP05.phx.gbl... > On 4/29/2010 4:30 PM, Kalen Delaney wrote: >> Hi Dan >> >> I cannot open the attached file, so I am only looking at the info in >> your message. >> >> IX is the lock mode in the owner list, so they already have those locks. >> S is the mode of the waiter list elements, so that is what they are >> requesting. >> > > So does that mean the task already had a IX lock before the SQL in the > file deadlock graph? now that statement is requrested a S lock? And it > can't convert the IX to a S. Hence the deadlock. Is that the process > here? > > Here is the whole thing. BTW, great book (SQL 2008 Internals); i am still > digesting. > > > > <deadlock-list> > <deadlock victim="process9695b8"> > <process-list> > <process id="process8db978" taskpriority="0" logused="2496" > waitresource="PAGE: 30:1:41452" waittime="953" ownerId="3955996" > transactionname="user_transaction" > lasttranstarted="2010-04-29T14:41:40.313" XDES="0x3a67d778" lockMode="S" > schedulerid="1" kpid="4508" status="suspended" spid="57" sbid="0" ecid="0" > priority="0" transcount="2" lastbatchstarted="2010-04-29T14:41:40.313" > lastbatchcompleted="2010-04-29T14:41:40.313" clientapp="R3Load.exe" > hostname="DHOLMES-dholmes-20100429:133324" hostpid="2824" > loginname="pmuser" isolationlevel="read committed (2)" xactid="3955996" > currentdb="30" lockTimeout="4294967295" clientoption1="673316896" > clientoption2="128056"> > <executionStack> > <frame procname="RM_PA_Septa_74346.dbo.spEnsureVehicleForDate2" > line="619" stmtstart="40060" stmtend="45364" > sqlhandle="0x03001e00cee4211f36a4f100679d00000100000000000000"> > INSERT dbo.tblRunActual( > RunID, > VehicleID, > StartDateTime, > FirstPickupTime, > LastDropoffTime, > EndDateTime, > Verified , > AgencyID, > Properties, > RunDate, > GarageActualID, > DriverID > ) > OUTPUT inserted.id, inserted.Runid INTO @PostedRuns (Runactualid, RunID) > SELECT > r.RunID, > r.VehicleID, > r.StartDateTime, > r.StartDateTime, > r.EndDateTime, > r.EndDateTime, > 0, > @AgencyID, > @RUN_PROPERTIES_POSTED, > dbo.DateReturnISO(r.RunDate), > @nVehicleRunActualID, > ( > SELECT MIN(d.DriverID) > FROM dbo.DriverScheduleByDate(@GarageDateAsINT) d > --driver scheduled overlap with run time > WHERE > dbo.cvtDatetimeToTime24(r.StartDateTime) <= d.EndTime AND > dbo.cvtDatetimeToTime24(r.EndDateTime) >= d.StartTime > AND d.Active = 1 > ) > FROM RunsForDateTimeRangeAndVehicleIDs(@VehicleStartDateTime, > @VehicleEndDateTime, @sVehicleID) r; > > --update driverID by driver scheduling info > --for vehicle = giving vehicle and run startdatetime within vehicle run > startdatetime </frame> > </executionStack> > <inputbuf> > Proc [Database Id = 30 Object Id = 522314958] </inputbuf> > </process> > <process id="process9695b8" taskpriority="0" logused="2496" > waitresource="PAGE: 30:1:41452" waittime="937" ownerId="3955680" > transactionname="user_transaction" > lasttranstarted="2010-04-29T14:41:40.140" XDES="0x52117b0" lockMode="S" > schedulerid="2" kpid="760" status="suspended" spid="56" sbid="0" ecid="0" > priority="0" transcount="2" lastbatchstarted="2010-04-29T14:41:40.140" > lastbatchcompleted="2010-04-29T14:41:40.140" clientapp="R3Load.exe" > hostname="DHOLMES-dholmes-20100429:133213" hostpid="3484" > loginname="pmuser" isolationlevel="read committed (2)" xactid="3955680" > currentdb="30" lockTimeout="4294967295" clientoption1="673316896" > clientoption2="128056"> > <executionStack> > <frame procname="RM_PA_Septa_74346.dbo.spEnsureVehicleForDate2" > line="619" stmtstart="40060" stmtend="45364" > sqlhandle="0x03001e00cee4211f36a4f100679d00000100000000000000"> > INSERT dbo.tblRunActual( > RunID, > VehicleID, > StartDateTime, > FirstPickupTime, > LastDropoffTime, > EndDateTime, > Verified , > AgencyID, > Properties, > RunDate, > GarageActualID, > DriverID > ) > OUTPUT inserted.id, inserted.Runid INTO @PostedRuns (Runactualid, RunID) > SELECT > r.RunID, > r.VehicleID, > r.StartDateTime, > r.StartDateTime, > r.EndDateTime, > r.EndDateTime, > 0, > @AgencyID, > @RUN_PROPERTIES_POSTED, > dbo.DateReturnISO(r.RunDate), > @nVehicleRunActualID, > ( > SELECT MIN(d.DriverID) > FROM dbo.DriverScheduleByDate(@GarageDateAsINT) d > --driver scheduled overlap with run time > WHERE > dbo.cvtDatetimeToTime24(r.StartDateTime) <= d.EndTime AND > dbo.cvtDatetimeToTime24(r.EndDateTime) >= d.StartTime > AND d.Active = 1 > ) > FROM RunsForDateTimeRangeAndVehicleIDs(@VehicleStartDateTime, > @VehicleEndDateTime, @sVehicleID) r; > > --update driverID by driver scheduling info > --for vehicle = giving vehicle and run startdatetime within vehicle run > startdatetime </frame> > </executionStack> > <inputbuf> > Proc [Database Id = 30 Object Id = 522314958] </inputbuf> > </process> > </process-list> > <resource-list> > <pagelock fileid="1" pageid="41452" dbid="30" > objectname="RM_PA_Septa_74346.dbo.tblRunActual" id="lock38c95d00" > mode="IX" associatedObjectId="72057601943470080"> > <owner-list> > <owner id="process8db978" mode="IX"/> > <owner id="process9695b8" mode="IX"/> > </owner-list> > <waiter-list> > <waiter id="process8db978" mode="S" requestType="convert"/> > <waiter id="process9695b8" mode="S" requestType="convert"/> > </waiter-list> > </pagelock> > </resource-list> > </deadlock> > </deadlock-list> >
From: Dan Holmes on 30 Apr 2010 10:21 On 4/29/2010 6:20 PM, Kalen Delaney wrote: > Dan > > It's hard to say when exactly the locks were acquired. But before the > process got blocked, it had an IX lock. It then tried to convert to S, > but it couldn't, because someone else had an IX lock. > OK i figured this out (and i am proud of me :-). The deadlock graph showed an IX lock on table RunActual. It also wanted to acquire a S lock on this table. The statement in question was this pattern. INSERT INTO RunActual (.., GarageActualID, ...) SELECT ..., @value, ... FROM ... The DDL for RunActual is CREATE TABLE RunActual ( ID INT NOT NULL IDENTITY(1,1) , ... , GarageActual INT NULL CONSTRAINT FK_Self_Reference FOREIGN KEY (GarageActualID) REFERENCES Runactual (ID) , ...); So on the insert a S lock was needed on the RunActual table to validate the FK reference. The proc this SQL is in was being run simultaneously with different data, so the multiple IX lock on the same table was to be expected but i didn't understand the S lock until i read the estimated plan. Does this seem like a reasonable explanation? Kalen, thanks for the help with the deadlock graph.
|
Pages: 1 Prev: DateTime conversion from Korean Next: seeking set based solution |