插入时锁定表 [英] Lock table while inserting

问题描述

我有一个很大的表，它是从视图中填充的.这样做是因为视图需要很长时间才能运行，并且更容易在表中轻松获得数据.经常运行一个过程以更新表.

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?

推荐答案

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

这篇关于插入时锁定表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！