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:
Locks and their compability:
Teradata Locks


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