使用INSERT INTO调用存储过程返回结果时，如何避免内存优化表锁定？

首先咱们得先捋清楚问题根源：当你用INSERT INTO @ModifiedSecurities (SecurityID, AttributeTypeID) exec Securities.spSecuritiesImportBody @ProcessingID = @ProcessingID这种方式时，内存优化表的事务会和存储过程的执行完全绑定——整个存储过程运行期间，事务会一直持有对相关行的锁，直到过程彻底执行完毕，这就直接导致了锁的持续时间过长，严重影响性能。

下面是几个经过验证的可行解决方案，你可以根据自己的场景选择：

1. 拆分存储过程输出与内存表插入操作 #

不要直接用一条语句完成EXEC和INSERT，先把存储过程的结果放到磁盘临时表（或常规表变量）中转，再批量插入内存优化表。这样能把存储过程执行时的锁和内存优化表完全隔离开，而且批量插入内存表本身的性能损耗可以忽略不计。示例代码：

-- 先把存储过程结果写入磁盘临时表
CREATE TABLE #TempSecurities (SecurityID INT, AttributeTypeID INT);
INSERT INTO #TempSecurities EXEC Securities.spSecuritiesImportBody @ProcessingID = @ProcessingID;

-- 再批量插入内存优化表
INSERT INTO @ModifiedSecurities (SecurityID, AttributeTypeID)
SELECT SecurityID, AttributeTypeID FROM #TempSecurities;

DROP TABLE #TempSecurities;

2. 改造存储过程，用内存优化表类型做输出参数 #

如果有权限修改spSecuritiesImportBody，可以把它改成通过内存优化表类型的输出参数返回数据，而不是用结果集。这样存储过程内部可以逐步写入数据，事务粒度更小，锁的持有时间会大幅缩短。

首先定义内存优化表类型：

CREATE TYPE dbo.SecuritiesType AS TABLE (
    SecurityID INT NOT NULL,
    AttributeTypeID INT NOT NULL,
    PRIMARY KEY NONCLUSTERED (SecurityID, AttributeTypeID)
) WITH (MEMORY_OPTIMIZED = ON);

然后修改存储过程：

ALTER PROCEDURE Securities.spSecuritiesImportBody
    @ProcessingID INT,
    @OutputSecurities dbo.SecuritiesType READONLY OUTPUT
AS
BEGIN
    -- 把原逻辑中要输出的数据直接插入到输出参数表
    INSERT INTO @OutputSecurities (SecurityID, AttributeTypeID)
    SELECT SecurityID, AttributeTypeID FROM YourSourceTable WHERE ProcessingID = @ProcessingID;
END

调用时的代码：

DECLARE @OutputSecurities dbo.SecuritiesType;
EXEC Securities.spSecuritiesImportBody @ProcessingID = @ProcessingID, @OutputSecurities = @OutputSecurities OUTPUT;

INSERT INTO @ModifiedSecurities (SecurityID, AttributeTypeID)
SELECT SecurityID, AttributeTypeID FROM @OutputSecurities;

3. 调整事务隔离级别 #

内存优化表支持快照隔离等轻量级隔离级别，你可以尝试把事务隔离级别改成SNAPSHOT，这样读取操作不会阻塞写入，写入锁的持有时间也会更短。示例：

-- 先开启数据库快照隔离（如果还没开）
ALTER DATABASE YourDatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON;

-- 执行插入时使用快照隔离
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRANSACTION;
INSERT INTO @ModifiedSecurities (SecurityID, AttributeTypeID)
EXEC Securities.spSecuritiesImportBody @ProcessingID = @ProcessingID;
COMMIT TRANSACTION;

注意：这个方法需要评估业务逻辑是否接受快照隔离的特性——读取到的是数据的版本，而非实时最新数据。

4. 优化存储过程内部逻辑 #

检查spSecuritiesImportBody的内部代码，看看是不是有不必要的长事务或锁：

• 把大事务拆成多个小事务，减少锁的持有时间
• 优化查询条件，避免全表扫描导致的大范围行锁
• 把零散的DML操作改成批量操作，降低锁的触发频率

总结下来，最直接有效的是第一种拆分方案；如果能修改存储过程，第二种方案的锁控制和性能表现会更优。

内容的提问来源于stack exchange，提问作者Reboon