有时仅从一个 SP 获取请求超时

Question

我有很多存储过程。但是我有时只得到这个SP的请求超时？

ALTER PROCEDURE [dbo].[Insertorupdatedevicecatalog] 
                (@OS                NVARCHAR(50) 
                ,@UniqueID          VARCHAR(500)
                ,@Longitude         FLOAT 
                ,@Latitude          FLOAT
                ,@Culture           VARCHAR(10)
                ,@Other             NVARCHAR(200)
                ,@IPAddress         VARCHAR(50)
                ,@NativeDeviceID    VARCHAR(50))
AS 
BEGIN 
    SET NOCOUNT ON;
    DECLARE @TranCount INT;
    SET @TranCount = @@TRANCOUNT;
    DECLARE @OldUniqueID VARCHAR(500) = ''-1'';
    SELECT @OldUniqueID = [UniqueID] FROM DeviceCatalog WHERE (@NativeDeviceID != '''' AND [NativeDeviceID] = @NativeDeviceID);
    BEGIN TRY
        IF @TranCount = 0
            BEGIN TRANSACTION
        ELSE
            SAVE TRANSACTION Insertorupdatedevicecatalog;
        DECLARE @Geo GEOGRAPHY = geography::STGeomFromText(''POINT('' + CONVERT(VARCHAR(100), @Longitude) + '' '' + CONVERT(VARCHAR(100), @Latitude) + '')'', 4326);
        IF EXISTS(SELECT 1 FROM DeviceCatalog WHERE [UniqueID] = @UniqueID) 
        BEGIN 
            DECLARE @OldGeo     GEOGRAPHY
                    ,@OldCity       NVARCHAR(100)
                    ,@OldCountry    NVARCHAR(100)
                    ,@OldAddress    NVARCHAR(100);
            SELECT  @OldGeo = [LastUpdatedLocationFromJob]
                    ,@OldCity = [City]
                    ,@OldCountry = [Country]
                    ,@OldAddress = [Address]
            FROM    DeviceCatalog
            WHERE   [UniqueID] = @UniqueID;
            UPDATE  DeviceCatalog 
                SET [OS] = @OS
                    ,[Location] = @Geo
                    ,[Culture] = @Culture
                    ,[Other] = @Other
                    ,[IPAddress] = @IPAddress
            WHERE   [UniqueID] = @UniqueID;
                            IF (@OldGeo IS NULL OR @OldAddress IS NULL OR @OldCity IS NULL OR @OldCountry IS NULL OR ISNULL(@Geo.STDistance(@OldGeo) / 1000,0) > 50)
            BEGIN
                UPDATE  DeviceCatalog 
                    SET [Lastmodifieddate] = Getdate()
                WHERE   [UniqueID] = @UniqueID;
            END
        END
        ELSE 
        BEGIN
            INSERT INTO DeviceCatalog
                        ([OS]
                        ,[UniqueID]
                        ,[Location] 
                        ,[Culture] 
                        ,[Other]
                        ,[IPAddress]
                        ,[NativeDeviceID])
                VALUES  (@OS
                        ,@UniqueID
                        ,@Geo
                        ,@Culture
                        ,@Other
                        ,@IPAddress
                        ,@NativeDeviceID);
                IF(@OldUniqueID != ''-1'' AND @OldUniqueID != @UniqueID)
                BEGIN
                    EXEC DeleteOldAndroidDeviceID @OldUniqueID, @UniqueID;
                END
        END
LBEXIT:
        IF @TranCount = 0
            COMMIT;

    END TRY
    BEGIN CATCH
        DECLARE @Error INT, @Message VARCHAR(4000), @XState INT;
        SELECT  @Error = ERROR_NUMBER() ,@Message = ERROR_MESSAGE() ,@XState = XACT_STATE();
        IF @XState = -1
            ROLLBACK;
        IF @XState = 1 AND @TranCount = 0
            rollback
        IF @XState = 1 AND @TranCount > 0
            ROLLBACK TRANSACTION Insertorupdatedevicecatalog;
        RAISERROR (''Insertorupdatedevicecatalog: %d: %s'', 16, 1, @error, @message) ;
    END CATCH
END

Answer 1

超时

是由于同一事务中对同一表的两次更新而发生的。你可以用案例陈述来避免它。也可以用合并替换整个 IF ELSE。

MERGE INTO DeviceCatalog DC
USING (SELECT @UniqueID AS UniqueID) T ON (DC.UniqueID = T.UniqueID)
WHEN MATCHED  THEN
    UPDATE  SET [OS] = @OS
               ,[Location] = @Geo
               ,[Culture] = @Culture
               ,[Other] = @Other
               ,[IPAddress] = @IPAddress
               ,[Lastmodifieddate] = (CASE 
                                       WHEN (LastUpdatedLocationFromJob IS NULL OR [Address] IS NULL OR [City] IS NULL OR [Country] IS NULL OR ISNULL(@Geo.STDistance(LastUpdatedLocationFromJob) / 1000,0) > 50) 
                                        THEN Getdate()
                                       ELSE [Lastmodifieddate]
                                      END)
WHEN NOT MATCHED THEN
   INSERT INTO DeviceCatalog
                        ([OS]
                        ,[UniqueID]
                        ,[Location] 
                        ,[Culture] 
                        ,[Other]
                        ,[IPAddress]
                        ,[NativeDeviceID])
                VALUES  (@OS
                        ,@UniqueID
                        ,@Geo
                        ,@Culture
                        ,@Other
                        ,@IPAddress
                        ,@NativeDeviceID)
WHEN NOT MATCHED BY SOURCE AND @OldUniqueID != ''-1'' AND @OldUniqueID != @UniqueID  THEN
DELETE; 

尝试一下并检查这是否是您所期望的。

Answer 2

已经在这里讨论过

了

您可以使用 TSQL 中的sp_getapplock来实现它。

但是你需要一个包装器存储过程或批处理。检查以下示例，它将帮助您设计包装器 sp/batch 语句。

示例代码片段

Create table MyTable
(
            RowId int identity(1,1),
            HitStartedAt datetime,
            HitTimestamp datetime,
            UserName varchar(100)
)

Go
Create proc LegacyProc (@user varchar(100), @CalledTime datetime)
as
Begin
            Insert Into MyTable
            Values(@CalledTime, getdate(), @user);
            --To wait for 10 sec : not required for your procedures, producing the latency to check the concurrent users action
            WAITFOR DELAY '000:00:10'
End
Go
Create Proc MyProc
(
            @user varchar(100)
)
as
Begin
            Declare @PorcName as NVarchar(1000), @CalledTime datetime
            Begin Tran
            --To get the Current SP Name, it should be unique for each SP / each batch
            SET @PorcName =  object_name(@@ProcID)
            SET @CalledTime = Getdate()
            --Lock the Current Proc
            Exec sp_getapplock @Resource = @PorcName, @LockMode = 'Exclusive'
            --Execute Your Legacy Procedures
            Exec LegacyProc @user, @CalledTime
            --Release the lock
            Exec sp_releaseapplock @Resource = @PorcName
            Commit Tran
End

Answer 3

您正在对 DeviceCatalog 表执行两个单独的更新，其中 [UniqueID] = @UniqueID 在同一事务中。

我敢打赌，您的锁定/请求超时问题发生在以下情况下：

如果 （@OldGeo 为 NULL 或 @OldAddress 为 NULL 或 @OldCity 为 NULL 或 @OldCountry 为

NULL 或 ISNULL（@Geo.STDistance（@OldGeo）/1000,0）> 50） 为 TRUE。

尝试这样的东西来代替两个更新。

显然首先在开发中进行测试。

在 else 子句中，如果 when 为 false，您希望它插入一些内容。在这里，我只是在更新字段内容之前插入当前内容。

        UPDATE  DeviceCatalog 
            SET [OS] = @OS
                ,[Location] = @Geo
                ,[Culture] = @Culture
                ,[Other] = @Other
                ,[IPAddress] = @IPAddress
                ,[Lastmodifieddate] = 
                            case when (
                                            @OldGeo is NULL 
                                            OR 
                                            @OldAddress is NULL 
                                            OR 
                                            @OldCity is NULL 
                                            OR 
                                            @OldCountry is NULL 
                                            OR 
                                            ISNULL(@Geo.STDistance(@OldGeo) / 1000,0) > 50
                                    ) then  Getdate()
                               else [Lastmodifieddate]
                            end
            WHERE   [UniqueID] = @UniqueID