Archive

Posts Tagged ‘transaction’

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

juin 14, 2009 3 commentaires

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

Étiquettes : , , ,
%d blogueurs aiment cette page :