In Teradata , LOCKS are used to maintain integrity of data present in the tables. Teradata , itself decides the kind of lock to implement depending on the type of query however in some cases users can also decide what kind of lock, the query should use. In TERADATA, locks can be implemented at three levels: DATABASE , TABLE and ROW HASH. There are 4 types of LOCKS available in TERADATA:
EXCLUSIVE LOCKS are applied on a table when it is undergoing any STRUCTURAL change. All DDL commands apply this LOCK. If this lock is applied then no other locks can be applied on that table.
WRITE LOCKS are applied when any tables data demography changes by either DELETE,INSERT or UPDATE operation on it. So whenever a DML operation is performed on the table , such locks are applied. Only ACCESS locks are permissible when WRITE locks are applied on the table. And ACCESS Locks may lead to STALE DATA READ from the table since data demography would be changed by DML operations holding WRITE LOCKS.
Read LOCKS are applied when user tries to READ data from the table and don't want to fetch STALE DATA. READ locks maintains the data integrity as the DATA in the tables cannot be modified while READ LOCK is applied on the tables. READ LOCK only allow READ or ACCESS LOCK on the table. READ Locks are the default lock used by Teradata while fetching data from any Table.
Access locks are applied when table needs to be accessed and the data demographics is not that important; even stale data is ok for the process. ACCESS LOCKS allows READ, WRITE or ACCESS Locks to be applied on the table. However EXCLUSIVE locks are not allowed.
Teradata Locks Tips
Proper LOCKS on table can help in reducing delay time in the query. If the table is locked then other queries accessing the same table has to wait for earlier query to finish and free the lock.
It can be considered as a good practice to apply ACCESS locks on the source table when multiple SQL Queries are using same Source Table. Even if the TARGET table is different still it can lead to bad performance as by default TERADATA applied READ lock.
Understanding LOCKS and LOCKING table manually can be very useful in an environment where several tables are loaded in parallel. Teradata automatically blocks the query trying to apply incompatible LOCK on the table. You can use below command before your actual DML commands:
LOCKING SOURCE_TABLE FOR ACCESS INSERT INTO TARGET_TABLE SELECT * FROM SOURCE_TABLE;
The other good practice is to use PRIMARY INDEX column while fetching data from SOURCE table. In such cases, the query becomes an AMP operation and Teradata applies lock at ROW HASH level and not at Table level.
If you are running only SELECT statement and using Primary Index column in Filter condition then you can use row level lock. Primary Index leverages hashing and in this case you can apply only access locks on hashed rows.
LOCKING ROWS FOR ACCESS SELECT * FROM SOURCE_TABLE WHERE PI_Column = 'some value';