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)
I think option 2 is better with some tweak.
This is what i'll do if i have to deal with such situation