IDS Forum

RE: Query to identify session holding lock@table

Posted By: Plugge, Joe R.
Date: Friday, 7 June 2013, at 5:09 p.m.

In Response To: Query to identify session holding lock@table (O KHAN)

This works well for us ...:

#!/usr/bin/ksh
# who-lock.sh - Determine who has locked what rows in which tables
#
# Author: Jacob Salomon
# JakeSalomon@netscape.net
# Version: 2.0
# Change History:
# o Release 2.0: Added parameter handling capability
# --------------------------------------------------------------------
# Parameter:
# - Names of tables in form:
# o database:table
# o database
#
# Outputs to stdout.
#
UNLFILE=/tmp/lock-list-$$.unl
OUTFILE=/tmp/lock-list-$$.out
parse_params() # Function to handle the parameters.

# Output: A WHERE clause
{
LTOP=$# # Parameter count
LC=1 # Initialize array & loop counter
while [ $LC -le $LTOP ]
do

if (echo $1 | grep -q :) # If it in the form of dbs:table

then

echo $1 | tr : " "| read DN TN # Separate dbs from table

WCL[$LC]='(dbsname = "'${DN}'" and tabname = "'${TN}'")'

else

WCL[$LC]='(dbsname = "'$1'")'

fi

shift

LC=$(( $LC + 1 ))
done
WH="where "
LC=1 # Restart the loop counter for new loop
while [ $LC -le $LTOP ]
do

if [ $LC -gt 1 ]

then # If more than 1 part to the WHERE clause

WH=${WH}" or"

fi

WH=${WH}" "${WCL[$LC]}

LC=$(( $LC + 1 ))
done
echo $WH # Spout to the caller
}

if [ $# -eq 0 ]
then
WHERE_CLAUSE=""
else
# Find out the nature of the parameter
WHERE_CLAUSE=`parse_params $*`
echo WHERE_CLAUSE: 1>&2
echo $WHERE_CLAUSE 1>&2
fi
dbaccess sysmaster - <<%%
set isolation to dirty read;
select dbsname, tabname,

hex(rowidlk) lock_row, type,

s.username locked_by,

l.owner, s.hostname, s.tty,

l.waiter,

w.username waiter_name
from sysmaster:syslocks l,

sysmaster:syssessions s,

outer sysmaster:syssessions w
where l.owner = s.sid

and l.waiter = w.sid
and not ( (l.dbsname = "sysmaster")

and (l.tabname = "sysdatabases"))
union
select d.name database_name, "(database)",

hex(rowidlk) lock_row, type,

s.username locked_by,

l.owner, s.hostname, s.tty,

l.waiter,

w.username waiter_name
from sysmaster:syslocks l,

sysmaster:sysdatabases d,

sysmaster:syssessions s,

outer sysmaster:syssessions w
where l.owner = s.sid

and l.waiter = w.sid

and ( (l.dbsname = "sysmaster")

and (l.tabname = "sysdatabases"))

and l.rowidlk = d.rowid
into temp t_locked_rows
;
unload to $UNLFILE
select * from t_locked_rows
$WHERE_CLAUSE
order by locked_by, owner, dbsname, tabname, lock_row
;
%%
cat >$OUTFILE <<%%
Database|Table|Row-ID|LK-type|Locked-by|Session|AtHost|TTY|Waiter|Wait-Name|
--------|-----|------|-------|---------|-------|------|---|------|---------|
%%
cat $UNLFILE >>$OUTFILE
beautify-unl.sh $OUTFILE
rm $UNLFILE $OUTFILE

-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of O KHAN
Sent: Friday, June 07, 2013 3:53 PM
To: ids@iiug.org
Subject: Query to identify session holding lock@table [30455]

Dear All,

I am trying to alter a table, but database is giving me the error of
non-exclusive access.

242: Could not open database table (informix.cust).
106: ISAM error: non-exclusive access.

I need to identify all those sessions who are either holding a shared or
exclusive lock on this particular table, so that I could kill those sessions
to release the lock and then could perform the alter.

Can someone share a query or procedure to which i passes the table-name and it
retrieves all those session-ids who are holding any kind of lock on that
table?

It will be great help.

Thanks in advance for your anticipation.

Regards,
Khan

*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.

Messages In This Thread

• Query to identify session holding lock@table
  O KHAN -- Friday, 7 June 2013, at 4:52 p.m.
  • Re: Query to identify session holding lock@table
    Art Kagel -- Friday, 7 June 2013, at 5:02 p.m.
    • Re: Query to identify session holding lock@table
      O KHAN -- Friday, 7 June 2013, at 5:19 p.m.
      • Re: Query to identify session holding lock@table
        Art Kagel -- Friday, 7 June 2013, at 5:30 p.m.
        • Re: Query to identify session holding lock@table
          O KHAN -- Friday, 7 June 2013, at 5:53 p.m.
          • Re: Query to identify session holding lock@table
            Cesar Inacio Martins -- Friday, 7 June 2013, at 7:49 p.m.
          • Re: Query to identify session holding lock@table
            david@smooth1.co.uk -- Saturday, 8 June 2013, at 1:03 p.m.
  • RE: Query to identify session holding lock@table
    Plugge, Joe R. -- Friday, 7 June 2013, at 5:09 p.m.
  • Re: Query to identify session holding lock@table
    John Miller iii -- Friday, 7 June 2013, at 5:23 p.m.
    • Re: Query to identify session holding lock@table
      O KHAN -- Friday, 7 June 2013, at 5:45 p.m.
      • Re: Query to identify session holding lock@table
        John Miller iii -- Friday, 7 June 2013, at 8:17 p.m.
      • Re: Query to identify session holding lock@table
        Kern Doe -- Friday, 7 June 2013, at 11:49 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.