Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Reference » System Procedures » System procedures

sa_load_cost_model system procedure Next Page

sa_locks system procedure

Displays all locks in the database.


[ connection
[ , creator
[ , table_name
[ , max_locks ] ] ] ]

Result set
Column nameData typeDescription
conn_nameVARCHAR(128)The name of the current connection.
conn_idINTEGERThe ID number of the connection
user_idCHAR(128)The user connected through connection ID.
table_typeCHAR(6)The type of table (either BASE or GBLTMP).
creatorVARCHAR(128)The owner of the table.
table_nameVARCHAR(128)The table on which the lock is held.
index_idINTEGERThe index ID or NULL.
lock_classCHAR(8)The lock class. One of Schema, Row, Table, or Position. See Objects that can be locked.
lock_durationCHAR(11)The duration of the lock. One of Transaction, Position, or Connection.
lock_typeCHAR(9)The lock type (this is dependent on the lock class).
row_identifierUNSIGNED BIGINTThe identifier for the row. This is either an 8-byte row identifier or NULL.

The 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 classLock typesComments

Shared (shared schema lock)

Exclusive (exclusive schema lock)

For schema locks, the row_identifier and index ID values are NULL. See Schema locks.


Read (read lock)

Intent (intent lock)

Write (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. See Locking during inserts. 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. See Row locks.


Shared (shared table lock)

Intent (intent to update table lock)

Exclusive (exclusive table lock)

See Table locks.


Phantom (phantom lock)

Insert (insert lock)

In most cases, 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. See Position locks.

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 with respect to 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.


DBA authority required

Side effects


See also

For an example of this system procedure, as well as tips to augment the amount of information you can return, see Obtaining information about locks.