Question 190732 in Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel – How can I make a select and insert query thread safe without unique indexes?

I need to check if a row exists, and if it does – update some column, and if not, create a new record.

But the queries I am using are not thread safe, as I do not have unique indexes on that table because I can create new rows with same values based on the last_update value:

$row = DB::table('some_table')
    ->where('last_update', '>=', now()->subMinutes(5))
    ->where('user_id', '=', $user_id)
    ->where('comment_type', '=', $comment_type)
    ->first();

if ($row === null) {
     // record not found, create new
     DB::table('some_table')->insert([
        'user_id' => $user_id,        
        'comment_type' => $comment_type,     
        'created_at' => $created_at,     
        'last_update' => $last_update   
    ]);
} else {
     // record found, update existing
     DB::table('some_table')
          ->where('id', '=', $row->id)
          ->update(['last_update' => now()]);     
}

Is there a way to make it more thread safe?

Edit: What I mean by safe is that, in the above code, there might be a situation where 2 threads reach the code and almost the same time, both of them getting null value for $row and then they will continue to insert a new row, ending up with two records

Tags:

2

Answers


  1. 0

    I don’t know if it is possible without a unique constraint, but one way you can do it is to create shallow copies of the row.

    In your case, you will insert directly regardless of whether the user_id and comment type exist or not in the database and add a timestamp to the row. When you want to read the row, do a SELECT … WHERE … ORDER BY timestamp DESC LIMIT 1. Use a cronjob or something like that to do a database cleanup at intervals.

    Login or Signup to reply.
  2. 0

    Use DB::transaction to guarantee a block is executed exlusively

    DB::transaction(function () {

    $row = DB::table('some_table')
    ->where('last_update', '>=', now()->subMinutes(5))
    ->where('user_id', '=', $user_id)
    ->where('comment_type', '=', $comment_type)
    ->first();

   if ($row === null) {
   // record not found, create new
   DB::table('some_table')->insert([
      'user_id' => $user_id,        
      'comment_type' => $user_id,     
      'created_at' => $user_id,     
      'last_update' => $user_id,     
   ]);

   } else {
   // record found, update existing
   DB::table('some_table')
      ->where('id', '=', $row->id)
      ->update(['last_update' => now()]);     
   }

});

    Transactions will lock your table so beaware about performance issues. You may also check laravel’s updateOrInsert or updateOrCreate methods to combine conditions and query

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search