When a connection accesses data, SQL Server will use a lock to control access. These locks are used by SQL Server to ensure data integrity and to control concurrent access of data. In most cases, SQL Server locks do not present a large problem. But, occasionally, a process may hold a lock and stop other processes from accessing data, as shown in the previous lesson as a blocked process. To find out what is locked and what type of locks are being used, you can either use SQL or SQL-EM.
The sp_lock stored procedure will return information about tables that have a lock on them at the time the stored procedure was called. The syntax of sp_lock is as follows:
sp_lock [[@spid1=]spid [, [@spid2=]spid]]
If you do not specify a process ID,
returns locks for all processes. Otherwise it will only return lock information for the spid(s) passed in.
The following diagram describes SQL-EM lock.
The system process ID of the connection locking the resource. You can compare this with the ID from sp_who to find out the login name.
The database ID of the object being locked.
The object identifier of the table that is being locked.
The index ID represents the index that is being locked.
Type identifies the type of lock in request.
The resource identifies the specific item that is being locked. For instance, for a page lock, it identifies the database page.
Identifies the mode of the lock. A lock mode can be Shared, Update, Exclusive, Intent, Schema, or bulk update, or a combination of them.
The status column represents the lock status. It can be GRANT for granted locked, WAIT for locks that have been requested, or CNVT for locks that are being converted.
Click the Active SQL connectionlink to see the output of the sp_lock stored procedure. As you can see in the link above, the type column in the sp_lock output indicates what type of lock is in request. Here is a list of the types of locks and the abbreviations used for them.
When looking at the locks you will always see locks for the connection running sp_lock.
To convert the object ID to an object name, use the database that is locked and then type select object_name(ID).
In the next lesson, SQL-EM to monitor locks will be discussed.
Lock: A SQL Server mechanism use to ensure transaction isolation levels.