Avoiding UniqueConstraintViolationException due to Doctrine commit order

The problem

Doctrine has a well defined internal order for the persist operations it executes during a flush.

It’s called “commit order” and it’s the following:

1. all entity inserts (topological order)
2. all entity updates (topological order)
3. all collection deletions
4. all collection updates
5. all entity deletions (reverse toplogical order)

This can cause problems on certain types of changesets if the entities have unique keys.

As an example, imagine you need to model a warehouse where each product has a location and only a single product can be stored in each location.

Your Product entity will have a location unique field.

Given the following initial state:

• Product A in location 1
• Product B in location 2
• Product C in location 3

If your application has a form that lets users change multiple products in a single request then the following scenarios are possible:

• Deleting product A and creating a product D with location 1
• Swapping locations of products B and C

Both operations will cause a UniqueConstraintViolationException because of Doctrine commit order as explained in detail in the issue DDC-604. In the scenario, product D INSERT is executed before product A DELETE, which causes a temporary violation of the unique key for location 1. In the second scenario, the first of the two UPDATEs causes the locations of products B and C to be the same before the second UPDATE has the chance to restore the uniqueness. MySQL checks the constraints during each statement and it can’t be configured to perform the check on transaction boundaries as some other RDBMSs do, so both scenarios will cause a UniqueConstraintViolationException.

The problem has a workaround for the first scenario: you can do the deletions first, flush, do the insertions, and flush once more, while wrapping everything in a single transaction. Unfortunately, this solution doesn’t solve the problem in the second scenario, and it’s difficult to apply in many real world applications, for instance when you need to change multiple entities in a single Symfony form just like the example above.

Solution

MySQL allows unique keys to have nullable fields, and having a null value in a unique key on multiple columns excludes the row from the constraint check.

A UNIQUE index permits multiple NULL values for columns that can contain NULL.

For example, if you have a table with a unique key defined on fields A and B, with field B being nullable, then the row A="FOO", B=null can be inserted multiple times, but the row A="FOO", B="BAR" can be present only once, and the same goes for A="FOO", B="".

This means that we can use an additional nullable field as an enabling flag for the unique key in each row: we only need to add an enabledUniqueKey nullable field to our product table and include it in the location unique key, and its possible values will be either 1 (default) or null.

Normally all the rows will have 1 in this field (meaning that the key is enabled on all the rows), but during the flush operation, before the statements execution, it will be set to null in all the affected rows. By doing this, the key will be disabled for all the rows that can potentially generate a temporary violation of the constraint. Eventually, when all the statements have been executed, we will need to reset the field to 1. This way, the unique constraint will never be violated, no matter the statement execution order. Of course, this process needs to be wrapped in a transaction so that it will not be possible to be left with rows with a disabled key.

Warning: This method addresses only the temporary key violations caused by the Doctrine commit order: it doesn’t prevent any real violations of the unique key to generate a UniqueConstraintViolationException. These violations still need to be avoided by using a validator.

Implementation

Let’s add the new field:

The process of disabling and re-enabling the key can be implemented in a listener: the keys can be disabled in the prePersist event for the INSERTs and in the preUpdate for the UPDATEs, but they need to be re-enabled after all the INSERTs and UPDATEs have been executed and after disabling the key for the rows that will be deleted. This can be done using the UnitOfWork::scheduleExtraUpdate() method. These extra updates are executed just after the updates already computed for the flush.

This is a timeline of the process:

Step	Statement type	Operation	Notes
prePersist event	INSERT	Disable row key
insert row	INSERT	Insert row
postPersist event	INSERT	Enable key in object instance in memory and schedule extra update to enable the key in the DB
first postPersist or postUpdate	DELETE	Disable key directly in DB for each row that will be deleted
preUpdate event	UPDATE	Disable row key
update row	UPDATE	Update row
postUpdate event	UPDATE	Enable key in object instance in memory and schedule extra update to enable the key in the DB
extra update	INSERT and UPDATE	Enable key in the DB on all the inserted and all updated rows	The keys can be re-enabled because at this point the possible temporary collisions between inserted and updated rows have been already resolved. Possible collisions with rows that will be deleted aren’t a problem because the keys for those rows remain disabled until their deletion.
deletion	DELETE	Delete rows

 

Please note that disabling the key is done on the entity which is then persisted by Doctrine, but enabling it is not automatically reflected on the database. For this reason, we need to manually do it on the object and schedule an extra update to keep the database in sync.

This solution is a team effort and was developed jointly by Angelo Milazzo and Alessandro Friscia. It has been in use on our systems for several weeks, and no problems arose. We hope you too can benefit from it.