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.

  • 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? Commented Oct 3, 2011 at 9:55
  • Most definitely not, not for any isolation level including serializable. Commented Oct 3, 2011 at 12:53
  • Wondering if I should expand on the edit above and add an answer to the question in question? Commented Oct 3, 2011 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). Commented Oct 3, 2011 at 12:58
  • Have added an answer to the original question. Commented Oct 3, 2011 at 14:54
  • Also: meta.stackexchange.com/q/8879/27535
    – gbn
    Commented Oct 4, 2011 at 8:15

4 Answers 4


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.
  • 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. Commented Oct 3, 2011 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 :) Commented Oct 3, 2011 at 13:16
  • 4
    Yes this! Downvotes for everybody! That's why we have them.
    – jcolebrand Mod
    Commented Oct 3, 2011 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 Exchange 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.

  • 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" :) Commented Oct 2, 2011 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 Mod
    Commented Oct 2, 2011 at 23:44
  • 1
    We could fill the site with questions on incorrect BOL and MSDN content - sqlskills.com/BLOGS/PAUL/post/… Commented Oct 2, 2011 at 23:51
  • Ha! Ain't that the truth!
    – jcolebrand Mod
    Commented Oct 3, 2011 at 0:29
  • 1
    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 Mod
    Commented Oct 3, 2011 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. Commented Oct 3, 2011 at 0:42
  • k, sounds like a plan
    – jcolebrand Mod
    Commented Oct 3, 2011 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
    Commented Oct 4, 2011 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 Mod
    Commented Oct 4, 2011 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.)

I can see the points made by the other answerers here that incorrect, invalid, and misleading answers can be useful - certainly if I wanted to know the wrong way to do something.

I tend to imagine people are searching the web for an answer not to confirm they are right when in fact they are wrong, but to find the actual answer.

Imagine your surprise if you looked up a word in a dictionary, and it had all the incorrect spellings of that word, along with a list of incorrect usages of the word. It might be nice for a laugh, but how do those details help?

When people come to StackExchange, it is for:

  1. I need an answer. This is how people find StackExchange in the first place.
  2. I have lots of time on my hands and want to help people who have questions.
  3. I like unicorn points.

If you need an answer, you are certainly not interested, at that point in time, in seeing incorrect answers and conversations about why they are incorrect, etc, etc, ad-nauseum. You may be inclined to find out more about why your own assumptions were perhaps incorrect, or you may want to have a discussion with someone about why the answers you see are correct, but that is not the purpose of the main StackExchange sites - if you want to chat, use chat.

If you have lots of time on your hands and want to help people who have questions, and you think you have the correct answer, then post it. If it becomes obvious, through comments or chat or further investigation etc, that you were wrong, you should delete your answer because what purpose does it serve? To show people the wrong way to do it? I've personally been wrong, and have deleted several wrong answers (some might even say a lot!). The problem with this approach is the vast majority of people who visit StackExchange do not become "regulars". They may in fact answer a question, and never come back to it. If we down-vote the answer and leave it like that for posterity, showing others we don't agree with the answer, what purpose does that serve beyond a slightly douche-bag-esque desire to prove others wrong, and yourself right? Deleting a clearly wrong answer, after a period of time that allows corrections to be made, should increase the overall quality of the site.

If you like Unicorn Points, and that is why you visit StackExchange sites, and you answer questions wrongly without subsequently deleting them, then perhaps you are just a sadist, and need to move along.

You must log in to answer this question.

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