May 1, 2012 at 9:03 pm
What all needs to be looked to troubleshoot this deadlock issue.Different value is choosen for iCustomer_ID and MD_Interestlist_ID in the below queries,however the deadlock pops up many times.There is a clustered index for MD_Interestlist_ID col.
Victim
UPDATE Customers_PHMD_Interests SET Registered_Date = '9/4/2009 6:21:03 PM', Lead_Source = 'NewHomeSource', Region = '3', MPC_Community = '10', Neighborhood = '25', Preferred_State = 'CA', Comments = 'Lead Type: Brochure', PhoneNumber = '', AddressLine1 = '1182 West 37th Place', City = '', State = '', ZipCode = '', LicenceNo = '', Builder = 'PAN', PlanName = '', PriceRange = '', PreferredMarket = 'Riverside-San Bernardino',BestContact = '', HowHeard = 'NewHomeSource', TimeFrame = '', PANCorporate = '', Updt_User_ID = 1, Updt_DateTime = getdate(), Uptd_Prgm = 'PHMDIntegration', PreferredProperty = '' WHERE iCustomer_ID = 120070 AND MD_InterestList_ID = 'NHS_3309'
Deadlocked with
UPDATE Customers_PHMD_Interests SET Registered_Date = '5/1/2012 10:34:21 AM', Lead_Source = 'PANHomesQuickReg', Region = '10', MPC_Community = '30', Neighborhood = '94', Preferred_State = '', Comments = '', PhoneNumber = '', AddressLine1 = '92130', City = '', State = '', ZipCode = '', LicenceNo = '', Builder = 'PAN', PlanName = '', PriceRange = '', PreferredMarket = '',BestContact = '', HowHeard = '', TimeFrame = '', PANCorporate = '', Updt_User_ID = 1, Updt_DateTime = getdate(), Uptd_Prgm = 'PHMDIntegration', PreferredProperty = '' WHERE iCustomer_ID = 120064 AND MD_InterestList_ID = 'PHQR_2252'
UPDATE Customers_PHMD_Interests SET Registered_Date = '2/4/2010 12:33:10 PM', Lead_Source = 'LivingSmartHomes Register', Region = '', MPC_Community = '', Neighborhood = '', Preferred_State = '', Comments = '', PhoneNumber = '', AddressLine1 = '', City = '', State = '', ZipCode = '', LicenceNo = '', Builder = 'PAN', PlanName = '', PriceRange = '', PreferredMarket = '',BestContact = '', HowHeard = '', TimeFrame = '', PANCorporate = '', Updt_User_ID = 1, Updt_DateTime = getdate(), Uptd_Prgm = 'PHMDIntegration', PreferredProperty = '' WHERE iCustomer_ID = 120084 AND MD_InterestList_ID = 'LSHR_45'
UPDATE Customers_PHMD_Interests SET Registered_Date = '9/3/2009 11:06:09 PM', Lead_Source = 'NewHomeSource', Region = '3', MPC_Community = '10', Neighborhood = '25', Preferred_State = 'CA', Comments = 'Lead Type: Brochure', PhoneNumber = '', AddressLine1 = '24640 Fuerte Rd.', City = '', State = '', ZipCode = '', LicenceNo = '', Builder = 'PAN', PlanName = '', PriceRange = '', PreferredMarket = 'Riverside-San Bernardino',BestContact = '', HowHeard = 'NewHomeSource', TimeFrame = '', PANCorporate = '', Updt_User_ID = 1, Updt_DateTime = getdate(), Uptd_Prgm = 'PHMDIntegration', PreferredProperty = '' WHERE iCustomer_ID = 120067 AND MD_InterestList_ID = 'NHS_3302'
May 2, 2012 at 12:34 am
Switch traceflag 1222 on. That will result in a deadlock graph been written to the error log every time a deadlock occurs. Post the result of that graph here.
DBCC TRACEON(1222,-1)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 2, 2012 at 5:24 am
Also, check for other statements before these that might be placing other locks on the system.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 2, 2012 at 12:36 pm
Thank you,here is the output of a new deadlock which occurred after fresh testing.
I am attaching the XML(As .txt file) and the output of results after running this query to analyze deadlocks by parsing the XML details.
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)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply