SQL Deadlock

Question

SQL Deadlock

SQL.AP

Hall of Fame

Points: 3244
More actions
October 6, 2015 at 10:34 pm

#303923

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
____________________________________________________________
AP

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL.AP Hall of Fame Points: 3244 More actions · Answer 2

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.

____________________________________________________________

AP

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 3

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass