Duplicate Entry Mysql TokuDB Wiht Many Clients

Question

I have a strange situation. Suppose I have a very simple function in php (I used Yii but the problem is general) which is called inside a transaction statement:

public function checkAndInsert($someKey)
{
   $data = MyModel::model()->find(array('someKey'=>$someKey)); // search a record in the DB.If it does not exist, insert
   if ( $data == null)
   {
     $data->someCol = 'newOne';
     $data->save();
   }
   else
   {
     $data->someCol = 'test';
     $data->save();  
   }

}
...
// $db is the instance variable used for operation on the DB
$db->transaction();
$this->checkAdnInsert();
$db->commit();

That said, if I run the script containing this function by staring many processes, I will have duplicate values in the DB. For example, if I have $someKey='pippo', and I run the script by starting 2 processes, I will have two (or more) records with column "someCol" = "newOne". This happens randomly, not always. Is the code wrong? Should I put some constraint in DB in form of KEYs? I also read this post about adding UNIQUE indexes to TokuDB which says that UNIQUE KEY "kills" write performance...

Answer 1

The approach you have is wrong. It's wrong because you delegate the authority for integrity/uniqueness check to PHP, but it's the database that's responsible for that.

In other words, you don't have to check whether something exists and then insert. That's bad because there's always some slight ping involved between PHP and MySQL and as you already saw - you can get false results for your checks.

If you need unique values for certain column or combination of columns, you add a UNIQUE constraint. After that you simply insert. If the record exists, insert fails and you can deal with it via Exception. Not only is it faster, it's also easier for you because your code can become a one-liner which is much easier to maintain or understand.