How to implement a stock issuance system ensuring no duplication using C# and SQL Server-CodePudding

I have a system implemented in C# API .Net Core 6. The database is SQL Server and I'm using Entity Framework. I have a table of individual stock items (each item has its own record). When I sell an item I record its sale date by changing the value of the 'Sold' field from NULL to DateTime.UtcNow. When I need to sell an item I want to get a reference to one of the items of that category in my stock table which I do with a ctx.Stock.Where(t=> t.Sold == null && t.Category==cat).First() that returns me a single item if it exists. That item then has its 'Sold' field updated.

Since the system is accessed via an API, it would seem possible that the code could be called twice (or more) at the same time, both calls would return the same item from the ctx.Stock.Where(t=> t.Sold == null && t.Category==cat).First() , both would then mark it as sold and when SaveChanges was called for both of them, it would save the later 'Sold' value and only 1 stock Record would be recorded as sold when in fact 2 sales had happened.

Now this could be avoided if the DbUpdateConcurrencyException is triggered when this type of situation occurs. Does anyone know if this would be the case? In that case I guess a retry loop could be implemented to attempt to get a new stock record using ctx.Stock.Where(t=> t.Sold == null && t.Category==cat).First() . Would this be the best way of doing it?

Otherwise would having a singleton service that has locks around the running of ctx.Stock.Where(t=> t.Sold == null && t.Category==cat).First() and SaveChanges of the populated 'Sold' field be a better bet? This could ensure there are no possible DbUpdateConcurrencyException as it would be sequential, but then maybe the locks would be a problem.

CodePudding user response：

I'm not much of a fan of trusting the database. If you want to be able to scale your app horizontally and have access to Redis Cache, you could use RedLock to prevent the two calls from hitting the DB at the same time. Otherwise just use a simple lock. The service doesn't have to be a singleton... you just need to use a static object for the locking.