时间戳

首页 > 解决方案 > 从存储过程插入时避免锁定表

sql - 从存储过程插入时避免锁定表

问题描述

我想通过使用 insert into 从存储过程返回输出。出于性能原因，目标表是内存优化表类型。我现在发现，当存储过程运行时，存储过程中所有受影响的行都保持锁定，直到存储过程完成。

例子：

insert into @ModifiedSecurities (SecurityID, AttributeTypeID)
    exec Securities.spSecuritiesImportBody
                @ProcessingID = @ProcessingID

在执行期间Securities.spSecuritiesImportBody（最多需要 10 分钟），所有受spSecuritiesImportBody影响的表行都被锁定，直到存储过程完成（甚至表与存储过程的输出无关）。

虽然在单个插入语句中这种行为可能有意义，但我看不到它有任何用途，因此想要摆脱这些锁。

有没有办法在不创建这些锁的情况下执行存储过程？

这是我制作的代码示例：

  1. 执行准备
  2. 运行代码
  3. 在代码运行时尝试从 dbo.ProcessingsTesting 中进行选择。这是不可能的，因为桌子被锁定了。在 dbo.UpdProcessing 期间正在创建锁。但是，由于某种原因，锁没有被释放。

从 dbo.ProcessingsTesting 中选择 *

-- 准备开始

drop procedure dbo.UpdProcessing 
drop table dbo.ProcessingsTesting
drop procedure dbo.spSecuritiesImportBody  


go


create table dbo.ProcessingsTesting
(
    ProcessingID int,
    EndDate datetime
)

insert into dbo.ProcessingsTesting
(
    ProcessingID
)
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5


-- stored procedure
go
create procedure dbo.spSecuritiesImportBody  
(
    @ProcessingID int
)
as
begin


    exec dbo.UpdProcessing  
        @ProcessingID = @ProcessingID


    WAITFOR DELAY '00:03:00' 


    -- return data

    select 1, 2


end


-- stored procedure

go
create procedure dbo.UpdProcessing  
(
    @ProcessingID int
)
as
begin


    update dbo.ProcessingsTesting
    set EndDate = null
    where ProcessingID = @ProcessingID


end

——准备结束 

-- run the code 

declare @ModifiedSecurities table
(
    [SecurityID] [int] NOT NULL,
    [AttributeTypeID] [smallint] NOT NULL
)


insert into @ModifiedSecurities (SecurityID, AttributeTypeID)
    exec  dbo.spSecuritiesImportBody 
        @ProcessingID = 1

标签： sqlsql-servertsqlstored-proceduressql-server-2016

解决方案

除非您开始并提交显式事务，否则锁定将保留在修改的行上，直到最外面的INSERT...EXEC语句完成。您可以向dbo.UpdProcessingproc 添加显式事务（或将EXEC dbo.UpdProcessingwithBEGIN TRAN和括起来COMMIT）以在完成之前释放更新行上的锁INSERT...EXEC：

ALTER PROCEDURE dbo.UpdProcessing  
(
    @ProcessingID int
)
AS

BEGIN TRAN;

UPDATE dbo.ProcessingsTesting
SET EndDate = null
WHERE ProcessingID = @ProcessingID

COMMIT;
GO

SELECT尽管这将提供所需的结果，但对我来说，在同一个存储过程中更新与结果无关的数据并没有多大意义。似乎应该独立调用 proc，因为它们执行不同的功能。

推荐阅读