ASA SQL Reference
System Procedures and Functions
System and catalog stored procedures
Displays all locks in the database.
sa_locks ( [ connection, ] [ [owner.]table_name, ] [max_locks ] )
DBA authority required
None
The sa_locks procedure returns a result set containing information about all the locks in the database.
The input parameters are as follows:
connection An integer representing a connection ID. The procedure returns lock information only about the specified connection. The default value is zero, in which case information is returned about all connections.
table_name A char(128) parameter representing a table name. The procedure returns information only about the specified tables. The default value is NULL, in which case information is returned about all tables.
If you do not include owner, it is assumed that the table is owned by the caller of the procedure.
max_locks An integer parameter representing the maximum number of locks for which to return information. The default value is 1000. The value -1 means return all lock information.
The information returned for each lock includes the following:
connection ID The connection ID that has the lock.
user name The user connected through connection ID.
table name The table on which the lock is held.
lock type The lock type is a string of characters indicating the type of lock. For lock_names other than NULL, these characters are:
S Shared
E Exclusive
P Phantom
A Anti-phantom
All locks listed have exactly one of S or E specified, and may also have P, A, or both. If a lock is a phantom or anti-phantom lock, a qualifier is added to the lock type. The qualifier is as follows:
T The lock is with respect to a sequential scan
* The lock is with respect to all scans.
nnn An index number. The lock is with respect to a particular index.
When the lock_name is NULL, the lock_types can be a combination of:
S Shared schema lock
E Exclusive schema lock
AT Shared row lock
PT Intent mode on a row lock
lock_name The LockName value identifying the lock. This value can be matched with sa_conn_info output to determine the responsible locks in a blocking situation.
Lock_names can be a row ID or can be NULL.
If the lock_name is NULL, then the row contains information about two types of lock: a schema lock, and a lock on rows.
The schema lock means that other transactions are prevented from modifying the table schema. This schema lock can be acquired in shared (S) or exclusive (E) mode.
The row lock applies to the rows in the table. It can be acquired in shared mode or intent mode. Shared mode is represented by lock_type AT, and intent mode by lock_type PT. If acquired in share mode, other transactions cannot modify the rows unless they acquire the lock in intent mode. However, the lock can only be acquired in share mode if there are no uncommitted modifications to the table by other transactions.
For example, if a connection has modified a table but not yet done a commit or rollback, then sa_locks will return a NULL lock_name for the table, and a lock_type of at least SPT. S indicates a shared lock on the schema of the table and PT indicates an intent lock on the rows in the table.
For more information, see Connection-level properties, and sa_conn_info system procedure.