20 Sep 2012
There are two lock levels in Teradata:
table: sessions block each other
hash: sessions can do transactions simultaniously unless they try to access the same RowHash (e.g. use the same PI values) or need a table level lock.
INSERT VALUES locks on a RowHash level.
An Insert/Select will be blocked, a Delete/Update specifying the PI will succed.
When you don't see a locking step in Explain it's a hash lock.
Dieter
Dieter
Hi,
I am new to teradata. I have a doubt regarding Teradata lock. Consider below Scenario, one insert Statement after a BT statement in SESSION 1 without any ET .
SESSION 1:
BT;
INSERT INTO TABLE1(C1,C2,C3) VALUES(100,'ABC',current_timestamp(0));
*** End transaction accepted.
*** Total elapsed time was 1 second.
-------------------------------------------
Now,at the same time I am able to insert new rows in the same table from another session.
SESSION 2:
INSERT INTO TABLE1(C1,C2,C3) VALUES(200,'XYZ',current_timestamp(0));
*** End transaction accepted.
*** Total elapsed time was 1 second.
---------------------------------------------
But while trying to UPDATE or DELETE something from this table, it waits for the session1 to complete.
My question is why behaviour of session2 is different in case of 'insert' and other dmls(update,delete) . As per me second insert should also WAIT till session1 completes.
Please help.
Thanks,