Name | Modified | Size | Downloads / Week |
---|---|---|---|
Parent folder | |||
README.1st | 2019-06-13 | 2.5 kB | |
spsl.sql | 2019-06-13 | 780 Bytes | |
Totals: 2 Items | 3.3 kB | 0 |
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