Question & Answer
Question
How can I investigate which query is putting a lock on an NPS table?
Answer
To resolve a locked table you need to know which query is causing the lock, the type of table lock being used, and if other queries are waiting, the user name and session ID for those queries.
***The show locks command is for internal use only. It has not been tested fully for customer use.***
In NPS 4.5 you can use the show locks command to investigate slow queries. The following example shows how an uncommitted Insert statement caused other queries to wait:
Execute the following commands from terminal1.
DEV(ADMIN)=> begin;
BEGIN
DEV(ADMIN)=> insert into t1 select * from t2;
INSERT 0 2
Execute the following commands from terminal2.
DEV(ADMIN)=> truncate t2;
Execute the following commands from terminal3.
DEV(ADMIN)=> show locks;
SESSIONID | DATABASEID | RELID | USERNAME | PROCESSID | CLIENTIP | LOCK | LOCKMODE | COMMAND
-----------+------------+--------+----------+-----------+-------------+------+---------------------+----------------------------------
10091 | 200126 | 228543 | ADMIN | 13383 | 10.77.1.140 | HOLD | AccessShareLock | insert into t1 select * from t2;
10090 | 200126 | 228543 | ADMIN | 13304 | 10.77.1.140 | WAIT | AccessExclusiveLock | truncate t2;
10091 | 200126 | 228531 | ADMIN | 13383 | 10.77.1.140 | HOLD | AccessShareLock | insert into t1 select * from t2;
10091 | 200126 | 228531 | ADMIN | 13383 | 10.77.1.140 | HOLD | RowExclusiveLock | insert into t1 select * from t2;
(4 rows)
Next, use the nzsession command to view the respective queries.
nzsession
ID Type User Start Time PID Database State Priority Client IP Client PID Command
----- ---- ----- ----------------------- ----- -------- ------- -------- ----------- ---------- ------------------------
10002 SQL ADMIN 22-Feb-08, 10:29:06 EST 9254 SYSTEM System normal 9252
10004 SQL ADMIN 22-Feb-08, 10:29:06 EST 9267 SYSTEM System normal 9265
10089 SQL ADMIN 23-Feb-08, 03:50:13 EST 13243 DEV Idle normal 10.77.1.140 13242 show locks;
10090 SQL ADMIN 23-Feb-08, 03:50:32 EST 13304 DEV Active normal 10.77.1.140 13303 truncate t2;
10091 SQL ADMIN 23-Feb-08, 03:59:58 EST 13383 DEV Tx-Idle normal 10.77.1.140 13382 insert into t1 select *
10093 SQL ADMIN 23-Feb-08, 04:19:59 EST 13927 SYSTEM Active normal 10.77.1.140 13926 select id,username,connt
Explanation of show locks Output:
Of interest are the three entries for a single Insert statement. From session id 10091 the user executes an Insert statement. Because the Insert statement is not committed, this session is holding the table.
a) 10091 | 200126 | 228543 | ADMIN | 13383 | 10.77.1.140 | HOLD | AccessShareLock | insert into t1 select * from t2;
b) 10091 | 200126 | 228531 | ADMIN | 13383 | 10.77.1.140 | HOLD | AccessShareLock | insert into t1 select * from t2;
c) 10091 | 200126 | 228531 | ADMIN | 13383 | 10.77.1.140 | HOLD | RowExclusiveLock | insert into t1 select * from t2;
Entry (a) shows an “AccessShareLock” which is due to the “select * from t2” portion of the query. You can verify this by using the corresponding relation id:
DEV(ADMIN)=> select objid,objname from _T_object where objid in (228543);
OBJID | OBJNAME
--------+---------
228543 | T2
(1 rows)
Entries (b) and (c) respectively show “AccessShareLock” and “RowExclusiveLock”; these are due to the “insert into t1” portion of the query. You can verify this by using the corresponding relation id:
DEV(ADMIN)=> select objid,objname from _T_object where objid in (228531);
OBJID | OBJNAME
--------+---------
228531 | T1
(1 rows)
The show locks output shows that from session id 10090, a truncate statement is executed. Because the Insert query is not committed and holding a lock, session 10090 is Waiting for the Insert query to be completed.
10090 | 200126 | 228543 | ADMIN | 13304 | 10.77.1.140 | WAIT | AccessExclusiveLock | truncate t2;
In this case only one lock is acquired by truncate which is “AccessExclusiveLock” and its status is waiting.
You can use the show locks command with a session-id parameter to display information about the query that is causing the table lock. In the following example, session ID 10090 is waiting for session ID 10091.
DEV(ADMIN)=> show locks 10090;
SESSIONID | DATABASEID | RELID | USERNAME | PROCESSID | CLIENTIP | LOCK | LOCKMODE | COMMAND | DESCRIPTION
-----------+------------+--------+----------+-----------+-------------+------+---------------------+----------------------------------
10090 | 200126 | 228543 | ADMIN | 13304 | 10.77.1.140 | WAIT | AccessExclusiveLock | truncate t2; THIS SESSION IS WAITING FOR A LOCK
10091 | 200126 | 228531 | ADMIN | 13383 | 10.77.1.140 | HOLD | AccessShareLock | insert into t1 select * from t2; REQUIRED LOCK HELD BY THIS SESSION
(2 rows)
Note: You cannot use the show locks command in a select query.
Related Information
Was this topic helpful?
Document Information
More support for:
IBM PureData System
Software version:
1.0.0
Document number:
196727
Modified date:
17 October 2019
UID
swg21577321