SQL插入竞态条件解决：拍卖系统最高出价插入并发问题

我们开发的拍卖竞价系统要求：仅当新出价高于对应商品的当前最高出价时，才能向AuctionBid表插入记录。单线程场景下，通过SELECT MAX(Amount) FROM dbo.AuctionBid WHERE ProductId = 9可以判断，但多线程并发时会出现竞态条件——比如两个线程同时读到当前最高出价150，一个准备插入170，另一个已经完成190的出价插入，最终导致170的无效出价被错误写入。

以下是基于SQL Server 2019（T-SQL）的解决方案，尽量避免全表锁：

第一步：创建必要的索引（核心前提） #

要避免全表锁，必须让数据库能快速定位到目标商品的出价记录，而非扫描全表。创建非聚集索引：

CREATE NONCLUSTERED INDEX IX_AuctionBid_ProductId_Amount
ON dbo.AuctionBid (ProductId) INCLUDE (Amount);

该索引会让所有针对ProductId的查询仅锁定对应商品的出价行，不会影响其他商品的并发操作。

方法1：显式事务+锁定提示（直观易维护） #

通过显式事务结合UPDLOCK和HOLDLOCK锁定提示，保证“查询最高出价→判断→插入”的原子性：

DECLARE @TargetProductId INT = 9;
DECLARE @NewBidAmount DECIMAL(18,2) = 170;

BEGIN TRANSACTION;

-- 锁定目标商品的出价记录，获取当前最高出价
DECLARE @CurrentMaxAmount DECIMAL(18,2);
SELECT @CurrentMaxAmount = MAX(Amount)
FROM dbo.AuctionBid WITH (UPDLOCK, HOLDLOCK)
WHERE ProductId = @TargetProductId;

-- 插入条件：无历史出价 或 新出价高于当前最高
IF (@CurrentMaxAmount IS NULL OR @NewBidAmount > @CurrentMaxAmount)
BEGIN
    INSERT INTO dbo.AuctionBid (ProductId, Amount)
    VALUES (@TargetProductId, @NewBidAmount);
    PRINT '出价成功';
END
ELSE
BEGIN
    PRINT '出价低于当前最高，插入失败';
END

COMMIT TRANSACTION;

• UPDLOCK：获取更新锁，阻止其他事务读取该商品的出价记录（避免脏读）；
• HOLDLOCK：保持锁直到事务结束，防止在判断与插入之间被其他事务修改数据；
• 索引保证仅锁定目标商品的行，不会产生全表锁。

方法2：MERGE语句（原子化单语句操作） #

利用MERGE的原子性，将判断与插入合并为一个语句，简化代码：

DECLARE @TargetProductId INT = 9;
DECLARE @NewBidAmount DECIMAL(18,2) = 170;

-- 用临时表接收插入结果，判断是否操作成功
DECLARE @InsertedBids TABLE (AuctionBidId INT, ProductId INT, Amount DECIMAL(18,2));

MERGE INTO dbo.AuctionBid AS Target
USING (
    SELECT 
        @TargetProductId AS ProductId, 
        @NewBidAmount AS Amount,
        -- 锁定并获取当前最高出价
        (SELECT MAX(Amount) FROM dbo.AuctionBid WITH (UPDLOCK, HOLDLOCK) WHERE ProductId = @TargetProductId) AS CurrentMax
) AS Source
ON 1=0 -- 强制不匹配任何现有行，仅走插入分支
WHEN NOT MATCHED AND (Source.CurrentMax IS NULL OR Source.Amount > Source.CurrentMax) THEN
    INSERT (ProductId, Amount) VALUES (Source.ProductId, Source.Amount)
OUTPUT inserted.* INTO @InsertedBids;

IF EXISTS(SELECT * FROM @InsertedBids)
    PRINT '出价成功';
ELSE
    PRINT '出价无效，插入失败';

MERGE语句本身是原子操作，结合锁定提示和索引，同样能避免竞态条件和全表锁。

方法3：INSERT...SELECT（简洁的原子判断） #

将条件判断与插入合并为一个INSERT语句，利用SQL的原子性实现需求：

DECLARE @TargetProductId INT = 9;
DECLARE @NewBidAmount DECIMAL(18,2) = 170;

INSERT INTO dbo.AuctionBid (ProductId, Amount)
SELECT @TargetProductId, @NewBidAmount
WHERE 
    -- 无历史出价 或 新出价高于当前最高
    (SELECT MAX(Amount) FROM dbo.AuctionBid WITH (UPDLOCK, HOLDLOCK) WHERE ProductId = @TargetProductId) IS NULL
    OR @NewBidAmount > (SELECT MAX(Amount) FROM dbo.AuctionBid WITH (UPDLOCK, HOLDLOCK) WHERE ProductId = @TargetProductId);

-- 通过@@ROWCOUNT判断是否插入成功
IF @@ROWCOUNT > 0
    PRINT '出价成功';
ELSE
    PRINT '出价低于当前最高，插入失败';

这种写法无需显式事务，INSERT语句本身是原子的，锁定提示保证判断期间数据不会被修改。

关键注意事项 #

1. 索引不可少：没有索引的话，所有查询都会触发全表扫描，即使加锁定提示也会导致全表锁，严重影响并发性能；
2. 锁定提示的必要性：UPDLOCK和HOLDLOCK的组合是防止竞态条件的核心，缺少任何一个都可能导致无效出价被插入；
3. 数据类型一致性：确保@NewBidAmount的类型与表中Amount字段一致，避免隐式转换导致的错误。

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