UPDATE with Race Condition

Question

I need keep track of the status of WhatsApp API status notifications received via a webhook.

I built a stored procedure like this:

UPDATE wam 
SET
    [timestamp]=j.[timestamp],
    [status]=j.[status]

FROM WAMessages wam
JOIN  OPENJSON(@json)
WITH  (
         message_id     nvarchar(128)   '$.entry[0].changes[0].value.statuses[0].id' 
        ,[status]       nvarchar(128)   '$.entry[0].changes[0].value.statuses[0].status'
        ,[timestamp]    bigint          '$.entry[0].changes[0].value.statuses[0].timestamp'             
    ) j on wam.message_id=j.message_id


IF @@ROWCOUNT=0
    BEGIN
    
        INSERT INTO WAMessages 
        (
             message_id
            ,[status]
            ,[timestamp]
            --,other fields
        }
        
        SELECT 
            message_id
            ,[status]
            ,[timestamp]
        FROM OPENJSON(@json)
        WITH  (
                 message_id     nvarchar(128)   '$.entry[0].changes[0].value.statuses[0].id' 
                ,[status]       nvarchar(128)   '$.entry[0].changes[0].value.statuses[0].status'
                ,[timestamp]    bigint          '$.entry[0].changes[0].value.statuses[0].timestamp'
                --,other fields                 
            ) j 
    END

That works, but since 'sent' status message and 'delivered' status message are received simultaneously I get randomly only one of the messages to be processed:

Sometime I get only sent, sometimes only delivered:

There is a race condition issue I've not been able to solve:

I tried adding a WITH UPDLOCK or a LEVEL SERIALIZABLE and also using MERGE Statement, but nothing works:

clearly if I remove the UPDATE statement (and IF @@ROWCOUNT=0) I get 2 separated records, one for 'sent' and the other for 'delivered' statuses, but the target is to have only one record for each message_id with sent/delivered/read details.

Can suggest the right path to solve this issue?

Yip - use a window function e.g. row_number to prioritise which row you want to receive when there are 2 — Dale K, May 25 '23 at 00:29
It's not a race condition, the server is just arbitrarily picking one of the rows. You need to tell it which one to pick using a row-numbering solution, see https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group?r=Saves_AllUserSaves — Charlieface, May 25 '23 at 00:49
Thanks for answering, probably I do not understand but I still think it's a race issue: I receive 'sent' message: sproc try to update but there is no record with same `message_id` therefore execute the insert MEANWHILE.. arrives the 'delivered' message: try to update but since previous operation is not committed does not update again and execute the insert BUT since there are some Locks somewhere... only one of the operation is executed and this can be randomly: reasonable? — Joe, May 25 '23 at 03:13
Really, is your sp running that frequently, and if so does it need to run that often? Secondly, if you are attempting a second insert while the first is still uncommitted, it would have to wait, because the locks of the first insert will still be in place. So what you are describing seems quite unlikely. — Dale K, May 25 '23 at 03:24
@DaleK thanks for answering.. sorry probably I've not been clear enough it's not me that want to do in this way: this is a webhook that receives WhatsApp API status messages notifications: here for example the sysdatetime() of the 2 messages: 2023-05-25 05:22:20.063 and 2023-05-25 05:22:20.217 it is some 150 ms — Joe, May 25 '23 at 04:22
but even less: 2023-05-25 02:30:08.833 and 2023-05-25 02:30:08.893: 60 ms.. — Joe, May 25 '23 at 04:24
OK gotcha, and I assume its multi-threaded i.e. you can be trying to insert/update both messages at the same time? Probably the only way to solve that is to ensure your insert/update code runs single threaded... — Dale K, May 25 '23 at 04:26
well, stored procedure is triggered by webhook: when message arrives has to be processed: I thought that using SERIALIZABLE could fix the issue but it didn't: perhaps I implemented wrongly (not so experienced with such options) — Joe, May 25 '23 at 04:37
put both rows in a staging table and then process the messages from that table from one thread with some delay to make sure both messages are properly received — siggemannen, May 25 '23 at 09:35

score 1 · Answer 1 · answered May 25 '23 at 04:51

It is generally not a great design to have UPDATE else INSERT logic on the same table with OLTP loads.

To fix it:

Assuming you always get two messages. I suggest to check the message status and if it is "sent" and no DB record exists then wait for 1 second and retry your update.
Insert both records separately and then have another process to remove duplicates.

UPDATE with Race Condition

1 Answers1