Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DeadLocking issue


DeadLocking issue

Author
Message
pamozer
pamozer
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1079 Visits: 443
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>


pamozer
pamozer
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1079 Visits: 443
Can anyone help me with this?
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 6000 Visits: 8314
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
Noetic DBA
Noetic DBA
SSC-Addicted
SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)

Group: General Forum Members
Points: 431 Visits: 419
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)
pamozer
pamozer
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1079 Visits: 443
Thanks. I will also take a look at the results of the code.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search