Teradata: Select Table With No Lock

Question

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.

Answer 1

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.