• You should not commit within a loop. This has a significant impact on the performance of the microflow. You should commit outside of the loop since then everything is committed at once.
• A Mendix microflow typically consists of one database transaction, which opens at the first activity in the microflow that interacts with the database (retrieve-from-db, commit, delete or create-commit) and always ends (commits everything) at the end of the microflow.

If you look closely, you will find that these statements do not go well with each other. At least, not in the way they are currently formulated: If there is only one transaction which commits everything at the end of the transaction, then what are my hundreds of commits doing that are triggered in a microflow that commits inside a loop? That would imply that a Mendix commit is not a database call. If that is the case, why should I explicitly make a difference in committing outside a loop, since committing inside already doesn’t make a database call. All very confusing in my head…

During the Rapid developer Course the only thing you learn about the commit activity is that it “saves your changes”. This is ofcourse widely interpretable. After introducing my reasoning problem to multiple colleagues from different Mendix levels Ibgot the impression that a lot more people could have interpreted it in different ways, because there wasn’t one clear answer. To get a clear answer, we started an investigation.

Luckily if you actively look though the Mendix documentation you will get some more information about how the Mendix commit activity really works:

“When you commit an object, the current value is saved. This means that you cannot roll back to the previous values of the object using the rollback object activity of a microflow. However, a Mendix commit is not the same as a database commit. For an object of a persistable entity, the saved value is not committed to the database until the microflow and any microflows from which it is called, complete. This means that errors in a microflow can initiate a rollback. If a microflow activity errors and has Error handling set to Rollback or Custom with rollback, the value of the object is rolled back to the value it had at the start of the microflow.”

This section explains that a commit activity isn’t a real database-commit. It also confirms the second initial statement that everything is actually committed to the database at the end of the microflow. But what the Mendix-commit-activity actually does remains up for interpretation. For instance when it is stated that “the saved value is not committed to the database”, you could think that there is no call to the database, so where is it then “saved”? And for the people who are less familiar with raw SQL; what is a real “database commit”?

Let’s start with explaining what a database commit is in raw SQL using the example below:

INSERT INTO customer (name, age, gender, …)

VALUES (“John”, 35, whatever, …);

This line of SQL tries to create a new line in our database table “customer” with some of its attributes filled. If you query this on your local database and afterwards try to retrieve it using the following statement:

SELECT * FROM customer

WHERE name = “John”;

It will return the line you just created. This will make you think you stored it in the database. However you are missing a crucial element if you want to make sure it is really stored in the database, namely: the COMMIT; statement:

INSERT INTO customer (name, age, gender, …)

VALUES (“John”, 35, whatever, …)

COMMIT;

If you leave out the COMMIT statement it will only save it in the database for your session. No other sessions that are connecting to your database will see your record until you’ve added the COMMIT part. Thus, in SQL committing means making sure your data is available for other users in the database.

In order to check what a Mendix commit does we did some simple tests: We created a test environment where we connected a Mendix server running on localhost to an external Postgres database also running on localhost and we turned on the SQL logging in Postgres. Then we created a microflow which created and committed an object and added a breakpoint after it. When we triggered the microflow and the breakpoint was triggered (so the microflow was halted before it reached it’s end) we checked the Postgres logs and we saw an INSERT statement and no COMMIT statement (-yet).

From this test we can with certainty say that a COMMIT activity triggers a call to the database, since the database activity was logged in Postgres. However, this object object has not actually been released for other sessions yet. In this way Mendix can still rollback these session-changes whenever an error occurs. In this sense a Mendix-Commit can be seen more as an SQL INSERT or UPDATE statement.

Coming back to the first statement of my reasoning problem, we now know for sure that committing in a loop is actually making a connection to the database on each commit. Having a lot of round trips to do similar database calls after each other could indeed be the cause of performance reduction and this is a good reason to commit outside of your loop.

But there is more. The Mendix-commit offers more out of the box functionality than only being an insert statement:

If we create a microflow that commits inside a loop and one that commits outside the loop and look into the Postgres logs again, we see the following results:

Logs of committing inside a loop

Logs of committing outside a loop

Spot the difference? When committing outside the loop, each object has it’s own insert statement and after all insert statements, everything is (SQL-)committed in one commit. When committing inside of the loop, actually about the same insert statements are formatted but two extra lines appear for each object:

“SAVEPOINT JDBC_SAVEPOINT[iterator]

RELEASE SAVEPOINT JDBC_SAVEPOINT[iterator]”,

Each Mendix-Commit is automatically adding these SQL SAVEPOINT lines (if there is already previous committed data for this microflow transaction). Savepoints in SQL are used to store a moment in the transaction to which it can rollback to whenever an error occurs/is handled. This is probably explaining why the Mendix documentation says it is “saving the data”.

Now that we have cleared the air about what the difference is between an SQL commit and a Mendix commit. We can maybe try to reanswer some of the frequently asked questions:

If i commit objects in a microflow and afterwards in that same microflow an unhandled error occurs, are my objects then stored in the database or not?

No, they will not be stored in the database because the microflow is terminated before it could get to the hardcoded SQL COMMIT statement at the end of the (successful-)microflow transaction. The database that you did are rolledback by the default rollback mendix behaviour. (they are actually removed from your sessions database storage)

If i commit objects in a microflow and afterwards try to retrieve those objects from the database, will the retrieve action return these objects?

Yes, since the commit action is actually a call to the database, it is stored in the database already for your session. So when your session tries to retrieve data it will find your inserted object already. Another session will not be able to retrieve the object though since it has not been released for others yet (at least until the end of your microflow).

Committing inside a loop VS committing outside a loop:

Like we’ve learned in all mendix courses, committing outside a loop is better for performance. Not only because the reduction of individual database calls, but also that it does not have to manage a lot of unnecessary savepoints.

Conclusion

In the end it all comes down to how we interpret the word “commit” in different contexts. To prevent further confusion when talking about commits we could make sure that we are either talking about a Mendix commit, which is a call to the database and a creation of a savepoint or that we are talking about an SQL commit, which releases the database changes for other users of your app/database.