Retry if Unique Constraint Violation Exception occurs

david.sporer · December 4, 2019, 8:46am

Hi,

I did some performance tests and from what I tested it is faster to try to insert something that is supposed to contain a unique ID (that a customer may provide) and let it fail compared to checking if the value the customer did provide is really not used, yet and then inserting it.
The only thing I’m struggling with is that if I have a list of 10 IDs where the system is always taking the next ID and uses it the Insert will fail with a UniqueConstraintViolationException which I can catch. In this case I’d like to just retry it with the next number from that list. The issue I have is that the EntityManager gets closed when the exception is thrown which makes it impossible to simply get the next number from the database and retry it.
Is there any way to work around this (e.g. reopening the EntityManager) or should I try to find another way of handling it?

aberl · December 4, 2019, 11:49am

Hey David,

if you need incrementing, possibly user provided IDs, you will not fully get around this issue in any way. It’s like the distributed systems law of thermodynamics. Also the thing with doctrine EntityManager is, that any database level exception is considered invalid state and no longer to be trusted, hence why the EntityManager closes for good. Reopening is theoretically possible with some effort, but I’d rather try to solve the issue in the domain.
You should either use truly unique IDs (i.e. UUIDs) or you need some ID providing service that tackles all the requirements. If you need IDs to be incrementing and without gaps though, this will be super hard to achieve. If gaps are allowed then that’s doable with a simple monotonic counter but the whole system process is still serialized on that ID generator. Depending on how loose the requirements on the ID can be, you can trade back some scalability and complexity (e.g. if they don’t need to be in order).

In general: avoid incremental identifiers wherever possible!

sorenmalling · December 4, 2019, 12:00pm

I would address it like the IdentityRoutePart does it, to ensure no duplicate

github.com

neos/flow/blob/8.3/Classes/Mvc/Routing/IdentityRoutePart.php#L297


      
              return $pathSegment;
          }
          
          /**
           * Creates a new ObjectPathMapping and stores it in the repository
           *
           * @param string $pathSegment
           * @param string|integer $identifier
           * @return void
           */
          protected function storeObjectPathMapping($pathSegment, $identifier)
          {
              $objectPathMapping = new ObjectPathMapping();
              $objectPathMapping->setObjectType($this->objectType);
              $objectPathMapping->setUriPattern($this->getUriPattern());
              $objectPathMapping->setPathSegment($pathSegment);
              $objectPathMapping->setIdentifier($identifier);
              $this->objectPathMappingRepository->add($objectPathMapping);
              $this->objectPathMappingRepository->persistEntities();
          }

It has the PersistenceManager persist any new route identity along the way of creation.

david.sporer · December 4, 2019, 12:18pm

Thanks!
The IDs I’m talking about are not incrementing but they have to be unique. In general our system uses cryptographically secure functions to generate the IDs (string and number combinations) and writes them to a column with a unique index. The issue is rather when a customer provides the ID (or the very unlikely case that there’s a collision between our IDs). In these cases the system could try to insert an already existing ID a second time but on the next call we can be pretty sure that there’s no collision.
Performance-wise the issue is that in 99.999% of cases it would simply be a waste of resources to check if an ID is already present in the database since that barely ever happens.
That’s the reason why my idea was to just catch the exception and retry the whole thing if that happens.