sp_iqlocks Procedure

Shows information about locks in the database, for both the IQ store and the catalog store.

Syntax

sp_iqlocks ([connection,] [[owner.]table_name] max_locks,][sort_order])

Permissions

DBA authority required. Users without DBA authority must be granted EXECUTE permission to run the stored procedure.

Usage

Optional sp_iqlocks parameters you can specify to restrict results:

Optional sp_iqlocks parameters
Parameter	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: C sorts by connection (default) T sorts by table_name

Description

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:

sp_iqlocks columns
Column	Data type	Description
conn_name	VARCHAR(128)	The name of the current connection.
conn_id	INTEGER	Connection ID that has the lock.
user_id	CHAR(128)	User associated with this connection ID.
table_type	CHAR(6)	The type of table. This type is either BASE for a table, GLBTMP for global temporary table, or MVIEW for a materialized view. Materialized views are only supported for SQL Anywhere tables in the IQ catalog store.
creator	VARCHAR(128)	The owner of the table.
table_name	VARCHAR(128)	Table on which the lock is held.
index_id	INTEGER	The index ID or NULL
lock_class	CHAR(8)	String of characters indicating the type of lock: S – share. SW – share and write. EW – exclusive and write. E – exclusive. P – phantom. A – antiphantom. W – write. All locks listed have one of S, E, EW, or SW, and may also have P, A, or both. Phantom and antiphantom locks also have a qualifier of T or : T – the lock is with respect to a sequential scan. – the lock is with respect to all scans. `nnn` – Index number; the lock is with respect to a particular index. Sybase IQ obtains a share lock before a write lock. If a connection has exclusive lock, share lock does not appear. For write locks, if a connection has all-exclusive, share, and write locks, it is EW.
lock_duration	CHAR(11)	The duration of the lock. One of Transaction, Position, or Connection.
lock_type	CHAR(9)	Value identifying the lock (dependent on the lock class)
row_identifier	UNSIGNED BIGINT	The identifier for the row or NULL.

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.

Note: Exclusive, phantom, or antiphantom locks can be placed on SQL Anywhere tables, but not on Sybase IQ tables. Unless you have explicitly taken out locks on a table in the catalog store, you never see these types of locks (or their qualifiers T, *, and nnn) in a Sybase IQ database. For information on how locking works in SQL Anywhere tables, see SQL Anywhere Server – SQL Usage.

Examples

The 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()

conn_name  conn_id  user_id  table_type  creator  table_name  
=========  =======  =======  ==========  =======  ==========  
con1       70187172 'mary'   BASE        DBA       t1         

index_id              lock_class  lock_duration  lock_type  row_identifier
========              ==========  =============  =========  ==============
ASIQ_IDX_T452_C19_FP  Table       Position       Table      1