0

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)
values(......)

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).

Improve this question
2
  • 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, 2018 at 15:55
  • @DavidBrowne-Microsoft The deadlock story is interesting and I'll investigate this point carrefully. Thx for this idea!
    – irimias
    Jul 16, 2018 at 7:48

1 Answer 1

Reset to default
1

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.

https://drillchina.wordpress.com/2012/12/18/use-tablock-to-boost-your-insert-into-select-performance/

Improve this answer
3
  • 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, 2018 at 14:47
  • Looks like the original question has changed to reflect your comments as well.
    – Sting
    Jul 13, 2018 at 14:50
  • indeed, I added the serializable point.
    – irimias
    Jul 13, 2018 at 14:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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