In this article ,we will see SHOWLOCKS UTILITY
The Show Locks utility, showlocks, provides information about Host Utility (HUT) locks placed on databases and tables by the Archive/Recovery (ARC) utility during database operations.
These locks might interfere with application processing and should be released after utility processing is complete. Show Locks also displays information about locks placed during a Teradata Database system failure.
Lock information can also be monitored and displayed using the Lock Viewer portlet in Teradata Viewpoint.
Scenarios Runs From
Database Window or comparable interface to the Teradata Database console subsystem, such as
- Teradata Viewpoint Remote Console portlet
- Host Utility Console
Host Utility Locks
You can release HUT locks either by submitting a separate RELEASE LOCK SQL command, or by using the RELEASE LOCK option of the appropriate command. For example, ARCHIVE, ROLLBACK, RESTORE, BUILD, and ROLLFORWARD.
HUT locks placed by the Archive/Recovery utility differ from locks placed by other operations or transactions.
HUT locks have the following characteristics
Archive/Recovery locks are associated with the currently logged-on user who entered the command, rather than with a batch job or transaction.
Only the AMPs that are participating in the Archive/Recovery operation are locked.
Archive/Recovery locks placed at one level of an object never conflict with a utility lock at another level that was placed on the same object for the same user. The locking modes and levels are applied as follows:
A Read lock is placed on an object being dumped.
Locks are placed at the cluster level during a CLUSTER dump.
If a table being dumped is defined for an after-image permanent journal (and the appropriate option was selected on the DUMP command), a group Read lock is placed on the table rows.
A Write lock is placed on all tables involved in ROLLFORWARD and ROLLBACKWARD recovery operations.
A Write lock is placed on a journal table that is being deleted.
A Write lock is placed on a permanent journal table that is being restored.
An Exclusive lock is placed on any object being restored that is not a journal table.
Archive/Recovery locks remain active until you release them.
Note: If Archive/Recovery locks are not specifically released, they are automatically reinstated following a Teradata Database or client system restart.
Interpreting the Show Locks Display
The Show Locks utility display provides this information:
Summary of the showlocks function
- Name of the databases and tables on which locks are placed
- Username that placed each lock
- Lock mode: read, write, exclusive, or access
- Number of the AMPs on which the locked database or table resides
Show Locks reports All AMPs rather than individual AMP numbers when the locked database or table resides on all AMPs. Information is provided for only the most restrictive lock a user has placed on an object
USER ADMIN MODE Read AMP All AMPs
USER PETERS MODE Read AMP 24
USER ACCMGR MODE Excl AMP 27
USER DBC MODE Excel AMP 3
--ShowLocks Processing Complete--
In the previous example, exclusive locks are placed on the employee table in the personnel database and on the entire service database. One read lock is placed on the parts database, and another read lock on the invoice table in the accounting database.
From 16.10 onwards, along with the above details you will get the jobid too for which the lock was placed or left.
In order to release the HUT lock, we need to run release lock command from dbc user.
Release lock databasename, override;
Release lock databasename where jobid =<<value>>