Transaction not working as I expected in Laravel to prevent duplicate entries

user_2912101 0
Viewed 32

Within a Laravel-based platform there are three relevant tables :

  1. users.
  2. codes.
  3. users_codes.

Users can claim a code - in which case, what should happen is that the system gets the next available code from the codes table, marks it as allocated, and then sets up a new entry in users_codes which ties the user to the code.

Nothing special would need to be done whilst the site is under low load, however anticipating high load, when initially written this was done as a Transaction, which I had thought would prevent the same code being allocated twice.

    DB::transaction(function () use($user) {
        $code = Codes::getNextAvailableCode(); // Not the actual function, but works for the sake of example
        $code->allocated = 1;
        $code->save();
        $uc = new UserCode();
        $uc->user_id = $user->id;
        $uc->code_id = $code->id;
        $uc->save();
    });

Now that the site is under high load, a couple of times the same code has been allocated to two different users. So it's clear that a Transaction isn't what I want.

Thinking it through, I initially thought that replacing the Transaction with locking would be an option, but the more I think about it, I can't really lock a table.

Instead, I think I need to be focussing on checking, before creating and saving the new UserCode() to ensure that there is no existing UserCode with the same $code->id?

Any suggestions? Is there a way that I've not considered that will allow this to work smoothly under high load (ie. not continually throw errors back to the user when they try and claim a code that has been taken a millisecond before)?

laravel mysql
1 Answers

You want PESSIMISTIC_WRITE type lock which allows us to obtain an exclusive lock and prevent the data from being read, updated or deleted.

Laravel's query builder supports Pessimistic Locking.

The query builder also includes a few functions to help you achieve "pessimistic locking" when executing your select statements. To execute a statement with a "shared lock", you may call the sharedLock method. A shared lock prevents the selected rows from being modified until your transaction is committed:

DB::table('users')
   ->where('votes', '>', 100)
   ->sharedLock()
   ->get();

I think you definitely need table lock instead of Transaction.

user_52019140
Related