Teradata Locks

TERADATA LOCKS

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:

Types of Lock EXCLUSIVE

WRITE

READ ACCESS

EXCLUSIVE	The resource is temporarily owned. Not available to users until the lock is released. This is normally obtained at the database or table level when the structure/definition is being modified.
WRITE	A data block has been retrieved from disk with the intention of modifying one or more rows. The block  containing the modified data will be written back to disk.
READ	A data block has been retrieved from disk. No changes will be made to the data.
ACCESS	Affectionately called a Dirty Read lock. Allows a SELECT to read data that is locked for WRITE. It is a very minimal form of locking.

This chart shows the automatic locking in Teradata respective to SQL commands:    Type of Lock  Caused by Locks Blocked for Other Users EXCLUSIVE DDL EXCLUSIVE, WRITE, READ, ACCESS WRITE INSERT , UPDATE, DELETE EXCLUSIVE, WRITE, READ  READ SELECT EXCLUSIVE, WRITE ACCESS   EXCLUSIVE

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. This chart shows the impact of these levels of locking: If Locked at          Resource(s) unavailable to other users    DATABASE     All tables, views, macros and triggers owned by the database/user.       VIEW     All tables referenced in the View.      TABLE     All rows in the table.       ROW     All rows with the same row hash. 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 16-2 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. It is also seen in Figure 16-2 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: LOCKING [<table-name>] FOR <desired-locking> [NOWAIT] LOCKING ROW FOR <desired-locking> LOCKING DATABASE <database-name> FOR <desired-locking> LOCKING VIEW <view-name> FOR <desired-locking> LOCKING TABLE <table-name> FOR <desired-locking> 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. This chart shows which SQL commands can use the modifier: Modifier SQL allowed with Modifier Level EXCLUSIVE INSERT,UPDATE,DELETE,SELECT Table, View or Row WRITE SELECT Table, View or Row  READ SELECT Table, View or Row ACCESS SELECT Table, View or Row 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: EXPLAIN LOCKING ROW FOR ACCESS WHERE customer_name LIKE ‘Billy%’ ; Explanation  1) First, we lock MIKEL.Customer_table for access.  2) 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.  3) 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 1. 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.