When Your Dirty Read is Kept Waiting

Blog
The best minds from Teradata, our partners, and customers blog about whatever takes their fancy.
Teradata Employee

Access locking, Teradata’s dirty read capability, is a good match if you want your online application to read through a write lock placed by load activity running at the same time. Works like a charm.  But there is one type of lock that access locking is helpless when faced with—an exclusive lock. Here’s what to watch out for, and here’s some recommendations to reduce the time your access lock queries will ever have to be kept waiting.

You may not have thought much about exclusive locks when designing your application, because they are commonly only placed by DDL operations, such as ALTER TABLE, MODIFY DATABASE, or DROP TABLE. These are not the kinds of operations that usually take place very often, and when they do, it’s often after-hours, or they only have a limited, local impact

Once applied, an exclusive lock is intended to stick around for only a short time, at least the automatic ones that the DBS code sets. However, if the exclusive lock has to wait on other transactions to complete in order to be placed, because other transactions are either reading or updating the desired object, then queries with access locks that are queued for a lock on the same object behind the exclusive lock will have a longer wait. Transactions with access locks cannot jump the queue ahead of transactions waiting to place exclusive lock.

Here is something to keep your eye on when a partitioned primary index (PPI) table is undergoing maintenance. An ALTER TABLE is the usual way to add or remove partitions from a PPI table, and it requires an exclusive lock on the base table. This might need to be performed during busy query times, and the action and its exclusive lock could have to wait until all the queries using that PPI table have completed. Indexes on the PPI table can lengthen the time it takes for that ALTER to complete.

When dropping partitions, if NUSIs or join indexes are present, it is recommended that the rows be deleted from the partition (an activity which does not require an exclusive lock) before issuing the ALTER TABLE to drop the partition. This will minimize the amount of time the exclusive lock will be held.

Here’s another example of minimizing exclusive lock conflicts. Changing the space requirements of a database will put an exclusive lock on the entire database. There is a way to avoid the exclusive lock entirely when you need to do this kind of thing: When modifying a database (call it Database X) to add space, build a dummy database and assign that database the space you wish to add to Database X. Then use the GIVE command to give the dummy database to Database X. When you drop the dummy database, Database X will get the space that was originally assigned to the dummy database, with no exclusive lock.

Your time, and mine, is too precious to put up with unnecessary waiting.

2 Comments
Enthusiast
Hello Carrie

While reading above article, I need one clarification. You mentioned -

" When dropping partitions, if NUSIs or join indexes are present, it is recommended that the rows be deleted from the partition (an activity which does not require an exclusive lock) before issuing the ALTER TABLE to drop the partition "

My question is - How to delete rows from partition , without deleting partition?
Teradata Employee
Karam,

You can delete all the rows in a partition by specifying a DELETE FROM ppi-table WHERE... command where the partitioning key value or range of partitioning key values describes rows contained within that partition. This all-AMPs DELETE statement will place a write lock on the table for the time it takes to perform the deletes from the PPI table and to maintain the NUSIs or/or join indexes.

After the DELETE statement is completed all the rows within that partition will have been deleted (and the various indexes updated), but the partition will still be defined on the PPI table. To remove the empty partition, you then issue an ALTER TABLE that will drop that partition from the PPI definition, even though it has already had its rows deleted. Under these conditions, the exclusive lock will be held for a shorter length of time during the ALTER using this technique.