防止并发选择相同的下一个计算值

本文关键字：下一个 计算 并发 选择 | 更新日期: 2023-09-27 17:52:17

我的数据库中有一个名为SerialNumber的表，看起来像这样:

[Id] [bigint] IDENTITY(1,1) NOT NULL,
[WorkOrderId] [int] NOT NULL,
[SerialValue] [bigint] NOT NULL,
[SerialStatusId] [int] NOT NULL

在我的应用程序中，对于给定的工作订单(WorkOrderId)，我总是需要获得MIN SerialValue, SerialStatusId为1。
例如，如果一个客户端选择了某个序列号，它应该立即更改为SerialStatusId =2，以便下一个客户端不会选择相同的值。
该应用程序安装在可以同时访问数据库的多个站点上。
我尝试了一些方法来解决这个问题，但没有成功:

update SerialNumber
set SerialStatusId = 2
output inserted.SerialValue
where WorkOrderId = @wid AND 
SerialValue = (select MIN(SerialValue) FROM SerialNumber where SerialStatusId = 1)

和

declare @val bigint;
set @val = (select MIN(SerialNumber.SerialValue) from SerialNumber where SerialStatusId = 1
and WorkOrderId = @wid);
select SerialNumber.SerialValue from SerialNumber where SerialValue = @val;
update SerialNumber set SerialStatusId = 2 where SerialValue = @val;

我测试的方法是:

    public void Test_GetNext()
    {
        Task t1 = Task.Factory.StartNew(() => { getSerial(); });
        Task t2 = Task.Factory.StartNew(() => { getSerial(); });
        Task t3 = Task.Factory.StartNew(() => { getSerial(); });
        Task t4 = Task.Factory.StartNew(() => { getSerial(); });
        Task t5 = Task.Factory.StartNew(() => { getSerial(); });
        Task.WaitAll(t1, t2, t3, t4, t5);

        var duplicateKeys = serials.GroupBy(x => x)
                    .Where(group => group.Count() > 1)
                    .Select(group => group.Key).ToList();
    }
    private List<long> serials = new List<long>();
    private void getSerial()
    {
        object locker = new object();
        SerialNumberRepository repo = new SerialNumberRepository();
        while (serials.Count < 200)
        {
            lock (locker)
            {
                serials.Add(repo.GetNextSerialWithStatusNone(workOrderId));
            }
        }
    }
到目前为止，我所做的所有测试都从包含200个序列的表中给出了大约70个重复的值。我如何改进我的SQL语句，使它不会返回重复的值?

尝试实现@Chris的建议，但仍然得到重复的数量: 
SET XACT_ABORT, NOCOUNT ON
DECLARE @starttrancount int;
BEGIN TRY
SELECT @starttrancount = @@TRANCOUNT
IF @starttrancount = 0 BEGIN TRANSACTION
update SerialNumber
set SerialStatusId = 2
output deleted.SerialValue
where WorkOrderId = 35 AND
SerialValue = (select MIN(SerialValue) FROM SerialNumber where SerialStatusId = 1)
IF @starttrancount = 0
COMMIT TRANSACTION
ELSE
EXEC sp_releaseapplock 'get_code';
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0 AND @starttrancount = 0
ROLLBACK TRANSACTION
RAISERROR (50000,-1,-1, 'mysp_CreateCustomer')
END CATCH

防止并发选择相同的下一个计算值

您得到重复的原因是因为在读取前一个值和将新值更新到SerialNumber之间存在竞争条件。如果两个并发连接(例如两个不同的线程)同时读取相同的值，它们都将获得相同的新SerialNumber。第二个连接也将再次冗余更新状态。

更新解决方案-前一个导致死锁

要使用当前的设计来解决这个问题，当读取下一个值时，您需要先锁定表中的行，以防止其他并行读取器。

CREATE PROC dbo.GetNextSerialLocked(@wid INT)
AS 
BEGIN
    BEGIN TRAN
        DECLARE @NextAvailableSerial BIGINT;
        SELECT @NextAvailableSerial = MIN(SerialValue) 
           FROM SerialNumber WITH (UPDLOCK, HOLDLOCK) where SerialStatusId = 1;
        UPDATE SerialNumber
        SET SerialStatusId = 2
        OUTPUT inserted.SerialValue
        WHERE WorkOrderId = @wid AND SerialValue = @NextAvailableSerial;
    COMMIT TRAN
END
GO

由于acquire + update现在被分割在多个语句中，我们确实需要一个事务for来控制HOLDLOCK的生命周期。

您还可以将事务隔离级别提高到SERIALIZABLE -尽管这不会阻止并行读，但它会导致第二个写程序在尝试写表时死锁，因为读取的值现在已经更改了。

您也可以考虑其他设计来避免以这种方式获取数据，例如，如果序列号应该增加，则考虑IDENTITY。

还要注意，c#代码锁定没有效果，因为每个Task将有一个独立的locker对象，并且永远不会对同一个锁产生争用。

    object locker = new object(); // <-- Needs to be shared between Tasks
    SerialNumberRepository repo = new SerialNumberRepository();
    while (serials.Count < 200)
    {
        lock (locker) <-- as is, no effect.

另外，在添加序列时，需要在serials集合周围设置一个内存屏障。最简单的可能是将其更改为排序的并发集合:

private ConcurrentBag<long> serials = new ConcurrentBag<long>();

如果你想从c#序列化访问，你需要在所有任务/线程中使用共享的static object实例(显然，从代码序列化将要求所有对序列号的访问都来自同一个进程)。数据库中的序列化更具可伸缩性，因为您还可以为不同的WorkOrderId设置多个并发读取器。

  • SqlFiddle的Sql代码在这里
  • GitHub c#代码摘要

看一下这个问题的答案:存储过程中的TSQL互斥访问。

您需要确保对表的互斥访问，您目前只是依靠运气(正如您所看到的，这不起作用)。

相关文章：