Concurrency, MySQL and Node.js: A journey of discovery
Our story begins like so many others with a code loving protagonist — someone we all can relate to. His days are largely filled with designing code, writing code and reading about code — keeping clients happy while learning and having fun. This has been going on for years now with both MySQL and Node.js among others and as such our protagonist considers himself quite proficient with both those technologies.
One day however, a wild bug appeared — one that seemed to defy reason. A thing happened twice where only once was allowed. “Not possible”, cried the protagonist and holding his head mumbled, “But.. I have already handled this”.
So begins our journey of discovery into the intricacies of concurrency with MySQL and Node.js.
Before we wander into the unknown, let’s first take a small refresher on concurrency and how it is usually handled. After that reminder, we’ll be ready to take the plunge with our protagonist to the world of misconceptions and common errors that might be lurking out there (assuming he is not the only one who made those mistakes).
The problem with concurrency
In this section, we will briefly look into what concurrency is and what problems it brings. For a deeper look into this subject, I recommend RisingStack’s dive into Concurrency and Parallelism.
In a nutshell, concurrency in computing is a situation where programs or parts of a program run in overlapping timeframes. Problems with this arise when both want to modify the same common resource. A common example used to illustrate a concurrency error is double processing during withdrawal.
Imagine that you are developing a bank service — you have users, with balances, who can deposit and withdraw money. A simple withdrawal flow can look something like this: check if user has money, and if they do, decrease their balance and hand out money, but if they don’t, return an error.
Sounds simple enough, but what if the same user makes two withdrawal requests at the same time (with very short intervals) for the full balance. Suddenly we have a problem — while the first request proceeds as we would expect, the second one also succeeds, because the very moment we calculate the balance, it has not yet changed. So our user gets double the amount, as if from thin air.
Locks, locks everywhere
There is no easy solution to for a concurrency issue where multiple agents want to modify the same resource in parallel. The most prominent solution is to just avoid concurrency by using locks. As concurrency gives a significant performance boost, having everything run serially is not really an option, which is why programs use locks to run serially only when needed.
Using locks for our withdrawal problem could look something like this: when we get a withdrawal request, we try to acquire a lock. If there already is a lock, either wait or return an error, otherwise proceed as before. After balance has changed, we release the lock and carry on with the rest of our work.
Now we have solved the issue of double processing at the expense of speed — as long as one process holds the lock, others have to wait. So we have changed our concurrency issue into one of optimisation — minimising the time and area that is handled by the lock.
When moving on to implementation, there are loads of cases we have to handle: acquiring the lock without creating a concurrency issue itself, handling cases where the program holding the lock crashes or halts, handling cases where the whole system crashes or halts etc. While seemingly simple, it gets complex fast and, as such, it is in our best interest not to actually write lock mechanisms, but instead use existing systems.
MySQL — atomic operations and transactions
Since databases are probably one of the most common sources of concurrency issues, they usually come with specific methods to deal with concurrency. At minimum, they need to have atomic operations — these are operations that can be performed with the data, which are atomic (unbreakable) in nature.
In MySQL, all modifying operations are atomic — this operation can either succeed or fail, but by design, it should never half succeed or half fail. Neither can it run at the same time as another modifier on the same row — one has to run first. In other words, an atomic operation is an operation (usually a small one) that runs as a single unit and avoids concurrency issues with other operations in the database, usually by using some form of internal lock system.
MySQL also has transactions for times when we are unable to design our data in a way that atomic operations are enough. Transactions are a way to group operations into a single unit that can avoid half succeeding or half failing between operations. They also allow us to handle concurrency problems by utilising transaction wide locks.
A journey of Discovery
Now that we have had a small refresher on the basics of concurrency, its issues and common solutions with MySQL, we are ready to continue our journey with the protagonist.
At first glance, the knowledge we covered should be enough to solve concurrency issues, but apparently it is not — there are a few nuances that don’t seem to get enough attention, which we shall go over in this chapter.
First expedition into the familiar unknown
Our protagonist found himself in a situation where he had used MySQL transactions to surround withdrawal process operations (a commonly used example for a reason), only to find that someone had indeed managed to withdraw more money than they had. It seemed impossible and yet, there it was — plain as the screen in front of him.
Some keyboard shaped dents on his forehead later, the problem revealed itself — optimisation. As covered in the previous section, solutions to concurrency using locks tend to translate it into a problem of optimisation. MySQL and other databases have taken this to heart and come up with various different methods to avoid locking rows and tables as much as possible. One of those changes is the fact that read queries within transactions do not actually lock those rows. So the illustration of withdrawal using transactions is actually flawed — the calculation of a user’s balance using SELECT is subject to concurrency. To balance this, MySQL has introduced new keywords to enforce read locking as needed.
SELECT… FOR UPDATE — Adding FOR UPDATE to the end of the select statement will lock these rows, as if doing an UPDATE statement. This means other transactions can’t lock or modify these rows.
SELECT… LOCK IN SHARE MODE — This is a somewhat in-between lock. It does not fully lock the rows as the UPDATE. Instead it puts a soft lock on those rows, which stops other transactions from modifying these rows, but they can run select statements, and once your transaction commits, will use the new values to continue.
So to fix our issue, we have to actually add these modifiers to our SELECT statement to enforce locks and actual expected behaviour. Personally, I find you should start with using FOR UPDATE and if there are performance issues, then look into possibly using SHARE MODE.
After quite a few swear words, the protagonist exclaimed “fine” and rushed to apply the newfound knowledge. A few days after the fix had gone to live, a new wild bug appeared. In some situations, and unfortunately not so rare situations, the whole database locked down.
Now to clarify, when using locks and especially transactions that span and lock multiple tables, it is very important to be consistent with the order of operations. If we have multiple transactions that require multiple tables, we run the risk of creating a circular lock. This is because MySQL creates locks as needed, and if your transaction first modifies Table A followed by Table B, this will lock Table A first and then Table B. If, at the same time, we have a different transaction that modifies the same tables in the opposite order, we risk running into a situation where Table A has been locked by one transaction, Table B locked by the other, and both wait for the other transaction to release their lock.
Our protagonist knew all this and had already accounted for it— and so, since the problem persisted even after double checking the order of locks, we find ourselves at the beginning of another journey.
Second expedition to the familiar unknown
Prepare yourselves — so far we have talked exclusively about MySQL and concurrency, but now in order to get an understanding of this subplot, we have to look at actual code for a change.
As looking at actual production code is not possible due to NDAs and the codebase being large and complex, not to mention the limited time everyone has, we will instead construct a simple example. Let’s start by defining our database connection with mysql driver and wrapping it in a Promise just to make it pretty.
Now let’s create the database, add some data.
And now we can require the mysql-wrapper and just send a transaction to the database as we want.
Everything looks good — if we insert enough funds for the account in the transactions table, the query succeeds, and if there is not enough, then it will get a ER_BAD_NULL_ERROR, which is exactly what we are going for.
But now, if we try to run the same file twice concurrently, we will find that something is wrong —no error, no success. The second query simply hangs there and does not complete until we get ER_LOCK_WAIT_TIMEOUT.
So our first query completed with an error as expected, but the second query ran into a lock — what the heck is happening?
Well, it turns out that even if you have AUTOCOMMIT flag set, then using mysql module, and sending the whole transaction as a single string does not actually treat it as such. The statements are executed as if sent one by one and while we did get the right error at the right time, the transaction was not actually terminated. No COMMIT was sent, the connection is still up and so is the lock. The worst part is that it will not time out — it will stay there until we either end a COMMIT/ROLLBACK on the same connection or terminate the connection, at which point MySQL will automatically trigger a ROLLBACK.
This also means that if we continue using this connection as if the transaction would have been completed, then we keep issuing statements that are all added to the transaction we started ages ago.
So how do we handle this? Probably the most foolproof way is to simply terminate the connection every time an error occurs. This, of course, won’t work with our simplistic example, as we have no mechanism that would spawn a new connection, but you get the gist of it.
This approach is simple and robust, which of course means that the downside is performance. If we simply toss the connection every time something happens, we will usually do it for non-transaction connections. This means we have to reconnect to the database all the time and we end up losing our valuable processing power.
Now, a more nuanced solution could be to add an extra parameter to our input to automatically rollback on error if it is set.
This way we can specifically tell our DB layer to send ROLLBACK when we know we are sending it a transaction. This also means we don’t have to kill the connection and pay the price of setting up a new one.
So this is another thing you should keep in mind when doing DB queries in Node.js using mysql directly and not relying on some query builder library or ODM that handles it for you (I suggest you check if it does).
Another day another journey
What can we take away from all this? Well, if you are anything like our code loving protagonist, you will gleam that there is always something new to learn. Even if you might think that you are well versed in the technology in question, times change, things change, and unless you learn all the time, you’ll find yourself at the beginning of another journey down the rabbit hole of bugs.
Epilogue
As a testimony to the previous sentence, I ran into another interesting “feature” of MySQL while writing this blog post.
Let’s say we happen to have a table with the following data.
Now, if we were to run a query such as SELECT * FROM transactions WHERE description = 2;
What do you expect the outcome to be?
If you expected it to only have one row, like I originally did, then you would be wrong. It appears that if we are dealing with textual columns and you query it with a numeric value then MySQL tries to parse the string into a number, and ‘2 stones on a rock’ will also end up being number 2, matching the query.
Adding quotation marks around 2 in our query would solve the issue, but if you send it as a number, you might be in for a nasty surprise.
It goes to show that there there are always new quirks just around the corner and you might need a pillow on your table to save yourself from a concussion.
