We are facing the below issue with the database ID generator.
org.camunda.bpm.engine.OptimisticLockingException: ENGINE-03005 Execution of ‘UPDATE PropertyEntity[next.dbid]’ failed. Entity was updated by another transaction concurrently.
And, we came to know that the UUID generator helps with the high level of concurrency as recommended by Camunda.
However, we would like to understand it better from the Camunda team for the reasoning behind proposing the UUID generator and not keeping the separate numeric sequence generator for each table( generally Oracle does the same thing and MySQL maintains implicit sequencing for each table). Since numeric primary id will give better performance with indexing.
We are a bit surprised that Camunda had a single UUID generator for all tables even when the tables are not related - Instance table, Task table, etc. Why would I need a unique ID across tables? The primary key is only table-specific.
Why would Camunda generate an alphanumeric UUID and that also 25 characters? This is a nightmare in the making. The index on the primary key is going to be extremely slow.
Why are we not leveraging database id generation for a unique primary key generation? Databases have the best transaction handling - Why do we need to reinvent the wheel?
Having a single ID generator is a simpler design than one per table.
If that is correct, then the nightmare has been already made. This ID generator is the default in the product for 8 years now, we haven’t received a lot of (if any) issues with this root cause. Nevertheless, I agree that a shorter key length is more efficient. Feel free to implement your own ID generator. It’s pluggable.
Since the design decision was made long in the past, I can’t tell you the exact reason at the time. Some thoughts:
The current design works the same across all supported databases and keeps application complexity low
I think in some cases the application needs the ID before the entity is inserted into the table (potentially acquiring locks). I guess that would have to work differently with database sequences, but I am certainly no expert on them.