Teradata Locking prevents multiple users who are trying to change the same data at the same time and in turn helps in preventing data corruption.

Teradata Locks

Locking prevents multiple users who are trying to change the same data at the same time and in turn helps in preventing data corruption.

There are four types of locks:

• Exclusive : prevents any other type of concurrent access.
• Write : prevents other reads, writes, exclusives access.
• Read : prevents writes and exclusives access.
• Access : prevents exclusive access only.

Locks and their compability:
Teradata Locking can be Implicit (automatically by Teradata) & Explicit (specified by users)

1. Implicit lock based on the SQL command:
   • SELECT - applies a Read lock
   • UPDATE - applies a Write lock
   • CREATE TABLE - applies an Exclusive lock
   
   
2. Explicit lock using LOCKING modifier:
   • Access lock for Select

     
     LOCKING FOR ACCESS SELECT * FROM tutorial_db.employee; 
     

     
     
   • Exclusive lock for Update

     
     LOCKING FOR EXCLUSIVE UPDATE tutorial_db.employee SET job_title = 'CEO' WHERE emp_no=1000245; 
     

     
     
   • Write lock for Update

     
     LOCKING FOR WRITE NOWAIT UPDATE tutorial_db.employee SET job_title = 'CEO' WHERE emp_no=1000245;
     

How Teradata Locks Objects

• Tables need to be locked on all the AMPs as the Teradata tables are spread across AMPs.
• Teradata assigns 1 AMP (called Gatekeeper AMP) per table to handle locking on all the AMPs for that particular table.
• Teradata uses hash of the table Id and determine the Gatekeeper AMP, finding AMP through hashing ensure that no AMP is overloaded with locking responsibility.
• When Query is made for that particular table,internally Teradata(PE) reports to the Gatekeeper AMP which is responsible for Locking all Amps.
• "Locking a Pseudo Table" phrase is often present in the Explain plan. This means that Gatekeeper AMP is responsible for telling other AMPs to lock the table.
• Teradata Locking works on First Come First Serve basis which means Pseudo table lock for a table is sequential lock to avoid deadlocks on the table. The first request to place a pseudo table lock acquire a lock on that particular table across all AMPs

NOWAIT Clause: When NOWAIT is used in the query request and if the requested lock cannot be granted immediately then the query will be aborted by Teradata with below error message

*** Failure 7423 Object already locked and NOWAIT. Transaction Aborted.

Example 1: NOWAIT clause

LOCKING Department FOR READ NOWAIT 
SELECT 
  Dept_No
  ,Department_Name 
FROM Department
ORDER BY Dept_No;

Note: NOWAIT option should only be used when users dont want requests waiting in the queue.