ETL Process results in contention on Teradata table

Database

ETL Process results in contention on Teradata table

Folks,

I'm using Informatica to load from files/tables to Teradata tables. I have a strange problem in an ETL scenario (that's pretty common and I've never had such an issue with any other DB).

There are 3-4 Informatica workflows trying to update the same table (albeit different records) simultaneously and in such cases Teradata throws up a contention error!!!! Doesn't Teradata have row-level locks?? I have done a similar thing in Oracle and never ever faced any issue!!!

Please help.

Thanks

3 REPLIES

Re: ETL Process results in contention on Teradata table

Have you created a view on top of each table defined as LOCK ROW FOR ACCESS?  Writing directly against the table, instead of through the view, may be your issue.

Re: ETL Process results in contention on Teradata table

"Doesn't Teradata have row-level locks??"

Teradata uses rowhash locks (whenever can be applied), which can be 'row-level' locks depending primarily on the Primary Index. The locks can be escalated to table locks.

Teradata IS NOT Oracle.

Cheers.

Carlos.

Teradata Employee

Re: ETL Process results in contention on Teradata table

If the WHERE clause allows Teradata to identify a single specific RowHash to be updated by the statement (e.g. access by PI or USI) then RowHash locking can be used. Otherwise (e.g. access by scanning the table or NUSI) Teradata will use full table lock.