utility Script: who-locks.sh
----------------============
Synopsys: Displays information on active locks, more comprehensible than
the output of onstat -k
Dependencies:
- fragment-list.sh: A component of fragments.shar. Obtain at:
ftp://ftp.iiug.org/pub/informix/pub/fragments.shar.gz
- beautify-unl: Perl utility and module to straighten out columns
of delimited data. Obtain at:
ftp://ftp.iiug.org/pub/informix/pub/beautify-unl.shar.gz
Author: Jacob Salomon
jakesalomon@yahoo.com
Release: 2.1
Date: 2014-05-06 (I was surprised to see I had not submitted it to
IIUG before. My apologies.)
Motivation and Purpose of this Utility:
---------------------------------------
When a table lock, be it shared or exclusive, gets in the way of an
operation you need to run e.g. HPL (which demands exclusive access to
the table) it might be a good idea to find out who has a lock on that
table. Your first instinct, ifyou're not too jaded yet, mightbe to run:
$ onstat -k # (Output edited to fit in 72 columns)
Locks
address wtlist owner lklist type tblsnum rowid
10ae9ca58 0 13dbb7130 0 HDR+S 100002 2102
10ae9cad8 0 13dbb9a98 0 S 100002 2102
10ae9cb58 0 13dbbed68 0 S 100002 2101
...
Good luck figuring that out. Most of the locks displayed are database
locks and thus probably irrelevant to your quest. If you happen to
know the tblspace number of your target table perhaps you can grep the
above listing for it. (Plug alert: YOu can get the tblspace number[s] of
a table using my long-posted utility fragments.sh.) Still, there really
should be an easier way.
Well, seek no more, my friends! Here it is: Everything you want to
know about the locks in your server!
Example: You wish to run HPL on table yutz in database abcd i.e. abcd:yutz.
But you get the message that it is lock by another user. You simply run
who-locks.sh, specifying the database and name of your target table:
$ who-locks.sh -d abcd -t yutz.
The output would look something like he lines below, except it would be
much wider than my [self-imposed] 80-column editing window, so I will show
the heading and data lines split up:
|Table |Index|Row-ID |LKTY|Sess|Locked-by|AtHost
|abcd:jake.yutz|(NA) |0x00000000|X |2399|joe |robin.sherwood.com
|TTY|PID |Waiter|WName |WHost |Lock-Life |
| |23198| 2228|jsalomon|localhost| 0 00:01:03.000|
So I see that my target table, abcd:yutz (owned by user jake) is exclusively
locked (row ID 0 is the giveaway) by user joe, who is accessing it from his
host machine, robin.sherwood.com. Of course, there is no TTY data because
that is available only for a local session. If user joe were on a local
session (indicated by host name localhost) the TTY woule read something
like pts/29 and the PID would be that of the front-end program with the
database connection. Actually, even on the remote host as in the above
example, you might be able to log in on that host and use options to the
"ps" command to determine what application program is accessing the table.
The remainder of this README file is merely the output of who-locks.sh -H
That is: The supplied help text, which give a pretty good (if I may say
so myself) explanation of all the options.
$ who-locks.sh -H
who-locks.sh queries SMI tables tnd the catalogs of individual databases
in order to get expanded information on locks, including owner
identification, database and table names, waiter info, and lock
duration. All options that have parameters follow the same convention:
The option with a comma-separated list or repeating the option with one
or more parameters to follow. Examples below.
Usage:
who-locks.sh [-H]
[-d database] [-t table] [-u User-ID of lock owner]
[-h host machine] [-p PID] [-w waiter id] [-W] [-D]
-H : Display this help text and exit. Ignores other parameters
-d database : Display locks only on tables in the specified databases.
If multiple database are specified, the parameter
consists of a list of databases sepearted by commas.
Eg: -d store,utils
Also, the parameter me be repeated:
Eg: -d store -d utils,inventory
Default: All database.
-t table : Display locks only on tables in the list.
Eg: -t employee,task,project
Eg: -t employee -t task,project
Default: All tables.
-u owner : Specify a login ID. Display only those locks that are
held by the specified user[s].
Default: All owners
-h host : Specify a host machine - Show only those locks that are
owned by a user running on the named [client] machine.
(This is of arguable use for PC-based clients.)
Default: All locks, regardless of host.
-p pid : Process ID. Once you have identified some suspicious
usage, you can track those locks that were created
courtesy of only that client program by specifying its
process ID (PID).
-w waiter : Specify login ID of waiter. Suppose a user with the
login ID of yutz calls you (the DBA) and says "My app is
hanging; please see who is holding locks on the rows I
need." You can specify the "-w yutz" option and view only
those locks that are waited for by yutz.
-W : Show only those locks with someone waiting on them. This
is useful if there is a general applications slowdown and
you suspect users are waiting too long for locked rows.
If you specify both the -W option and a -w list, the list
will be ignored and who-locks.sh will use this -W option only.
-D : Show database locks. Since most database locks are
innocuous - nearly always shared - it is a waste of screen
(and eyeball) space to display the database locks. Hence,
the default is to omit them. Should it be necessary to
display database locks eg. someone needs to run dbexport,
use this option to see who's in the way.
==========================================================================
Download Latest Version
tpc-c-ifmx.tar.gz (392.7 kB)
