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.