Teradata Extension Teradata

Compatibility: Teradata Extension

Locking in Teradata is automatic and cannot be turned off for normal tables. There are four types of locks that are used and they are:

Compatibility: Teradata Extension

The resource that is locked depends on the SQL command requested by the user. The lock may be set at the database, view, table, or row level.

lock may be set at the database, view, table, or row level.

All SQL commands automatically request a lock. The Teradata RDBMS attempts to lock the resource at the lowest level possible. The lowest level is a row lock. However, Teradata places more importance on performance than resource availability. This implies that the optimizer has the last say in the locking level that is used.

For instance, an UPDATE has the option of locking at the table or row level. The optimizer knows that when an entire table is locked, all other users must wait to read even a single row from the table. However, when only a row is WRITE locked, other users still have access to the table, and only have to wait if they need to read the row currently locked. Therefore, normally row level locks are preferable so that rows have a maximum availability for users. This is especially important if another user is requesting a UPI value not used in the UPDATE. This type of locking provides more opportunity for concurrency of user requests and better overall performance.

However, the optimizer also knows when all rows in a table are going to be changed. It could follow the row locking to allow as much access as possible. However, eventually all rows are locked. Also, it knows that to lock a row and then read a row over and over again takes longer than locking the table once, reading all rows as fast as possible, and then releasing all locks at once. A full table scan needs all rows. Therefore, the normal row level lock will be escalated to a table level lock for speed on a full table scan. Additionally, by locking the table, it eliminates the potential for a deadlock between multiple user requests. Regardless of the approach to locking that the optimizer chooses, eventually all access to locked rows is denied for a period of time. The chart in Figure indicates that a WRITE lock blocks other WRITE locks requested by other users. Additionally all READ lock requests are also blocked because the current data is being changed and therefore, not available until it is finished. This is where the ACCESS lock can be useful.

ACCESS lock can be useful.

It is also seen in Figure that the WRITE lock does not block an ACCESS lock. Therefore, a user can request an ACCESS lock for a SELECT instead of the default READ lock. This does however mean that the data read may or may not be the latest version. Hence, the nickname "Dirty Read." This is commonly done in views. To request a locking change, the LOCKING FOR modifier can be used. It is written ahead of the SQL statement to modify the way it executes.
These are the various syntax formats of the LOCKING Modifier:

The first syntax listed above defaults to a ROW level lock using the desired lock. So, the first two LOCKING requests do the same thing. To make the command shorter, the LOCKING can be abbreviated to LOCK.

The first syntax format also shows the NOWAIT option. It indicates that if a resource is not available the statement should not wait. Instead, it will instantly ABORT. There is another option called MODE that can be used. However, it does not do anything and is there strictly for compatibility with DB/2 SQL. Lastly, also for compatibility, the FOR can be changed to an IN. The NOWAIT is available for all locking requests. The other specification in the above formats is used for specifying the database object to lock. In reality, multiple LOCKING modifiers might exist on a single SELECT. For instance, in a join operation an ACCESS lock might be requested for one table and not the other.

join operation an ACCESS lock might be requested for one table and not the other

The above chart shows that the SELECT is the only command that can use the LOCKING FOR modifier for anything other than EXCLUSIVE. This is because most locks cannot be downgraded to a lesser lock, without causing potential data integrity issues. Since the SELECT is not changing data, it can be downgraded safely.

It is very common to use the ACCESS locking when creating a view. Since most views only SELECT rows, a WRITE lock is not needed. Plus, if maintenance is being performed on a table, selecting rows using a view with an ACCESS lock is not delayed due to a WRITE lock. So, users are happy and don't call to complain that the "system is slow."

Another time to use the LOCKING modifier is for multi-step transactions. Consider this situation: The first step is a SELECT and obtains a READ lock. This lock allows other users to also SELECT from the table with a READ lock. Then, the next step of the transaction is an UPDATE. It must now upgrade the READ lock to a WRITE lock.

This upgrade of the lock cannot occur while other users have a READ lock on the resource. Therefore, the transaction must wait for the READ locks to disappear. This might dramatically increase the time to complete the maintenance transaction. Therefore, by upgrading the initial default of a READ lock to a WRITE lock for the SELECT it eliminates the potential for a delay in the middle of the transaction.

The next SELECT uses the ACCESS lock, common in a View:

EXPLAINLOCKING ROW FOR ACCESS WHERE customer_name LIKE 'Billy%' ; 10 Rows Returned


  • First, we lock MIKEL.Customer_table for access.
  • Next, we do an all-AMPs RETRIEVE step from MIKEL.Customer_table by way of an all-rows scan with a condition of ("MIKEL.Customer_table.Customer_name LIKE ‘Billy%’") into Spool 1, which is built locally on the AMPs. The size of Spool 1 is estimated with no confidence to be 4 rows. The estimated time for this step is 0.15 seconds.
  • Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.
  • -> The contents of Spool 1 are sent back to the user as the result of
    statement. The total estimated time is 0.15 seconds.

Since the locking modifier can name the table, each table may use different locking when multiple tables are referenced in the same SQL statement. We recommend that you consult the User Reference Manual if you are going to be using the LOCKING modifier extensively. It is not the intent of this to make you an expert on LOCKING.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Teradata Topics