4

I've a scenario where I get a cab request from a customer and I send pickup request to multiple drivers. Now Imagine if two of the drivers who got the request hit the "Accept" button at the exact same time, then which driver will get the ride.

I've a ride table with columns ride_id, driver_id, fulfilled(Boolean).

As of now what I'm doing is calling a API as soon as "Accept" button is hit. This GET request API checks if the ride is fulfilled or not. If Yes, I display a message to driver saying ride is already fulfilled, else I hit another POST API request which updates the fulfilled value in DB to true and also update driver_id.

Now coming to our scenario, when both the drivers will hit "Accept" at the same time two GET request will be made and both will get "Not Fulfilled" as response thereafter both will send a POST request. Now I'm confused whose data will be updated in DB.

I'm using PHP in back-end with MYSQL for database.

Deepak Singh
  • 610
  • 1
  • 7
  • 23
  • 2
    The Database should look after that all for you. You just have to process the failed update correctly – RiggsFolly Oct 12 '19 at 17:25
  • Agree. But is there any way I can determine that DB is going to pick which request first? – Deepak Singh Oct 12 '19 at 17:27
  • 2
    @RiggsFolly more or less means run -> `UPDATE ... SET fulfilled = 1 [,...] WHERE fulfilled = 0 AND ride_id = ?` then the order does not matter which update comes first. for a ride. – Raymond Nijland Oct 12 '19 at 17:31
  • @RaymondNijland Yeah, I get that, but there is a driver_id column as well. So how can I determine which driver's ID is going to be there? OR I can check that only after the request is inserted and then I make a select call to check the updated driver's ID? – Deepak Singh Oct 12 '19 at 17:32
  • See @GMB s SQL also which uses the same idea as i had but with a added explainment.. – Raymond Nijland Oct 12 '19 at 17:34
  • Another option is to use [`SELECT .. FOR UPDATE`](https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html) – Madhur Bhaiya Oct 12 '19 at 17:41

2 Answers2

4

You are creating a race condition by running a SELECT, and then an UPDATE. But one thing all databases can do very efficiently is manage concurrency. Hence, a simpler solution would be to run directly an update when the driver hits the accept button, like:

UPDATE ride 
SET driver_id = :driver_id, fulfilled = 1 
WHERE ride_id = :ride_id AND fulfilled = 0

In your application, you then check if a record was affected by the query. If yes, then this driver won the ride. If no record was affected, then it means that some other driver took over the ride before.

GMB
  • 216,147
  • 25
  • 84
  • 135
  • 1
    Ok, and to determine which driver won the ride I have to make a another select statement for the ride_id and check for driver's ID there? – Deepak Singh Oct 12 '19 at 17:34
  • 2
    *"Ok, and to determine which driver won the ride I have to make a another select statement for the order_id and check for driver's ID there? "* No @DeepakSingh, GMB means running `mysqli_affected_rows()` or a PDO function which does the same after the `UPDATE` – Raymond Nijland Oct 12 '19 at 17:36
  • 1
    @RaymondNijland mysqli_affected_rows does not specify which row (id) was affected. It just gives a count on how many rows were actually affected. An explicit select is necessary in this case, after the update process – Madhur Bhaiya Oct 12 '19 at 17:43
  • 1
    @MadhurBhaiya: in this scenario, I actually don't see the need to get the id of the driver who won the ride. The query is coming from the frontend of a given driver, the results tells him if he won the ride. – GMB Oct 12 '19 at 17:44
  • 2
    @GMB ..yeah, right. On second reading on the question, makes sense! – Madhur Bhaiya Oct 12 '19 at 17:46
  • *"An explicit select is necessary in this case, after the update process "* why ? i assume prepared statements here @MadhurBhaiya then `$driver_id` is known right.. ? `if (mysqli_affected_rows()) == 1) { // use $driver_id }` ... Anyhow need to see code but pretty sure it can work like that :-) – Raymond Nijland Oct 12 '19 at 17:49
3

Two drivers hitting the accept button at the same time does not translate to updating a single row at the same time. The probability of your kind of scenario occurring is infinitesimally small.

But if it does happen, it is not necessarily a case of two transaction updating a row. It is a case of one transaction trying to update a row that is currently being updated. In any case the transaction currently updating the row obtains a lock on the row and any other transaction trying to update the same row is queued.

After the first transaction completes the update, it releases the lock and the first among the other transactions in the queue obtains the lock to perform it's own update. This continues until the waiting queue is empty.

Interestingly, this facility is already built into most relational databases, so you needn't worry about two transactions updating the database at the same time. However, an interesting area of research is how to mananage transaction queues (e.g. prioritizing waiting transactions) in this kind of scenerio.

Udo E.
  • 2,665
  • 2
  • 21
  • 33
  • the point of the system and handling which the topcistarter is after is a FIFO win system.. As in the first clicker gets the ride pretty sure you didn't interpreted question correctly.. – Raymond Nijland Oct 12 '19 at 17:39
  • Locking was my initial thought. But since the he frontend and the backend are decoupled, I can not see how this would work. – GMB Oct 12 '19 at 17:43
  • indeed @GMB it you would lock it would mean that the second click or the many other clicks after would still overrule that first click.. – Raymond Nijland Oct 12 '19 at 17:44
  • @RaymondNijland, your solution works. But Modern Relational databases employ locking to prevent what the question title suggests is possible – Udo E. Oct 12 '19 at 17:49
  • 1
    *"But Modern Relational databases employ locking to prevent what the question title suggests is possible"* What happens with `START TRANSACTION UPDATE ride SET driver_id = 1, fulfilled = 1 WHERE ride_id = 1; COMMIT;` as first click and running `START TRANSACTION UPDATE ride SET driver_id = 2, fulfilled = 1 WHERE ride_id = 1; COMMIT;` after as second click ? That is what happing in MySQL's point of view, driver_id 2 gets the ride using a transaction will not change that.. Unless you alter the state -> `fulfilled` and use that condition check in the `WHERE` like i have suggested. – Raymond Nijland Oct 12 '19 at 17:59