Lock row for access : In insert-Select

Database
Enthusiast

Lock row for access : In insert-Select

I tried :

Lock row for access

sel * from tab1

This works.

But when i tried

insert into tab2

Lock row for access

sel * from tab1

This doesn't work. Is Lock row for access not allowed while insert - select

10 REPLIES
Enthusiast

Re: Lock row for access : In insert-Select

LOCKING ROW FOR ACCESS is only valid for SELECT statements, as it allows the dirty reads from the table and allows INSERT/UPDATE/DELETE operations on the table...

INSERT statements put the WRITE lock on the table during its operation...

Re: Lock row for access : In insert-Select

In case of insert into select *, table level locks are applied on both the tables. Insert will undergo write and select will undergo read lock at table level

So
Teradata Employee

Re: Lock row for access : In insert-Select

Create a view for the "tab1" table using LOCK ROW FOR ACCESS and then select from the view when doing the INSERT/SELECT.

Enthusiast

Re: Lock row for access : In insert-Select

if i do it this way : 

Lock table tab1 for  access

insert into tab2

sel * from tab1

This works. How is this different from the row level access that we generally apply

Teradata Employee

Re: Lock row for access : In insert-Select

Locking Row for Access only works (locks only a limited number of rows in a table) when there is an equality condition on a UPI or NUPI in a WHERE clause. This is because it is generally a row hash level lock.

Example:

LOCKING ROW FOR ACCESS

SEL * FROM TAB1

WHERE  COL1 = 1

In the case where there is no WHERE clause, Locking Row for Access is automatically converted to a Table Level lock (LOCKING TABLE FOR ACCESS)

Re: Lock row for access : In insert-Select

Hi ,

I was trying --

Locking View for Access, inplace of Locking Table for Access(when i was inserting data from a view, which was taking data from a table).

Interestingly, it went through.

Now my question is, is it locking the table which was being referred by the view ??.

Thanks,

Binayak

Junior Contributor

Re: Lock row for access : In insert-Select

Hi Binayak,

as there's no actual lock on a view it's always passed to the table. Simply Explain your query.

Enthusiast

Re: Lock row for access : In insert-Select

Hi,

Locking row for access can be used in an INSERT, but, the Locking statement should be provided on top.

Lock tab1 for access

insert into tab2 sel * from tab1;

Sravan.

Enthusiast

Re: Lock row for access : In insert-Select

The insert statement will overwride the access lock with write lock. Review the explain plan and you will notice it.