Lock on DBC.Accessright table and other Create/drop/Alter statement in Block state

Database
Teradata Employee

Lock on DBC.Accessright table and other Create/drop/Alter statement in Block state

Hi All,

We are facing issue of locking DBC.Accessright table due to Alter statement running on a table. When this alters statement runs all other drop/create/alter statement get block with alter statement.
This alter table contain data approx. 60k records.

Query-1
Alter table database.table drop colmn;

Other queries :
Create/Drop /Alter will block with query-1 and lock on table DBC.Accessright table till the Query-1 gets complete

Any suggestion why Dbc.accessright table getting lock for long time and other DML stament are in block state during this time period

Any solution ?
8 REPLIES
Enthusiast

Re: Lock on DBC.Accessright table and other Create/drop/Alter statement in Block state

Hi Sandeep,

I remember a discussion. Can it be because of:

http://forums.teradata.com/forum/database/deadlock-on-dbc-accessrights?

Teradata Employee

Re: Lock on DBC.Accessright table and other Create/drop/Alter statement in Block state

Thanks Raja for your reply.

No Deadlock present here.

Here different database and creator are different. There is no case of deadlock.

when the alter query complete all other create/drop/alter/replac view, start working and this alter query takes aprox. 15-20 min to complete. till that all other DML statement are in Block state.

why Accessright table is lock for ~20 min .

Teradata Employee

Re: Lock on DBC.Accessright table and other Create/drop/Alter statement in Block state

What Teradata database version and what kind of system are you running on?

Teradata Employee

Re: Lock on DBC.Accessright table and other Create/drop/Alter statement in Block state

Teradata 14.10. I am not getting your 2nd question.

Enthusiast

Re: Lock on DBC.Accessright table and other Create/drop/Alter statement in Block state

Just my thought again, throwing more directions:

Is it  a one-off situation or it is recurring?

Background tasks such as defrag,..cylpack, fsg cache purging,TJ purging,cache flushing ..., if they happen co-incidentally also may not be the cause since they are minimal in most cases.

However, it looks like locking duration for dbc.accessrights takes longer, when alter is used, like older version.

Teradata Employee

Re: Lock on DBC.Accessright table and other Create/drop/Alter statement in Block state

My second question was about what system and system size you are running this on. A VM on your laptop? A large production system? A small appliance?

Teradata Employee

Re: Lock on DBC.Accessright table and other Create/drop/Alter statement in Block state

Hi Raja

Itis recurring situation.

@Todd- Its a appliance develpment box with sles-11 and TD 14.10.

Teradata Employee

Re: Lock on DBC.Accessright table and other Create/drop/Alter statement in Block state

On a platform like that, this ALTER should run very quickly. That suggestes to me that the ALTER itself is blocked for some period of time by some other piece(s) of work. You can investigate this in DBQL by looking at the period in which it actually ran and the period is was waiting. And look in viewpoint to see what is blocking it while it is waiting.

When a lock is outstanding in the blocked queue, we do not allow other work to jump past that lock thus other work gets blocked. It appears that the AT is the blocker but the real issue is that the AT is blocked and has a lock request outstanding which in turn is blocking other work.