Skip to content
Advertisement

Race condition with multiple updates PHP & MySQL

I have a table called cards, Each user can create/have multiple cards. One of the cards must be set as default.
Sometimes when a user performs multiple requests to set another card as default at the same time, In the end, There are two or more default cards.

Initial table:

id user_id is_default
1 50 0
2 50 1

Requests (performed at the same time):
PATCH http://localhost:8000/cards/1/default
PATCH http://localhost:8000/cards/2/default

Result:
Both of them are set as default.

id user_id is_default
1 50 1
2 50 1

Code:

use AppModelsCard;

public function setAsDefault(Request $request, $id)
{
  Card::where('user_id', $request->user()->id)->update(['is_default' => false]);
  Card::where([
    'id' => $id,
    'user_id' => $request->user()->id
  ])->update(['is_default' => true]);

  return ['status' => true'];
}

Possible solutions:

  1. Transaction with shared lock
  2. Rate Limiting

Are there other simpler solutions?

Advertisement

Answer

Try using transactions. Something like this…

   
     DB::transaction(function() use ($request, $id) {
       Card::where('user_id', $request->user()->id)
             ->update(['is_default' => false]);
 
       Card::where([
           'id' => $id,
           'user_id' => $request->user()->id
         ])->update(['is_default' => true]);

                    
     });

Almost all high load apps need transactions mechanisms to make “database change” atomic.

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement