Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DeadLocking issue Expand / Collapse
Author
Message
Posted Tuesday, November 20, 2012 5:38 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 7:06 PM
Points: 1,068, Visits: 416
I want to confirm that I am reading the deadlock information properly. It looks like to me that there are two update procedures running against the patient table using overlapping indexes. Is that what is causing the deadlock?

<deadlock-list><deadlock victim="processaac49b8"><process-list><process id="process600868" taskpriority="0" logused="516" waitresource="KEY: 815:1438969838829568 (2200564780d4)" waittime="3010" ownerId="2052797617" transactionname="UPDATE" lasttranstarted="2012-11-12T09:44:14.633" XDES="0x9e2df5170" lockMode="U" schedulerid="5" kpid="8056" status="suspended" spid="88" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2012-11-12T09:44:12.303" lastbatchcompleted="2012-11-12T09:44:12.303" clientapp=".Net SqlClient Data Provider" hostname="KPROD-API01" hostpid="2864" loginname="dev" isolationlevel="read committed (2)" xactid="2052797617" currentdb="815" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056"><executionStack><frame procname="superbill_8647_prod.dbo.tr_IU_Patient_ChangeTime" line="20" stmtstart="1136" stmtend="1422" sqlhandle="0x03002f03f90a702aee0870017f9d00000000000000000000">
UPDATE P
SET DOB = dbo.fn_ReplaceTimeInDate(i.DOB)
FROM dbo.Patient P INNER JOIN
inserted i ON
P.PatientID = i.PatientID </frame><frame procname="superbill_8647_prod.dbo.PatientDataProvider_UpdatePatient" line="58" stmtstart="3208" stmtend="5998" sqlhandle="0x03002f039813de1694ce7a01fa9f00000100000000000000">
UPDATE dbo.Patient
SET Prefix = @prefix,
FirstName = @first_name,
MiddleName = @middle_name,
LastName = @last_name,
Suffix = @suffix,
AddressLine1 = @address_1,
AddressLine2 = @address_2,
City = @city,
State = @state,
Country = @country,
ZipCode = @zip,
Gender = @gender,
MaritalStatus = @marital_status,
HomePhone = @home_phone,
HomePhoneExt = @home_phone_x,
WorkPhone = @work_phone,
WorkPhoneExt = @work_phone_x,
DOB = @dob,
SSN = @ssn,
EmailAddress = RTRIM(LTRIM(@email)),
SendEmailCorrespondence = @sendEmailCorrespondence,
ResponsibleDifferentThanPatient = @guarantor_different,
EmploymentStatus = @employment_status,
ReferringPhysicianID = @referrer_id,
PrimaryProviderID = @primary_provider_id,
PatientReferralSourceID = @patient_referral_source_id,
DefaultServiceLocationID = @patient_default_location_id,
EmployerID = @employer_id,
MedicalRecordNumber = @medical_record_number,
MobilePhone = @mobile_phone,
MobilePhoneExt = </frame><frame procname="superbill_8647_prod.dbo.WebServiceDataProvider_UpdatePatient" line="1715" stmtstart="176350" stmtend="178234" sqlhandle="0x03002f0327118b1de1d17a01fa9f00000100000000000000">
EXEC dbo.PatientDataProvider_UpdatePatient @PatientID, @PatientPrefix,
@PatientFirstName, @PatientMiddleName, @PatientLastName,
@PatientSuffix, @PatientAddressLine1, @PatientAddressLine2,
@PatientCity, @PatientState, @PatientCountry, @PatientZipCode,
@PatientGender, @PatientMaritalStatus, @PatientHomePhone,
@PatientHomePhoneExt, @PatientWorkPhone, @PatientWorkPhoneExt,
@PatientDateOfBirth, @PatientSSN, @PatientEmail,
@PatientEmailCorrespondence, @GuarantorDifferentThanPatient,
@EmploymentStatus, @ReferringProviderID,
@DefaultRenderingProviderID, @PatientReferralSourceID, @LocationID,
@EmployerID, @PatientMedicalRecordNumber, @PatientMobilePhone,
@PatientMobilePhoneExt, @PrimaryCarePhysicianID, @userId, @active,
0, @EmergencyName, @EmergencyPhone, @EmergencyPhoneExt </frame></executionStack><inputbuf>
Proc [Database Id = 815 Object Id = 495653159] </inputbuf></process><process id="processaac49b8" taskpriority="0" logused="0" waitresource="KEY: 815:313069931986944 (2300bd474c5c)" waittime="3026" ownerId="2052797637" transactionname="SELECT" lasttranstarted="2012-11-12T09:44:14.643" XDES="0xd98f2ebf8" lockMode="U" schedulerid="8" kpid="3740" status="suspended" spid="96" sbid="0" ecid="0" priority="0" transcount="0" lastbatchstarted="2012-11-12T09:44:12.303" lastbatchcompleted="2012-11-12T09:44:12.303" clientapp=".Net SqlClient Data Provider" hostname="KPROD-API01" hostpid="2864" loginname="dev" isolationlevel="read committed (2)" xactid="2052797637" currentdb="815" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056"><executionStack><frame procname="superbill_8647_prod.dbo.WebServiceDataProvider_UpdatePatient" line="1639" stmtstart="165698" stmtend="176348" sqlhandle="0x03002f0327118b1de1d17a01fa9f00000100000000000000">
SELECT @PatientPrefix = COALESCE(@PatientPrefix, Prefix) ,
@PatientFirstName = COALESCE(@PatientFirstName, FirstName) ,
@PatientMiddleName = COALESCE(@PatientMiddleName, MiddleName) ,
@PatientLastName = COALESCE(@PatientLastName, LastName) ,
@PatientSuffix = COALESCE(@PatientSuffix, Suffix) ,
@PatientAddressLine1 = COALESCE(@PatientAddressLine1,
AddressLine1) ,
@PatientAddressLine2 = COALESCE(@PatientAddressLine2,
AddressLine2) ,
@PatientCity = COALESCE(@PatientCity, City) ,
@PatientState = COALESCE(@PatientState, State) ,
@PatientCountry = COALESCE(@PatientCountry, Country) ,
@PatientZipCode = COALESCE(@PatientZipCode, ZipCode) ,
@PatientGender = CASE WHEN @PatientGender IS NULL THEN Gender </frame></executionStack><inputbuf>
Proc [Database Id = 815 Object Id = 495653159] </inputbuf></process></process-list><resource-list><keylock hobtid="313069931986944" dbid="815" objectname="superbill_8647_prod.dbo.Patient" indexname="CI_Patient_PracticeID_PatientID" id="lock8fd58fd00" mode="X" associatedObjectId="313069931986944"><owner-list><owner id="process600868" mode="X"/></owner-list><waiter-list><waiter id="processaac49b8" mode="U" requestType="wait"/></waiter-list></keylock><keylock hobtid="1438969838829568" dbid="815" objectname="superbill_8647_prod.dbo.Patient" indexname="PK_Patient" id="lockddf593000" mode="U" associatedObjectId="1438969838829568"><owner-list><owner id="processaac49b8" mode="U"/></owner-list><waiter-list><waiter id="process600868" mode="U" requestType="wait"/></waiter-list></keylock></resource-list></deadlock></deadlock-list>

Post #1387196
Posted Wednesday, November 21, 2012 9:21 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 7:06 PM
Points: 1,068, Visits: 416
Can anyone help me with this?
Post #1387480
Posted Thursday, November 22, 2012 7:14 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:31 AM
Points: 4,356, Visits: 6,187
You have a lot of VERY complex statements there (which actually don't make sense to me) and LOTS of missing information like query plans, complete table/index definitions, etc. This problem looks to be well beyond the simple, straight-forward type of issues that forums were designed to help others solve. It could take hours to decipher things and find a root cause and resolution.

I strongly recommend that you get a paid consultant on board to help you fix this issue. Forums are "staffed" by free volunteers and most won't take the time necessary to help with a problem like this. And even when someone bites off on it, I have seen some posters spend days and even weeks going back and forth trying to resolve a complex problem that could have been resolved in a matter of minutes/hours by a qualified, dedicated professional resource.


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1387883
Posted Friday, November 23, 2012 10:21 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 8:37 AM
Points: 381, Visits: 358
I agree that there is not enough information to determine the cause of the block; however, I ran your information through some analysis code that was posted by WayneS, which I use all the time (thanks WayneS), and it looks like you are doing a Update and Select which both have a lock mode of U. According to books online:

U Mode is used on resources that can be updated. Prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and potentially updating resources later.

That said, it does not appear to be the locking mode causing the deadlock. My next plan of attack would be to look at the query plan. It is possible that the update statement is using a table scan which might be locking the entire table. Again, more info is needed. I am posting the analysis code below for you to use.

declare @deadlock xml
set @deadlock = 'put your deadlock graph here'

select
[PagelockObject] = @deadlock.value('/deadlock-list[1]/deadlock[1]/resource-list[1]/pagelock[1]/@objectname', 'varchar(200)'),
[DeadlockObject] = @deadlock.value('/deadlock-list[1]/deadlock[1]/resource-list[1]/objectlock[1]/@objectname', 'varchar(200)'),
[Victim] = case when Deadlock.Process.value('@id', 'varchar(50)') = @deadlock.value('/deadlock-list[1]/deadlock[1]/@victim', 'varchar(50)') then 1 else 0 end,
[Procedure] = Deadlock.Process.value('executionStack[1]/frame[1]/@procname[1]', 'varchar(200)'),
[LockMode] = Deadlock.Process.value('@lockMode', 'char(1)'),
[Code] = Deadlock.Process.value('executionStack[1]/frame[1]', 'varchar(1000)'),
[ClientApp] = Deadlock.Process.value('@clientapp', 'varchar(100)'),
[HostName] = Deadlock.Process.value('@hostname', 'varchar(20)'),
[LoginName] = Deadlock.Process.value('@loginname', 'varchar(20)'),
[TransactionTime] = Deadlock.Process.value('@lasttranstarted', 'datetime'),
[InputBuffer] = Deadlock.Process.value('inputbuf[1]', 'varchar(1000)')
from @deadlock.nodes('/deadlock-list/deadlock/process-list/process') as Deadlock(Process)
Post #1388275
Posted Monday, November 26, 2012 10:21 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 7:06 PM
Points: 1,068, Visits: 416
Thanks. I will also take a look at the results of the code.
Post #1388717
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse