October 6, 2015 at 10:34 pm
Hello guys,
I am facing a deadlock problem in one of my processes.
We've two jobs JOB1 and JOB2, both executes at the same time and uses the same table.
JOB1 - Inserts data to the table.
JOB2 - Updates few records in the table, but this data is different from what JOB1 is inserting i.e. JOB2 updates data where a column1 value is 0 while JOB1 only inserts records where column1 value is 1. (There's another job which inserts data where column1 is 0)
So, while execution sometimes I am getting Deadlock while executing the JOB2 when it is trying to update the table.
Error: "Transaction was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction."
Does insertion using SSIS create a full table lock?
Please suggest, how could I avoid such deadlock situation.
TA
____________________________________________________________
APOctober 6, 2015 at 11:07 pm
Please post the deadlock graph and details of the two queries involved.
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
October 6, 2015 at 11:37 pm
Sorry, I don't have the deadlock graph available.
In JOB1 - I have a file to table load via SSIS (fast table load)
In JOB2 - I am fetching some data from this table with(nolock) using SSIS and after processing I am updating the processed records.
____________________________________________________________
APOctober 7, 2015 at 3:18 am
You might have it. Check your system_health extended event session. You can use this query [/url]to take a look.
"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
October 7, 2015 at 5:01 am
Please can you get the deadlock graph and post it? Can't diagnose a deadlock on vague descriptions of processes.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply