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:

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.

Code:

use App\Models\Card;

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?

CodePudding user response：

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.

CodePudding user response：

You can try to change the code for updates.

In first set is_default = 1 for default card.

In second set is_default = 0 for others card exclude default card.

Don't know how to make this is your "framework" but in SQL it should by two query For example, set card with id 1 as default

UPDATE cards SET is_default = 1 WHERE user_id = 50 AND id = 1;
UPDATE cards SET is_default = 0 WHERE user_id = 50 AND id <> 1;