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, sp_lock returns locks for all processes. Otherwise it will only return lock information for the spid(s) passed in.
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.