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 hrsinsert into TBL2select * from TBL1;now in these 2 hrs if a user fires a query against the TBL2 he/sheshould get: "0 rows returned".Please help.Thank you.Shubh
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.
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.
Thanks a lot!! That was a great help..I am all set for this..