1

Problem statement:
Its very similar to a traditional booking system, however it is a bit different.

There are non-distinguishable items (e.g. like money on bank account) of different types that can be obtained by a user.
If a user selects an item it is reserved and can't be obtained by another user for a specfic time.
Every item has limited availability, e.g. quota of 10.
If an item is out of stock (quota = 0, all items have been ordered) or all items are currently reserved (reservation not expired), a waiting list entry is created (user waiting for item X to be available again).
This process is concurrent, multiple reservation requests can be created simultaneously, deciding if a reservation for an order or a waiting list position and inserting the reservation is dome within an transaction.

If the user "checkouts" the item selection

  • the quota is decreased by the number of ordered items
  • the reservation entry is deleted (= expires will be set to NULL and won't be considered for calculating current availability)
  • an order entry is created

If a number of items will be available again (e.g. reservation expires) users on the waitinglist move up and will be notified that they are now able to order the item (with expiration).
They can then checkout the selection and a order entry is created like above Reservation and waiting list positions is ordered (First Come First Served).

If the user is directly sent to the waiting list, the position is reserved as well until expiration. (e.g. user that comes first has waitling list position 1, second has 2, ...)

So to sum it up:

  • item available => reservation for item resulting in order on checkout
  • item out of stock => reservation for position on waiting list for item on confirmation

Database schema:
In order not to use a runner that runs every X minutes and deletes expired reservations and increases the quota again I only decrease the quota when an order gets fulfilled. To obtain the available amount of an item I use the following (for deciding if a new user can reserve an item or will be sent to the waitinglist):
item_quota_available - number_reservations_of_item - number_waitinglist_entries_of_item

Example:
5 items available, 3 are reserved currently, 2 are on waiting list => new user will be sent to waitinglist, because the free items can be ordered by 2 users on the waitinglist
2 items available, 1 reserved, 0 on waitinglist => user can reserve item

Problems:
I am not sure if this is the best approach and database layout, an alternative would be to decrease the quota everytime a reservation is created and then let a runner every X minutes increase the quota again by counting expired reservations.

With the approach I explained above, reservations just expire and are not considered "automatically" (e.g. SELECT COUNT(*) FROM reservations where expired > NOW()), however I can imagine that contention is pretty high because in order to count all valid reservations I need to lock the table to prevent any inserts of new reservations.

Questions:

  • Is this approach really feasible and is is better than using a runner that increments the quota periodically in terms of performance, complexity?
  • How can I assign a total order to reservations resulting in orders and reservations resulting in waiting list entries? In the end, all orders and waitinglist entries should be order by the time they became reserved. I don't wan't to optimistically insert either a order or waitinglist row, just to reserve the primary key. Time is not precice enough as it is possible that two transactions get the same fraction of seconds.
    • i could use the primary key of the reservation row, as it is guaranteed to be unique, however reservation and order table get gaps in ids (e.g. reservation for an order in between 2 reservations for waiting list)
    • i can use a temporary table with a counter and increment it atomically, is this really done in production?
  • What would be a clean design for a reservation that can lead to an order or a waiting list entry? I considered using a simple "target: waitinglist|order" column in a reservation row, but that feels ugly.

Tables (simplyfied) for clarity:

CREATE TABLE quota (
    id INT PK AI,
    available INT
)

CREATE TABLE item (
    id INT PK AI,
    name TEXT,
    quota_id INT,
)


CREATE TABLE reservation (
    id INTEGER PK AI,
    item_id INT,
    user_id INT,
    expires TIMESTAMP,
    target TEXT, (either order or waitinglist, not sure if this is the best approach)
)


CREATE TABLE user (
    id INT PK AI
)


CREATE TABLE order (
    id INTEGER PK AI,
    item_id INT,
    user_id INT
)


CREATE TABLE waiting_list (
    id INTEGER PK AI,
    item_id INT,
    user_id INT
    (position?)
)

Thanks for reading all this text! :)

Thomas
  • 11
  • 1

0 Answers0