Activity Monitor (Locks by Process Page)

Use this page to view information about locks belonging to specific processes.

Options

  • Selected process
    Specify the system process ID (SPID) to display what locks are related to each process.

The following columns are displayed for each lock. The order of the columns in the grid can be changed by dragged this column headers to a preferred location.

  • Object
    The name of the object being locked.
  • Type
    The type of lock. Possible types are:

    Type Description

    RID

    Row identifier. Used to lock a single row individually within a table.

    Key

    A row lock within an index. Used to protect key ranges in serializable transactions.

    Page

    Data or index page.

    Extent

    A contiguous group of eight data pages or index pages.

    Table

    An entire table, including all data and indexes.

    Database

    An entire database.

    Metadata

    Descriptive information about the object.

    Other types are possible.

  • Subtype
    Additional type information. This column may be blank.
  • Object ID
    The object ID of the database object that is the subject of the lock.
  • Description
    Descriptive information, if any, provided by the thread.
  • Request Mode
    The type of lock requested.

    Type Description

    Shared (S)

    Used for operations that do not change or update data (read-only operations), such as a SELECT statement.

    Update (U)

    Used on resources that can be updated. Prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and then potentially updating resources later.

    Exclusive (X)

    Used for data modification operations, such as UPDATE, INSERT, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.

    Intent

    Used to establish a lock hierarchy.

    Schema

    Used when an operation dependent on the schema of a table is executing. There are two types of schema locks: schema stability (Sch-S) and schema modification (Sch-M).

    Bulk update (BU)

    Used when bulk copying data into a table and the TABLOCK hint is specified.

    RangeS_S

    Shared range, shared resource lock; serializable range scan.

    RangeS_U

    Shared range, update resource lock; serializable update scan.

    RangeI_N

    Insert range, null resource lock. Used to test ranges before inserting a new key into an index.

    RangeX_X

    Exclusive range, exclusive resource lock. Used when updating a key in a range.

  • Request Type
    The type of object requested.
  • Request Status
    The status of the lock request.

    Type Description

    GRANT

    Lock was obtained.

    WAIT

    Lock is blocked by another process.

    CNVT

    Lock is being converted to another lock. A lock being converted to another lock is held in one mode but is waiting to acquire a stronger lock mode (for example, update to exclusive). When diagnosing blocking issues, a CNVT can be considered similar to a WAIT.

  • Owner Type
    The internal owner of the lock, such as TRANSACTION, SESSION, or CURSOR.
  • Owner ID
    The owner ID associated with the process.
  • Owner GUID
    The GUID of the lock owner.
  • Database
    The database containing the locks.
  • Process ID
    The process ID that is holding or requesting the lock. This is the same as the Process ID selected at the top of the page.
  • Context
    Execution context ID. Represents the ID of a given thread associated with a specific SPID.
  • Batch ID
    The batch ID.

See Also

Other Resources

sp_lock (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance