Wrong IsLatest value due to exceptions during draft saving in Elsa 2

[alessandro-gandolfi-ors]

Wrong IsLatest value due to exceptions during draft saving in Elsa 2

[glime-ai]

The issue you're describing seems to be related to a concurrency problem in the Elsa Workflow definitions update process. The error message indicates that there is an attempt to insert a duplicate key row into the WorkflowDefinitions table, which violates the unique index constraint IX_WorkflowDefinition_DefinitionId_VersionId.

This kind of problem can occur when the operation to update the IsLatest flag and the creation of a new draft are not atomic, and an exception occurs between these two operations. If the IsLatest flag is set to false for the current latest version, but the new draft is not created due to an exception, you end up in a state where there is no "latest" version, which causes subsequent operations to fail.

As for why transactions are not used, it's hard to say without more context. Transactions are a common way to ensure atomicity of operations, so their absence might be an oversight or a design decision based on other factors such as performance considerations or the underlying database support for transactions.

To address this issue, you mentioned a pull request (PR #3752) that inverts the order of operations for Elsa 3. Backporting such changes to Elsa 2 could indeed help to mitigate the issue by ensuring that the new draft is created before the IsLatest flag is updated. However, the best solution would be to wrap these operations in a transaction to ensure that either both operations succeed or neither does, maintaining database consistency.

If you are experiencing this issue frequently, it might be worth raising it on the Elsa GitHub repository as a bug or enhancement request, if you haven't already. The maintainers of the project would be best placed to explain the design decisions and potentially address the issue in a future release.

In the meantime, as a workaround, you might consider manually handling the transaction in your code when updating workflow definitions, if the Elsa API allows for it.

[sfmskywalker]

Elsa 3 has mastered this logic flawlessly. You might consider backporting the logic from there and submitting a pull request.

[alessandro-gandolfi-ors]

I'm asking my boss if I can spend time working on it, it would help us but there is already the idea to migrate to elsa 3 in the future...

Meanwhile, could you elaborate a bit on why those operations are not atomic?

[sfmskywalker]

Absolutely, the abstraction of the persistence layer—encompassing EF Core, MongoDB, Dapper, and possible third-party implementations—makes handling this from the application layer quite practical. Given the smooth operation with Elsa 3 and workflow publications, there hasn't been a pressing need to alter this method. Nevertheless, I'm very interested in any suggestions for integrating this logic within a transaction that's compatible across all persistence providers. Your insights on achieving this would be greatly appreciated.