determining who has locks on tables
Meet my problem, I know onstat -k shows who has locks on records,
but sometimes I don't see the locks shown (7.31.UC4 on AIX),
does anyone have scripts that show who has locks on what and
who is waiting for a lock on a record?
brett
--
-----------------------------------------------------------------
Brett's ninth law of UNIX administration...
Ye shall read the words:
http://cm.bell-labs.com/who/ken/index.html
http://cm.bell-labs.com/who/dmr/index.html
-----------------------------------------------------------------
Brett Geer - UNIX Admin/Analyst/Programmer - Intratex Holdings.
Tel. +27 31 717 4000 Direct. +27 31 717 4146
Fax. +27 31 717 4001
-----------------------------------------------------------------
If you have any trouble sounding condescending,
find a Unix user to show you how it's done.
--Scott Adams
-----------------------------------------------------------------
In article <905lof$roc$1@news.xmission.com>,
Brett Geer <brett@brabys.co.za> wrote:
>
> Meet my problem, I know onstat -k shows who has locks on records,
> but sometimes I don't see the locks shown (7.31.UC4 on AIX),
> does anyone have scripts that show who has locks on what and
> who is waiting for a lock on a record?
>
> brett
> --
>
> -----------------------------------------------------------------
> Brett's ninth law of UNIX administration...
> Ye shall read the words:
> http://cm.bell-labs.com/who/ken/index.html
> http://cm.bell-labs.com/who/dmr/index.html
> -----------------------------------------------------------------
> Brett Geer - UNIX Admin/Analyst/Programmer - Intratex Holdings.
> Tel. +27 31 717 4000 Direct. +27 31 717 4146
> Fax. +27 31 717 4001
> -----------------------------------------------------------------
> If you have any trouble sounding condescending,
> find a Unix user to show you how it's done.
> --Scott Adams
> -----------------------------------------------------------------
>
If you are saying onstat -k doesn't show the lock, I don't imagine
anything will.
If you want a more readable format than onstat -k, I pilfered this
from the newsgroup, and it has helped me out in the past.
SELECT
l.dbsname, l.tabname, l.rowidlk, l.type, s.sid, s.username,
s.hostname, l.waiter, w.username, w.hostname
FROM syslocks l,
syssessions s, outer syssessions w
WHERE l.owner = s.sid
AND l.waiter = w.sid
Hope this helps,
Will
Sent via Deja.com http://www.deja.com/
Before you buy.
On Thu, 30 Nov 2000 15:21:50 +0200, Brett Geer <brett@brabys.co.za> wrote:
>
>Meet my problem, I know onstat -k shows who has locks on records,
>but sometimes I don't see the locks shown (7.31.UC4 on AIX),
>does anyone have scripts that show who has locks on what and
>who is waiting for a lock on a record?
>
>brett
Here's a script I found and modified from the Informix users group:
Gary Q
#!/bin/sh
########################################################################
########
#
# Shell Script: lockrep
#
# Description: Program to find out tables which is locked by users.
#
# Author: Jayakumar George Date: 12/08/97
#
# Ver 1.1 Modified the script(31/10/97) to access the database once
for
# each database since the script is very slow and also lists
# user created temp tables if it is locked.
# Ver 1.2 Modified output for just released locks to fix output bug
# 08/14/2000 Gary Quiring
########################################################################
########
onstat -u | nawk ' BEGIN { getline;getline;getline;getline;getline;ctr = 1
}
{ if ( $2 == "active,")
{
while(getline)
}
else
{
if ($8 > 1)
{
username[ctr]=$4
sessionid[ctr]=$3
useraddr[ctr]=$1
ctr++
}
}
tmpctr=1
while("onstat -g sql"|getline)
{
if(tmpctr > 5)
databases[$1]=substr($0,22,18)
tmpctr++
}
close("onstat -g sql ")
}
END {
if (ctr == 1)
{
print "No User(s) Has a lock"
exit 1
}
print "The following User(s) has lock(s)"
print "User ID TTY Host/IP Sess. ID
Database Table(s) "
print "------- --- ------------- --------
-------- ----------------"
for(forctr=1;forctr< ctr;forctr++)
{
cmd="onstat -g ses " sessionid[forctr]
while(cmd | getline)
{
if($1 == "partnum")
{
while(cmd | getline)
{
tmptabname[$1]=$2
}
break
}
}
close(cmd)
cmd=""
"listusers|grep " username[forctr] | getline uname
printf "%-36s%8d %-15s",uname
,sessionid[forctr],substr(databases[sessionid[forctr]],1,15)
close("listusers")
cmd = "onstat -k | grep " useraddr[forctr]
dbtabflag = 0
printnull = 0
while(cmd | getline)
{
if($4 != 0)
{
if(tmptabname[$6])
tmptabs_locked[$6]=tmptabname[$6]
else
{
dbtabflag++
hextoint($6)
select_tablename(databases[sessionid[forctr]])
tabname_arr[res]=dbtab_arr[databases[sessionid[forctr]],res]
}
}
}
close(cmd)
ctrcmd2=1
if(dbtabflag)
{
for (tabname in tabname_arr)
{
if(ctrcmd2 == 1)
printf "%-18s",tabname_arr[tabname]
else
printf "%60c%-18s",32,tabname_arr[tabname]
ctrcmd2++
printf "\\n"
printnull = 1
delete tabname_arr[tabname]
}
}
for(tmptabvar in tmptabs_locked)
{
if(ctrcmd2 == 1)
printf "(T) %-18s",tmptabs_locked[tmptabvar]
else
printf "%60c(T)
%-18s",32,tmptabs_locked[tmptabvar]
ctrcmd2++
printf "\\n"
printnull = 1
delete tmptabs_locked[tmptabvar]
}
if (printnull == 0)
printf "Lock just released\\n"
}
}
function hextoint(a)
{
res=0
base=16
expn=0
for(i=length(a);i>0;i--)
{
val1=substr(a,i,1)
if(match(val1,"[Aa]"))
val1 = 10
if(match(val1,"[Bb]"))
val1 = 11
if(match(val1,"[Cc]"))
val1 = 12
if(match(val1,"[Dd]"))
val1 = 13
if(match(val1,"[Ee]"))
val1 = 14
if(match(val1,"[Ff]"))
val1 = 15
res=res + (val1 * (base^expn))
expn++;
}
}
function select_tablename(dbname)
{
if(dbtab_arr[dbname ,0])
{
return
}
else
{
selcmd1="echo select partnum,tabname from systables |
dbaccess " dbname " 2>/dev/null | grep -v \\"^$\\" | grep -v tabname"
while(selcmd1 | getline)
dbtab_arr[dbname,$1]=$2
close(selcmd1)
}
}
'
Hi Gary,
I'm interested in the script. But it looks like not finished. Will you
be able to paste the left part of it? Thanks.
In article <5TY2OpcTVWiOP3TZqEi6nc6q1eHq@4ax.com>,
Gary Quiring <garyq@emcosales.com> wrote:
> On Thu, 30 Nov 2000 15:21:50 +0200, Brett Geer <brett@brabys.co.za>
wrote:
>
> >
> >Meet my problem, I know onstat -k shows who has locks on records,
> >but sometimes I don't see the locks shown (7.31.UC4 on AIX),
> >does anyone have scripts that show who has locks on what and
> >who is waiting for a lock on a record?
> >
> >brett
> Here's a script I found and modified from the Informix users group:
>
> Gary Q
>
> #!/bin/sh
>
########################################################################
> ########
> #
> # Shell Script: lockrep
> #
> # Description: Program to find out tables which is locked by users.
> #
> # Author: Jayakumar George Date: 12/08/97
> #
> # Ver 1.1 Modified the script(31/10/97) to access the database
once
> for
> # each database since the script is very slow and also
lists
> # user created temp tables if it is locked.
> # Ver 1.2 Modified output for just released locks to fix output
bug
> # 08/14/2000 Gary Quiring
>
########################################################################
> ########
>
> onstat -u | nawk ' BEGIN {
getline;getline;getline;getline;getline;ctr = 1
> }
> { if ( $2 == "active,")
> {
> while(getline)
> }
> else
> {
> if ($8 > 1)
> {
> username[ctr]=$4
> sessionid[ctr]=$3
> useraddr[ctr]=$1
> ctr++
> }
> }
> tmpctr=1>
> while("onstat -g sql"|getline)
> {
> if(tmpctr > 5)
> databases[$1]=substr($0,22,18)
> tmpctr++
> }
> close("onstat -g sql ")
> }
> END {
> if (ctr == 1)
> {
> print "No User(s) Has a lock"
> exit 1
> }
> print "The following User(s) has lock(s)"
> print "User ID TTY Host/IP Sess.
ID
> Database Table(s) "
> print "------- --- ------------- -------
-
> -------- ----------------"
> for(forctr=1;forctr< ctr;forctr++)
> {
> cmd="onstat -g ses " sessionid[forctr]
> while(cmd | getline)
> {
> if($1 == "partnum")
> {
> while(cmd | getline)
> {
> tmptabname[$1]=$2
> }
> break
> }
> }
> close(cmd)
> cmd=""
> "listusers|grep " username[forctr] | getline
uname
> printf "%-36s%8d %-15s",uname
> ,sessionid[forctr],substr(databases[sessionid[forctr]],1,15)
> close("listusers")
> cmd = "onstat -k | grep " useraddr[forctr]
> dbtabflag = 0
> printnull = 0
> while(cmd | getline)
> {
> if($4 != 0)
> {
> if(tmptabname[$6])
> tmptabs_locked[$6]=tmptabname[$6]
> else
> {
> dbtabflag++
> hextoint($6)
>
> select_tablename(databases[sessionid[forctr]])
>
> tabname_arr[res]=dbtab_arr[databases[sessionid[forctr]],res]
> }
> }
> }
> close(cmd)
> ctrcmd2=1
> if(dbtabflag)
> {
> for (tabname in tabname_arr)
> {
> if(ctrcmd2 == 1)
> printf "%-18s",tabname_arr[tabname]
> else
> printf "%60c%-18s",32,tabname_arr
[tabname]
> ctrcmd2++
> printf "\\n"
> printnull = 1
> delete tabname_arr[tabname]
> }
> }
> for(tmptabvar in tmptabs_locked)
> {
> if(ctrcmd2 == 1)
> printf "(T) %-18s",tmptabs_locked
[tmptabvar]
> else
> printf "%60c(T)
> %-18s",32,tmptabs_locked[tmptabvar]
> ctrcmd2++
> printf "\\n"
> printnull = 1
> delete tmptabs_locked[tmptabvar]
> }
> if (printnull == 0)
> printf "Lock just released\\n"
> }
> }
>
> function hextoint(a)
> {
> res=0
> base=16
> expn=0
> for(i=length(a);i>0;i--)
> {
> val1=substr(a,i,1)
> if(match(val1,"[Aa]"))
> val1 = 10
> if(match(val1,"[Bb]"))
> val1 = 11
> if(match(val1,"[Cc]"))
> val1 = 12
> if(match(val1,"[Dd]"))
> val1 = 13
> if(match(val1,"[Ee]"))
> val1 = 14
> if(match(val1,"[Ff]"))
> val1 = 15
> res=res + (val1 * (base^expn))
> expn++;
> }
> }
>
> function select_tablename(dbname)
> {
> if(dbtab_arr[dbname ,0])
> {
> return
Here is what I use on my SCO system. It also shows the idle time for the
user so I can pull out my ruler on the users that have not hit a key in 2
hours!
usage: showlock (list all locks w/users idle times)
showlock username (only list locks for given username)
Steve
$ cat /usr/local/bin/showlock
#!/bin/ksh
tmpfil=showlock.$$
echo "
select unique dbsname[1,11],tabname[1,11],username,
rowidlk,type,tty[1,15]
from syssessions,syslocks
where owner=sid
and type != 'S'
order by 1 desc,2,3" | dbaccess sysmaster > $tmpfil
if [ "" = "$1" ]
then
while read ln
do
if [ ! -z "`echo $ln | grep /`" ]
then
t=`echo $ln | cut -f3 -d"/"`
x=`w | grep $t`
i=`echo $x | grep $t | cut -f4 -d" "`
echo "$ln\\t idle:$i"
else
echo "$ln"
fi
done < $tmpfil
else
grep $1 $tmpfil
fi
rm $tmpfil
--
-------------------------------------------------
Steven L Cooper
Manager, Systems Engineering
-------------------------------------------------
"Gary Quiring" <garyq@emcosales.com> wrote in message
news:5TY2OpcTVWiOP3TZqEi6nc6q1eHq@4ax.com...
> On Thu, 30 Nov 2000 15:21:50 +0200, Brett Geer <brett@brabys.co.za> wrote:
>
> >
> >Meet my problem, I know onstat -k shows who has locks on records,
> >but sometimes I don't see the locks shown (7.31.UC4 on AIX),
> >does anyone have scripts that show who has locks on what and
> >who is waiting for a lock on a record?
> >
> >brett
> Here's a script I found and modified from the Informix users group:
>
> Gary Q
>
> #!/bin/sh
> ########################################################################
> ########
> #
> # Shell Script: lockrep
> #
> # Description: Program to find out tables which is locked by users.
> #
> # Author: Jayakumar George Date: 12/08/97
> #
> # Ver 1.1 Modified the script(31/10/97) to access the database once
> for
> # each database since the script is very slow and also lists
> # user created temp tables if it is locked.
> # Ver 1.2 Modified output for just released locks to fix output bug
> # 08/14/2000 Gary Quiring
> ########################################################################
> ########
>
> onstat -u | nawk ' BEGIN { getline;getline;getline;getline;getline;ctr = 1
> }
> { if ( $2 == "active,")
> {
> while(getline)
> }
> else
> {
> if ($8 > 1)
> {
> username[ctr]=$4
> sessionid[ctr]=$3
> useraddr[ctr]=$1
> ctr++
> }
> }
> tmpctr=1>
> while("onstat -g sql"|getline)
> {
> if(tmpctr > 5)
> databases[$1]=substr($0,22,18)
> tmpctr++
> }
> close("onstat -g sql ")
> }
> END {
> if (ctr == 1)
> {
> print "No User(s) Has a lock"
> exit 1
> }
> print "The following User(s) has lock(s)"
> print "User ID TTY Host/IP Sess. ID
> Database Table(s) "
> print "------- --- ------------- --------
> -------- ----------------"
> for(forctr=1;forctr< ctr;forctr++)
> {
> cmd="onstat -g ses " sessionid[forctr]
> while(cmd | getline)
> {
> if($1 == "partnum")
> {
> while(cmd | getline)
> {
> tmptabname[$1]=$2
> }
> break
> }
> }
> close(cmd)
> cmd=""
> "listusers|grep " username[forctr] | getline uname
> printf "%-36s%8d %-15s",uname
> ,sessionid[forctr],substr(databases[sessionid[forctr]],1,15)
> close("listusers")
> cmd = "onstat -k | grep " useraddr[forctr]
> dbtabflag = 0
> printnull = 0
> while(cmd | getline)
> {
> if($4 != 0)
> {
> if(tmptabname[$6])
> tmptabs_locked[$6]=tmptabname[$6]
> else
> {
> dbtabflag++
> hextoint($6)
>
> select_tablename(databases[sessionid[forctr]])
>
> tabname_arr[res]=dbtab_arr[databases[sessionid[forctr]],res]
> }
> }
> }
> close(cmd)
> ctrcmd2=1
> if(dbtabflag)
> {
> for (tabname in tabname_arr)
> {
> if(ctrcmd2 == 1)
> printf "%-18s",tabname_arr[tabname]
> else
> printf "%60c%-18s",32,tabname_arr[tabname]
> ctrcmd2++
> printf "\\n"
> printnull = 1
> delete tabname_arr[tabname]
> }
> }
> for(tmptabvar in tmptabs_locked)
> {
> if(ctrcmd2 == 1)
> printf "(T) %-18s",tmptabs_locked[tmptabvar]
> else
> printf "%60c(T)
> %-18s",32,tmptabs_locked[tmptabvar]
> ctrcmd2++
> printf "\\n"
> printnull = 1
> delete tmptabs_locked[tmptabvar]
> }
> if (printnull == 0)
> printf "Lock just released\\n"
> }
> }
>
> function hextoint(a)
> {
> res=0
>
Related threads
- record locked
- who locks a record?
- Regarding Non-Default Page Sizes
- Don't Understand Table's Space Requirement