如何编写无死锁的T-SQL Upsert？兼谈API数据库CUD策略与问题排查

嘿，咱们一步步来解决你遇到的这两个问题——先拆解两种CUD端点策略的优劣，再搞定你Upsert代码里拿不到标识的坑。

一、两种CUD策略的深度对比 #

策略回顾 #

• 策略1：先查后改，分两次数据库调用
  CUD存储过程先调用读取SP验证记录存在性：找不到就返回401+自定义提示；找到就传入主键执行CUD操作。比如添加客户车辆时，先查客户是否存在，再执行车辆的插入/更新。

• 策略2：单次SP调用，内置关联判断
  CUD存储过程直接接收业务属性参数，必要时通过SQL连接关联其他表，单次调用完成操作；执行失败返回500+原生SQL错误。比如添加车辆时，直接传入车辆和客户属性，SP内部处理客户关联逻辑。

策略2的核心优势解答 #

你问到策略2“数据库请求更少”的重要性，以及其他隐藏优势，咱们一一说：

1. 少请求的核心价值

• 降低网络往返开销：哪怕是本地数据库，每次请求都有进程间通信的延迟；分布式场景下，跨机房的网络延迟会被放大。高并发时，多次请求的累积延迟会直接拖慢API响应速度，甚至引发超时。
• 减轻连接池压力：数据库连接池的资源是有限的，每个请求都会占用一个连接直到完成。更少的请求意味着连接占用时间更短，能支撑更多并发用户，避免连接池耗尽的问题。
• 减少竞态条件风险：策略1的两次调用之间存在时间窗口——比如你刚查到客户存在，在执行车辆插入前，另一个请求删除了这个客户，就会导致CUD失败。虽然可以用分布式事务补救，但复杂度会飙升；而策略2的单次调用可以在SP内部封装原子事务，从根源避免这种中间状态。

2. 其他额外优势

• 简化API层逻辑：不用在API里写“先判断再执行”的分支代码，把业务判断逻辑下沉到数据库层，API只需要负责参数校验和结果返回，代码更简洁易维护。
• 更好的原子性保障：SP内部可以用事务包裹整个CUD操作，确保要么全部成功，要么全部回滚；而策略1如果不在API层加分布式事务，很容易出现数据不一致的情况。
• 更高效的锁机制：策略2可以在SP内部针对目标数据加合适的锁（比如UPDLOCK、SERIALIZABLE），避免多并发下的数据覆盖问题；而策略1的两次调用之间，锁的粒度和时效很难控制。

二、解决Upsert无法获取插入行Identity的问题 #

你的代码有几个小问题导致拿不到标识，咱们先看问题，再给修正方案：

原代码的核心问题 #

1. 语法错误：UPDATE语句的WHERE子句末尾多了一个右括号，而且customerId没加@符号，属于未定义变量；
2. Identity获取逻辑不全：SCOPE_IDENTITY()只会返回当前会话中最后一次INSERT生成的标识。如果走UPDATE分支，因为没有执行INSERT，@id会保持初始值0，不会自动获取已存在的行ID。

修正后的代码方案 #

这里给你两种可靠的写法，按需选择：

写法1：先查ID，再分支处理（直观易读）

BEGIN TRAN

DECLARE @existingId INT;

-- 先检查记录是否存在，同时获取现有ID（加锁避免竞态）
SELECT @existingId = [Id]
FROM [dbo].[Auto] WITH (UPDLOCK, SERIALIZABLE)
WHERE [Make] = @make
  AND [Model] = @model
  AND [Year] = @year
  AND [Customer] = @customerId; -- 修复@符号问题

IF @existingId IS NULL
BEGIN
  -- 插入新记录，获取新Identity
  INSERT [dbo].[Auto] ([Make],[Model],[Year],[Customer]) 
  VALUES (@make, @model, @year, @customerId);
  SET @id = SCOPE_IDENTITY();
END
ELSE
BEGIN
  -- 更新现有记录，直接用已存在的ID
  UPDATE [dbo].[Auto] 
  SET [Make] = @make,
      [Model] = @model,
      [Year] = @year,
      [Customer] = @customerId
  WHERE [Id] = @existingId; -- 用主键ID作为条件，效率更高
  SET @id = @existingId;
END

COMMIT

写法2：用MERGE语句（更简洁，适合复杂场景）

MERGE是SQL Server官方推荐的Upsert写法，能通过OUTPUT子句统一获取ID：

DECLARE @output TABLE (Id INT);

MERGE [dbo].[Auto] WITH (UPDLOCK, SERIALIZABLE) AS target
USING (SELECT @make, @model, @year, @customerId) AS source ([Make],[Model],[Year],[Customer])
ON target.[Make] = source.[Make]
   AND target.[Model] = source.[Model]
   AND target.[Year] = source.[Year]
   AND target.[Customer] = source.[Customer]
WHEN MATCHED THEN
  UPDATE SET [Make] = source.[Make],
             [Model] = source.[Model],
             [Year] = source.[Year],
             [Customer] = source.[Customer]
WHEN NOT MATCHED THEN
  INSERT ([Make],[Model],[Year],[Customer])
  VALUES (source.[Make], source.[Model], source.[Year], source.[Customer])
OUTPUT inserted.[Id] INTO @output; -- 不管插入还是更新，都能拿到ID

-- 从输出表变量中获取最终ID
SELECT @id = Id FROM @output;

这两种写法都能解决你返回0的问题，而且避免了原代码中的语法错误和竞态风险。

内容的提问来源于stack exchange，提问作者BaltoStar