HAO788 GAME ONLINE GRATIS — Teradata Locks

April 13, 2020September 23, 2015

All the objects in the database are shared among variety of users who are accessing the data in parallel. For example, one user is updating a table and if another user tries to access that table at the same time, then second user will get inconsistent and wrong information from that table. To prevent this kind of data inconsistency locking mechanism has been invented.

Teradata has dedicated lock manager to automatically lock at the database, table and row hash level.

Database Lock: Lock will be applied to all the tables and views.
Table Lock: Lock applied to the all rows in table/view.
Row hash Lock: Single or multiple rows in a table will be locked.

Teradata uses four types of locks to lock its objects-

The Exclusive lock:-

When anyone tries to modify the structure of any objects like a table or  view, Teradata applies an exclusive lock on it. That means no other user can access or any kind of operations until Teradata releases the lock from that object.

Exclusive lock can be applied only on database level or table level. This is the most restrictive level of lock in Teradata.

For example, if we submit any CREATE TABLE statement, Teradata will apply exclusive lock on the table-

Explain CREATE MULTISET TABLE mydb.table_name
(
column_1 integer,
column_2 varchar(10)
)
primary index (column_1);

1) First, we lock mydb.table_name for exclusive use.

 

The Write Lock:-

Write lock will be applied on table if you submit any insert, delete or update request. A write lock restricts other user to access the same table. The only exception is that if a user wants to read the data and not concern about the consistency of data. In this case the user can apply an access lock to read the data from the table.

Consider the below delete query-

Explain delete from mydb.table_name;

1) First, we lock a distinct mydb."pseudo table" for write on
a RowHash to prevent global deadlock for mydb.table_name.

The Read Lock:-

A read lock is used when a user submits a SELECT query. This ensures the data integrity of the table. Unlimited user can apply read on the same table simultaneously. This lock is not compatible with EXCLUSIVE lock and WRITE lock, that means no data modification or structural change is permitted.

Explain select * from mydb.table_name;

1) First, we lock a distinct mydb."pseudo table" for read on
a RowHash to prevent global deadlock for mydb.table_name.
2) Next, we lock mydb.table_name for read.

The Access Lock:-

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

Explain locking mydb.AR_P200 for access
select * from mydb.AR_P200;

1) First, we lock mydb.AR_P200 for access.

Also Read:-

Pseudo Lock in Teradata

Teradata Explain Plan