November 20, 2012 at 5:38 pm
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">
UPDATEdbo.Patient
SETPrefix = @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>
November 21, 2012 at 9:21 am
Can anyone help me with this?
November 22, 2012 at 7:14 am
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 on googles mail service
November 23, 2012 at 10:21 pm
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)
November 26, 2012 at 10:21 am
Thanks. I will also take a look at the results of the code.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply