Getting Request Timeout sometimes from only one SP

问题:

I have a lot of stored procedures. But I am only getting Request Timeout sometimes only for this 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

回答1:

The timeout occurs due to two updates to same table inside same transaction. You could avoid it with a case statement. Also whole IF ELSE can be replaced with a merge.

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; 

Try it and check whether this is what you expected.

回答2:

Already discussed here

You can achieve it using sp_getapplock in TSQL.

But you need a wrapper storedproc or batch for this. Check the following example it will help you to desing your wrapper sp/batch statement.

Sample Code Snippet

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

回答3:

You are doing two seperate updates on the DeviceCatalog table where [UniqueID] = @UniqueID in the same transaction.

I bet your locking/request timeout issue is happening when:

IF (@OldGeo IS NULL OR @OldAddress IS NULL OR @OldCity IS NULL OR @OldCountry IS NULL OR ISNULL(@Geo.STDistance(@OldGeo) / 1000,0) > 50) is true.

Try something like this in place of the two updates.

Obviously test in dev first.

In the else clause, you want to have it insert something if the when is false. Here I am just inserting the current before update field contents.

        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