Transaction (Process ID 107) was deadlocked on lock

dataservices job design
1

Hi All,

I am using BODS 3.1 on sql server 2005 (Repository, Source, Target).

In one of the jobs, I am updating a table using SQL function in a DI script. I am running this job parallelly for three different customers data. Two instances of execution completed successfully, but the third instance of execution failed and given the below error.

[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 107) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Update statement looks like below.

" Update tablename set columnname=‘AAA’ where customerid=‘I-0001’

Could any one please let me know the solution for this problem.

Thanks
Chandrashekar

althatichandrashekar :india: (BOB member since 2009-03-05)

2

It seems that another process has exclusive lock on that table. Check with your DBA that is there anything happening on that table.Usually it happens when you kill the job during execution and if the lock on that table is not yet released for the next process.

SantoshNirmala :india: (BOB member since 2006-03-15)

3

Hi Santhosh,

Since I am running this job for three different customers data parallelly, the table is locked by the first execution and it is not allowing other two executions and giving that deadlock error. Something to do from database side to deal with the locks. Did anyone face this kind of scenario ?

Thanks
Chandrashekar

althatichandrashekar :india: (BOB member since 2009-03-05)

4

Indeed. As you are running the job in parallel (UPDATE statement), the table gets locked. Run the second instance of the job after completion of the first one or you can keep all the update statements in a single script.

Regards,
Sameer

sameer81 :new_zealand: (BOB member since 2007-09-15)