All Forums Tools
Marcus Connolly 16 posts Joined 07/10
23 Aug 2010
<Internal Session> blocking users and causing deadlock

Hi there,
We are having a problem here at our site for some time and we are wondering if anybody else has had a similiar problem.
The problem is a locking issue we are having where the system deadlocks where the only remedy seems to be a database restart or by a manual job abort.

Here are some instances where it has occured:

-Arcmain acquires locks on dictionary tables triggering deadlock with any user action needing access to the same tables. The system hangs until restarted or one of the jobs holding a lock is aborted.
-Arcmain attempts to access a database that the Multiload process is already using then deadlock will occur.
-If multiple drop/alter table commands are submitted deadlock can occur.
-If the system has multiple Fastload / Multiload functions being executed using the same user id writing to the same database,then deadlock can occur.

We can view this situation in PMON as an that starts blocking people out.This session cannot be viewed,or aborted.

Obviously this has been raised with Teradata Support and we are given workarounds like
'The best way to avoid this situation is to not run backups and loads at the same time.',or 'Limit the amount of DDL work during arc RESTORE/COPY dictionary phases'.

Naturally we are taking as many steps as possible to try to avoid this but seems impossible to avoid all the time as it inevitably happens again and again.

I'm just wondering if anyone else has ever experienced an issue like this? and if so what did you change? as it seems very strange as it seems that a fairly basic fundamental lock detection mechanism is not working correctly at present.

We are using Teradata Version 12.

Any help appreciated,

plentyfish 16 posts Joined 05/10
05 Oct 2010

We had the same problem beginning of the year, and Teradata support created a patch for us. We r also in v 12. So other than getting the patch, you'll still experience the problem.

I'm suprised they havent given you the patch as they know abt this problem.

Metas 4 posts Joined 06/10
18 Oct 2010

Please run arcmain command to execute the below content (save them in a file will be more convinient) :
(target database name)

shoebox22 1 post Joined 07/10
29 Oct 2010

We have the same problem and have to be infinitely careful about aborting arcmain as it leaves phantom processes with locks on whatever object it happened to be on at the time. We're running No patch has been offered to us.

Jim Chapman 449 posts Joined 09/04
29 Oct 2010

I believe that is considered a feature, not a bug. The locks are left in place so that you can restart the archive job from the point where it left off. A restart of the database does not clear the lock. Simply execute the release lock command as suggested by a previous post in this thread.

lgscheurich 27 posts Joined 09/06
05 Jul 2011

We are having a similar problem, but it's not related to arcmain locks. We have the situations described above:

-If multiple drop/alter table commands are submitted deadlock can occur.
-If the system has multiple Fastload / Multiload functions being executed using the same user id writing to the same database,then deadlock can occur.

Does the patch cover that as well or is it specific to arcmain?


Tdarc 6 posts Joined 07/11
12 Jul 2011

Hi Marcus,
we are also having same problem, just wondering how you were able to resolve this issue
could you please share if there is any workaround. ..

Thank you

Thank you

Marcus Connolly 16 posts Joined 07/10
13 Jul 2011

Hi there,
We were not able to resolve this issue(as we were not aware of a patch to correct it,if there is one). We have dramatically reduced the occurances of the problem by:

- ensuring that we do not run archives of a database that is being loaded.
- we significantly reduced the size of our dbc.accessrights table e.g. moved to role based access.
- reduced the length of time that a table drop takes by first deleting the data from the table first,so all drops statements are dropping an empty table.

Hope this helps,it seems to have done the trick for now,but as I said the issue has not been fully resolved.


Tdarc 6 posts Joined 07/11
14 Jul 2011

Thank you Marcus..,
I came to know that Online backups will allow some overlap of etl activity while backups are in progress but DDL changes are not allowed. So we are trying to avoid archives while ETL running to avoild locks. and planning to run backups in smaller batches and allow ETL in between.

Thank you

Fahdkhan111 3 posts Joined 09/13
21 Jan 2015

Any one has an update regarding this issue we are using teradata 14 and still facing the issue. Also tried the Online backups.


kvz 10 posts Joined 08/13
05 Jul 2016

We are getting blockings even in online backup. Blocking gets clear once the backup of that DB get completed.

Locks will be applied on DB level or Table level during online backups?

what is the solution to avoid this situation?




You must sign in to leave a comment.