SQL 插入时锁定表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9842253/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Lock table while inserting
提问by JBone
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?
有没有更好的方法来解决这个问题？
采纳答案by Darren
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
回答by Andomar
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.
在插入完成之前，您无需执行任何操作即可使表看起来为空。插入始终在事务中运行，并且没有其他进程可以读取未提交的行，除非它们明确指定with (nolock)或set transaction isolation level read uncommitted。据我所知，没有办法避免这种情况。
