In-memory retry in store procedure for updating same row

Question

https://learn.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/transactions-with-memory-optimized-tables?view=sql-server-ver15#conflict-detection-and-retry-logic

So dealing with in-memory table should use Native-Compile store proc? And the waitfor delay is not able to use in native store proc . what's the work around ?

CREATE PROCEDURE usp_update_salesorder_dates
AS
BEGIN
DECLARE @retry INT = 10;

WHILE (@retry > 0)  
BEGIN  
    BEGIN TRY  
        BEGIN TRANSACTION;  

        UPDATE dbo.SalesOrder_mo WITH (SNAPSHOT)  
            set OrderDate = GetUtcDate()  
            where CustomerId = 42;  

        UPDATE dbo.SalesOrder_mo WITH (SNAPSHOT)  
            set OrderDate = GetUtcDate()  
            where CustomerId = 43;  

        COMMIT TRANSACTION;  

        SET @retry = 0;  -- //Stops the loop.  
    END TRY  

    BEGIN CATCH  
        SET @retry -= 1;  

        IF (@retry > 0 AND  
            ERROR_NUMBER() in (41302, 41305, 41325, 41301, 41823, 41840, 41839, 1205)  
            )  
        BEGIN  
            IF XACT_STATE() = -1  
                ROLLBACK TRANSACTION;  

            WAITFOR DELAY '00:00:00.001';  
        END  
        ELSE  
        BEGIN  
            PRINT 'Suffered an error for which Retry is inappropriate.';  
            THROW;  
        END  
    END CATCH  

END -- //While loop  

END;
GO

-- EXECUTE usp_update_salesorder_dates;

Answer 1

You would do this differently, if you were to move the business logic to a natively compiled procedure. You would not trap the concurrency error inside the Hekaton procedure, because the error can only occur on COMMIT (if I recall correctly). And in a natively compiled procedure, you cannot commit explicitly, as the transaction is the entire procedure.

So in a Hektaton procedure you would only have the UPDATE statements, and the loop with the trapping of the concurrency exceptions would be a in a regular T-SQL procedure that calls the natively compiled procedure in the loop, which means that you can keep the WAITFOR. from the page you linked to.

Answer 2

Why would you want to use a waitfor when you use a feature that is designed to give you as good performance as possible? Just remove that waitfor from your procedure.