All Forums Database
teradatauser2 236 posts Joined 04/12
24 Aug 2012
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

Qaisar Kiani 337 posts Joined 11/05
24 Aug 2012

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...

SandshGS 2 posts Joined 08/12
25 Aug 2012

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 1 post Joined 04/11
28 Aug 2012

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

teradatauser2 236 posts Joined 04/12
23 Sep 2012

 
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

FarhanArif 7 posts Joined 05/12
26 Sep 2013

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)

 

19 Dec 2013

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

dnoeth 4628 posts Joined 11/04
19 Dec 2013

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

Dieter

07 May 2014

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.

Qaisar Kiani 337 posts Joined 11/05
08 May 2014

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

You must sign in to leave a comment.