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...

Of course you should put unique constraints if you want *unique records* in your database, I don't know why you're even asking this question. — N.B., Jan 03 '15 at 11:28
Actually, that function is inside is called inside a transaction: if the insert fail because of duplicate entry, all the transaction will fail. And this is not good for my application.... — gdm, Jan 06 '15 at 08:22

score 1 · Answer 1 · answered Jan 03 '15 at 11:40

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.

Thank you for you response. Actually, in the beginning there were such constraints but since that function was inside a very long transaction statement, I could not afford the failure of one insert because all the transaction failed. See my edit. Shall I split the transaction statement in many different statements? — gdm, Jan 06 '15 at 08:26

Duplicate Entry Mysql TokuDB Wiht Many Clients

1 Answers1