Is it possible to rollback an autonumber if the user cancels before the object is committed?

9
I assume not - as it would cause problems with multiple users creating objects simultaneously. However it is a common requirement to generate sequential numbering with an unbroken sequence, so can anyone offer any suggestions for the best/most efficient way to achieve this in mendix? Normally in an SQL-based solution I would either use an SQL max() query against the database or hold the highest number used in a separate table, and use some kind of commit/rollback/retry processing with database constraints etc. to ensure that uniqueness and consistency are maintained. Any ideas? Thanks. UPDATE * Re. Michel's answer below. Using the total number of objects approach, how would Mendix handle the situation where 2 concurrent users try to commit an object at exactly the same time? I would guess, from what I've experienced so far that both objects would be included in the total number, therefore Mendix would try to allocate the same number to both of them, and would also potentially have missed a number in the sequence. However this may depend on how it's handled by the underlying database. Can anyone clarify? Also, as the database grows this will become progressively less efficient, as I believe Mendix will need to retrieve & instantiate a list of all objects before it can perform the Count or Max functions. Is this correct? Thanks. UPDATE 2 * I can't find any documentation on 'concurrent execution' or 'sleep' - so how do I use this? I have two mciroflows; 'ObjectBeforeCommit' and 'ObjectUpdateSequenceNumber' ObjectBeforeCommit does nothing except call 'ObjectUpdateSequenceNumber'. Object_BeforeCommit has disallow concurrent execution = false Object_UpdateSequenceNumber has disallow concurrent execution = true What I want to include in ObjectBeforeCommit is some processing so that if ObjectUpdateSequenceNumber fails because it is already being run then Object_BeforeCommit will try again until it succeeds. How do I do this in a microflow? Thanks UPDATE 3 * Just realised - this can't be done in a before commit event, as this will be run everytime an update is committed & not just when the object is committed for the first time. It can't be done in a before or after create either, in case the user cancels. So it would probably have to done in a custom save button which is only used when the object is first created & saved, and not for subsequent updates.
asked
4 answers
6

You could use "disallow concurrent execution" in combination with the before commit microflow mentioned by Michel to prevent concurrency problems. Upon concurrent execution you could execute a microflow which will sleep for a while and then tries again. This will go on until it succeeds.

Microflow aggregate functions are optimized automatically if the variable resulting from the retrieve activity used as input for the aggregate function is not used elsewhere in the microflow. So instead of retrieving a list and counting the number of objects an SQL COUNT(), MAX(), .., function is called directly in this case.

UPDATE (in response to UPDATE 2 and 3)

What I would do is a make an "ObjectBeforeCommit" microflow in which the sequence number is incremented and set "disallow concurrent exeuction" to false. When you tick "disallow concurrent execution" you can choose to display an error message or to execute a microflow when an attempt for concurrent execution is encountered.

For this particular case I would choose to execute a microflow which will sleep for a while (e.g. by calling a java action which calls Thread.sleep() ) and then call the "ObjectBeforeCommit" microflow again. This process will continue until it succeeds.

In the before commit microflow you should check whether the object is new (with the isNew() function call in microflow expression). This will return true if the object has been created but not yet committed. In that case the sequence number should be incremented, otherwise nothing has to be done.

answered
4

I think creating sequential numbers is not the intention of autonumbers. However a solution to your problem might be using an attribute which is set in the before commit to the total number of objects. (which, when all number are sequential and start at zero, equals the highest number + 1)

Another solution is to use the max aggregate function, and add 1 to it.

answered
4

I do not dare to say what will happen in your case when two concurrent users commit an object at the same time.

Concerning the second question; max and count are translated and executed at the database level, so the performance penalty is not that bad; the runtime does not retrieve and instantiate all objects to calculate aggregates.

(i will notify the r&d guys on the question)

answered
2

If you want an unbroken sequence you should generate the number when nothing can fail, so not on create but with an update doing just the number generation.

You need some kind of locking to ensure unique numbers and to only give out the next number when the current is committed. I don't know how to lock tables/records in Mendix.

answered