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


Resolving a deadlock


Resolving a deadlock

Author
Message
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: 5957 Visits: 8312
My vote is for the huge covering index being the culprit here...

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8239 Visits: 14368
These are the two indexes involved in the deadlock:

CREATE UNIQUE CLUSTERED INDEX [CI_Patient_PracticeID_PatientID] ON [dbo].[Patient] 
(
[PracticeID] ASC,
[PatientID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Patient]...
...
CONSTRAINT [PK_Patient] PRIMARY KEY NONCLUSTERED
(
[PatientID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
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
One of my thoughts on the indexes was to Make the Primary Key PK_Patient the Clustered index and then make the Practice_patientid index only a nonclustered index on the practiceID.
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
Sorry About that here is the code:
CREATE PROCEDURE dbo.PatientDataProvider_UpdatePatient
@patient_id INT,
@prefix VARCHAR(16),
@first_name VARCHAR(32),
@middle_name VARCHAR(32),
@last_name VARCHAR(32),
@suffix VARCHAR(16),
@address_1 VARCHAR(128) = NULL,
@address_2 VARCHAR(128) = NULL,
@city VARCHAR(32) = NULL,
@state VARCHAR(2) = NULL,
@country VARCHAR(32) = NULL,
@zip VARCHAR(9) = NULL,
@gender VARCHAR(1) = 'U',
@marital_status VARCHAR(1) = 'U',
@home_phone VARCHAR(10) = NULL,
@home_phone_x VARCHAR(10) = NULL,
@work_phone VARCHAR(10) = NULL,
@work_phone_x VARCHAR(10) = NULL,
@dob DATETIME = NULL,
@ssn CHAR(9) = NULL,
@email VARCHAR(128) = NULL,
@sendEmailCorrespondence BIT = 1,
@guarantor_different BIT = 0,
@employment_status CHAR(1) = NULL,
@referrer_id INT = NULL,
@primary_provider_id INT = NULL,
@patient_referral_source_id INT = NULL,
@patient_default_location_id INT = NULL,
@employer_id INT = NULL,
@medical_record_number VARCHAR(128) = NULL,
@mobile_phone VARCHAR(10) = NULL,
@mobile_phone_x VARCHAR(10) = NULL,
@primary_care_physician_id INT = NULL,
@modified_user_id INT = 0,
@active BIT,
@PhonecallRemindersEnabled BIT = 0,
@EmergencyName VARCHAR(128) = NULL,
@EmergencyPhone VARCHAR(10) = NULL,
@EmergencyPhoneExt VARCHAR(10) = NULL
AS
BEGIN

IF @email IS NULL OR LTRIM(@email) = ''
BEGIN
SET @sendEmailCorrespondence = 0 ;
END

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 = @mobile_phone_x,
PrimaryCarePhysicianID = @primary_care_physician_id,
ModifiedDate = GETDATE(),
ModifiedUserID = @modified_user_id,
Active = @Active,
PhonecallRemindersEnabled = @PhonecallRemindersEnabled,
EmergencyName = @EmergencyName,
EmergencyPhone = @EmergencyPhone,
EmergencyPhoneExt = @EmergencyPhoneExt
WHERE PatientID = @patient_id ;
END


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: 5957 Visits: 8312
So, you update the DOB with the sproc UPDATE statement, and then the trigger updates the DOB too (after removing the time)? Uh, maybe just strip off the time before you do the sproc UPDATE and eliminate the trigger?

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
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
That would be ideal. The problem is there are many places that update or insert into the patient table. And no one can tell me all the places. They may not all be in stored procs. So If I don't have the trigger it could be missed somewhere and cause other issues.
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8239 Visits: 14368
Just had this same question on another thread with a trigger and doing a large insert...almost verbatim from the other thread:

One thing I can say is that if you were to alter your trigger to skip the update and you supplied the correct DOB in your INSERT statement you would save yourself a ton of I/O. You would need to do some impact analysis however to ensure a change like this would not compromise your data should other inserts or updates supply invalid values for that column thereby circumventing the usefulness of the trigger. If that were a concern there are other things you could do with CONTEXT_INFO to skip the work in the trigger for only your batch process.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
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
How do you determine the Context_info? I don't understand how I could use that to avoid the trigger?
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8239 Visits: 14368
You would modify your proc to set CONTEXT_INFO before you did the UPDATE.

SET CONTEXT_INFO 0x1256698456

Then in your trigger you would check CONTEXT_INFO and if you found it had the same value you set in your proc you would skip the UPDATE:

SET @var = CONTEXT_INFO();
IF @var != 0x1256698456
BEGIN
-- do update as usual
END



http://msdn.microsoft.com/en-us/library/ms180125(v=sql.90).aspx

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
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
Thats worth a shot. I'll do some testing. Thanks
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