Understanding locks part 2 - Types of Locks

In my last post we saw how to find and determine locks on your resources.  But what are the different types of locks and what do they do?

The two main ones would be exclusive (X) and shared (S).  An exclusive locks will lock that resource to prevent any other transaction from either writing to or reading from that resource, while a shared lock will allow others to read, but not write or update.

So multiple read locks can be made on the same resource.  I guess that's why they called it shared.  Here's a simple table showing which simultaneous locks are allowed.

	S	X
S	Yes	No
X	No	No

So if you have a exclusive lock, you have to wait for that transaction to complete, before your shared lock can take place.  Or vice versa.  But if you have a shared lock first, and the second one is a shared lock as well, no blocking occurs. Another type of lock are the intent locks.  The main flavours will be intent shared (IS) and intent exclusive (IX).   Think of these as a kind of marker, placed at the top of a hierarchical structure.  If a lock is required at row level then a shared lock, for example, could be placed on the row.  This would mean that if an exclusive lock is required on the same row, a lot of resource would be wasted in finding that row, only to find out it's already locked.  What you could do is place a marker at the table level with details about the row lock.  This saves the next transaction needlessly traversing the hierarchy to determine if a lock at row level is already in place. Let's see it in action.  In AdventureWorks (2008 or 2012), begin a transaction with an update:

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

In a separate query window / connection, run the query to find locks posted as in my previous post, Understanding locks part 1 - Finding locks and blocks.  You would expect to see row or key (for indexed columns) level blocks for this as we're updating only one row.  In this case you should see an IX lock on the table, and again on at least one page, finally followed by an X lock on the resource.  As you can see the details on the final actual exclusive lock trickle down through the hierarchy of the index pages.  Now in yet another query window, run the following select statement:

begin transaction selectit1; select * from person.Person where BusinessEntityID = 1;

If you check the locks again, you will now see something similar for S and IS locks. Same principle applies.  Now try running the same update statement in yet another window:

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

Check the locks again and you will see anew type of lock. U and IU for update and intent update.  An update locks is simply a type of exclusive lock that will become a proper exclusive lock once it has finished being blocked.  If you now commit or rollback updateit1, the exclusive locks for that transaction will vanish, as will the shared locks, and the update locks from updateit2 will change to exclusive locks.  Commit or rollback updateit2 to clear down all locks. Further details can be gleaned from http://technet.microsoft.com/en-us/library/aa213039(v=sql.80).aspx

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.