SQL Server - 如何锁定表直到存储过程完成
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3662766/
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
SQL Server - How to lock a table until a stored procedure finishes
提问by Greg
I want to do this:
我想做这个：
create procedure A as
lock table a
-- do some stuff unrelated to a to prepare to update a
-- update a
unlock table a
return table b
Is something like that possible?
这样的事情可能吗？
Ultimately I want my SQL server reporting services report to call procedure A, and then only show table a after the procedure has finished. (I'm not able to change procedure A to return table a).
最终，我希望我的 SQL Server Reporting Services 报告调用过程 A，然后仅在过程完成后显示表 a。（我无法更改过程 A 以返回表 a）。
回答by Graham
Needed this answer myself and from the link provided by David Moye, decided on this and thought it might be of use to others with the same question:
我自己需要这个答案，并从David Moye 提供的链接中决定，并认为它可能对其他有相同问题的人有用：
CREATE PROCEDURE ...
AS
BEGIN
BEGIN TRANSACTION
-- lock table "a" till end of transaction
SELECT ...
FROM a
WITH (TABLOCK, HOLDLOCK)
WHERE ...
-- do some other stuff (including inserting/updating table "a")
-- release lock
COMMIT TRANSACTION
END
回答by Xin
BEGIN TRANSACTION
select top 1 *
from table1
with (tablock, holdlock)
-- You do lots of things here
COMMIT
This will hold the 'table lock' until the end of your current "transaction".
这将保持“表锁”，直到您当前的“交易”结束。
回答by David Moye
Use the TABLOCKX lock hint for your transaction. See this articlefor more information on locking.
