3

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)

Mordy
  • 522
  • 7
  • 22
  • 1
    There is no "right way". Both work. option 1) has scale limits, but is easier, you can optimize lots of things here, there are entire books on locking in relational databases. It depends on the volume of requests/clients, etc. If you want to scale to the sky, option 2) is probably better. – Simon Mourier Jun 26 '18 at 08:59
  • Option 1 seems over simplified, you won't lock the entire table you should only lock the records being used by the current transaction. So if I pull a van from inventory, every other transaction should wait until I complete before it can check the status of that specific van. That doesn't mean I can't read the status of other vans or vehicle types. – Alex Terry Jun 28 '18 at 15:11
  • Let database handle it !! , there is no need to setup any kind of locking at client and unnecessary overload your server to distribute locking across multiple clients. Also most of the times you don't need locking at all, I solved similar issue with single INSERT SELECT or UPDATE SELECT combination statements that removes any need of locking. Only disadvantage is unit testing becomes little bulky. EF is only good for UI related stuff, simple CRUD and Report generation, but absolutely bad idea for computation on server. – Akash Kava Jul 02 '18 at 09:07
  • @AkashKava You solution does not work as INSERT SELECT and UPDATE SELECT are not atomic. They are performed as a subquery and then an INSERT, between which another INSERT can (and eventually will) happen and thereby break your datamodel allocating the same resource multiple times. – Jesper Feb 08 '19 at 13:55

4 Answers4

3

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).
Mihir Dave
  • 3,954
  • 1
  • 12
  • 28
  • This is quite similar to the route I went with in the end, I built a light application-level cache and kept a list of guids to uniquely identify the item(s) I am working with for the lifecycle of the request and then remove those items from the list as I exit. It effectively maintains a lifecycle of blacklisted items for the duration of a request and any other requests for those same items are blocked until the lock is released (or times out). – Mordy Jul 03 '18 at 08:39
0

I'm not sure how your database is laid out, but if each inventory item is its own record in the database, just have a IsUsed flag on the table. When you go to update the record, just make sure you put IsUsed = 0 as part of the where clause. If total modified comes back as 0, then you know something else updated it before you.

Daniel Lorenz
  • 4,178
  • 1
  • 32
  • 39
  • The problem is the race condition that can happen between checking if the stock is available and claiming it. If a second process jumps in between those two points it will see available stock just before the first process claims it and then it will also attempt to claim it. At this point the "IsUsed" hasn't been updated to true yet so the second process still sees an unclaimed item. – Mordy Jul 03 '18 at 13:05
  • Ah, I see. You'd have to do either a stored procedure or transaction where you updated it first and then selected it back to make sure nothing else grabbed it before you... – Daniel Lorenz Jul 03 '18 at 18:19
  • Transactions or stored procedures in itself does not guarantee that a select and a subsequent insert is performed atomically. – Jesper Feb 07 '20 at 10:21
0

There are different approaches to this problem and I'm just answering what I've thought about and eventually settled on when having to tackle this problem for a customer.

1. If the traffic is not heavy on your INSERT and UPDATE on these resources you can completely lock the table by doing something like this for example in a stored procedure, but this can also be done in simple client-side code:

CREATE PROCEDURE ...
AS
BEGIN
  BEGIN TRANSACTION

  -- lock table "a" till end of transaction
  SELECT ...
  FROM a
  WITH (TABLOCK, HOLDLOCK)
  WHERE ...

  -- do some other stuff (including inserting/updating table "a")



  -- release lock
  COMMIT TRANSACTION
END

2. Use pessimistic locking by having your code obtain locks you yourself create. Put in an extra table pr resource-type you want to lock and set a unique constraint on the Id of the resource you want to lock. You then obtain a lock by trying to insert a row and you release the lock by deleting it. Put timestamps on so that you can have a job to clean up locks that got lost. The table could look like this:

Id         bigint
BookingId  bigint        -- the resource you want to lock on. Put a unique constrain here
Creation   datetime      -- you can use these 2 timestamps to decide when to automatically remove a lock
Updated    datetime
Username   nvarchar(100) -- maybe who obtained the lock?

With this approach it's easy to decide which of your code needs to obtain a lock and what pieces of code can tolerate reading your resource and reservation table without a lock.

3. If it's a resource that is allotted by a begin- and end-time you could set the granularity of this timespan to e.g 15min. Each 15min timeslot of the day will then get a number starting from 0. Then you could create a table beside your reservation-table where start and end timestamps now consist of a number for the timeslot. Choose a reasonable starting timestamp as number 0. You will then insert as many rows with the different timeslot number as needed for every reservation. You of course need to have a unique constraint on the "Timeslot"+"ResourceId" so that any insert will be rejected if it is already reserved for that timeslot. Updating this table could nicely be done in triggers on your table with reservations so that you can still have real timestamp on reservation-table and when an insert or update is performed you can update the timeslot-table and it can raise an error if you violate the unique constraint thereby rolling back the transaction and preventing a change in both tables.

Jesper
  • 396
  • 4
  • 13
0

If you have a table for storing vehicles in your db then you can take pessimistic no wait lock on vehcile to be allotted in slot selected by user.

This lock will be held by one transaction once aquired till it commits or rollbacks. All the other transaction if try to aquire the lock on the vehicle will fail immediately. Hence no waiting in db for transactions. This will scalable as no waiting queues for txns in db to get the lock on vehicle to be allotted.

For failing transactions you can immediately roll back them and ask user to select different vehcile or slot.

Now it also applies if you have multiple vehicle of the same type and you get a chance to alott same vehicle I mean having same registration number to two user in same slot. As only one transaction wil win and others will fail.

Below is the postgresql query for this:

SELECT *
FROM   vehicle
WHERE  id = ?
FOR UPDATE nowait
Vaneet Kataria
  • 575
  • 5
  • 14