Views and Locks

Database
Enthusiast

Views and Locks

Can some tell me if my understanding on the views and locks are correct based on the below example.

I have created a view as below

--View Definition Type 1 (Implicit Read Lock)

CREATE VIEW ETL_DEV_DB.EMP_V2 AS

SEL * FROM ETL_DEV_DB.EMP_T;

 

--View Definition Type 2 (Explicit Access Lock)

CREATE VIEW ETL_DEV_DB.EMP_V1 AS

LOCKING ROW FOR ACCESS

SEL * FROM ETL_DEV_DB.EMP_T;

The view EMP_V1 is used in COGNOS reporting. Consider it takes 12 minutes for the COGNOS report to fetch the data from EMP_V1

(There could be many reasons why it takes 12 minutes, may be because of poor primary index or the base table EMP_T has billions of rows).

also consider the base table EMP_T is loaded using ETL process. Say it takes 30 minutes to complete the table loading by ETL.

Now say a user runs the cognos report at 10.00 AM (As per the above statistics, it should take 10.12 AM for the user to see the data in the report). Now if the scheduled ETL process which loads the table EMP_T starts at 10.05 AM, then does it mean that the ETL process will have to wait till 10.12 AM if the view is created in Type 1 above because of the Implicit read lock on EMP_T by the COGNOS user trying to see the data. But if the view is created in Type 2, then the ETL process will not have to wait till 10.12 AM and will immediately start at 10.05 AM itself because of the access lock (However in this case user might not have data integrity till ETL load is completed).

Is my understanding on the above two scenarios correct?

1 REPLY
Teradata Employee

Re: Views and Locks

If your session is using ANSI mode, your understanding is correct. The READ lock for "Type 1" scenario will be held until you stop retrieving rows and COMMIT, so will block ETL that needs a WRITE lock.

In Teradata mode, assuming you have not issued explicit BEGIN TRANSACTION (BT), the table READ lock will be released as soon as the answer set is in spool - i.e. before the first row is returned to the client. So when the lock gets released depends on how much of your 12 minutes is query execution and how much is data fetch.

In the "Type 2" scenario, it is likely that the lock will be escalated to table-level ACCESS but that won't prevent ETL from concurrently applying inserts, updates, and deletes.

See the SQL Reference: Request and Transaction Processing manual for more details.