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.
Yes it has to wait till other process completes: LOCKING ROW FOR WRITE is used to lock row for write.
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.
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.
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....
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 .
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.