我的数据库中有一个名为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互斥访问。
您需要确保对表的互斥访问,您目前只是依靠运气(正如您所看到的,这不起作用)。