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.
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.