Sometimes if you try to delete or truncate data from the table that is being used by the other process then session may lock the table resulting deadlock. You have to identify and kill such sessions before attempting to delete or truncate that table.In this article, we will discuss about Netezza identify and kill tables locks with an examples.
Read:
Netezza Identify and Kill Table Locks
The mail question is how to identify the locks? There are couple of options available to identify the table locks in Netezza.
Netezza script nz_show_locks
You can use the Netezza provided scripts to identify table locks
Syntax:
nz_show_locks <db name> <tablename>
Samples Output:
[nz@netezza bin]$ nz_show_locks TRAINING TEST123 Database: TRAINING Object: TEST123 ObjectId: 212972 Timestamp: 2017-05-14 14:16:19 =========================================================== The following session(s) are HOLD'ing a lock on the object Requested | Granted @ | Wait Time | SESSIONID | PROCESSID | USERNAME | LOCKMODE | Current SQL Command -----------+-----------+-----------+-----------+-----------+ (0 rows) ============================================================ The following sessions are WAIT'ing to access the object Requested | Granted @ | Wait Time | SESSIONID | PROCESSID | USERNAME | LOCKMODE | Current SQL Command -----------+-----------+-----------+-----------+-----------+ (0 rows) ============================================================
The second option is to use and check the tables using Netezza system tables.
Netezza System Table _T_PG_LOCKS
Netezza uses implicit table locking when there is a DDL operation on it, you cannot explicitly lock the table. You can check the table _T_PG_LOCKS to see the Netezza table locks.
Read:
Below is the query to identify the locks using tpg_locks system table:
select SESSIONID,DBNAME,RELNAME,USERNAME,USERNAME from _t_pg_locks;
Sample output:
TRAINING.ADMIN(ADMIN)=> select SESSIONID,DBNAME,RELNAME,USERNAME,USERNAME from _t_pg_locks; SESSIONID | DBNAME | RELNAME | USERNAME | USERNAME -----------+----------+--------------+----------+---------- 17378 | TRAINING | ADMIN | ADMIN | ADMIN 16991 | GLOBAL | TRAINING | ADMIN | ADMIN 16009 | GLOBAL | SYSTEM | ADMIN | ADMIN 16008 | SYSTEM | ADMIN | ADMIN | ADMIN (4 rows) TRAINING.ADMIN(ADMIN)=>
Kill/Terminate the Table Locks
Once you identify the table lock and its associated session id, then simply kill the session using Netezza Session commands to release the table.
Read: Netezza nzsession commands and Examples.
[nz@netezza bin]$ nzsession abort -id 12345 -force