August 26, 2012 at 9:45 pm
Edited to say: "Nothing"
August 27, 2012 at 8:36 pm
SQL Kiwi (8/26/2012)
Sergiy (8/26/2012)
Was not so clear to me, as it turns out. Learned couple of things here, thanks to the good samaritans.Good. Well the last (important) thing to be learned is that the storage engine does not maintain an 'old version' of the page to compare with the 'new version' after the update. The engine knows whether it had to make any changes or not, and simply sets the is_dirty bit if so. As Gail mentioned, the way you have been thinking it works would be horribly inefficient. If the engine knows that it didn't have to update any bytes on the page, it doesn't set the dirty bit.
Fail.
There is no smallest proof for this statement.
And MS clearly sais it's wrong:
http://msdn.microsoft.com/en-us/library/ms172909%28v=sql.105%29.aspx
Lock mode Description
Shared (S) Protects a resource for read access. No other transactions can modify the data while shared (S) locks exist on the resource.
Exclusive (X) Indicates a data modification, such as an insert, an update, or a deletion. Ensures that multiple updates cannot be made to the same resource at the same time.
http://msdn.microsoft.com/en-us/library/ms175519%28v=SQL.105%29.aspx
Shared (S) Used for read operations that do not change or update data, such as a SELECT statement.
Update (U) Used on resources that can be updated. Prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and potentially updating resources later.
Exclusive (X) Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.
All locks applied on the rows are exclusive.
Which "Indicates a data modification".
Unless, of course, you can prove that MS have made "a bad choice of words".
🙂
_____________
Code for TallyGenerator
August 27, 2012 at 9:19 pm
At the risk of being shot down in flames for trying to 'simplify'what I believe is going on (and this based on:
a) my prior knowledge
b) the considerable amount I have learned via this thread and subsequent fiddling around
)
I think an update where no update occurs can perhaps being thought of as a logical update. That is, as has already been said, locks are applied in preparation for the update but there is no physical, bit level change. As, Gail mentioned, it seems so many years ago now, you see the transaction start and commit in the log, but there is no associated MODIFY_ROW record and the page is not marked as dirty. Every part of the UPDATE process still occurs except the physical update - which makes sense..to me...as I said...before 😎
August 27, 2012 at 11:00 pm
MissTippsInOz (8/27/2012)
I think an update where no update occurs can perhaps being thought of as a logical update. That is, as has already been said, locks are applied in preparation for the update but there is no physical, bit level change. As, Gail mentioned, it seems so many years ago now, you see the transaction start and commit in the log, but there is no associated MODIFY_ROW record and the page is not marked as dirty. Every part of the UPDATE process still occurs except the physical update - which makes sense..to me...as I said...before 😎
A good summary. The physical update can be optimized away sometimes, but transactions, latches and locks are still required to make sure everything stays consistent.
August 27, 2012 at 11:13 pm
Sergiy (8/26/2012)
You failed to explain how does it compare pages while the locks are applied to RID.
It doesn't compare whole pages - that would be horribly inefficient. It knows if it has modified the page or not and sets the dirty bit if so.
And at which stage "Tom" is translated to 0x546F6D.
Tom is always represented on the page as 0x546F6D, the collation simply sets the rules for comparison and sorting within the query processor.
To perform a valid comparison SQL Server needs to apply at least shared lock to the page, to make sure no other records occupying other parts of the page are changed by some other process.
Latches (not locks) are used to protect physical page integrity. Locks are a mechanism to protect logical data integrity in accordance with the current isolation level. See links below for more information on latches.
http://msdn.microsoft.com/en-us/library/ms175066.aspx (see BUFFER latch)
http://msdn.microsoft.com/en-us/library/ms179984 (see PAGELATCH and PAGEIOLATCH)
http://www.youtube.com/watch?v=9n6FzIf5Hy4&feature=player_embedded#! Inside SQL Server Latches - Bob Ward presentation (90 minutes) - level 500
August 28, 2012 at 12:30 am
MissTippsInOz (8/27/2012)I think an update where no update occurs can perhaps being thought of as a logical update. That is, as has already been said, locks are applied in preparation for the update but there is no physical, bit level change.
It's not the case.
For the operations you described SQL Server would use Update locks.
See the quote above.
And it uses Exclusive locks all the time.
_____________
Code for TallyGenerator
August 28, 2012 at 12:34 am
What is not the case?
August 28, 2012 at 2:44 am
MissTippsInOz (8/27/2012)
I think an update where no update occurs can perhaps being thought of as a logical update. That is, as has already been said, locks are applied in preparation for the update but there is no physical, bit level change. As, Gail mentioned, it seems so many years ago now, you see the transaction start and commit in the log, but there is no associated MODIFY_ROW record and the page is not marked as dirty. Every part of the UPDATE process still occurs except the physical update - which makes sense..to me...as I said...before 😎
Absolutely. The reason locks are taken is that the optimisation we're 'discussing' happens in the storage engine (at least that would be my assumption given the evidence). In short and at a very high level as far as I can tell, and based on what I know of the engine, what happens is:
- Optimiser produces a plan for the update. This plan has to be reusable, hence doesn't can't optimise away any updates that don't change data
- Query processor executes the plan, requests locks, requests rows and starts to execute the update (query processor doesn't work with pages, just rows)
- Storage engine logs the begin transaction (it's the component that works with transactions and transaction log records)
- Query processor hands updated rows to the storage engine telling it to make the appropriate changes to the page and log the changes.
- Storage engine latches the page (exclusively) to write the altered value and set the is_dirty bit if necessary. It's at this point that the actual change to the page can be optimised away if they leave the page exactly as it was before the update.
Since the non-updating optimisation is at the storage engine level, not the query processor, the locks are exclusive, the update shows in the execution plan, the update events fire (they're also going to be QP level) and the storage engine just has the ability to decide whether or not to ignore the QP's request to modify the page.
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
August 28, 2012 at 2:52 pm
Now, all together:
Please explain why:
- X locks applied to RID
- IX locks applied to pages.
What makes then different?
_____________
Code for TallyGenerator
August 28, 2012 at 2:56 pm
There's a good coverage of locking modes in chapter 6, including why SQL needs IX locks at higher levels.
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
August 28, 2012 at 3:04 pm
GilaMonster (8/28/2012)
(query processor doesn't work with pages, just rows)
Sure?
Ever heard of locks escalation?
_____________
Code for TallyGenerator
August 28, 2012 at 3:05 pm
GilaMonster (8/28/2012)
http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/There's a good coverage of locking modes in chapter 6, including why SQL needs IX locks at higher levels.
So, what would be your explanation?
_____________
Code for TallyGenerator
August 28, 2012 at 3:37 pm
Sergiy (8/28/2012)
GilaMonster (8/28/2012)
(query processor doesn't work with pages, just rows)Sure?
Ever heard of locks escalation?
*scratches his head* Out of curiousity, you realize these are other people you're talking to, not your drinking buddies who don't take offense to your constantly aggressive attitude? They have patiently answered all of your questions that you've made, but you're still acting like an 8 year old who didn't get their ice cream earlier. Chill. I'm not saying don't inquire, clarify, or challenge. Just think about how you're writing.
The query processor, aka execution plan, does not work with pages or tables. The engine it runs in, which keeps the data consistent as the query processor asks for rows, DOES work with row level, page level and table level locking. The query processor itself works only at the row level, but that's a seriously deep dive to get at. Check out Grant Fritchey's book on execution plans and then look up some of Paul White's (SQLKiwi) dives into execution plan mechanics and that'll help that concept make more sense. Basically each operator in the plan calls for rows and only rows.
There's a few layers to the engine.
EDIT:
Oh, yes, and...
Sergiy (8/28/2012)
GilaMonster (8/28/2012)
http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/There's a good coverage of locking modes in chapter 6, including why SQL needs IX locks at higher levels.
So, what would be your explanation?
How about you read the articles and/or books provided instead of trying to play a game of stump the chump on the internet and continuing to lose badly? Your basics are poor (double memory pages, as an example) and are leading you to poor conclusions about the rest of the engine's mechanics.
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
August 28, 2012 at 4:27 pm
Sergiy (8/28/2012)
GilaMonster (8/28/2012)
http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/There's a good coverage of locking modes in chapter 6, including why SQL needs IX locks at higher levels.
So, what would be your explanation?
Well I wrote that portion of that chapter, so I guess what's in there would be my explanation.
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
August 28, 2012 at 4:30 pm
Sergiy (8/28/2012)
GilaMonster (8/28/2012)
(query processor doesn't work with pages, just rows)Sure?
Yes. Completely sure.
Ever heard of locks escalation?
Sure have. What's that got to do with the price of vegetables?
p.s. You do know the lock manager is part of the storage engine not the query processor, don't you? SQL Server 2008 Internals, Chapter 1, Page 9.
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 15 posts - 61 through 75 (of 159 total)
You must be logged in to reply to this topic. Login to reply