September 14, 2012 at 3:55 am
I receive deadlock on primary key for insert and update stetement. Please find the deadlock graph below and do let me know the actionplan?
012-09-13 19:46:40.23 spid13s deadlock-list
2012-09-13 19:46:40.23 spid13s deadlock victim=processa5a6b048
2012-09-13 19:46:40.23 spid13s process-list
2012-09-13 19:46:40.23 spid13s process id=processa5a6b048 taskpriority=0 logused=26606908 waitresource=PAGE: 14:1:6039196 waittime=4401 ownerId=2885790797 transactionname=user_transaction lasttranstarted=2012-09-13T19:40:00.863 XDES=0x181a1b6f0 lockMode=IX schedulerid=2 kpid=35680 status=suspended spid=134 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2012-09-13T19:40:00.860 lastbatchcompleted=2012-09-13T19:40:00.860 clientapp=SQLAgent - TSQL JobStep (Job 0xB4B7AEB214113F408029D6FA249A90D3 : Step 1) hostname=ADCDBPRO06 hostpid=3188 loginname=BCGCOM\srvglbsql isolationlevel=read committed (2) xactid=2885790797 currentdb=14 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
2012-09-13 19:46:40.23 spid13s executionStack
2012-09-13 19:46:40.23 spid13s frame procname=SystemCheck2.dbo.sp_IMPORT_PROCESS_tb_Import_Data line=113 stmtstart=6108 stmtend=7504 sqlhandle=0x03000e00a3f06206d13cc100449f00000100000000000000
2012-09-13 19:46:40.23 spid13s UPDATE [dbo].[tb_Import_Data]
2012-09-13 19:46:40.23 spid13s SET comp_id = d.subject_id
2012-09-13 19:46:40.23 spid13s FROM tb_Import_Data i
2012-09-13 19:46:40.23 spid13s INNER JOIN tb_Working_Data d
2012-09-13 19:46:40.23 spid13s ON d.value = i.comp_name
2012-09-13 19:46:40.23 spid13s INNER JOIN tb_Working_Computer c
2012-09-13 19:46:40.23 spid13s ON d.subject_id = c.comp_id
2012-09-13 19:46:40.23 spid13s WHERE i.import_record_id> 1188
2012-09-13 19:46:40.23 spid13s --WHERE i.process_id = @process_id -- update records from this process only
2012-09-13 19:46:40.23 spid13s AND d.field_id = @field_id_pk_computer -- update record's computer name compares
2012-09-13 19:46:40.23 spid13s -- only to working data computer names
2012-09-13 19:46:40.23 spid13s AND d.[date_replaced] IS NULL -- working data computer name is current
2012-09-13 19:46:40.23 spid13s AND c.[is_active] = 1 -- working data computer name is active
2012-09-13 19:46:40.23 spid13s -- update import data table with calculated user_id
2012-09-13 19:46:40.23 spid13s frame procname=SystemCheck2.dbo.sp_IMPORT_UTILITY_RUN_PROCESS_tb_Import_Data line=29 stmtstart=1486 stmtend=1580 sqlhandle=0x03000e00e823327a70810201b09e00000100000000000000
2012-09-13 19:46:40.23 spid13s EXEC [dbo].[sp_IMPORT_PROCESS_tb_Import_Data]
2012-09-13 19:46:40.23 spid13s frame procname=adhoc line=1 sqlhandle=0x01000e001b16e113d03c48a0000000000000000000000000
2012-09-13 19:46:40.23 spid13s sp_IMPORT_UTILITY_RUN_PROCESS_tb_Import_Data
2012-09-13 19:46:40.23 spid13s inputbuf
2012-09-13 19:46:40.23 spid13s sp_IMPORT_UTILITY_RUN_PROCESS_tb_Import_Data
2012-09-13 19:46:40.23 spid13s process id=process4d83b88 taskpriority=0 logused=1116615184 waitresource=PAGE: 14:1:6039197 waittime=353357 ownerId=2874975911 transactionname=INSERT lasttranstarted=2012-09-13T19:21:39.637 XDES=0x466ba4080 lockMode=S schedulerid=11 kpid=45840 status=suspended spid=141 sbid=0 ecid=24 priority=0 trancount=0 lastbatchstarted=2012-09-13T19:20:00.150 lastbatchcompleted=2012-09-13T19:20:00.150 clientapp=SQLAgent - TSQL JobStep (Job 0x927AC05AEF42B64CAA57BA844BD8E14B : Step 1) hostname=ADCDBPRO06 hostpid=3188 isolationlevel=read committed (2) xactid=2874975911 currentdb=14 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
2012-09-13 19:46:40.23 spid13s executionStack
2012-09-13 19:46:40.23 spid13s frame procname=SystemCheck2.dbo.sp_IMPORT_CLEAN_tb_Import_Data line=77 stmtstart=3818 stmtend=5874 sqlhandle=0x03000e00924a7974b990ca00c9a000000100000000000000
2012-09-13 19:46:40.23 spid13s INSERT INTO [dbo].[tb_ARCHIVE_Import_Data]
2012-09-13 19:46:40.23 spid13s ([import_record_id]
2012-09-13 19:46:40.23 spid13s ,[comp_name]
2012-09-13 19:46:40.23 spid13s ,[user_name]
2012-09-13 19:46:40.23 spid13s ,[field_name]
2012-09-13 19:46:40.23 spid13s ,[value]
2012-09-13 19:46:40.23 spid13s ,[timestamp]
2012-09-13 19:46:40.23 spid13s ,[m1]
2012-09-13 19:46:40.23 spid13s ,[m2]
2012-09-13 19:46:40.23 spid13s ,[m3]
2012-09-13 19:46:40.23 spid13s ,[m4]
2012-09-13 19:46:40.23 spid13s ,[process_id]
2012-09-13 19:46:40.23 spid13s ,[is_duplicate]
2012-09-13 19:46:40.23 spid13s ,[subject_id]
2012-09-13 19:46:40.23 spid13s ,[comp_id]
2012-09-13 19:46:40.23 spid13s ,[user_id]
2012-09-13 19:46:40.23 spid13s ,[field_id]
2012-09-13 19:46:40.23 spid13s ,[omit_code]
2012-09-13 19:46:40.23 spid13s ,[import_date]
2012-09-13 19:46:40.23 spid13s ,[process_id_sc3]
2012-09-13 19:46:40.23 spid13s ,[omit_code_sc3]
2012-09-13 19:46:40.23 spid13s ,[model_number]
2012-09-13 19:46:40.23 spid13s ,[serial_number]
2012-09-13 19:46:40.23 spid13s ,[asset_id])
2012-09-13 19:46:40.23 spid13s SELECT
2012-09-13 19:46:40.23 spid13s i.[import_record_id]
2012-09-13 19:46:40.23 spid13s ,i.[comp_name]
2012-09-13 19:46:40.23 spid13s ,i.[user_name]
2012-09-13 19:46:40.23 spid13s ,i.[field_name]
2012-09-13 19:46:40.23 spid13s ,i.[value]
2012-09-13 19:46:40.23 spid13s ,i.[timestamp]
2012-09-13 19:46:40.23 spid13s ,i.[m1]
2012-09-13 19:46:40.23 spid13s ,i.[m2]
2012-09-13 19:46:40.23 spid13s ,i.[m3]
2012-09-13 19:46:40.23 spid13s ,i.[m4]
2012-09-13 19:46:40.23 spid13s ,i.[process_id]
2012-09-13 19:46:40.23 spid13s ,i.[is_duplicate]
2012-09-13 19:46:40.23 spid13s ,i.[subject_id]
2012-09-13 19:46:40.23 spid13s ,i.[comp_id]
2012-09-13 19:46:40.23 spid13s ,i.[user_id]
2012-09-13 19:46:40.23 spid13s ,i.[field_id]
2012-09-13 19:46:40.23 spid13s ,i.[omit_code]
2012-09-13 19:46:40.23 spid13s ,i.[import_date]
2012-09-13 19:46:40.23 spid13s ,i.[process_id_sc3]
2012-09-13 19:46:40.23 spid13s ,i.[omit_code_sc3]
2012-09-13 19:46:40.23 spid13s ,i.[model_number]
2012-09-13 19:46:40.23 spid13s ,i.[serial_number]
2012-09-13 19:46:40.23 spid13s ,i.[asset_id]
2012-09-13 19:46:40.23 spid13s FROM tb_Import_Data i
2012-09-13 19:46:40.23 spid13s LEFT JOIN tb_ARCHIVE_Import_Data a
2012-09-13 19:46:40.23 spid13s ON i.import_record_id = a.import_record_id
2012-09-13 19:46:40.23 spid13s WHERE a.import_record_id IS NULL
2012-09-13 19:46:40.23 spid13s AND i.process_id <= (
2012-09-13 19:46:40.23 spid13s SELECT MAX(p.process_id)
2012-09-13 19:46:40.23 spid13s FROM tb_Import_Processes p
2012-09-13 19:46:40.23 spid13s WHERE p.process_cleaned IS NOT NULL
2012-09-13 19:46:40.23 spid13s frame procname=adhoc line=1 sqlhandle=0x01000e001af1551d40ffe9d6020000000000000000000000
2012-09-13 19:46:40.23 spid13s exec sp_IMPORT_CLEAN_tb_Import_Data
2012-09-13 19:46:40.23 spid13s inputbuf
2012-09-13 19:46:40.23 spid13s resource-list
2012-09-13 19:46:40.23 spid13s pagelock fileid=1 pageid=6039196 dbid=14 objectname=SystemCheck2.dbo.tb_Import_Data id=lock154426a80 mode=SIU
associatedObjectId=72057594041991168
2012-09-13 19:46:40.23 spid13s owner-list
2012-09-13 19:46:40.23 spid13s owner id=process4d83b88 mode=S
2012-09-13 19:46:40.23 spid13s waiter-list
2012-09-13 19:46:40.23 spid13s waiter id=processa5a6b048 mode=IX requestType=convert
2012-09-13 19:46:40.23 spid13s pagelock fileid=1 pageid=6039197 dbid=14 objectname=SystemCheck2.dbo.tb_Import_Data id=lock39da7d400 mode=UIX
associatedObjectId=72057594041991168
2012-09-13 19:46:40.23 spid13s owner-list
2012-09-13 19:46:40.23 spid13s owner id=processa5a6b048 mode=UIX
2012-09-13 19:46:40.23 spid13s waiter-list
2012-09-13 19:46:40.23 spid13s waiter id=process4d83b88 mode=S requestType=wait
"More Green More Oxygen !! Plant a tree today"
September 14, 2012 at 3:58 am
I have added the missing indexes from the update code. But still not helpfull .
ON tb_Import_Data
for fields ([comp_name],[import_record_id])
INCLUDE ([comp_id])
Still I recive the same deadlock
"More Green More Oxygen !! Plant a tree today"
September 14, 2012 at 4:34 am
well, basically deadlock happens when one process is blocked by another process..
(Edit : and vice versa)
you can check the processes using sp_who2 . that might give you some information to start with ..
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:
September 14, 2012 at 4:53 am
There are two process one running update another running insert statement accessing same table primary key.
"More Green More Oxygen !! Plant a tree today"
September 14, 2012 at 6:35 am
Minaz Amin (9/14/2012)
There are two process one running update another running insert statement accessing same table primary key.
If you have multiple queries trying to update the same table that is probably the problem. Each query is consuming too many resources to allow the other to wait. You need to find a way to run the queries individually.
Why do you have an UPDATE and an INSERT occuring at the same time, anyway?
September 14, 2012 at 6:55 am
Two stored procedures are run at the same time by agent jobs.
SystemCheck2.dbo.sp_IMPORT_UTILITY_RUN_PROCESS_tb_Import_Data
- UPDATE [dbo].[tb_Import_Data]
SystemCheck2.dbo.sp_IMPORT_CLEAN_tb_Import_Data
- Copy rows FROM tb_Import_Data i to [dbo].[tb_ARCHIVE_Import_Data]
They should be run serially with the running order determined by whether the copied rows must be updated or not. Attempting to run them simultaneously makes no sense - you don't know what's going to end up in the archive table.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply