October 14, 2013 at 6:49 am
I didn't know how to answer as I took the statement that no records are inserted too literally, I guess. Records were inserted before the rollback.
:crazy:
October 14, 2013 at 8:06 am
raulggonzalez (10/14/2013)
Thanks for the question!I personally find this link more illustrative than the provided one.
http://technet.microsoft.com/en-us/library/ms189336(v=sql.105).aspx
Thanks. That link was helpful.
October 14, 2013 at 9:00 am
Easy one! Thanks for submitting.
October 14, 2013 at 9:03 am
Good question, got it it right due to previously having some troubles with this. I wanted to respond to again bump the quoted post below as i think it's a great resource on the how/why of this question.
raulggonzalez (10/14/2013)
Thanks for the question!I personally find this link more illustrative than the provided one.
http://technet.microsoft.com/en-us/library/ms189336(v=sql.105).aspx
October 14, 2013 at 11:23 am
Good question, and a good additional link posted by raulggonzalez - I was already getting ready to jump in with additional explanation, but I don't see the need after reading that link.
David Burrows (10/14/2013)
Due to the amount of criticism of previous QOD of the phrasing of the Q'a and A's I am surprised that no one posted anything about the wording of the answer!It is technically incorrect as records are inserted into the table, they do not remain after the rollback.
I am not surprised to see this comment, but I don't agree.
Remember what people always say when warning against using NOLOCK or other methods of dirty read? You run the risk of reading data from an uncommitted transaction that later rolls back - so the data was actually never there and you still read it.
I'm going to apply the same logic here. The transaction rolls back; this means that the changes that were in the transaction officially never happened.
Yes, technically the rows were inserted and then rolled back. But from a logical point of view, the inserts never happened. Which makes the answer correct.
October 15, 2013 at 1:32 am
Nice question...
October 15, 2013 at 2:05 am
Hugo Kornelis (10/14/2013)
Yes, technically the rows were inserted and then rolled back. But from a logical point of view, the inserts never happened. Which makes the answer correct.
The answer was 'no records inserted into table' which is factually incorrect, irrespective of any 'logical point of view' or final result of the batches.
As I has already stated I was not criticising the question, I took the answer in the spirit the way the question was phrased.
I suppose we will have to agree to disagree 😀
Far away is close at hand in the images of elsewhere.
Anon.
October 15, 2013 at 2:21 am
David Burrows (10/15/2013)
Hugo Kornelis (10/14/2013)
Yes, technically the rows were inserted and then rolled back. But from a logical point of view, the inserts never happened. Which makes the answer correct.The answer was 'no records inserted into table' which is factually incorrect, irrespective of any 'logical point of view' or final result of the batches.
As I has already stated I was not criticising the question, I took the answer in the spirit the way the question was phrased.
I suppose we will have to agree to disagree 😀
This is the first occasion I've been confident enough to disagree with Hugo. The fact that a read WITH NOLOCK could have picked up the rows means that they did exist and therefore were inserted. I can make no technical or logic distinction here. It's like if my building society makes a mistake and credits me with £1,000,000 and then realises its mistake and debits the money from my account. At the end of the day, I'm not £1m better off but the credit and debit did happen.
However, as David has intimated, the answer is correct in the spirit of the question.
October 15, 2013 at 5:27 am
Nice Question
October 15, 2013 at 1:22 pm
marlon.seton (10/15/2013)
David Burrows (10/15/2013)
Hugo Kornelis (10/14/2013)
Yes, technically the rows were inserted and then rolled back. But from a logical point of view, the inserts never happened. Which makes the answer correct.The answer was 'no records inserted into table' which is factually incorrect, irrespective of any 'logical point of view' or final result of the batches.
As I has already stated I was not criticising the question, I took the answer in the spirit the way the question was phrased.
I suppose we will have to agree to disagree 😀
This is the first occasion I've been confident enough to disagree with Hugo. The fact that a read WITH NOLOCK could have picked up the rows means that they did exist and therefore were inserted. I can make no technical or logic distinction here. It's like if my building society makes a mistake and credits me with £1,000,000 and then realises its mistake and debits the money from my account. At the end of the day, I'm not £1m better off but the credit and debit did happen.
However, as David has intimated, the answer is correct in the spirit of the question.
re: The £1,000,000 pounds - true. In that case, the transaction has been committed to the database; the money was in your account. The funds were wired back later, when the mistake was discovered.
However, if there is a check (or other constraint) that limits transfers to your account to a maximum of £250,000, the faulty transfer would have been rejected because the constraint is violated. This data violates a business rule, so it can never exist in the database. The details of the constraints checking are not defined in the relational model; this is an implementation choice. SQL Server happens to make the choice to first insert the rows (with locks to prevent others from seeing data that might not be able to actually exist) and then undo the change if a constraint is violated. It's just as possible to do all the checks first, and only actually insert the rows after the checks are passed. In one case, the row is technically inserted in the database, then removed again; in the other case, the row is technically never inserted at all. But logically, there is no difference - the row never existed.
On the possibility of a NOLOCK read picking it up - I believe that was exactly my point. People warn against using NOLOCK because it can pick up changes that never really existed in the database. (In fact, a NOLOCK read can even pick up inserts and deletes the not only didn't happen logically, but also inserts and deletes that never happened technically either - I have seen code where a SELECT COUNT(*) FROM SomeTable WITH NOLOCK produced incorrect results as a result of another connection that was not doing inserts or deletes, but only updates...)
Just for the record:
1. I am not disagreeing with you. Just stating that you can look at this from two angles. From the technical, implementation-dependent angle, as you do, but also from the high-level, relational angle - Codd's eighth rule.
2. Please don't hesitate to disagree with me more often. I'm wrong quite often, as my children will be happy to tell you. If you have a different POV from me, don't be afraid to point it out. I might or might not respond - but in any case, at least one of us will learn, and probably we both. (And all others who read the discussion).
October 15, 2013 at 1:50 pm
As others have said, nice question to demonstrate a fundamental point, and the possible answers don't give away the correct one which is good (tougher to just guess).
However, it could use a more detailed explanation in the answer about the nesting of transactions without visiting the link.
October 15, 2013 at 3:31 pm
Nice one - thanks, Sasidhar!
October 16, 2013 at 2:28 am
But I made the wrong selection . beacuse i was thinking (and this was my understanding ..earlier) that once the commit has been fired, the following rollback doesnt work for commit transaction.
like
begin tran
update
commit
rollback
Above rollback wont "UNDO" the update (this was my understanding earlier)
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 16, 2013 at 2:31 am
Hugo Kornelis (10/15/2013)
2. Please don't hesitate to disagree with me more often. I'm wrong quite often, as my children will be happy to tell you. If you have a different POV from me, don't be afraid to point it out. I might or might not respond - but in any case, at least one of us will learn, and probably we both. (And all others who read the discussion).
Hugo, I'd never hesitate to disagree with you if I had a different point of view or thought you were wrong. However, I actually know very little about SQLServer (frighteningly little considering I write a system that uses it) so I've never found the occasion that warrants such disagreement before.
October 16, 2013 at 6:19 am
David Burrows (10/14/2013)
Nice question.Due to the amount of criticism of previous QOD of the phrasing of the Q'a and A's I am surprised that no one posted anything about the wording of the answer!
It is technically incorrect as records are inserted into the table, they do not remain after the rollback.
Since the question asks for the result of the batch (although there are several bacthes) the answer should be 'no rows returned' or '0 Row(s) affected'
Please note I am not criticising the question or answer, just making an observation.
I would disagree with the observation - being a pedantic type, I take the view that the records were inserted into a page in the server's buffer and may even have been copied to disc (twice: once in te log and once if the dirty page was forced out) but were never inserted into the database. Insertion into the database occurrs when (and if) the unnested transaction in whose domain the update , insert, or merge command took place is committed, not before. As I've often pointed out, people playing with NOLOCK (or equivalently with READ UNCOMMITTED) are accepting that they are reading data which is not yet in the database, and may indeed never get there, which is asking for trouble, while people who maintain even a read-committed level of isolation (which is still far from true transactional isolation) read only stuff that is actually in the database (which for many cases is good enough, true transactional isolation is not always needed).
edit: I see Hugo has already made the same point. I guess I should have read to the end of the discussion before chipping in. Now that I have read on, I can see that I'm quite horrified to see people claiming that Hugo is wrong, that these records really were inserted into the table, and using dirty read to support that nonsensical claim. The word "nonsensical" is justified if we are thinking in terms of a relational database; if on the other hand someone claims that the table is the set of data currently in the server's cache or in the data file regardless of what is in the logfile they are clearly talking about something which is buried in implementation detail and is no part of the definition of the database and that of course can change at the drop of a hat. Of course it would be an extremely difficult position to hold, since it would of course guarantee that if there were any dirty pages on the disc at the time of a server failure, or indeed any dirty pages in cache that were still clean on the disc, restart would be fun - after all, the claim that the table is not what is defined by the log records is a bit hard to sustain when you have the server merrily doing rollbacks and roll forwards on restart.
Tom
Viewing 15 posts - 16 through 30 (of 47 total)
You must be logged in to reply to this topic. Login to reply