Displays all locks in the database.
Syntaxsa_locks( [ connection [ , creator [ , table_name [ , max_locks ] ] ] ] )
Argumentsconnection Use this INTEGER parameter to specify a connection ID number. The procedure returns lock information only about the specified connection. The default value is 0 (or NULL), in which case information is returned about all connections.
creator Use this CHAR(128) parameter to specify a user ID. The procedure returns information only about the tables owned by the specified user. The default value for the creator parameter is NULL. When this parameter is set to NULL, sa_locks returns the following information:
if the table_name parameter is unspecified, locking information is returned for all tables in the database
if the table_name parameter is specified, locking information is returned for tables with the specified name that were created by the current user
table_name Use this CHAR(128) parameter to specify 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.
max_locks Use this INTEGER parameter to specify the maximum number of locks for which to return information. The default value is 1000. The value -1 means return all lock information.
Result set| Column name | Data type | Description |
|---|---|---|
| conn_name | VARCHAR(128) | The name of the current connection. |
| conn_id | INTEGER | The connection ID number. |
| user_id | CHAR(128) | The user ID for the connection. |
| 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. |
| creator | VARCHAR(128) | The owner of the table. |
| table_name | VARCHAR(128) | The 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. This is either an 8-byte row identifier or NULL. |
RemarksThe sa_locks procedure returns a result set containing information about all the locks in the database.
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 (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 (intent to update table lock) Exclusive (exclusive table lock) |
See Table locks. |
| Position |
Phantom (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. |
A position lock can be associated with a sequential table scan, or an index scan. The index_id column indicates whether the position lock is associated with a sequential scan. If the position lock is held because of a sequential scan, the index_id column is NULL. If the position lock is held as the result of a specific index scan, the index identifier of that index is listed in the index_id column. The index identifier corresponds to the primary key of the ISYSIDX system table, which can be viewed using the SYSIDX view. If the position lock is held for scans over all indexes, the index ID value is -1.
PermissionsDBA authority
Side effectsNone
See also ExampleFor an example of this system procedure, and tips to augment the amount of information you can return, see How to obtain information about locks.
 |
Discuss this page in DocCommentXchange.
|
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 |