sp_iqlocks Procedure

Syntax

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

Parameter

All parameters are optional to restrict results.

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

Privileges

You must have EXECUTE privilege on the system procedure, as well as the MONITOR system privilege.

Remarks

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	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)	The lock class. One of Schema, Row, Table, or Position.
lock_duration	CHAR(11)	The duration of the lock. One of Transaction, Position, or Connection.
lock_type	CHAR(9)	The lock type (this is dependent on the lock class).
row_identifier	UNSIGNED BIGINT	The identifier for the row the lock starts on, or NULL.
row_range	BIGINT	The number of contiguous rows that are locked. Row locks in the RLV store can either be a single row, or a range of rows.

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.

The value in the lock_type column depends on the lock classification in the lock_class column. The following values can be returned:

Lock Class	Lock Types	Comments
Schema	Shared – Shared schema lock. Exclusive – (IQ catalog store tables only) Exclusive schema lock.	For schema locks, the row_identifier and index ID values are NULL.
Row	Read – Read lock. Intent – Intent lock. ReadPK – Read lock. Write – Write lock. WriteNoPK – Write lock. Surrogate – Surrogate lock.	Row read locks can be short-term locks (scans at isolation level 1) or can be long-term locks at higher isolation levels. The lock_duration column indicates whether the read lock is of short duration because of cursor stability (Position) or long duration, held until COMMIT/ROLLBACK (Transaction). Row locks are always held on a specific row, whose 8-byte row identifier is reported as a 64-bit integer value in the row_identifier column. A surrogate lock is a special case of a row lock. Surrogate locks are held on surrogate entries, which are created when referential integrity checking is delayed. There is not a unique surrogate lock for every surrogate entry created in a table. Rather, a surrogate lock corresponds to the set of surrogate entries created for a given table by a given connection. The row_identifier value is unique for the table and connection associated with the surrogate lock. If required, key and non-key portions of a row can be locked independently. A connection can obtain a read lock on the key portion of a row for shared (read) access so that other connections can still obtain write locks on other non-key columns of a row. Updating non-key columns of a row does not interfere with the insertion and deletion of foreign rows referencing that row.
Table	Shared – Shared table lock. Intent – Iintent to update table lock. Exclusive – (IQ catalog store tables only) Exclusive table lock.	None
Position	Phantom – (IQ catalog store tables only) Phantom lock. Insert – Insert lock.	Usually a position lock is also held on a specific row, and that row's 64-bit row identifier appears in the row_identifier column in the result set. However, Position locks can be held on entire scans (index or sequential), in which case the row_identifier column is NULL.

Note: Exclusive, phantom, or antiphantom locks can be placed on IQ catalog store tables, but not on SAP Sybase IQ tables in the IQ main store. Unless you have explicitly taken out locks on a table in the catalog store, you never see these types of locks in an SAP Sybase IQ database.

Example

The example shows the sp_iqlocks procedure call and its output in the SAP 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  
=========         =======      =======  ==========  =======  ==========  
SQL_DBC_13cd6038  3            DBA      BASE        DBA      rv_locks2  
SQL_DBC_13cd6038  3            DBA      BASE        DBA      rv_locks2 
SQL_DBC_13cd6038  3            DBA      BASE        DBA      rv_locks2  
RVL_CONN_T775     1000000407            BASE        DBA      rv_locks2 

index_id  lock_class  lock_duration  lock_type  row_identifier   row_range
========  ==========  =============  =========  ==============   ========= 
          Schema      Transaction    Shared                        
          Row         Transaction    Row        1                4
          Row         Transaction    Row        281474976710656  1
          Table       Transaction    Intent