How to lock set of tables used by Particular Session (Session Level Integrity)

Database
Enthusiast

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

5 REPLIES
Enthusiast

Re: How to lock set of tables used by Particular Session (Session Level Integrity)

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.  

Enthusiast

Re: How to lock set of tables used by Particular Session (Session Level Integrity)

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

Enthusiast

Re: How to lock set of tables used by Particular Session (Session Level Integrity)

Thanks a lot Harpreet and Jim

Enthusiast

Re: How to lock set of tables used by Particular Session (Session Level Integrity)

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

Teradata Employee

Re: How to lock set of tables used by Particular Session (Session Level Integrity)

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