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