<Internal Session> blocking users and causing deadlock

Tools & Utilities
Enthusiast

<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,
Marcus
10 REPLIES
Enthusiast

Re: <Internal Session> blocking users and causing deadlock

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

Re: <Internal Session> blocking users and causing deadlock

Please run arcmain command to execute the below content (save them in a file will be more convinient) :
LOGON 127.0.0.1/DBC,DBC;
RELEASE LOCK
(target database name)
,OVERRIDE;
LOGOFF;

Re: <Internal Session> blocking users and causing deadlock

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 12.0.0.2.35. No patch has been offered to us.
Enthusiast

Re: <Internal Session> blocking users and causing deadlock

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

Re: <Internal Session> blocking users and causing deadlock

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?

Thanks!
Enthusiast

Re: <Internal Session> blocking users and causing deadlock

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
Enthusiast

Re: <Internal Session> blocking users and causing deadlock

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.

Marcus
Enthusiast

Re: <Internal Session> blocking users and causing deadlock

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.
Thanks,

Re: <Internal Session> blocking users and causing deadlock

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

Regards