Teradata Lock - 3 Insert Statements

General
Enthusiast

Teradata Lock - 3 Insert Statements

Hi All,

   I am new to teradata and reading about Teradata Locks. I have a specific question related to locking.

I have 3 or more processes which can run simultaneously and try to insert data in the same table. As such, I think there can be an issue as the process which runs first will hold a WRITE Lock and will not allow the other processes to insert data in that table. But, the real question is, will the other processes wait till the LOCK is released(i.e., the 1st process completes the insert) and then in a fashion(not sure what that is - whether round robin or a queue in queue out mechanism) insert the records in the target table.

Any help in this is much appreciated as I am currently stuck up with this issue.

Thanks

Mithun

Tags (1)
8 REPLIES
Enthusiast

Re: Teradata Lock - 3 Insert Statements

Yes it has to wait till other process completes: LOCKING ROW FOR WRITE is used to lock row for write.

Deadlock can occur  when two PI SELECTs are followed by PI UPDATES and the SELECTs are on different row-hashes.

Cheers,

Raja

Enthusiast

Re: Teradata Lock - 3 Insert Statements

Hi,

This is a basic transactional processing mechanism that every database adopts to avoid deadlocks. If multiple statements are trying to write the same object they will be processed sequentially.

For muliamp requests, teradata uses the concept of pseudo table locks in order to avoid deadloacks.

Khurram
Enthusiast

Re: Teradata Lock - 3 Insert Statements

Thanks Raja and Khurram for your response. But, I want a more specific answer as to whether the lock will be automatically released and whether I have to write a piece of code for it. Let me demonstrate with an example.

Say there are 4 processes running simultaneously which wants to insert data into target Table A.

Process P1 acquires a write lock first and thus other processes P2,P3 and P4 will have to wait.

Now when P1 is complete, whether the lock will be automatically released and if yes, which one of the processes - P2,P3 and P4 will acquire the next lock and how is this decided.

I am raising this question because I read on a different blog that Teradata blocks other sessions which tries to acquire incompatible lock in the table.

Your help in this is much appreciated.

Thanks,

Mithun

Enthusiast

Re: Teradata Lock - 3 Insert Statements

User P1:

BT;

LOCKING ROW FOR WRITE

SELECT y

FROM t

WHERE x=1;

UPDATE t

SET y=0

WHERE x=1

ET;

User P2:

BT;

LOCKING ROW FOR WRITE

SELECT z

FROM t

WHERE x=1;

UPDATE t

SET z=0

WHERE x=1;

ET;

Deadlock will not occur because the User P2 LOCKING ROW FOR WRITE request is blocked by the User P1 LOCKING ROW FOR WRITE.

The User p2 LOCKING ROW FOR WRITE request will take place after the User P1 END TRANSACTION statement is complete.

The table t is selected with a where condition x=1 for example.

Cheers,

Raja

Re: Teradata Lock - 3 Insert Statements

what if i want to load a table at the same time. 

Lets say i have table and multiple countries are loding the table. what if i want to load 2 countries data at the same time to one table.  how to achive this is there any way to do this....

Enthusiast

Re: Teradata Lock - 3 Insert Statements

Hi Rohit,

It is processed in sequence.

Cheers,

Raja

Re: Teradata Lock - 3 Insert Statements

Mithun ,  This is simple .

for e.g. Let's assume P1, P2 , P3 and P4 are  executed  in the sequence P1, P2,P3 , P4 ....

As P1 is  fired first it will execute first , P2, P3 and P4 would be in block status ( This can be viewed/monitored in Viewpoint) . You can also validate from the DBQL log tables for the starttime to see the sequence of execution.

Once P1 completes P2 will execute  and P3 , P4 will be  in blocked  state . Once P2 completes P3 and so on .

Subrata 

Enthusiast

Re: Teradata Lock - 3 Insert Statements

Thanks Raja, Khurram ans Subrata for your valuable response.

Subrata - I was not sure whether Teradata handled this automatic unblocking once the process P1 has completed so that P2 can run and once P2 completes P3 is picked up. But, according to you if this works in Teradata, then it makes my ETL Logic much simpler.

Thanks,

Mithun