Online Documentation for SQL Administrator for SQL Server

Locks

This node of the Performance section displays locks belonging to specific processes.

Using the context menu of the selected process or toolbar buttons you can:

kill the process

show command text of the process

create and print the report

The list displays locks as a grid with the following columns: Process ID, Request Mode, Resource Type, Resource Subtype, Database, Schema, Object, Resource Description, Request Mode, Request Type, Request Owner GUID, Request Owner ID, Request Status, Request Reference Count, Request Owner Type.

Shared (S) locks allow concurrent transactions to read (SELECT) a resource. No other transactions can modify the data while shared (S) locks exist on the resource.

Update (U) locks are used on resources that can be updated. They locks prevent a common form of deadlock.

Exclusive (X) locks prevent access to a resource by concurrent transactions.

Intent shared (IS) locks indicate the intention of a transaction to read some (but not all) resources lower in the hierarchy by placing S locks on those individual resources.

Intent exclusive (IX) locks indicate the intention of a transaction to modify some (but not all) resources lower in the hierarchy by placing X locks on those individual resources.

Shared with intent exclusive (SIX) locks indicates the intention of the transaction to read all of the resources lower in the hierarchy and modify some (but not all) resources lower in the hierarchy by placing IX locks on those individual resources. Concurrent IS locks at the top-level resource are allowed.

Schema modification (Sch-M) locks are used when a table data definition language (DDL) operation (such as adding a column or dropping a table) is being performed.

Schema stability (Sch-S) locks are used when compiling queries. Schema stability (Sch-S) locks do not block any transactional locks, including exclusive (X) locks. Therefore, other transactions can continue to run while a query is being compiled, including transactions with exclusive (X) locks on a table. However, DDL operations cannot be performed on the table.

Bulk update (BU) locks allow processes to bulk copy data concurrently into the same table while preventing other processes that are not bulk copying data from accessing the table.