All Forums Database
Prabhu_Teradata 17 posts Joined 06/12
27 Nov 2012
How to lock set of tables used by Particular Session (Session Level Integrity)

Hi,
           I have a bteq script, which insert and update data in 5 table (table1,table2,table3,table4,table5) and same bteq is called by different unix script and it might run parallel. So How to Lock all the 5 tables at session level and only writeable for the session and other session run will not update and wait for completion of first session ( session level integrity)
 
Thanks and Regards,
Prabhu
 
 

Thanks, Prabhu
Jim Chapman 449 posts Joined 09/04
27 Nov 2012

Teradata, like every other commercial database system, serializes access at the transaction level.  If you cannot use transactions to accomplish what you want, you could perhaps implement an exclusivity protocol using a queue table as a kind of semaphore.  
 

Harpreet Singh 101 posts Joined 10/11
28 Nov 2012

you can use BT ET syntax or multistatement request to keep locks till end

Prabhu_Teradata 17 posts Joined 06/12
28 Nov 2012

Thanks a lot Harpreet and Jim

Thanks,
Prabhu

Prabhu_Teradata 17 posts Joined 06/12
17 Dec 2012

Is it possible to lock the tables used in the session with below statements for reading and writing and maintain integrity at session level?
 
LOCK Table table1 for WRITE;
LOCK Table table2 for WRITE;
LOCK Table table3 for READ;
 
Thanks,
Prabhu

Thanks,
Prabhu

SmarakDas 51 posts Joined 02/12
19 May 2014

Hello Prabhu,
 
You can specify locking mechanism on your own, but Teradata will upgrade those locks as necessary. The Locking mechanism are specified in a specific format. You can find these details in the manuals.
 
Having said that, please mind that INSERT command puts a lock @ row level, hence if BTEQ # 1 Insert is executing, then it's possible for BTEQ # 2 to begin it's own INSERT operation for non-matching INSERTs. Once the WRITE Lock @ Row Level is updated to Write Lock @ Table Level for UPDATE operation, then only 01 BTEQ will continue. This can be your BTEQ # 1 or BTEQ # 2, depending on who completes it's INSERT operation first and gets the Table-level WRITE lock.
 
Thanks,
Smarak

 

You must sign in to leave a comment.