April 19, 2011 at 9:34 am
I run a "select * from my table" and see that it is using 'IS' type of lock and simulataneously i run another select * from the same table and that also shows 'IS' type of lock and suprisingly i do not see any blocking. I thought selects will lock the tables?
April 19, 2011 at 10:56 am
Check this for details: http://technet.microsoft.com/en-us/library/ms175519.aspx
Essentially, an IS lock is "Intent Shared", which allows other shared locks (selects, essentially) to access the same data.
Shared (S) locks allow concurrent transactions to read (SELECT) a resource under pessimistic concurrency control. For more information, see Types of Concurrency Control. No other transactions can modify the data while shared (S) locks exist on the resource. Shared (S) locks on a resource are released as soon as the read operation completes, unless the transaction isolation level is set to repeatable read or higher, or a locking hint is used to retain the shared (S) locks for the duration of the transaction.
So they specifically allow other selects. If you want to see a block occur, get something to take an IS lock, then run an update or other data-altering operation on the locked resources.
In one of the few cases where I actually do use locking hints, I've used exclusive, repeatable locks to select data in one operation, and then update it in another, guaranteeing that I update the exact data that I selected. Because of the way IS locks work, I can't count on them to allow that.
For example, I have an SSIS package that exports some data to to a text file, and then has to mark which data it exported. One proc that takes an exclusive, repeatable lock on the table I'm selecting from, followed by an update with the same Where clause, and I get what I need. If I didn't intentionally escalate the lock, once the select was done, data could change, and I'd potentially mark rows as "exported" when they weren't, and so on.
- 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
April 19, 2011 at 11:20 am
GSquared (4/19/2011)
Check this for details: http://technet.microsoft.com/en-us/library/ms175519.aspxEssentially, an IS lock is "Intent Shared", which allows other shared locks (selects, essentially) to access the same data.
Shared (S) locks allow concurrent transactions to read (SELECT) a resource under pessimistic concurrency control. For more information, see Types of Concurrency Control. No other transactions can modify the data while shared (S) locks exist on the resource. Shared (S) locks on a resource are released as soon as the read operation completes, unless the transaction isolation level is set to repeatable read or higher, or a locking hint is used to retain the shared (S) locks for the duration of the transaction.
So they specifically allow other selects. If you want to see a block occur, get something to take an IS lock, then run an update or other data-altering operation on the locked resources.
In one of the few cases where I actually do use locking hints, I've used exclusive, repeatable locks to select data in one operation, and then update it in another, guaranteeing that I update the exact data that I selected. Because of the way IS locks work, I can't count on them to allow that.
For example, I have an SSIS package that exports some data to to a text file, and then has to mark which data it exported. One proc that takes an exclusive, repeatable lock on the table I'm selecting from, followed by an update with the same Where clause, and I get what I need. If I didn't intentionally escalate the lock, once the select was done, data could change, and I'd potentially mark rows as "exported" when they weren't, and so on.
Thanks. So IS lock with allow mostly selects and might/will cause block for inserts/updates/deletes?
April 19, 2011 at 11:32 am
Gus has a good explanation. shared locks are compatible with other shared locks. It's exclusive locks that block, or Intent Exclusive locks.
You can see lock compatibility here: http://msdn.microsoft.com/en-us/library/ms186396.aspx
April 19, 2011 at 11:43 am
Easiest way to think of Intent locks (IS, IX, SIX) is that they're high level locks used to optimize other processes so they don't have to go looking across the table if they're trying to get an xlock at the tbl level.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply