Lock Row VS Lock Table

Database
Enthusiast

Lock Row VS Lock Table

Hello,

 

what is the differene between LOCKING ROW FOR ACCESS & LOCKING TABLE FOR ACCESS in view?

 

any guidance on this would be much appriciated, Thank you.

3 REPLIES
Teradata Employee

Re: Lock Row VS Lock Table

LOCKING TABLE FOR ACCESS will take an all-AMPs table-level access lock, even if a SELECT from the view otherwise might have used a single-AMP RowHash lock.

LOCKING ROW FOR ACCESS will allow RowHash read locks to be downgraded to RowHash access locks, and table-level read locks to be downgraded to table level access locks.

Re: Lock Row VS Lock Table

I wanted to discuss how important this statement would be in real-time /active data warehouses where lot of users will be striking queries on same database at the time.

 

create view view_employ
as
locking table Employee for access
select *
from Employee ;

 

By using locking table for access, we make sure that normal "access" lock is applied on table which is required to fetch results. By doing so ,

- There is no waiting for other locks to release since access lock can be applied on table which has read/write lock applied to it
- This will cause the query to execute even when some lock is applied , but accessing data using this lock might not be consistent as it might result in dirty read due to concurrent write on the same table.

 

If you put a LOCKING ROW (or Tablename) FOR ACCESS on the Select query, the Select will read through any Write lock at row or table level. (So-called "Dirty Read".) This only applies to Select - a Write lock cannot be downgraded to an Access Lock.

 

To overcome "Stale Read", we can allow read access through Views - put the LOCKING FOR ACCESS clause in the views.

 

Let me explain the strategy I use:

 

I have a view that is refreshed everyday.


View definition:

replace view test_v
as
locking row for access
(
select * from test1
);

 

Now I load the delta rows into test2. Then once the processing completes and test2 table is ready, refresh the view as:

 

replace view test_v
as
locking row for access
(
select * from test2
);

 

This will always give consistent data and very little downtime (required only during view refresh)

Highlighted
Enthusiast

Re: Lock Row VS Lock Table

Thank you, for the detailed expalnation.