Unique Foreign Keys and Concurrency: Mendix and Me
Even though Mendix manages relations via the domain model and you usually won’t come into contact with keys and foreign keys, it is very likely that you will reach a point where you need them. Typical examples are the avoidance of cross-module associations or data that you receive from a third-party system and want to cache locally.
In many cases, it is necessary for a key to exist only once. For example, if you synchronize records from a foreign system, you want to avoid storing the same record multiple times. A simple but effective approach is to use the CreateOrRetrieveIfExisting pattern.
Let’s look at the following data set:
The identifier attribute is the unique key. If a record with a certain key is needed, a CreateOrRetrieveIfExisting microflow can be used with an existing record or, if there is no such record, to create a new record. This is used in event-driven systems where an event in another system creates or changes a record that must be kept synchronous. The corresponding microflow would now look as follows:
For a given identifier, the system now searches for a data record from the database and creates it if necessary. This way, you can ensure that you always get a record and that a record exists only once.
The Problem: Concurrency
This pattern works perfectly, but has one problem: It cannot handle concurrency. Especially (but not only) with event-driven architecture, there can be several events within a short time that all refer to the same record. If the record already exists, this is not a problem. If, however, it does not exist at this point in time, several processes are running that know nothing about each other. None of these processes can find the object in the database because the transaction of the other processes is not finished at this time. This is especially dangerous if the process has a long runtime. The result is that each of these processes creates the record over again, with the result that the record exists several times in the database.
The Solution: Database-Level Validation and Retry Mechanism
First, we need to create a unique constraint in the database. Fortunately, this can be done easily using the Mendix Domain Model. To do this, we create a validation rule for the identifier that looks like this:
The database now does not allow the same value to exist more than once. If you try to commit an object which has an already used identifier, an exception is thrown.
Of course, we do not want our process to terminate with an exception. Ideally we want to know the problem already when we generate it. For this, the CreateOrRetrieveIfExisting microflow can be adapted as follows:
The object is now saved directly when it is created. This leads to the fact that a database lock exists and other processes must wait if necessary for the end of the first process. If it turns out that the first process has already created the object, the create action throws an exception.
Now we know there is a problem as soon as we create the problem. However, it would be more convenient if our process would continue successfully and not terminate because of an exception. If we extend the microflow with error handling in combination with a retry mechanism, we can achieve exactly this comfort.
The IsRetry variable is a boolean which is initialized with ‘false’ and changed to ‘true’ in case of an error. This ensures that the retry is attempted only once. More is not necessary for this problem.
If the first process now ends its transaction, an exception is thrown in the Create Action in the other processes. This is handled by starting the microflow again from the beginning. Due to the completed transaction of the first process, the data set is now found in the database and can be used. Parallelism still exists if different data sets are created in parallel. If it concerns the same data record, then with the first creation process (if necessary) one waits for the fact that the first process is locked. These queries are thus processed serially, while in the further course parallelism is again given.
I hope this article is helpful. As always, I am happy to hear any feedback.
Originally published in German at https://mendixandme.de on March 31, 2021.
From the Publisher -
If you enjoyed this article you can find more like it at our Medium page or at our own Community blog site.
For the makers looking to get started, you can sign up for a free account, and get instant access to learning with our Academy.
Interested in getting more involved with our community? You can join us in our Slack community channel or for those who want to be more involved, look into joining one of our Meet ups.
