Shows information about locks in the database, for both the IQ Store and the Catalog Store.
sp_iqlocks ([ connection,] [[ owner.]table_name] max_locks,] [sort_order])
Table 10-31 lists the optional sp_iqlocks parameters you can specify to restrict results.
Name |
Data type |
Description |
|---|---|---|
connection |
integer |
Connection ID. With this option, the procedure returns information about locks for the specified connection only. Default is zero, which returns information about all connections. |
owner.table_ name |
char (128) |
Table name. With this option, the procedure returns information about locks for the specified table only. Default is NULL, which returns information about all tables in the database. If you do not specify owner, it is assumed that the caller of the procedure owns the table. |
max_locks |
integer |
Maximum number of locks for which to return information. Default is 0, which returns all lock information. |
sort_order |
char(1) |
Order in which to return information:
|
Displays information about current locks in the database. Depending on the options you specify, you can restrict results to show locks for a single connection, a single table, or a specified number of locks.
sp_iqlocks displays the following information, sorted as specified in the sort_order parameter:
Column |
Description |
|---|---|
connection |
Connection ID that has the lock. |
user_id |
User associated with this connection ID. |
table_name |
Table on which the lock is held. |
lock _type |
String of characters indicating the type of lock:
.All locks listed have exactly one of S, E, or W, and may also have P, A, or both. Phantom and antiphantom locks also have a qualifier of T or *:
|
lock name |
Value identifying the lock. |
If sp_iqlocks cannot find the
connection ID or user name of the user who has a lock on a table,
it displays a 0 (zero) for the connection ID and User unavailable for
the user name.
Exclusive, phantom, or antiphantom locks can be placed
on Adaptive Server Anywhere tables, but not on Sybase IQ tables.
Unless you have explicitly taken out locks on a table in the Catalog
Store, you will never see these types of locks (or their qualifiers
T, *, and nnn) in a Sybase IQ database. For
information on how locking works in Adaptive Server Anywhere tables, see
the Adaptive Server Anywhere SQL User’s Guide.
The first example shows the sp_iqlocks procedure call and its output in a Sybase IQ database. The procedure is called with all default options, so that the output shows all locks, sorted by connection.
call sp_iqlocks()
connection user_id table_name lock_type lock_name70187172 'mary' 'DBA.t2' 'S' (NULL) 604945019 'russ' 'russ.t3' 'S' (NULL) 604945019 'russ' 'russ.t3' 'W' (NULL) 1550990889 'DBA' 'dbo.spt_mda' 'S' (NULL) 1744647885 'DBA' 'russ.t1' 'S' (NULL) 1744647885 'DBA' 'russ.t1' 'W' (NULL) 1744647885 'DBA' 'russ.t3' 'S' (NULL) 2120842322 'shemp' 'shemp.t1' 'S' (NULL) 2120842322 'shemp' 'shemp.t1' 'W' (NULL)
The next example shows sp_iqlocks with sorting by table name.
call sp_iqlocks(0,null,0,'t')
connection user_id table_name lock_type lock_name70187172 'mary' 'DBA.t2' 'S' (NULL) 1550990889 'DBA' 'dbo.spt_mda' 'S' (NULL) 1744647885 'DBA' 'russ.t1' 'S' (NULL) 1744647885 'DBA' 'russ.t1' 'W' (NULL) 604945019 'russ' 'russ.t3' 'S' (NULL) 604945019 'russ' 'russ.t3' 'W' (NULL) 1744647885 'DBA' 'russ.t3' 'S' (NULL) 2120842322 'shemp' 'shemp.t1' 'S' (NULL) 2120842322 'shemp' 'shemp.t1' 'W' (NULL)
