Geek Answers Handbook

Insert lock table

I have a large table that populates from a view. This is because the view takes a lot of time, and it is easier to get the data in the table. The procedure is performed so often that it updates the table.

 TRUNCATE TABLE LargeTable

 INSERT INTO LargeTable
 SELECT * 
 FROM viewLargeView
 WITH (HOLDLOCK)

I would like to lock this table when pasting, so if someone tries to select a record, they will not get any after truncation. The lock that I use locks the view, not the table.

Is there a better way to approach this problem?

+5
source share
2 answers
BEGIN TRANSACTION t_Transaction

BEGIN TRY

TRUNCATE TABLE LargeTable

 INSERT INTO LargeTable
 SELECT * 
 FROM viewLargeView
  WITH (HOLDLOCK)


 COMMIT t_Transaction

END TRY 

BEGIN CATCH
  ROLLBACK t_Transaction
END CATCH
+4
source

It is true that your correct hint of blocking affects the presentation of the source.

Make it so that no one can read from the table during insertion:

insert into LargeTable with (tablockx)
...

, . , , with (nolock) set transaction isolation level read uncommitted. , .

+4

More articles:


All Articles