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
Thanks a lot, Dieter,
Hi Dieter,
Is it possible to Lock a particular Table for Complete Session?
Thanks and Regards,
Prabhu
Thanks,
Prabhu
A user can lock the table/Row only for access. They cant be locked for DML operations by the user. The system will apply the lock at table/row level based on the DML operations.
Not quite true. With a SELECT, you can downgrade from the default READ lock to ACCESS, or you can upgrade to WRITE or EXCLUSIVE. For INSERT/UPDATE/DELETE you can upgrade from the default WRITE to EXCLUSIVE. You can also upgrade anything that would normally use ROW (RowHash) locking to a full TABLE lock. It is also possible to explicitly apply table level locks to tables that are not otherwise referenced by the DML statement.
Once applied, locks are held until the end of the transaction (implicit auto-commit, COMMIT / ET, or ROLLBACK / ABORT).
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,