Access Lock and DDL Statement

Database

Access Lock and DDL Statement

One of our database deployments failed because an ALTER statement was blocked by a SELECT statement against a view with a locking row for access modifier. It is important to note that the view referenced the table being altered one to one and the SELECT statement started before the ALTER. Here is a model of what happened:

CREATE TABLE foo_table (a integer, b integer) PRIMARY INDEX(a) ;
REPLACE VIEW foo_view AS LOCKING ROW FOR ACCESS SELECT a, b FROM foo_table;
--First sql statement 
SELECT a,b FROM foo_view;
--Alter statement
ALTER TABLE foo_table  ADD c INTEGER;

 In this situation the ALTER TABLE statement hung, as it was being blocked by the SELECT. Looking at viewpoint we saw this:

Granted access lock on table foo_table

This seems a bit unintuitive, as the LOCKING ROW modifier would override this lock. Can anyone provide some input on this issue. 

 

2 REPLIES
Senior Apprentice

Re: Access Lock and DDL Statement

The ALTER TABLE (AT) will require an Exclusive lock. Nothing else can run at the same time as the AT command, the At command cannot run at the same time as anything else.

 

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Highlighted
Teradata Employee

Re: Access Lock and DDL Statement

The optimizer could not identify a specific RowHash to lock, so it automatically escalated to Table level. But as Dave pointed out, even a single RowHash lock would not be compatible with an exclusive table lock.