Teradata Locking – Ensure the Integrity

1
1548

Teradata locking

What is locking?

Historically, all databases were designed for transaction processing. Nowadays, big unstructured data is becoming more important, and the evolution moves into a direction of data consistency exchanged for performance.

Locking of database objects is a must in OLTP systems to avoid that multiple users can change and access the same data at the same time in a way which is causing data inconsistencies. In other words, whenever a user is accessing or changing data, the affected objects are locked to ensure integrity.

Lock handling works on two different levels. Each time a database row is accessed, Teradata has to decide how granular the lock will be (the locked object type) and secondly, which kind of interactions on that object should be locked.

What should be locked?

Teradata can apply locks at the following levels of granularity:

Database lock: All objects located in a database are locked
Table lock: all rows located in a table are locked
Rowhash lock: single or multiple rows in a table are locked

What type of interactions should be locked?

The Exclusive Lock

Exclusive locks can only be applied to databases or tables only. An exclusive lock on a table or database avoids that any other user can obtain any lock on this object. Mainly, you will see Teradata locking exclusively during DDL command execution.

The Write Lock

Whenever a write lock is put onto an object, other users can obtain only access locks to the same object. Access locks allow dirty reads of the data “as is.” No exclusive locks, read locks or other write locks can be obtained during the time any user holds a write lock on the object of desire.

The Read Lock

Whenever a user holds a read lock on an object, no write locks or exclusive locks can be obtained by any other users. However, unlimited read locks (and access locks) can be held by other users on the same object at the same time.

The Access Lock

Access locks allow a user to read “inconsistent” data. Access locks work even if another user holds already a write lock on the object. The only exception when not even an access lock is allowed: in case an exclusive lock is already owned by another user on the same object.

Lock handling occurs all the time automatically on Teradata but is your decision to upgrade or downgrade locks.

For example:

SELECT * FROM <TABLE>;

Requires by default requires a read lock on the table,  and this is the default behavior to ensure data integrity.

Maybe you don’t care about having 100% accurate data and can live with changes going on at the same time you access the data. In this case, you could downgrade explicitly to an access lock:

LOCKING TABLE <TABLE> FOR ACCESS
SELECT * FROM <TABLE>;

The locking for access modifier in the query above now allows you to read the rows of <TABLE> even if they are currently “write locked” by another user.

Similarly, you could upgrade your lock, let’s say from a read lock to a write lock:

LOCKING TABLE <TABLE> FOR WRITE
SELECT * FROM <TABLE>;

Any other user who would need now a read lock, write lock or an exclusive lock will have to wait until your query finished. On the other hand, dirty reads would be still allowed at the same time (obtaining an access lock).

Our Reader Score
[Total: 10    Average: 3/5]
Teradata Locking – Ensure the Integrity written by Roland Wenzlofsky on May 7, 2014 average rating 3/5 - 10 user ratings

1 COMMENT

LEAVE A REPLY

Please enter your comment!
Please enter your name here