How do I block this race condition in an inventory allocation scenario?

asp.net-web-api c# concurrency entity-framework-6 pessimistic-locking

Question

I'm wrestling with the classic problem of inventory allocation and concurrency and I wondered if anyone could give me a steer on best practice in this situation.

My situation is that we have an order prepared with several "slots" which will be filled by unique inventory items at a certain stage in the process and at this point I want to make sure that nobody allocates the same unique unit to a slot on a different order. For example a user wants a van next Thursday so I reserve a "van" slot but at a later point in time I allocate a specific vehicle from the yard to this slot. I want to make sure that two different operators can't allocate the same van to two different customers next Thursday.

We already have a stock availability check process where we compare the aggregate of two tables within a date range, the result of summing these two tables (one is items in and the other is items out) tells me whether we have the specific item that I want to allocate to this slot on this date but I want to prevent another user from allocating the same item to their own slot at the same point in time.

I've already done some googling and research on this site and it looks like I need a "pessimistic locking" solution but I'm not sure how to put one in place effectively.

The allocation process will be called from a web API (rest api using .Net) with entity framework and I've considered the following two solutions:

Option 1 - Let the database handle it

At the point of allocation I begin a transaction and acquire an exclusive lock on the two tables used for evaluating stock availability.

The process confirms the stock availability, allocates the units to the slots and then releases the locks.

I think this would prevent the race condition of two users trying to allocate the same unique unit to two different orders but I'm uncomfortable with locking two tables for every other process that needs to query these tables until the allocation process completes as I think this could cause a bottleneck to other processes attempting to read those tables. In this scenario I think the second process which attempts to perform the duplicate allocation should be queued until the first has released the locks as it won't be able to query the availability tables and when it does it will fail the availability check and report an out of stock warning - so effectively blocking the second order from allocating the same stock.

On paper this sounds like it would work but I have two concerns; the first is that it will hit performance and the second is that I'm overlooking something. Also I'm using Postgres for the first time on this project (I'm normally a SQL Server guy) but I think Postgres still has the features to do this.

Option 2 - Use some kind of manual locking

I think my scenario is something like ticketing websites would encounter during the sales process for concerts or cinemas and I've seen them put up timers saying things like "your tickets will expire in 5 minutes" but I don't know how they implement this kind of system in the back end. Do they create a table of "reserved" stock before the allocation process begins with some kind of expiry time on them and then "blacklist" other users attempting to allocate the same units until that timer expires?

Sorry for the long intro but I wanted to explain the problem completely as I've seen plenty of questions about similar scenarios but nothing that really helped me to make a decision on how to proceed.

My question is which of the two options (if any) are "the right way" to do this?

Edit: The closest parallel to this question I've seen is How to deal with inventory and concurrency but it doesn't discuss option 1 (possibly because it's a terrible idea)

1
2
6/21/2018 8:56:24 AM

Popular Answer

I think option 2 is better with some tweak.

This is what i'll do if i have to deal with such situation

  1. Whenever user tries to book a vehicle for a slot, i'll make an entry(Entry should contain unique key which is made up with unique car id + slot time, And no duplicate entries should be allowed here for that combination that way you'll get Error in your application if two user tries to book same car for same slot at the same time so you can notify other user that van is already gone) in temporary holding area(normal table will do but if u have higher transaction you want to look into some caching database solutions.)
  2. So before second user tries to book a vehicle user must check for lock in that slot for that car. (or you can show unavailability of cars for that slot using this data).
3
6/30/2018 7:22:10 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow