SQL Azure中查询插入分析循环时事务死锁问题求助

一、彻底关闭查询锁：利用只读数据特性调整隔离级别 #

既然底层数据已不再变更，可通过宽松隔离级别完全避免读锁：

• 会话级临时调整：执行INSERT...SELECT前切换到READ UNCOMMITTED，查询不会请求共享锁，也不会被排他锁阻塞，执行后恢复默认级别：

  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
  INSERT INTO dbo.UserSurveyReportData
  ( SurveyQuestionID, UserSurveyID, SurveyID, AnswerMeanRole, SurveyScaleID )
  SELECT SurveyQuestionID, UserSurveyID, SurveyID, AnswerMeanRole, SurveyScaleID
  FROM viewReport WITH (NOLOCK)
  WHERE UserSurveyID = @UserSurveyID;
  SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
  

• 数据库级快照隔离：若所有报表查询都基于只读数据，开启快照隔离后，查询会读取数据版本，完全无锁且不会读到脏数据：

  ALTER DATABASE YourDatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON;
  

  后续查询前执行SET TRANSACTION ISOLATION LEVEL SNAPSHOT;即可生效。

二、重构标量函数：减少逐行锁开销 #

你当前用的标量函数会逐行调用，每次调用都对viewAnswersOptimised加锁，是死锁的核心诱因之一。改成表值函数批量计算：

CREATE FUNCTION dbo.GetAnswerMeanRole(@UserSurveyID INT)
RETURNS TABLE
AS
RETURN (
    SELECT 
        SurveyQuestionID,
        ParticipantRoleID,
        CONVERT(DECIMAL(30,6), AVG(CASE WHEN AnswerNumeric > 0 THEN AnswerNumeric END)) AS AnswerMeanRole
    FROM viewAnswersOptimised
    WHERE UserSurveyID = @UserSurveyID 
      AND IsComplete = 1
    GROUP BY SurveyQuestionID, ParticipantRoleID
);

在viewReport中关联该表值函数替代原标量函数调用，一次性完成所有聚合计算，大幅减少锁的持有时间和次数。

三、优化索引：缩短锁持有时间 #

即使已有索引，也要确保聚合查询能走覆盖索引，避免全表扫描：

• 为viewAnswersOptimised创建聚合专用覆盖索引：

  CREATE NONCLUSTERED INDEX IX_viewAnswersOptimised_Aggregation
  ON viewAnswersOptimised (UserSurveyID, SurveyQuestionID, ParticipantRoleID)
  INCLUDE (AnswerNumeric, IsComplete);
  

  该索引包含过滤、分组及聚合所需列，AVG计算可直接在索引上完成，无需回表，查询速度和锁效率都会大幅提升。
• 为目标表UserSurveyReportData创建行级锁友好的索引：

  CREATE NONCLUSTERED INDEX IX_UserSurveyReportData_UserSurveyID
  ON dbo.UserSurveyReportData (UserSurveyID)
  INCLUDE (SurveyQuestionID, SurveyID, AnswerMeanRole, SurveyScaleID);
  

  确保插入时仅触发行级锁，避免页锁或表锁冲突。

四、拆分操作：用临时表隔离并发 #

将INSERT...SELECT拆分为两步，用会话私有临时表隔离查询与插入的锁冲突：

-- 第一步：查询结果存入临时表（仅当前会话可见，无并发锁问题）
SELECT SurveyQuestionID, UserSurveyID, SurveyID, AnswerMeanRole, SurveyScaleID
INTO #TempReportData
FROM viewReport WITH (NOLOCK)
WHERE UserSurveyID = @UserSurveyID;

-- 第二步：插入目标表，指定ROWLOCK缩小锁粒度
INSERT INTO dbo.UserSurveyReportData WITH (ROWLOCK)
( SurveyQuestionID, UserSurveyID, SurveyID, AnswerMeanRole, SurveyScaleID )
SELECT SurveyQuestionID, UserSurveyID, SurveyID, AnswerMeanRole, SurveyScaleID
FROM #TempReportData;

DROP TABLE #TempReportData;

五、预计算报表数据：从根源消除并发 #

既然底层数据不再变更，可提前批量预计算报表：

• 用SQL Azure弹性作业定期（如每日凌晨）生成所有UserSurveyID的报表数据，存入UserSurveyReportData。
• Web函数直接读取预生成数据，无需实时执行聚合查询，彻底解决并发死锁问题。

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