All Forums Database
pratyushkhosla 2 posts Joined 07/12
05 Feb 2013
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
 
 

goldminer 118 posts Joined 05/09
06 Feb 2013

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.

CarlosAL 512 posts Joined 04/08
06 Feb 2013

"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.

Fred 1096 posts Joined 08/04
07 Feb 2013

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.

You must sign in to leave a comment.