3

As I understand, when selecting from a table in Teradata the table is locked by default. I would like to do a simple select * from table without locking the table. Meaning, i want to do a select and allow other users to select from the same table without having to wait for my select to be over.

I know that i can downgrade the select lock using LOCKING modifier as follows:

locking table my_table for access select * from my_table

But this will allow my operation not to wait for locks. To my understanding it will still lock the table and so other operations will have to wait for the lock.

6

If those other users only need to Select from that table neither your nor the other session must add a LOCKING modifier.

A Select applies a READ lock by default which doesn't prevent other Selects, only INSERT/UPDATD/DELETE & DDL is blocked.

If you want other sessions to be able to modify rows you should use LOCK ROW ACCESS which allows dirty reads, i.e. a 2nd session might modify data while you're reading.

Teradata supports two out of the four Standard SQL Transaction isolation levels, the least and the most restrictive:

Read Uncommited -> ACCESS LOCK
Read Commited
Repeatable Read
Serializable -> READ LOCK

Teradata 15.10 implements a new feature named Load Isolation which adds a kind of Read Commited.

2
  • Thank you @dnoeth that what i was thinking. To add to this, what is the practical difference if I lock the ROW or TABLE? If neither one is waiting for the lock, I don't see any actual difference. – zuckermanori Jan 3 '17 at 6:41
  • When you specify ROW the optimizer will try to place a row hash lock*(which allows concurrent WRITE access) if the access is based on PI or USI, but TABLE will alway place a *table lock. And if you Select from multiple tables you must list them all when using TABLE, this is not needed for ROW. – dnoeth Jan 3 '17 at 9:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.