Contents Index sa_java_loaded_classes system procedure sa_make_object system procedure

ASA SQL Reference
  System Procedures and Functions
    System and catalog stored procedures

sa_locks system procedure


Function 

Displays all locks in the database.

Syntax 

sa_locks ( [ connection, ] [ [owner.]table_name, ] [max_locks ] )

Permissions 

DBA authority required

Side effects 

None

See also 

How locking works

Description 

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:

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:

When the lock_name is NULL, the lock_types can be a combination of:

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.

NULL lock_name 

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.


Contents Index sa_java_loaded_classes system procedure sa_make_object system procedure