Re: Create view with "locking table"

When querying the view in your first example (with locking), the Teradata optimizer will use an ACCESS lock when reading from the table. This kind of lock can co-exist with any other lock except an exclusive. It will also not block any other type of lock except an exclusive. That's the plus side. The downside is that you can get inconsistent results from the view because it is possible that the underlying tables can be updated whil you are querying the table. This is sometimes called a "dirty read" or "read un-committed" because you can view data that may be rolled back due to an error later in the data stream.

When writing to the table, you'll still obtain a write lock.

In your second example, Teradata will apply a read lock when reading data from the view. A read lock will block a write and an exclusive and will have to wait for write and exclusive locks to release before it can take it's lock. The plus side here is you get consistent data but may end up having to wait for any ETL processes to finish completely before your query can execute.