Accueil > Non classé > InnoDB checks UNIQUE and FOREIGN KEY constraints row-by-row

InnoDB checks UNIQUE and FOREIGN KEY constraints row-by-row

Hi

For my first post here, let me speak about this issue which bogged us down a few time ago :

InnoDB checks UNIQUE and FOREIGN KEY constraints row-by-row

It may sound trivial, but it means that unique and foreign key constraints aren’t transaction safe. But, when using some ORM like hibernate, it’s pretty easy and apparently harmless to switch two values using an intermediate in memory variable (save first value, move second value to the first, put the saved value in the second). In a transaction, it should normally work… But not with innodb !

As such, you’re better off not using unique and foreign key constraints with innodb if you’re serious about transactions, since it could blow up at anytime for actions which should have been possible . Quite a pity isn’t it ?

As as side note, this issue made me realize the value of the documentation’s paragraphs dealing with « Deviation from SQL standards »… which clearly states this kind of deviations, as you can check there : http://dev.mysql.com/doc/refman/6.0/en/innodb-foreign-key-constraints.html for the current issue.

To conclude : beware of innoDb transaction handling !

++
joseph

Publicités
Étiquettes : , , ,
  1. Uwe Schaefer
    juin 15, 2009 à 7:03

    does this change with Falcon, maybe?

  2. Joseph
    juin 15, 2009 à 8:26

    Good question I was willing to look investigate later on.

    Looking at it quickly, it appears that Falcon doesn’t support foreign keys (cf http://dev.mysql.com/doc/refman/6.0/en/se-falcon-features.html).

    Then its syntax looks like not having unique constraints definition possible (cf http://dev.mysql.com/doc/refman/6.0/en/se-falcon-createtable.html).

    Nice transactional engine isn’t it ?

  3. Joseph Pachod
    juin 15, 2009 à 9:27

    Some new update : in fact foreign key constraint seems to be planned at mysql level in some future release of mysql 6 (see http://forums.mysql.com/read.php?133,149493,149493#msg-149493 for more details).

    Not too bad in the end, just have to wait & see !

  1. No trackbacks yet.

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s

%d blogueurs aiment cette page :