Locking a table

• brian118

  Hall of Fame

  Points: 3506

  More actions

  September 1, 2010 at 6:19 am

  #226440

  Hi

  I need to lock a heap table at the start of a stored procedure and perform ~ 1000000 inserts. The table is truncated at the start of the process and no one will have access (read) this data, but just the stored procedure.

  Doing an INSERT statement using the table hint WITH (TABLOCKX) would do the job? Is there another way to lock the table at the start i.e. before the inserts start and then release the lock when finished?

  Also, will this help performance if locks are disabled?

  Regards

  Brian

  Regards
  Brian Ellul
  ----------------------------------------------------------------------------------
  Computers are incredibly fast, accurate, and stupid. Human beings are incredibly slow, inaccurate, and brilliant. Together they are powerful beyond imagination.
  - Albert Einstein -

• NickDBA

  Hall of Fame

  Points: 3792

  More actions

  September 1, 2010 at 6:58 am

  #1214913

  You can set the isolation level to be serializable at the start of your batch IE-

  [SET TRANSACTION ISOLATION LEVEL SERIALIZABLE]