Teradata Lock in Insert Statement

Database

Teradata Lock in Insert Statement

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,

6 REPLIES
Junior Contributor

Re: Teradata Lock in Insert Statement

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

Re: Teradata Lock in Insert Statement

Thanks a lot, Dieter,

Enthusiast

Re: Teradata Lock in Insert Statement

Hi Dieter,

       Is it possible to Lock a particular Table for Complete Session?

Thanks and Regards,

Prabhu

Enthusiast

Re: Teradata Lock in Insert Statement

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.

Teradata Employee

Re: Teradata Lock in Insert Statement

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).

Enthusiast

Re: Teradata Lock in Insert Statement

Thanks Fred for correcting me.

I have tried this and worked :)