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