Why "Begin Transaction" before "Insert Query" locks the entire table?

The question above contains outright, no two ways about it, plain old factually incorrect answers. I've commented on one and flagged the post that is misinformed but the moderator response has come back as:

"If you will comment on the answer to the effect that this is factually wrong and state why, I'll mod the post further. Flag it again after you comment."

In my opinion I've already done this, haven't I? Should I add a full answer explaining the mechanics of transactions, which would in effect be an answer to address a poor answer rather than the original question?

I'd be interested in opinions on this specific example as well as a general approach.

Edit: Following @Jack's question

The BOL link states:

An INSERT statement always acquires an exclusive (X) lock on the table it modifies, and holds that lock until the transaction completes. With an exclusive (X) lock, no other transactions can modify data; read operations can take place only with the use of the NOLOCK hint or read uncommitted isolation level. For more information, see Locking in the Database Engine.

You can view this badly worded and misinterpreted, or plain nonsense. Exclusive locks will be acquired on "some" resources but it is not always going to be the entire table. If this were true, inserts could only ever occur serially which is an absurd notion. Depending on the isolation level and the query it may be necessary to lock rows, pages or ranges of one or both but not the table by default.

Comically, the link in the quoted paragraph takes the reader to a full and accurate explanation of how various resources are locked, starting with Lock Granularity and Hierarchies.

share
    
What exactly is wrong with the docs quoted in the Q you have commented on? It isn't right in general (eg with snapshot isolation) but is it correct for the default isolation level? –  Jack Douglas Oct 3 '11 at 9:55
    
Most definitely not, not for any isolation level including serializable. –  Mark Storey-Smith Oct 3 '11 at 12:53
    
Wondering if I should expand on the edit above and add an answer to the question in question? –  Mark Storey-Smith Oct 3 '11 at 12:55
    
I don't doubt that you are right, but if you do add an answer, perhaps a simple test case would help - my own effort to do this confused me as it seemed to confirm table locking and serialization at least for some cases (to my surprise). –  Jack Douglas Oct 3 '11 at 12:58
    
Have added an answer to the original question. –  Mark Storey-Smith Oct 3 '11 at 14:54
    
Also: meta.stackexchange.com/q/8879/27535 –  gbn Oct 4 '11 at 8:15

3 Answers 3

The obvious solution is to let answers that are "plain wrong" be downvoted into oblivion - where they will serve a useful purpose advertising their wrongness :)

The problem is that there are two kinds of votes:

  1. informed votes from people who could have answered the question themselves or who have taken the time to double check the answer
  2. all other votes (eg "this looks reasonable", "wow, you've obviously done a lot of work on this answer", "you sound authoritative", "I sympathise with you getting these downvotes" etc etc

But this is where clear, accurate, concise comments can be very helpful: they encourage more of those who might cast type 1 votes to take and interest, and discourage a lot of type 2 votes from happening at all.

In the specific case of your comment:

  1. It is very helpful as it stands
  2. It could have been even more effective if it quoted or linked to some sort of evidence that backs up your assertion. In some cases, I think the most helpful thing to do is ask and answer a new question on the site and link to it, if you have the time to do that, eg "Does an INSERT statement really always acquires an exclusive (X) lock on the table it modifies", linking to the docs that make that claim.
share
5  
I'm going to agree with @Jack here (+1). Instead of deleting something because the docs are misleading, leave it downvoted. The comments can indicate it is wrong, or those can be edited into the answer. If you delete it, someone else might come along and re-answer the same way. –  Derek Downey Oct 3 '11 at 13:11
    
Great angle DTest, I'll be honest and say I hadn't considered the chance of someone else adding the same response. Between the three of you, I'm won over :) –  Mark Storey-Smith Oct 3 '11 at 13:16
4  
Yes this! Downvotes for everybody! That's why we have them. –  jcolebrand Oct 3 '11 at 15:31

I'm going to quote this Q from the "Stack Exchange Grand Central Meta" (asked by our own star GBN) How to deal with upvoted yet clearly wrong answers

As others said before me, you should use the options SO already provides to you:

  • Leave a comment
  • Downvote the wrong answer
  • Provide or upvote a correct answer
  • If it is really, really wrong (as in dangerously worng, like 'delete C:\NTLDR in order to improve computer performance'), flag it for moderator attention.

I am opposed to any vote for deletion feature. The voting system works well enough, in my opinion. Given enough time, the wrong answers tend to sink to the bottom. No need to add more features that are not really needed.

For additional reading:

as you can see, this has been discussed a lot on Meta Stack Overflow and there's not much more to do than to leave a comment and downvote.

For what it's worth, all I was gonna do on the reflag was delete if your comment showed authority that it needed to be deleted or downvote otherwise.

share
    
My point and question remains. Having flagged (as suggested) and provided a comment that explains why the answer is incorrect (as suggested), it remains in place. As both a moderator and experienced DBA, I would assume you can see why the answer is wrong. I'll adopt the approach suggested in those linked questions, essentially "ignore it, move along" :) –  Mark Storey-Smith Oct 2 '11 at 23:30
    
I would prefer it if you commented more explicitly why it's wrong (BOL are not often wrong) and I would encourage you to downvote it if you have not done so. Otherwise, I can't really do something more drastic without further explicit proof as to why the answer is harmful in its existence. Otherwise, yes, move along, and thank you for downvoting and drawing attention to this topic. –  jcolebrand Oct 2 '11 at 23:44
1  
We could fill the site with questions on incorrect BOL and MSDN content - sqlskills.com/BLOGS/PAUL/post/… –  Mark Storey-Smith Oct 2 '11 at 23:51
    
Ha! Ain't that the truth! –  jcolebrand Oct 3 '11 at 0:29
    
Following that post, I see only this line that seems relevant: Stick to those whitepapers and blogs published by (or through) the SQL Server team, the SQL CAT team, and to the information in ***Books Online*** (which is improving in leaps and bounds). –  jcolebrand Oct 3 '11 at 0:31
    
"I'm just fed up of seeing people posting garbage and of having to defend the truth in the face of misleading misinformation" and "improving in leaps and bounds" both strike me as pertinent. We should move this to chat, will catch you there in the next couple of days. –  Mark Storey-Smith Oct 3 '11 at 0:42
    
k, sounds like a plan –  jcolebrand Oct 3 '11 at 0:49
    
gee thanks. I just added that as a comment to the question. I tend to be somewhat argumentative now in shooting folk down when I see recycled myths or untruths as answers. Possibly going too far. However, I've noted SQL "usual suspects" crowd on SO who will do it too and you can see the same, er, hit squad blasting dodgy answers... –  gbn Oct 4 '11 at 8:18
    
I would not be gun-shy about calling down recycled myths and untruths. And if people here start to complain, I've got your back @gbn –  jcolebrand Oct 4 '11 at 15:03

Agree with the top two answers. Just writing separately to add that wrong answers can be useful:

  • Seeing wrong answers is a great way to learn something! This is especially true if the comments explain why the answer is wrong.
  • It often happens that the experts are mistaken. Keeping the answer visible means that experts can come back and double check, and change their vote.
  • A visibly wrong answer will prevent other people from posting the same wrong answer (which would require downvoting again.)
share

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .