December 28, 2011 at 1:59 pm
Okay, its the quiet time between holidays, and I thought I'd do some saw sharpening. I'm a bit of an accidental DBA, and I don't often darken the doorways of the forums here, so please forgive if I'm post this in the wrong area.
Does someone have an easy to understand resource for understanding locks in SQL 2008? (I can't easily identify an article on the site, but would appreciate a link if someone has one.)
At issue today:
There is an Update (U) lock which is explained as a Shared (S) lock, where an Exclusive (X) lock is anticipated. (I'm getting this definition from "Microsoft SQL Server 2008 - Database Development Self Paced Training Kit" p71)
So far so good.
But how does that that differ from a Shared with Intent Exclusive (SIX) lock?
and when I look at the MSDN it tosses me for a complete loop when it mentions yet another lock:
"Update intent exclusive (UIX) - A combination of U and IX locks, as a result of acquiring these locks separately and simultaneously holding both locks." So this is a lock mode which is a "shared lock with an exclusive lock anticipated", that also has the intent for Exclusive Lock?
December 28, 2011 at 2:07 pm
The difference between S and U is that multiple connections/transactions can take an S lock on data, but only one can take a U lock on something at a time. This helps prevent deadlocks when the U is upgraded to an X (exclusive) for the actual update to take place.
Does that help?
(That data is actually in the detailed description of the Update lock on the MSDN page you linked. I'd recommend reading that and discounting any part of the book you're referencing if it disagrees with the MSDN article.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 28, 2011 at 2:16 pm
Yes, it does help! Thank you.
(I'd been looking at Isolation Levels so long this afternoon, I was thinking in terms of the locks across that dimensions, and missed Intents being directed at higher objects (tables vs rows).)
Thank you for the quick response!
December 28, 2011 at 2:17 pm
Rich Weissler (12/28/2011)
There is an Update (U) lock which is explained as a Shared (S) lock, where an Exclusive (X) lock is anticipated. (I'm getting this definition from "Microsoft SQL Server 2008 - Database Development Self Paced Training Kit" p71)
Well, I suppose it could be explained that way, but it's not really that.
An update lock is half way between shared and exclusive. What happens when an update occurs is that SQL must lock all the rows to be updated exclusively. If it starts with exclusive, it could have to wait for some rows, a wait that will leave any readers also waiting and could also allow something else to start taking exclusive locks, potentially causing a deadlock. So what first happens is update locks are taken. These are compatible with shared locks (which exclusive isn't), but not with other updates, so this means that only one update can be 'pending'. The advantage here is that if SQL has to wait for some rows to release U or X locks, readers aren't blocked in the process.
Once all the rows are locked U (meaning that the only other locks around could be shared), then they are all converted to exclusive once any shared locks are released. It's to improve concurrency.
But how does that that differ from a Shared with Intent Exclusive (SIX) lock?
Shared with Intent Exclusive (which can only be found at page or table) means the resource is locked shared, and a subset (page or row if this is a table lock, row if this is a page lock) is locked exclusively. So a page could be locked S with a row on that page locked X.
"Update intent exclusive (UIX) - A combination of U and IX locks, as a result of acquiring these locks separately and simultaneously holding both locks." So this is a lock mode which is a "shared lock with an exclusive lock anticipated", that also has the intent for Exclusive Lock?
Err, no. That's an Update with Intent Exclusive, not shared. The resource locked update and a subset of that resource (page or row if this is a table lock, row if this is a page lock) is also locked Exclusively. Not a common combo. One way that could happen is if a row gets deleted (exclusive lock) and then the page needs locking U for an update. It wouldn't be around long, the U would have to go to X for the actual update, so that will reduce to just an X lock.
There's a 6 or so page intro to locking modes in chapter 6 of this: http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 28, 2011 at 2:42 pm
Thank you Gail, that just underscored my misunderstanding of 'Intent'. Re-reading the 'why' on Intent locks, and getting a different perspective from the book you linked, I think I understand where I'm going wrong. (I haven't internalized the information yet, but I think I understand why it was making me dizzy!)
(I just wish that book had a native Kindle format. 🙂 )
Thank you very much, I think this is putting me in the correct direction now.
December 28, 2011 at 3:02 pm
In short, to maybe clear things up a little.
If SQL has to take an X lock on a row, it does the following:
Take an IX lock on the table - meaning some resource within this will be locked X
Take an IX lock on the page - meaning some resource within this will be locked X
Take an X lock on the row.
This is for ease of checking lock compatibility. If all SQL had was an X lock on the row without any of the intents, and another process wanted an S lock at the table level, SQL would have to check locks on every single row in the table before it would know whether or not the table S lock could be granted. That could take a while
With the IX lock at the table level, it's a single check. Can an S lock be taken on this table? No, S is not compatible with IX.
As another example, let's say that there's the same X and IX locks, and something wants an S lock on a page.
First thing, SQL tries to take an IS lock on the table. That succeeds, IS and IX are compatible.
Then SQL tries to take the S lock on the page. If it's the same page that's locked IX, that S lock will have to wait. If it's a different page (hence has no locks), the lock is taken, and the table ends up with an IX and an IS at the table level, an S and an IX on different pages in the table and an X lock on a row
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply