I am getting a Deadlock when I run multiple Informatica sessions in parallel which insert into a table in Teradata. Please find the attached below error.
Teradata odbc teradata driver teradata database transaction ABORTED due to deadloack.unable to get catalog string
I rerun the session one by one (sequential order) and the session is completed successfully.
I need your help to fix the issue.
When you run multiple sessions that insert into the same table, One of the sessions will lock table for write and others will have to wait.
I think you either can load this table using sequential order, because in the order you doing it, it is still handled as sequntial by the database. i.e. the one sessions locks the tables and others wait in queue.
You must reconsider your application design to avoid deadlock.
Just seconding Khurram here.
I usually overcome this issue by creating view with following definition.
Create view <view_name> as locking rows for access select * from <table>
Hope this helps
Kanna, I am not sure if the same is the table that you are loading usinge multiple parallel sessions. If that is the case what Khurram suggested will help.
But usually we see this situation in case you are loading multiple tables and you are not getting an access to the DBC tables. what you can do is there is a session parameter in informatica(session retry on deadlock) which will wait for a specified time and retry for getting the lock on the dbc tables.Also in the internal parameters in dbscontrol we have RetryAccLogDeadlock which can be set to TRUE. Also DBC tables clean up will improve the query performance on these tables and will get faster accessibility.
Deadlock only happens when multiple rows with same PI index combinations are there in queue. Because INSERT values lock on a RowHash level. Same PI combination in multple records mean they will be involved for the same RowHash which puts the system in confusion which record to accept causing a Deadlock situation. So I will say, you are having duplicate records on PI index over all the sessions.
Do load serially and else follow Create View method as suggested by Irfan above.
well said AB,I have also incountered these sitution in loading jobs.ETL tools read files and distribute data in round robin fashin to various parrellel sessiosn, while data is being laoded in teradata through fastload and multiload being laoded based on row hash of PI columns.if records in twor or more parrellel sessions of ETL job results same row hash value, Teradata optimizer gets confused which to except.It causes deadlock. only solution is load data in serial mode from ETL tool.
When multiload, fastload or the TPT load/update operator methods are used then multiple sessions can be used without deadlocks. The previous comment can be true if multiple concurrent ODBC sessions are used for the loading. In that case, multiple sessions may try to lock the same PI at the same time and if there are multiple of those conflicts between two packages of rows on the multiple sessions, then a deadlock can occur.
If multiple INFA jobs are run concurrently against the same table, then it is more likely that deadlock would occur using ODBC. If they are run concurrently and use one of the loaders, then the jobs will run sequentially since the loaders acquire a table level write lock on the target table.