如何编写防死锁的upsert - sql ?

Question

对数据库执行标准CRUD操作的web api:

对于DML CUD操作，我可以在端点看到两个策略:

策略1:CUD sp接受表键的参数。首先调用read SP以确定是否存在相关记录;如果没有找到，返回401;如果找到，则继续SP插入，更新，删除，传递相关密钥。

示例:端点为客户添加新的auto。自动表有FK到PK的客户表。第一次呼叫自动读取SP(名，姓，电话等)。如果没有找到行，则返回401 +自定义消息。如果发现行，调用自动更新SP传递客户密钥。

策略2:CUD sp接受属性参数和SQL连接到其他表(如果相关)。

示例:相同的端点。单次呼叫自动插入服务，参数包括汽车品牌，车型，年份+客户名称，电话。如果失败，返回500 + SQL错误信息。

策略1的优点:为最终用户提供更好的体验(自定义友好的错误消息)，并且如果由于不存在所需的记录而预先确定失败，则不会尝试调用CUD操作。

策略2的优势:每次对web端点的调用对数据库的调用更少。这通常有多重要?还有其他优势吗?

编辑:我正在尝试Aaron bertrds upsert，并试图检索插入的新行，但始终为零的身份:

BEGIN TRAN
INSERT [dbo].[Auto]
([Make],[Model],[Year],[Customer]) 
SELECT @make, @model, @year, @customerId
WHERE NOT EXISTS
(SELECT 1 
FROM [dbo].[Auto] WITH (UPDLOCK, SERIALIZABLE)
WHERE [Make] = @make
AND [Model] = @model
AND [Year] = @year
AND [Customer] = customerId)
IF @@ROWCOUNT = 0
BEGIN
UPDATE [dbo].[Auto] 
SET [Make] = @make
,[Model] = @model
,[Year] = @year
,[Customer] = @customerId
WHERE [Make] = @make
AND [Model] = @model
AND [Year] = @year
AND [Customer] = @customerId)
END
SET @id = SCOPE_IDENTITY()
COMMIT

Answer 1

如果不是因为策略1绝对没有好处，这个问题可能是基于意见的。

您不需要多次调用来获得友好的错误消息。只需调用存储过程(或临时SQL批处理)。如果失败，则捕获异常并发送适当且有用的HTTP响应。

如果需要额外的逻辑，将其放在存储过程中。