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

irimias
  • 1,921
  • 2
  • 14
  • 27

1 Answers1

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/

Sting
  • 2,808
  • 13
  • 20