Obtaining information about locks

In order 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 Table 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 it is possible that the rows visible in Sybase Central, or returned by the sa_locks system procedure, no longer exist by the time a query completes.

Viewing locks using Sybase Central

You can view locks in Sybase Central. Select the database in the left pane and then click the Table Locks tab in the right pane. For each lock, this tab shows you the connection ID, user ID, table name, lock type, and lock name.

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 uniquely identify the row in a table the lock refers to. In order 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.