This article starts with a reasoning problem. Over time you learn two Mendix principles:

  • You should not commit within a loop. It 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 that interacts with the database (retrieve, commit, delete or create-commit) and always ends, committing everything, at the end of the microflow.

Look closely and these statements do not go well together, at least not as formulated. If there is only one transaction that commits everything at the end, what are my hundreds of commits doing in a microflow that commits inside a loop? That would imply that a Mendix commit is not a database call. And if that is the case, why should I make a difference between committing inside or outside a loop?

What the documentation says

The Mendix documentation gives more information about how the 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 explains that a commit activity is not a real database commit. It also confirms the second statement: everything is actually committed to the database at the end of the microflow.

SQL commits and savepoints

In raw SQL, when you execute an INSERT statement, the data is only visible in your session until you execute a COMMIT. Committing means making your data available to other users of the database.

A Mendix commit is actually more similar to an SQL INSERT or UPDATE plus a SAVEPOINT. It triggers a database call immediately, but the data is not released to other sessions (the SQL COMMIT) until the microflow completes successfully. So every commit activity in Mendix sends the data to the database immediately, creates a savepoint for a potential rollback, and keeps the data invisible to other sessions until the microflow ends.

Performance implications

This explains why committing inside loops has a performance impact. Each commit makes a round trip to the database, creates a new savepoint and adds overhead to transaction management. Committing outside the loop means only one round trip and one savepoint, regardless of how many objects you are saving.

Conclusion

Understanding the difference between Mendix commits and database commits is crucial for writing performant microflows. Commit outside of loops when possible, and remember that the actual database commit only happens when your microflow successfully completes.