Lock table while inserting

Question

I have a large table that get populated from a view. This is done because the view takes a long time to run and it is easier to have the data readily available in a table. A procedure is run every so often that updates the table.

 TRUNCATE TABLE LargeTable

 INSERT INTO LargeTable
 SELECT * 
 FROM viewLargeView
 WITH (HOLDLOCK)

I would like to lock this table when inserting so if someone tries to select a record they will not receive none after the truncate. The lock I am using seems to lock the view and not the table.

Is there a better way to approach this problem?

Solution 1

BEGIN TRY
 BEGIN TRANSACTION t_Transaction

 TRUNCATE TABLE LargeTable

 INSERT INTO LargeTable
 SELECT * 
 FROM viewLargeView
  WITH (HOLDLOCK)

 COMMIT TRANSACTION t_Transaction
END TRY 
BEGIN CATCH
  ROLLBACK TRANSACTION t_Transaction
END CATCH

Solution 2

It's true that your correct locking hint affects the source view.

To make it so that nobody can read from the table while you're inserting:

insert into LargeTable with (tablockx)
...

You don't have to do anything to make the table look empty until after the insert completes. An insert always runs in a transaction, and no other process can read uncommitted rows, unless they explicitly specify with (nolock) or set transaction isolation level read uncommitted. There is no way to protect from that as far as I know.

Comments

[Andomar]

Don't think this would do anything at all. The truncate is not affected by a transaction, and an insert all by itself is also a transaction, even if you don't specify one.

[Tim Friesen]

Actually TRUNCATEs are rolled back with some caveats. See this

[Emo]

According to this docs.microsoft.com/en-us/sql/t-sql/statements/… "TRUNCATE TABLE cannot be ran inside of a transaction."

[Amro]

@Emo, that applies only for: "In Azure SQL Data Warehouse and Parallel Data Warehouse, TRUNCATE TABLE cannot be ran inside of a transaction."

[Emo]

Ah yes, I see that now