英文:
UPDATE with Race Condition
问题
我需要跟踪通过webhook接收到的WhatsApp API状态通知的状态。
我构建了一个像这样的存储过程：
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
这个方法有效，但由于'sent'状态消息和'delivered'状态消息同时接收，所以随机地只处理一个消息：
有时我只收到sent，有时只收到delivered：
存在一个我无法解决的竞争条件问题：
我尝试添加WITH UPDLOCK或LEVEL SERIALIZABLE，并且使用MERGE语句，但都没有效果：
显然，如果我删除UPDATE语句（和IF @@ROWCOUNT=0），我会得到2条单独的记录，一条用于**'sent'，另一条用于'delivered'**状态，但目标是每个message_id只有一条包含发送/已送达/已读详情的记录。
你能提供正确的解决方法吗？
英文:
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?
答案1
得分: 1
通常，在具有 OLTP 负载的同一表上使用 UPDATE 和 INSERT 逻辑并不是一个很好的设计。为了解决这个问题：
- 假设您总是收到两条消息。我建议检查消息状态，如果是“sent”并且没有数据库记录，则等待1秒并重试您的更新。
- 分别插入两条记录，然后使用另一个进程删除重复项。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论