How to make a system for assigning orders to couriers?

Question

MySQL

I am designing a system for courier delivery.

Orders are coming in. Each order has 3 parameters - this is the area and convenient time of receipt, and the weight of the parcel.

The courier has 3 main parameters - these are the areas where he works and his work schedule and load capacity

. The service has an api method that, when called, runs through orders and assigns the maximum number of orders for one courier.

The problem is this. With a large number of requests, it is possible that 2 couriers will take the same order. This cannot be allowed.

Everything is stored in MySQL. On the Python backend. As I understand it, making a request from the server to the database and working with the data already in the code is not an option. During the period of time when the code performs calculations in the database, the order may change (the order that we received with select will already go to another courier) and we will assign the already assigned order.

What I see ways to solve this problem.

1) a synchronous single-threaded order assignment handler. When the method is called, the server gives a unique id to this order assignment and queues it. After that, the server runs on a timer (every 50 ms) in Redis and looks for its id there. At this time, a synchronous single-threaded worker is running on the other side of the queue, which physically cannot assign 1 order to 2 couriers. The worker throws the answer into Redis and moves on.

2) complex sql queries. I'm not particularly strong here. Question to you. Let's implement all the logic in 1 sql query. Can a situation happen when 1 order is assigned to 2 couriers?

3) take the data with a select, execute logic on the server and update the database with the condition that the order has not yet been assigned.

Most likely these methods are bad, so I would like to know from you ways to solve a similar problem.

Answer 1

Mark an order with a courier? When an order is selected for a specific courier, the courier id and timestamp are written to the order database. Think over the business logic there - when to detach the order from the courier if he did not take it for some reason.

Answer 2

for this, there is a lock at the base level via SELECT ... FOR UPDATE