Understanding locks part 1 - Finding locks and blocks

One thing I've had trouble with has been locks. What are the different types of locks and why are they important.  Locking can occur on various resources, such as a row, page, table or database.

How these locks work vary according to the lock type.  For example, you may get a lock on a row that prevents any updates to that row, but allows reading.  Or it may even prevent reading as well. SQL Server creates locks on resources to maintain compliance with the ACID principles.  In a nutshell, a lock will prevent a second user from either modifying a record at the same time as someone else or prevent reading from a resource before it has finished updating. 

A block occurs when a lock prevents another transaction from completing when that other transaction attempts to handle the same resource.  Locking and blocking are part of normal every day activity for SQL server, but sometimes they can cause application issues due to things like badly written queries or running large reports in a live environment.  So it helps to know how to locate locks to zero in on the source of the issue.

Finding locks:

First, let's create a lock or two. I'm using the AdventureWorks2008R2 database for this example.

begin transaction updateit;
update person.Person set FirstName = 'Kenny' where BusinessEntityID = 1;

Now that we have an un-commited transaction with an update, this should have created a lock, probably a row and / or key lock (more on the lock types in a later post).  Now how do we locate the locks and what types they are?  This is where the sys.dm_tran_locks DMV comes in (2005+).  Try this:

select * from sys.dm_tran_locks;

This gives some useful information but it would make a lot more sense if we joined to some system tables.  Now try this:

select db.name as DBName,l.resource_type,o.name as ObectName,au.type_desc,i.name as IndexName,p.rows,
    l.resource_description,l.request_mode
from sys.dm_tran_locks l  --Locking DMV
join sys.databases db on db.database_id = l.resource_database_id
left join sys.allocation_units au on au.allocation_unit_id =      l.resource_associated_entity_id
left join sys.partitions p on p.hobt_id =   l.resource_associated_entity_id
left join sys.indexes i on i.index_id = p.index_id
    and i.object_id = p.object_id
left join sys.objects o on o.object_id =
    case
        when p.partition_id is null then l.resource_associated_entity_id
        else p.object_id
    end
where db.database_id = DB_ID()  --Limit results to this database
    and l.resource_associated_entity_id <> 0 
    and o.name not like 'sys%'  --Ignore system base tables
order by o.name,i.name,l.resource_type,l.request_type;

 You will need to run this in the relevant database, i.e. AdventureWorks2008R2 for this example.  This should give you something that looks like:

Now we know what resources are locked.  We can see it's against the Person table and they are row level and key locks.  We also know the lock types, which are exclusive or intent exclusive locks.


Finding Blocks:

Now let's create a block.  Open up a separate tab / connection in SSMS to AdventureWorks2008R2 while the first transaction still remains uncommited, and run the following:

select * from person.Person where BusinessEntityID = 1;

Now because the first transaction hasn't been committed or rolled back yet, this statement won't complete and is now blocked.  If you run the above query again you should notice a few more rows, in particular intent shared locks.  But we're interested in blocks only at this point.

You could run "execute sp_who2; " and check the BlkBy column for Spids that are blocking others, and then run "DBCC INPUTBUFFER(spid); " with the source spid (there may be several nested blocks) to get the locking statement that's creating the block.

Or you could again use a DMV again to make things easier to spot.  Try this:

SELECT r.session_id, db.name as DBName, r.start_time, r.status, r.command, r.blocking_session_id, wait_type, wait_time, wait_resource, cpu_time, st.text as SQLText
FROM sys.dm_exec_requests r
join sys.databases db on db.database_id = r.database_id
cross apply sys.dm_exec_sql_text(r.sql_handle) st
where r.blocking_session_id > 0
order by start_time;

This should give you something like this:

Unlike sp_who2, this will only return blocked transactions, and will also give you the statement that is blocked and the time that it started.  Now you can use DBCC INPUTBUFFER as above.

Now commit or rollback the original update statement.  e.g. "rollback transaction updateit; "  You should now notice three things.
  1) The select statement has now returned its results.
  2) If you run the query to find locks, you should now longer see the locks
  3) If you run the query to find blocks, these will be gone too.

If push came to shove, and if there was no alternative, you could kill the spid of the transaction causing the blocks. In the above example, instead of committing or rolling back, you could run "kill 59 "  Note that in our example screenshot, 59 is the blocking spid.

In the next post I intend to talk about the different lock types.