Types of Locks in Teradata

Share via:

Types of Locks in Teradata 

Locks are used to protect data integrity when multiple users try to access or change the same data simultaneously. This can be achieved by locking the target data.

Example : Assuming user_1 is updating a table, so if user_2 tries to access that table at the same time, then user_2 will get inconsistent and wrong information from that table. To prevent this kind of data inconsistency locking mechanism has been invented.

  • Locks are automatically acquired during the processing of a request and released when the request is terminated.
  • Teradata Database locks have two dimensions:

1.Level : meaning its scope or granularity : the type and, by inference, the

size of the object locked.

Example : database lock is the highest level of lock.

2.Severity : meaning its degree of restrictiveness or exclusivity.

Example : WRITE lock is more restrictive than ACCESS lock.

About Locking Levels

  • The locking level determines whether other users can access the target object.
  • Teradata has dedicated lock managerto automatically lock at the database, table and row hash level.

Locks can be placed on the following objects depending on the request being processed :

  1. Database
  2. Table
  3. View
  4. Partition
  5. RowHash
  6. RowKey (Partition and RowHash)

Types of locks

Teradata uses four types of locks to lock its objects :

  1. EXCLUSIVE lock
  2. WRITE lock
  3. READ lock
  4. ACCESS lock

Let us understand these locks in little detail :

1)The Exclusive lock:

  • This lock is the most restrictive level of Lock in Teradata
  • This lock can be placed only on a database or table/view when there are any structural changes being made to the object like adding or dropping a column.
  • When this lock is placed, no other user can access or run any kind of operation until lock is released on that object.
  • You can also specify this lock explicitly using the LOCKING request modifier.

Example:  When a CREATE TABLE statement is submitted, Teradata will apply exclusive lock on

that table.

Explanation

———————————————————————-

  • First, we lock db_1.table_1 for exclusive use.

 

2) The Write Lock:

  • This lock is placed on table when you submit any DML statement like Insert, delete or update request.
  • A write lock restricts another user to access the same table.
  • The only exception is that if a user wants to read the data and choose to override the automatically applied WRITE lock by specifying a less restrictive ACCESS lock and is not concerned about the consistency of data. In this case the user can apply an access lock to read the data from the table.
  • You can also specify this lock explicitly using the LOCKING request modifier.

 

Example:  When an INSERT statement is submitted, Teradata will apply WRITE lock on

that table.

Explanation

—————————————————————————

  • First, we lock db_1.table_1 for write on a reserved RowHash to prevent global deadlock.
  • Next, we lock db_1.table_1 for write.

 

3)The Read Lock:

  • This lock is placed when a user submits a SELECT query. This ensures the data integrity of the table.
  • It restricts access to other users who need EXCLUSIVE or WRITE locks which means no data modification or structural change is permitted.
  • Multiple users can apply read on the same table simultaneously.
  • You can also specify this lock explicitly using the LOCKING request modifier.

 

Example : SELECT statement is submitted.

Explanation

———————————————————————-

  • First, we lock db_1.table_1 for read on a reserved RowHash to prevent global deadlock.
  • Next, we lock db_1.table_1 for read.

 

4)The Access Lock:

  • This lock is placed when a user explicitly defines LOCKING FOR ACCESS statement and user is not concerned about the data consistency.
  • An access lock permits the user to read the data from the table that may already be locked for the READ or WRITE.
  • The only restriction is that Teradata cannot apply access lock when object is locked by EXCLUSIVE lock i.e. some structural change is in progress.

 

Example :

Explanation

—————————————————————————

  • First, we lock db_1.table_1 for access.

Locks and their combability:

 

Thank you for reading …

I hope above information is useful and helpful ..

Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

Add Comment