Ingres Q & A
>From: adcsmgr@sgp.hp.com (ADCS Account Manger)
>Subject: Finding the Locking Process Id
>Date: Fri, 15 Sep 1995 03:48:47 GMT
>Hi,
> Sometimes, we find that one of the processes locks the tables and
>so all the other processes/sessions end up in a 'WAIT' state. Is there a
>quick way to identify this process,other than finding the transaction id
>from the Locks summary of IPM and then using logstat to find the corresponding
>session and using IPM again to find the process id.
> Also, in case a session connected thru a net happens to lock, is there
>a way to identify the process id. We can use dba_operations to delete, but
>Ingres suggests that we use a unix kill for which knowing the process id is
>essential.
> Thanks for any ideas in advance
>Cheers
>Jumbu
I have done a snap shot of lockstat and logstat to show you the problem.
Referring to the snap shot, Table 187 (which is toys_eis - info from
iirelation table) from Database 2FC3A9E0 (which is ippms_dev - info
from "infodb database" is requiring an exclusive lock and can not get it,
therefore it is waiting. You get the Tran_id: which is 00002F6FBF236B8F from
lockstat and this should appear in logstat. From logstat you can get
the user name which is "ingres" and the session id: which is 005D5020. From
there you can go into iimonitor and remove the session. Pretty easy hay? Off
course you can go thro this long and tedious process. But you can make it
easy for oneself and "script it" in which I have done by using awk anfd unix
shell.
eg
show_locks (script )
Grant: Requ: State: Table: Who: Session ID:
--- X WT toys_eis ingres 005D5020
S S GR toys_eis martinm 005D5012
Would it be better of to identify the session and killing that instead of
killing the process?
Lockstat:
Id: 00120516 Tran_id: 00002F6FBF236B8F R_llb: 000B08A9 R_cnt: 0 Wait: 02BD001F L
ocks: (1,0/80) Status: WAIT
Id: 0011055A Rsb: 02BD001F Gr: Req: X State: WT PHYS(1) KEY(TABLE,DB=
2FC3A9E0,TABLE=[187,0])
Logstat:
Tx_id: 00710017 Tran_id: 00002F6FBF236B8F Database: 0032000E
Process: 0001000A Dis_tran_id: 0000000000000000 Session: 005D5020
First: <0,0,0> Last: <0,0,0> Cp: <795852643,890,72>
Write: 0 Split: 0 Force: 0 Wait: 0
Status: INACTIVE,PROTECT,JOURNAL
User:
Michael
Hi!
I have a utility written in ingperl. Maybe this is what you need. If
you like it use it. I had sent it to few people of this news group.
They may copy this, since some of the bugs have been removed in this.
Let me know if you feel some improvement can be made.
Sample Output:
Database: chronos User: aennen Session: 403FE340 Status:
Database: trissdr User: kmahesh Session: 405B2040 Status:
Database: production User: ukrishna Session: 4031C040 Status:
Database: unit301 User: cfoofat Session: 404E44A0 Status:
Gr: S Req: S State: GR PHYS(1) Table: srva_disconnect
Database: unit301 User: dyang Session: 404A4BC0 Status:
Gr: S Req: S State: GR PHYS(1) Table: ii_abfobjects
Gr: S Req: S State: GR PHYS(1) Table: ii_objects_index
----------------- Cut here-------------------------------
#!/usr/contrib/bin/ingperl
############################################################################
# inglock
############################################################################
# This script shows locks on various tables taken by users. One can
# specify database name on which to look for locks.
#
# Options : inglock [-ddatabase_name]
#
# Output: Lock output
#
# Limitation: The person looking for locks on a database should have
# access to that database.
#
# Written By: Gajendra Jain
############################################################################
require "getopt.pl";
&Getopt("d");
open(LOG,"logstat|") || die "Failed to execute LOGSTAT.....\n";
open(LOCK,"lockstat|") || die "Failed to execute lockstat.....\n";
while () {
if ((/Database:/) && (/Status:/)) {
$_ =~ m/^.*Id: (.*) Database: \((.*),.*\).*/;
$dataid{$1} = $2;
undef $1,$2;
}
if ((/Database:/) && (/Tran_id:/)) {
$_ =~ m/^.*Tran_id: (.*) Database: (.*)/;
$tran_id{$1} = $dataid{$2};
$id = $1;
undef $1,$2;
}
if (/Session:/) {
$_ =~ m/^.*Session: (.*)$/;
$tran_id{$id} .= ".".$1;
undef $1,$2;
}
if (/User:/) {
$_ =~ m/^.*<(.*)>.*/;
$tran_id{$id} .= ".".$1;
delete $tran_id{$id} if ($tran_id{$id} =~ /\$/);
undef $1,$2;
}
}
while () {
last if (/Locks by resource/);
if (/Tran_id:/) {
$analock = "";
$_ =~ m/^.*Tran_id: (.*) R_llb:.*Status:(.*)/;
if ($tran_id{$1}) {
@datauser=split(/\./,$tran_id{$1});
$dbname = $datauser[0];
$uname = $datauser[2];
$session = $datauser[1];
if ($opt_d) {
next if ($dbname ne $opt_d);
}
$analock = "TRUE";
print "Database: ",$dbname," User: ",$uname;
print " Session: ",$session," Status: ",$2,"\n";
next;
}
undef $1,$2;
}
if ($analock) {
if ((/KEY\(TABLE/) || (/KEY\(CONTROL/)) {
$_ =~ /^ Id:.*(Gr:.*)KEY.*TABLE=\[(.*),.*\].*/;
$statlock = $1;
if (!$tablist{$dbname.$2}) {
&tabproc($dbname,$2);
}
$key1 = $dbname.$2;
print "\t",$statlock, " Table: " ,$tablist{$dbname.$2},"\n";
}
}
}
#
# Procedure to get the table name corresponding to a relid.
#
sub tabproc {
&sql("connect ".$_[0]) || return;
$sql_stmt = "
select trim(relid)
from iirelation
where reltid = $_[1]";
&sql($sql_stmt) || return;
while ($table_name = &sql_fetch()) {
$key = $_[0].$_[1];
$tablist{$key} = $table_name;
}
&sql_exec("commit");
&sql("disconnect");
return;
}
------------------------Done-------------------------------------------
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Gajendra Jain
DBA,TRIS Division Voice.(217)351-8250 Ext2381
CSC CIS - TRIS Division Fax.(217)351-7420
115 N. Neil St. Suite 409 Email. gjain@csci.csc.com
Champaign, IL 61824-0770
********************************************************************
Ingres Q & A
To William's Home Page
© William Yuan 2000
Email William