| Lesson 4 | Locked resources |
| Objective | Monitor SQL Server locks. |
Monitor SQL Server Locks
When a connection accesses data, SQL Server will use a lock[1] 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.
Using sp_lock
The
If you do not specify a process ID,
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.
- The System Process Identifier (spid) uniquely identifies an active connection to SQL Server.
- Identifies the status of the program.
- Loginname identifies the SQL Server login or Windows NT login that is connected.
- Hostname identifies the computer from which the connection was made.
- Blk indicates if the process is blocked by another process. A zero means that the process is not blocked and any other number identifies the process that is holding the lock.
- Dbname identifies in the database that you are logged in to.
- Cmd provides a high-level understanding of what the process is currently doing.
Resources
Click the Active SQL connectionlink to see the output of the
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.
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
In the next lesson, SQL-EM to monitor locks will be discussed.
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.
[1]
Lock: A SQL Server mechanism use to ensure transaction isolation levels.
