UPDATE with Race Condition

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?

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

To fix it:

1. 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.
2. Insert both records separately and then have another process to remove duplicates.