IIUG Software Repository Files

SP_SHOW_LOCKS, by Thomas J. Girsch
----------------------------------

The enclosed procedure, sp_show_locks(), gives a detailed list of which user
sessions currently hold locks against a specified database table, and what
type of locks they hold.  It has proven to be (for me, at least) an
invaluable tool in solving concurrency issues.

To create the sp_show_locks() procedure on a database <db>, simply type:

        dbaccess <db> spsl.sql

The procedure is only intelligent enough to find locks held against the current
database, so if you have many databases you'll have to modify the proc or else
create it in each database.

Usage:

        EXECUTE PROCEDURE sp_show_locks(<table-pattern>);

where <table-pattern> is the name of a table (as a quoted string) or a wildcard
The procedure is only intelligent enough to find locks held against the current
database, so if you have many databases you'll have to modify the proc or else
create it in each database.

Usage:

        EXECUTE PROCEDURE sp_show_locks(<table-pattern>);

where <table-pattern> is the name of a table (as a quoted string) or a wildcard
pattern that will match one or more tables.

Examples:

        EXECUTE PROCEDURE sp_show_locks("orders");
                -- shows current locks held against the 'orders' table

        EXECUTE PROCEDURE sp_show_locks("sys*");
                -- shows current locks held against system catalogs

        EXECUTE PROCEDURE sp_show_locks("*");
                -- shows all locks held


Example Output:

	(expression)  User informix (606) is 'X   ' locking table orders

In the above output, 'informix' is the user locking the table.  The number in
parens is the session ID of the session that owns the lock.  'X' is the lock
mode (exclusive), and 'orders' is the table name.


CAVEATS:

- Because the SP uses the SMI interface to get its information, it can
  often be somewhat slow.  I've seen it take as long as 30-45 seconds to
  return, although this isn't typical
- The procedure doesn't tell you whether a row/page in the table is being
  exclusively locked, or if the whole table is being locked.
- I thought about modifying the procedure to expand the lock mode from the
  letter code to a descriptive name, but this would have required hard-
  coding a mapping, and I felt that for forward-compatibility's sake this
  would be unwise.

If you have questions, comments, or just want to talk, I can be reached via
my e-mail, <tgirsch@iname.com>

Enjoy!

- Tom Girsch