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 6:59 am
This was removed by the editor as SPAM
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.
Viewing 15 posts - 16 through 30 (of 47 total)
You must be logged in to reply to this topic. Login to reply