How to obtain information about locks

To diagnose a locking issue in the database it may be useful to know the contents of the rows that are locked. You can view the locks currently held in the database using either the sa_locks system procedure, or using the Locks tab in Sybase Central. Both methods provide the information you need, including the connection holding the lock, lock duration, and lock type.

Note

Due to the transient nature of locks in the database, the rows visible in Sybase Central, or returned by the sa_locks system procedure, may no longer exist by the time a query completes.

Viewing locks using Sybase Central

Viewing locks using the sa_locks system procedure

The result set of the sa_locks system procedure contains the row_identifier column that allows you to identify the row in a table the lock refers to. To determine the actual values stored in the locked row, you can join the results of the sa_locks system procedure to a particular table, using the rowID of the table in the join predicate. For example:

SELECT S.conn_id, S.user_id, S.lock_class, S.lock_type, E.* 
  FROM sa_locks() S JOIN Employees E WITH( NOLOCK ) 
     ON RowId(E) = S.row_identifier 
  WHERE S.table_name = 'Employees';

Note

It may not be necessary to specify the NOLOCK table hint; however, if the query is issued at isolation levels other than 0, the query may block until the locks are released, which will reduce the usefulness of this method of checking.