what lock is when inserting data

Analytics
Enthusiast

what lock is when inserting data

Hi,
I want to know if I am inserting data from TBL1 to TBL2, what lock is on the TBL2.

I want to make sure when data is being inserted to TBL2 it is still available to read but not with half full data.

Ex -> 1. TBL2 is empty initially.

2. My query runs, which say takes about 2 hrs

insert into TBL2
select * from TBL1;

now in these 2 hrs if a user fires a query against the TBL2 he/she
should get: "0 rows returned".

Please help.
Thank you.
Shubh
3 REPLIES
Enthusiast

Re: what lock is when inserting data

You don't need to worry about locks in Teradata, TD automatically place appropriate mode of lock on tables or other database objects. In your case, it'll place WRITE lock on the table TBL2. Other users can read the table data through ACCESS mode lock, but not through READ lock.
Enthusiast

Re: what lock is when inserting data

Pawan is right - TBL2 gets a write lock.
If a user accesses the table, he will normally aquire a read lock, so will be delayed until the write lock is released. (This can be avoided by setting the NOWAIT option in bteq - other tools usually have equivalent options - in which case the command is rejected with table not available.)
If the user specifies LOCKING FOR ACCESS, he can read through the write lock, but will get the rows currently in the table, so could get any number of rows.
Enthusiast

Re: what lock is when inserting data

Thanks a lot!! That was a great help..
I am all set for this..