June 29, 2012 at 1:26 pm
how does sql server prioritize while having both read locks and write locks together...Does it allow write locks ahead of read locks???
June 29, 2012 at 1:44 pm
At the 50,000 foot view without digging into a ton of details, they basically queue up behind each other, with the internals allowing for expected short shared locks (read) to cut in line of an exclusive lock (write) if it's blocked anyway by another read that's taking a bit.
Look up the internal stored procedure SP_Lock and it'll get you where you want to start reading, in all the different lock types and what their purposes are.
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
June 29, 2012 at 1:51 pm
Order they are requested in. Otherwise it would be possible to have a 'lock starvation' condition where newly requested locks jump in front of older requests due to the type. Hence that does not happen
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
June 29, 2012 at 1:57 pm
GilaMonster (6/29/2012)
Order they are requested in. Otherwise it would be possible to have a 'lock starvation' condition where newly requested locks jump in front of older requests due to the type. Hence that does not happen
Hm, I understood that some shared locks could 'jump the line'...
*scrounges around the intertubes*
Um, I can't find it. Trust Gila, Pradeep. Unless I can prove I'm not wrong... she's right. 😀
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
June 29, 2012 at 3:30 pm
Evil Kraig F (6/29/2012)
GilaMonster (6/29/2012)
Order they are requested in. Otherwise it would be possible to have a 'lock starvation' condition where newly requested locks jump in front of older requests due to the type. Hence that does not happenHm, I understood that some shared locks could 'jump the line'...
Consider this:
Table with 10000 rows. Update wants an exclusive table lock, because it wants to update > half of those. Lots and lots of people doing single-row select. Very fast compared with the update. What happens to that update's wait time if the shared locks from the selects are allowed to jump the queue because they are fast and compatible with the currently held locks?
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
June 29, 2012 at 3:36 pm
GilaMonster (6/29/2012)
Evil Kraig F (6/29/2012)
GilaMonster (6/29/2012)
Order they are requested in. Otherwise it would be possible to have a 'lock starvation' condition where newly requested locks jump in front of older requests due to the type. Hence that does not happenHm, I understood that some shared locks could 'jump the line'...
Consider this:
Table with 10000 rows. Update wants an exclusive table lock, because it wants to update > half of those. Lots and lots of people doing single-row select. Very fast compared with the update. What happens to that update's wait time if the shared locks from the selects are allowed to jump the queue because they are fast and compatible with the currently held locks?
I was more thinking of the other way, where a shared table lock exists and while that's running an IX is going for a few rows... and another read request for a different row comes in, or even the same one. Since the table lock won't clear the individual request can go. However, in theory the IX may want to escalate as well and... yeah, round and round the monkey goes.
However, until I can get another particular project off my desk and out the door I won't have the opportunity to build out the test bed to prove myself wrong. 🙂 I see your logic and I can see both possibilities being valid approaches.
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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply