ARTICLE
Consistency in the wild
7 April 2023
Mambu facilitates lending services, including personal loans, mortgages, purchase finance and more to over 250 customers in 65 countries, serving more than 90 million end-users. This is double the world’s largest banks, such as HSBC and ING, which serve closer to 40 million. The complexity of the lending business and Mambu’s global reach comes with specific challenges regarding data consistency.
Hundreds of requests are processed virtually at the same time. At peaks, the lending engine can receive more than 5,000 requests per minute from a single bank. Processing each request includes complex validations for multiple accounts. It is not unusual to have financial transactions involving a loan account, a savings account and the bank book-keeping ledger all at the same time. Banks and their customers rely on Mambu to keep their accounts consistent and with no down time.
Mambu’s lending engine relies heavily on the ACID properties of the relational database transactions to ensure consistency of loan accounts. More specifically it relies on the transactions as implemented by the InnoDB storage engine, which is used as part of Mambu’s MySQL deployments.
Sign up a test
MySQL/InnoDB comes with features that support data consistency like transactions, transaction isolation levels and locking. We have found two things to be very important in understanding and working effectively with these features:
- Reading the description of the features in the MySQL documentation carefully (it sounds obvious, but reading the fine print is vital) ;
- Spinning quick tests to exercise these features in scenarios that mimic real life.
In this section, we will focus on the latter point.
One convenient way to set up a MySQL server is to use a docker container on your local machine. This avoids polluting your local machine by installing MySQL directly. The MySQL team publishes a set of official images into the Docker Hub repository along with instructions on how to use them. Another option is to use a cloud platform like AWS, Azure or GCP to access a managed MySQL server instance; however, this may involve additional costs.
Let’s exemplify by means of testing the difference between the READ COMMITTED and REPEATABLE READ isolation levels.
We begin by creating a Docker container that contains the latest MySQL version (8.0.26 as we write this blog post). This is accomplished by issuing the below command in a terminal window.
Once the container is successfully created, you can launch a bash session on the container using:
At the bash prompt, launch the ‘mysql’ command line client and type your password when asked for.
We basically created a database LOANS that contains a table LOANACCOUNT with two accounts and a table SAVINGSACCOUNT with one account. Let’s set the isolation level to READ COMMITTED, start a transaction, and read the content of the LOANACCOUNT table:
Right now, we leave the transaction uncommitted, open a new bash console, start a MySQLclient and use the LOANS database to issue an update.
We go back to the initial console and query the SAVINGSACCOUNT table.
We remark that we are able to see in our first transaction the changes made in the second terminal (the amount was changed from 300 to 300 + 30 = 330). Let’s go to the second MySQL client session and modify the LOANACCOUNT table as well:
In the first console, read again the LOANACCOUNT table. It is changed too:
In conclusion, we can see that if a transaction uses the READ COMMITTED isolation level, the reading results are influenced by the other concurrent transactions.
We repeat the experiment, but this time using the REPEATABLE READ isolation level. COMMIT/ROLLBACK transaction in the first MySQL client session, delete the tables’ content, initialise them again, and type:
In the second MySQL client instance, we modify SAVINGSACCOUNT and LOANACCOUNT as before:
Back in the first MySQL client instance, we read:
This time, no change. The values within the in progress transaction were not impacted by the changes performed after the start of the transaction in the second MySQL client session. So, REPEATABLE READ provides a better isolation for a transaction, and we were able to experience first-hand what that means.
Locking
Another important lesson that we want to share is that when dealing with consistency issues, sticking with simple and well-known solutions usually pays off. If you want to ensure that your database updates are consistent, one common solution is to lock your data for any new change. That prevents anomalies like lost or inconsistent data that can occur because of a race between transactions. We discuss two main types of locking, optimistic and pessimistic, and which one is the best depends on your scenario.
Optimistic locking
Optimistic locking is based on detecting changes on entities by checking their version attribute on the insert time.
In short, you read a record, take note of its version number (which can be one of the following: dates, time stamps, checksums/hashes or counters) and make sure that the version has not changed before you write the record back.
E.g. introducing a VERSION column to the previous table: LOANACCOUNT:
When you update the record, you filter using the previously-read version to make sure it’s atomic (i.e. record has not been modified between the moment when you read the version and the moment when you write into the database) and update the version in one hit.
E.g. working on the example from above, our queries will look like this:
If we imagine two people, Alice and Bob, trying to update the same loan account, as in the below picture, we see that Bob is successful but Alice is not.
The MySQL function ROW_COUNT() can be called to see if changes have been made. In case of zero changes, you will need to take various actions depending on your workflow: retry the entire workflow, rollback if you are in a transaction …
Failing an update due to version check would mean that the computations before the update, made by Alice at step six, were done for nothing. This is a pitfall of optimistic locking. Using optimistic updates in a scenario with a high contention rate may incur a significant loss of resources. For the lending business, updating the same loan account by Alice and Bob is something that rarely happens in real life. A key learning is to check if the same is true for your scenario.
Many of us use Java persistence, either JDO or JPA, and these specifications have provisions for optimistic locking. In both Java object persistence worlds, JDO and JPA, optimistic locking is translated through the use of an annotation — @Version on the entity that we want to lock. If you want to read more on one of these topics you can use the below links:
For full disclosure, Mambu’s lending engine uses DataNucleus’s JDO implementation. If you are not using Java, other ORMs most likely have a similar support.
Pessimistic locking
Pessimistic locking is when you lock the record for your exclusive use until you have finished with it. It is suitable for short-lived operations where no user interaction is taking place.
Compared with optimistic locking, pessimistic has much better integrity as the database prevents any update, but requires you to be careful with your consistency design to avoid deadlocks. If you start locking on more tables, the possibility of deadlocks increases significantly.
In this example, Alice locks the LoanAccount with id=42
When Bob tries to get the entity,
his request will wait until either:
- the resource is unlocked by Alice that finished her work, and finally receives the updated object;
- or an error is thrown, if Bob waited too long.
One benefit of the pessimistic locking is that almost no resource is consumed by Bob while waiting at step four to get the lock. This makes pessimistic locking better suited for scenarios where high contention is expected.
In the Java object persistence worlds (JDO, JPA…) pessimistic locking is done in different ways:
- JDO: By default DataNucleus does not currently lock the objects fetched with pessimistic locking, but you can configure this behaviour for RDBMS data stores by setting the persistence property datanucleus.SerializeRead to true. This will result in all SELECT …​ FROM …​ statements being changed to SELECT …​ FROM …​ FOR UPDATE;
JPA: by using a specific Lock Mode on the query itself.
To be continued
In the article we have tried to share with you two important lessons:
- Test quickly how MySQL/InnoDB consistency features are working using several MySQL clients;
- Keep it simple by using the database support for locking — either optimistic or pessimistic — to ensure consistency in concurrent update scenarios.
There are other important lessons to share, such as using Sagas to ensure consistency when multiple services are involved or picking exclusive or shared locks, but these should perhaps be the subject of a future post.
Keep following our technical blog articles to explore the work we’re doing at Mambu and discover ideas for the challenges you face in your day-to-day work.