I found in some legacy code (originally SQLServer 2008) INSERT hints that I'm not sure to understand.

I have a table (storing many rows) and some procedures to fill this table. We insert rows one by one. One procedure call = one line inserted. Fine.

Sometimes (not always) SQL code looks like:

insert into my_table with(tablock, xlock)

Why tablock / xlock have been put here ?

What could be the benefit of locking a table when we insert only one line ?

Note this insert is embedded within a transaction with isolation serializable.

(I can imagine the benefit if we want to insert a bunch of records, ie. better performance).

  • 3
    Another reason to do this is to resolve a deadlock. A simple remediation to resolve a deadlock lock bigger and earlier. You only get a deadlock when two sessions first acquire compatible locks, then later attempt to acquire incompatible locks. If you force one to acquire an incompatible locks initially, no deadlock. – David Browne - Microsoft Jul 13 '18 at 15:55
  • @DavidBrowne-Microsoft The deadlock story is interesting and I'll investigate this point carrefully. Thx for this idea! – irimias Jul 16 '18 at 7:48

Exclusive Access or Increased Performance

From what I've seen, this "could be" someone's attempt to make sure that absolutely no one else enters identical rows, that you're inserting, into the table.

Or if it's mass inserts, it can save time and have minimal logging.


| improve this answer | |
  • as said in the question, this is not about mass insertion. We insert just one line. And regarding inserting duplicates, I don't see how tablock could prevent that since a transaction isolation level is serializable (and actually, duplicates is not an issue in this use case). – irimias Jul 13 '18 at 14:47
  • Looks like the original question has changed to reflect your comments as well. – Sting Jul 13 '18 at 14:50
  • indeed, I added the serializable point. – irimias Jul 13 '18 at 14:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.