Create view with "locking table"

Database

Create view with "locking table"

Hello how are you? could someone explain me which is the difference when i create a view using "locking table" and without using it?.
I know it is a basic question.but I'll be grateful if you can help

for example:
create view tony.view_employ_withLock
as
locking table tony.employ for access
select
*
from
tony.employ;

create view tony.view_employ_withoutLock
as
select
*
from
tony.employ;

thanks
1 REPLY
Enthusiast

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.

Hope this helps.