MySQL deferred constraints and unique checks

Diario del capitán, fecha estelar d227.y40/AB

MySQL and PostgreSQL are very similar but not exact. Take a look at this scenario that works with PostgreSQL but not with MySQL.

Hard disk - Photo by Jandira Sonnendeck

The SQL standard says that, by default, the constraints check (foreign and unique keys) should be deferred. This means that the DB server won't check those before a transaction is commited.

Unfortunately MySQL is not following this standard and this could cause problems like the following one.

I have a unique key on a position column. I don't want to have repeated keys there. And I want to change the position of 2 elements with this initial state:

            id=1, position=2
            id=2, position=1

… to this state:

            id=1, position=1
            id=2, position=2

You would do it like this:

            UPDATE table SET position = 1 WHERE id = 1;
            UPDATE table SET position = 2 WHERE id = 2;

This will work on PostrgreSQL but will fail in MySQL. Because MySQL checks the unique constraint inmediatelly and PostgreSQL will wait until the Transaction is commited.

Xavier Redó

Xavier Redó

Responsable de arquitectura y desarrollo. Es el culpable de que trabajemos con Ruby on Rails, Node.js, React y Angular, entre otros. Pocos desarrolladores que conozcamos tienen sus habilidades sociales.

comments powered by Disqus

Estás a un paso de conocer a tu mejor socio.