重负载下SQL Server检索错误(200并发用户)

Question

目前，我在 SQL Server 检索过程中遇到了一个问题。

当许多用户(用户数 > 40)针对相同参数同时执行时，存储过程返回意外结果。对于 No of users <= 20，它显示预期结果。使用 jMeter 测量的错误率为 0.07-0.10%。

以下 SQL 语句应返回一行。但在重负载下，它有时不返回任何行。

exec RetrievingNode @Keys='one,two,three'

这是存储过程。

CREATE PROCEDURE [dbo].[RetrievingNode] @Keys nvarchar(max) 
as 
  Begin 
      SET NOCOUNT ON; 

      --*************************************** 
      --*************************************** 
      -- Turn On or Off updation of LastAccessDateTime  
      -- To Turn on set @TurnOnUpdation=1 
      -- To Turn Off set @TurnOnUpdation=0 
      declare @TurnOnUpdation as bit 

      set @TurnOnUpdation=1 

      --*************************************** 
      --*************************************** 
      declare @Variable as table( 
        Value         nvarchar(max), 
        KeyedNodes_Id int, 
        KeyNodeValue  nvarchar(max), 
        IsParent      bit, 
        IsReadOnly    bit ) 
      -- this table will hold ids against which LastAccessDateTime can be updated.       
      declare @tbl_Keys table( 
        id    int identity(1, 1), 
        Value nvarchar(max)) 

      --following will take all the keys into @tbl_Keys 
      insert into @tbl_Keys 
                  (Value) 
      select Data 
      from   Split(@Keys, ',') 

      DECLARE @count as int 
      DECLARE @counter as int 

      SET @counter=1 

      SELECT @count = COUNT(*) 
      FROM   @tbl_Keys 

      --Declare @ChildId as int 
      declare @ChildNodes table( 
        id            int, 
        Value         nvarchar(max), 
        ParentNode_id int) 
      declare @ParentId as int 

      WHILE( @counter <= @count ) 
        BEGIN 
            DECLARE @Key as nvarchar(max) 

            SELECT @Key = Value 
            FROM   @tbl_Keys 
            WHERE  id = @counter 

            if( @counter = 1 ) 
              begin 
                  if not exists(select * 
                                from   keyednodes(nolock) KN 
                                where  KN.Value = @Key 
                                       and KN.ParentNode_Id is null) 
                    BEGIN 
                        ---node does not exists 
                        Break; 
                    END 

                  declare @ValueExistsForFirstKey as bit 

                  set @ValueExistsForFirstKey=0 

                  insert into @ChildNodes 
                  select k2.Id, 
                         k2.Value, 
                         k2.ParentNode_Id 
                  from   keyednodes(nolock) k1 
                         inner join keyednodes(nolock) k2 
                           on k1.id = k2.ParentNode_id 
                  where  K1.value = @Key 
                         and k1.ParentNode_Id is null 

                  if( @count = 1 ) 
                    BEGIN 
                        IF EXISTS(select GV.Value, 
                                         GV.KeyedNodes_Id, 
                                         KN.Value 
                                  from   keyednodes(nolock) KN 
                                         inner join GlobalVariables(nolock) GV 
                                           on KN.Id = GV.KeyedNodes_Id 
                                              and KN.Value = @Key 
                                              and KN.ParentNode_Id is null) 
                          BEGIN -- If value of node exists 
                              SET @ValueExistsForFirstKey=1 

                              insert into @Variable 
                              select GV.Value, 
                                     GV.KeyedNodes_Id, 
                                     KN.Value, 
                                     1, 
                                     GV.ReadOnly 
                              from   keyednodes(nolock) KN 
                                     inner join GlobalVariables(nolock) GV 
                                       on KN.Id = GV.KeyedNodes_Id 
                                          and KN.Value = @Key 
                                          and KN.ParentNode_Id is null 
                          END 
                        ELSE 
                          IF NOT EXISTS(select * 
                                        from   @ChildNodes) 
                            BEGIN 
                                insert into @Variable 
                                select -1, 
                                       -1, 
                                       -1, 
                                       0, 
                                       0 
                                --Node exists but No value or children exists 

                                GOTO ExitFromProcedure 
                            END 

                        IF( @counter = @count ) 
                          BEGIN 
                              if exists(select Id, 
                                               Value, 
                                               ParentNode_Id 
                                        from   keyednodes(nolock) KN 
                                        where  KN.Value = @Key 
                                               and KN.ParentNode_Id is null) 
                                BEGIN 
                                    if( @ValueExistsForFirstKey = 0 ) 
                                      and ( not exists(select * 
                                                       from   @ChildNodes) ) 
                                      BEGIN 
                                          insert into @Variable 
                                          select -1, 
                                                 -1, 
                                                 -1, 
                                                 0, 
                                                 0 

                                          GOTO ExitFromProcedure 
                                      END 
                                END 
                          END 
                    END 
              end 
            else 
              begin 
                  declare @KeyIsChild as int 

                  set @KeyIsChild=0 

                  if exists(select * 
                            from   @ChildNodes 
                            where  Value = @Key) 
                    begin 
                        set @KeyIsChild=1 
                    End 
                  ELSE 
                    BEGIN 
                        --Key path does not exist 
                        --print 'key path does not exist' 
                        GOTO ExitFromProcedure 
                    ENd 

                  if not exists(select * 
                                from   @ChildNodes 
                                where  Value = @Key) 
                    BEGIN 
                        set @ParentId=0 
                    END 
                  ELSE 
                    BEGIN 
                        select @ParentId = Id 
                        from   @ChildNodes 
                        where  Value = @Key 
                    ENd 

                  delete @ChildNodes 

                  if( @KeyIsChild = 1 ) 
                    begin 
                        insert into @ChildNodes 
                        select k2.Id, 
                               k2.Value, 
                               k2.ParentNode_Id 
                        from   keyednodes(nolock) k1 
                               inner join keyednodes(nolock) k2 
                                 on k1.id = k2.ParentNode_id 
                        where  k2.ParentNode_Id = @ParentId 
                    end 
              end 

            SET @counter=@counter + 1 
        END 

      if exists(select * 
                from   @ChildNodes) 
        begin 
            IF EXISTS (select GV.Value, 
                              CN.id, 
                              CN.Value 
                       from   @ChildNodes CN 
                              left outer join GlobalVariables(nolock) GV 
                                on CN.Id = GV.KeyedNodes_Id --children 
                       union all 
                       select GV.Value, 
                              GV.KeyedNodes_Id, 
                              KN.Value 
                       from   keyednodes(nolock) KN 
                              inner join GlobalVariables(nolock) GV 
                                on KN.Id = GV.KeyedNodes_Id 
                                   and KN.Id = @ParentId--children 
                      ) 
              BEGIN 
                  insert into @Variable 
                  select GV.Value, 
                         CN.id, 
                         CN.Value, 
                         0, 
                         GV.ReadOnly 
                  from   @ChildNodes CN 
                         left outer join GlobalVariables(nolock) GV 
                           on CN.Id = GV.KeyedNodes_Id --children 
                  union all 
                  select GV.Value, 
                         GV.KeyedNodes_Id, 
                         KN.Value, 
                         1, 
                         GV.ReadOnly 
                  from   keyednodes(nolock) KN 
                         inner join GlobalVariables(nolock) GV 
                           on KN.Id = GV.KeyedNodes_Id 
                              and KN.Id = @ParentId--children 
              END 
            ELSE 
              BEGIN 
                  if( @count <> 1 ) 
                    insert into @Variable 
                    select -1, 
                           -1, 
                           -1, 
                           0, 
                           0 

                  if( @count = 1 ) 
                    and not exists(select * 
                                   from   @Variable) 
                    insert into @Variable 
                    select -1, 
                           -1, 
                           -1, 
                           0, 
                           0 
              END 
        end 
      else 
        Begin 
            if ( @KeyIsChild = 1 ) 
              BEGIN 
                  IF EXISTS (select GV.Value, 
                                    GV.KeyedNodes_Id, 
                                    KN.Value 
                             from   keyednodes(nolock) KN 
                                    inner join GlobalVariables(nolock) GV 
                                      on KN.Id = GV.KeyedNodes_Id 
                                         and KN.Id = @ParentId) 
                    BEGIN 
                        ---IF value of the node exists 
                        insert into @Variable 
                        select GV.Value, 
                               GV.KeyedNodes_Id, 
                               KN.Value, 
                               1, 
                               GV.ReadOnly 
                        from   keyednodes(nolock) KN 
                               inner join GlobalVariables(nolock) GV 
                                 on KN.Id = GV.KeyedNodes_Id 
                                    and KN.Id = @ParentId --node 
                    END 
                  ELSE 
                    BEGIN 
                        insert into @Variable 
                        select -1, 
                               -1, 
                               -1, 
                               0, 
                               0 --Node exists but No value or children exists 
                    END 
              END 
        End 

      ---- 
      ExitFromProcedure: 

      select KeyedNodes_Id, 
             KeyNodeValue, 
             Value, 
             IsParent, 
             Isnull(IsReadOnly, 0)as IsReadOnly 
      from   @Variable 
      order  by IsParent desc, 
                KeyNodeValue asc 

      --update LastAccessDateTime 
      IF( @TurnOnUpdation = 1 ) 
        BEGIN 
            IF EXISTS(select * 
                      from   @Variable) 
              BEGIN 
                  UPDATE GlobalVariables 
                  SET    LastAccessDateTime = getdate() 
                  WHERE  KeyedNodes_Id IN (SELECT KeyedNodes_Id 
                                           FROM   @Variable) 
              END 
        END 
  End 

这是上述过程中引用的 Split() UDF:

CREATE FUNCTION [dbo].[Split] ( @RowData nvarchar(max), @SplitOn nvarchar(5) )
 RETURNS @RtnValue table 
 ( 
    Id int identity(1,1), 
    Data nvarchar(max) 
 ) AS 
BEGIN
 Declare @Cnt int; Set @Cnt = 1 

 While (Charindex(@SplitOn,@RowData)>0) 
 Begin 
    Insert Into @RtnValue (data) 
    Select Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1))) 
    Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData)) 
    Set @Cnt = @Cnt + 1 
 End 

 Insert Into @RtnValue (data) 
 Select Data = ltrim(rtrim(@RowData)) 

 Return 
END

Answer 1

这几乎可以肯定是事务隔离级别问题。我没有花时间真正深入研究过程的逻辑，只是快速浏览了一下，好像这​​个过程更新了被其他连接脏读的状态，导致间歇性数据问题。

可以尝试(在您的 TEST 数据库上!)删除所有 NOLOCK 提示，并在事务内执行此过程。就像我说的，我还没有真正分析过逻辑，但是试试这个:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; --Increase isolation level
BEGIN TRAN
... your proc ...
COMMIT
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  --Set back to the default on this connection

您的吞吐量可能会下降，但您的结果会变得正确。仔分割析过程以找到合适的事务隔离级别。 SET TRANSACTION ISOLATION LEVEL READ COMMITTED SNAPSHOT 可能会以更高的吞吐量返回正确的答案，但这是需要首先分析和测试的东西。