1

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

pileup
  • 1
  • 2
  • 18
  • 45
  • 3
    What you mean by "thread safe" in this context? Also, if you don't have unique indexes, then what is this `->where('id', '=', $row->id)`? Isn't `id` a unique index? If you want to make sure all rows are unique even without the id, then create another index on that table based on the columns you want to have a unique combo and set that index as "unique"? – M. Eriksson Jun 21 '23 at 09:45
  • Yes `id` is unique but after the record is created. In the above case there might be a case where 2 threads reach the above code in almost the same time, then it will create 2 records because both of them will have a `null` value for the `$row`, and `id` doesn't exist yet so it might insert two rows (I updated the post with this information) – pileup Jun 21 '23 at 09:53
  • 1
    So create a unique index, as I suggested in my first comment? Btw, in your insert, you insert `$user_id`. Aren't those unique per user at least? Or are you worried that the same user will create multiple rows at the exact same time? You also seem to be setting all values to `$user_id` in the posted code, so it's unclear what the table even contains at this point. – M. Eriksson Jun 21 '23 at 10:16
  • @M.Eriksson yes exactly what you said: the combination is unique, but worried for exact same time – pileup Jun 21 '23 at 10:26
  • 1
    If you create a unique index, it won't be able to create duplicates for those values. That's the whole point of having unique indexes. You also didn't really answer any of my questions. – M. Eriksson Jun 21 '23 at 10:28
  • Omg I now noticed what you said about the `$user_id`, misread it. It was a typo when I copied the lines. updated. sorry. Regarding the unique index - but won't that still leave the issue of inserting multiple rows when not needed if the same user does it the same time? (because I need `where('last_update', '>=', now()->subMinutes(5))`) – pileup Jun 21 '23 at 10:45
  • If you create a [unique composite index](https://stackoverflow.com/questions/635937/how-do-i-specify-unique-constraint-for-multiple-columns-in-mysql) on, for example: user_id, column_a and column_b, and try to insert another record with the same values for user_id, column_a and column_b, the database will throw an error if you try to add another row with the same values for those columns. Again, that's the whole point of creating unique indexes, to make sure there won't be any duplicates. – M. Eriksson Jun 21 '23 at 12:29

2 Answers2

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.

Anthony Poon
  • 837
  • 6
  • 14
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

Wildfire
  • 162
  • 2
  • 16