SQL 插入最大金额的争用条件-解网

问：

我们正在创建一个拍卖竞价系统。我们只允许在 AuctionBid ID 表格中插入产品的金额高于上次出价的情况。

此查询适用于单个操作。

select MAX(Amount) from dbo.AuctionBid where ProductId = 9   // Amount: 150

但是，在对同一产品进行多个竞价的多线程并发环境中，可能会导致争用条件。其中一个插入读取（150）并即将插入例如 170，而另一个插入同时已经插入（例如 190）。

如何在SQL中解决这个问题？研究不同类型的锁。如果可能，尝试防止完全锁定表。

我们的桌子有

AuctionBidId	产品 Id	量
1	2 （沙发）	175
2	9 （电视）	100
3	9 （电视）	150

当前使用 Microsoft SQL Server 2019。（T-SQL）

sql-server 数据库并发 azure-sql-database

这样的事情能解决问题吗？将其放入您的插入过程中，如果提供的低于最高出价，则不会插入。您可以添加额外的处理来大惊小怪或记录一些东西，以防出价未被接受，但这里的关键点是，这会消除桌子上的锁，防止任何其他 SPID 尝试做同样的事情。@CurrentBid

insert into AuctionBid
(
    Bid
)
select
    @CurrentBId
where @CurrentBId > (select max(Bid) from AuctionBId)

-- if you need to handle subsequent logic differently if the bid insert failed, do something like this:
if @@rowcount = 0
begin
    raiserror('Oh noes! ur bid wasn''t accepted!', 0, 1) with nowait
end

我在全局临时表上运行了一个测试，首先插入了一行出价为 50（为表设定种子）。然后，我打开了一个新表，并以 9000 的出价插入到该表中，但保持交易未结状态。最后，我打开了第三个选项卡并尝试插入值 8999，它挂起等待我的其他事务完成。这似乎意味着在插入完成时，该表被锁定在插入物中。

在另一个事务仍处于打开状态时尝试插入的 SPID 正在尝试获取共享锁，并且在另一个事务提交之前无法插入。LCK_M_S

当另一个 SPID 具有锁定时，您甚至无法从表中选择;正如你所希望的那样。max(Bid)

create or alter procedure dbo.addBid (
   @ProductID int,
   @BidAmount int
)
as
begin
   declare @maxBid int;
   begin transaction
      set @maxBid = (
         select max(Amount)
         from dbo.AuctionBid with (holdlock, updlock)
         where ProductId = @ProductID
      );

     if (@bidAmount > @maxBid)
     begin
        insert into dbo.AuctionBid
           (ProductId, Amount)
        values
           (@ProductID, @BidAmount);
     end
     else
     begin
        print 'Specified bid amount is not larger than current bid and is thus rejected';
     end
   commit
end

您可能还想要其他一些错误处理，但这是要点。这里的想法是，您以这样一种方式获得当前最高出价，以防止并发进程同时这样做，并在与该选择相同的事务中执行插入。

我们在此处使用的查询提示包括：

HOLDLOCK（可序列化）- “在当前事务完成之前，其他事务无法插入键值属于当前事务中任何语句读取的键范围的新行。

UPLOCK - 在这种情况下，我们将强制使用独占锁，因为 SQL Server 只能接受一个 UPLOCK ，而不是共享的 READLOCK

1赞 Joe 11/7/2023 #3

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
-- Get the current max bid for the product
SELECT MAX(Amount) FROM dbo.AuctionBid WHERE ProductId = 9;
-- Your application checks if the new bid is higher and proceeds to insert
INSERT INTO dbo.AuctionBid (ProductId, Amount) VALUES (9, <new_bid>);
COMMIT TRANSACTION;

上一个：日志文件自动增长 SQL-Server

下一个：如何从 AppSettings.Json 访问我的 ConnectionString，而不提及 Razor 页面项目中连接字符串的名称？

SQL 插入最大金额的争用条件

SQL Insert Race Conditions on Max Amount

评论

评论