Deadlocked on lock resources

[jpraveen]

Hi
while i am running the job i am getting the error

APT_CombinedOperatorController(2),0: Fatal Error: [IBM(DataDirect OEM)][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transa

Is this a Lock made by Datastage or it is a database lock

My job will had a Update SQL and the target is also is one of my source,
what my logic is i want to update my target table based on INNER JOIN on some other tables and some where Clauses
so can anyone explain why i am getting this lock.
and same updates are doing on other Tables its working fine
but for this Job only iam getting this error.

[BMSK]

After join u updateing all the colum?

[priyadarshikunal]

After join u updateing all the colum?

Please do not use SMS style abbreviations in your post. It will make future searcher's task difficult if their native language is not english.

[jpraveen]

Hi

I am doing only UPDATE ONLY in upsert mode.

my job will be

SRC1
-
-
------Lookup-----Transformer------Target(one of the source)
-
SRC2-


my actual query is

UPDATE Table_name
SET Table_name.HospitalGRP = [Hosp_Grp_Num], Table_name.HospitalFAC = [Hosp_Num]
from Table_name
INNER JOIN Table_name2 ON
( Table_name.SPECIALTY_PRODUCT_INDIC = Table_name2.PROBASE_PRODUCT_TYPE)
AND ( Table_name.EnrollmentFacility = Table_name2.PRVDR_FAC) AND
( Table_name.EnrollmentGroup = Table_name2.PRVDR_GRP) AND
( Table_name.StateCode = Table_name2.StateCode)
WHERE ((( Table_name.StateCode) In ('CA','OK','TX','OR')) AND
(( Table_name.PCP_INDIC)="Y") AND (( Table_name2.PRIMARY_HOSPITAL_INDICATOR)="Y"));

can any one help on this?

[priyadarshikunal]

It is a database lock which may be caused by datastage. Please give some more information on your job design.

[chulett]

Query isn't the issue and as noted it is a database deadlock. You could be locking yourself or you could have had the bad luck to be deadlocked against another process. Is this repeatable? Does running on one node solve the problem?

If yes, you may be able to solve this by using intelligent hash partitioning with multiple nodes so one node doesn't run into another.

[jpraveen]

Hi

i am running on 2-node processor
and i made the target as HASH partitioning and the job finished successful for a particular job and again for another job the job got aborted with folloeing error message

ODBC_Enterprise_76,1: Fatal Error: [IBM(DataDirect OEM)][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 65) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transa

my source and target is SQl-Server.I made target as HASH partition and job was successful for 1 job and for another job it was aborted due to Lock

could you please help on this?

Thanks in Advance

[lika97]

Hi

i am running on 2-node processor
and i made the target as HASH partitioning and the job finished successful

Thanks to all

What database you are using? If it is teradata add, LOCKING ROW FOR ACCESS for all selected queries then you wont be locked your self.

Thanks,
Kali.

[chulett]

It is specified in the error message - SQL Server.

[jpraveen]

Hi

i am running on 2-node processor
and i made the target as HASH partitioning and the job finished successful for a particular job and again for another job the job got aborted with folloeing error message

ODBC_Enterprise_76,1: Fatal Error: [IBM(DataDirect OEM)][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 65) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transa

my source and target is SQl-Server.I made target as HASH partition and job was successful for 1 job and for another job it was aborted due to Lock

could you please help on this?

Thanks in Advance

[chulett]

Please do not duplicate posts. You've should have simply put all of the detail in your new post into this one rather than start a brand new topic.

When I get some time later, I'll merge the two.