How to deal with SQL Blocking on Node
The complexities of handling concurrency successfully in a Node.js context can be overwhelming, especially if you are new to the concept and are sideswiped by bugs that emerge from it.
To prepare you for the troubleshooting tasks that lie in store when wrangling the code for an SQL database, here is a look at why concurrency can cause blocking and what steps you can take to overcome this.
Image Source: Pixabay
A quick overview of concurrency complications
In order to function efficiently and swiftly, an SQL server takes advantage of concurrency in order to keep multiple processes running simultaneously. Conflicts can occur if two or more processes want to access and alter a specific resource at the same time, although this ‘blocking’ is actually essential to the smooth operation of databases, as explained on this page.
The simplest way to understand the need for blocking is to think of a web-based banking platform that allows people to store, deposit, and withdraw cash as they see fit. So long as transactions take place one at a time, the balance can be tracked and maintained with ease. However, if two separate withdrawal requests are initiated simultaneously, this could create a doubling effect if there was not a block in place to ensure data integrity and avoid serious errors.
In an SQL environment, processes are assigned locks that allow them exclusive access to a resource until they have completed the necessary action. Locks are assigned to allow processes to take place in a logical sequence, delivering concurrency without the downsides, save the slight speed penalty.
Of course, locks and the blocking they generate can have their own concurrency hurdles to overcome, so a matter that seems straightforward on the surface quickly reveals its more intricate inner workings.
Integrated solutions
Most SQL-oriented database management systems, such as MySQL, are built so that the most fundamental operations are atomic, meaning that they are inviolable. This typically applies to operations that modify data, which means that there is no halfway house; they either succeed or they fail, again to prevent integrity and concurrency issues.
You can also leverage transactions to go one step further than is possible by relying on atomic operations alone. While transactions can contain multiple steps, they are treated as a standalone unit that will succeed or fail, not fall somewhere in between the two. Locks acquired at this level are another solution for tackling errors related to concurrency.
Potential pitfalls
Having said all of that, there are still some situations in which doubling issues might arise, specifically because several SQL database management systems do not allow read queries contained within transactions to lock down the rows they relate to.
Keywords like FOR UPDATE can be appended to SELECT to allow for read locking, while LOCK IN SHARE MODE is more of a soft lock that stops concurrent modification of rows even if other transactions are able to run statements and leverage any new values that are created as a result of the original transaction’s operation.
Orchestrating the order of operations correctly will avoid the creation of circular locks, which can clearly compromise performance. However, if problems still persist, digging into the code will be the next obvious step to resolve blocking issues.
Looking deeper
Here is an example of the kind of database connect you might be using, and take a few further steps to explore what can occur. In this case, we will stick with the idea of online banking service.
'use strict';
module.exports.query = query;const mysql = require('mysql');const connection = mysql.createConnection({
host: process.env.HOSTNAME,
port: process.env.PORT,
user: process.env.USERNAME,
password: process.env.PASSWORD,
database: process.env.DB_NAME,
// Keep in mind that having multipleStatements set to true
// leaves you more vulnerable to sql injections
// but in order to send transactions as a block in our example
// we will need it
multipleStatements: true,
});connection.connect();const query = (input) => {
const _defaults = {
params: [],
},
const { sql, params } = Object.assign(_defaults, input);
return new Promise((resolve, reject) => {
connection.query(sql, params, (err, resp) => {
if (err) {
return reject(err);
}
resolve(resp);
});
});
};
Populating the database with information is a good idea at this point.
CREATE TABLE transactions
(
id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
accountId INT(11) NOT NULL,
amount DECIMAL(10,2) NOT NULL
);INSERT INTO transactions (accountId, amount) VALUES (1, 10);
Next, we need a transaction on which the database can act.
'use strict';const {query} = require('./mysql-wrapper');const sql = `
START TRANSACTION;
# Calculate balance
SELECT @balance := SUM(amount) FROM transactions WHERE accountId = 1 FOR UPDATE;
# Compare balance with the amount we want to withdraw
SET @finalAmount = IF(@balance >= 100, -100, NULL);
# If our balance was too low then this will fail as NULL is not allowed as transaction amount
INSERT INTO transactions (amount, accountId) VALUES (@finalAmount, 1);
COMMIT;
`;query({sql})
.then(result => {
console.log('Success', result);
})
.catch(err => {
console.error('Failed', err);
});
At this point, it should be smooth sailing. Funds can be added and then if a withdrawal request is made and the balance is not high enough, an error will be generated.
Running two instances of this transaction concurrently will generate ER_LOCK_WAIT_TIMEOUT because it can neither fail nor succeed and simply times out automatically.
This is because the transaction is not treated correctly and the error itself is not accurate; the transaction remains open until a ROLLBACK is triggered.
Here is a simple solution to this problem.
const query = (input) => {
const _defaults = {
params: [],
};
const { sql, params } = Object.assign(_defaults, input);
return new Promise((resolve, reject) => {
connection.query(sql, params, (err, resp) => {
if (err) {
// Torch the connection
connection.destroy();
return reject(err);
}
resolve(resp);
});
});
};It may be elegant, but it is not the most optimal in terms of performance. To achieve a more streamlined approach, this is the alternative.
const query = (input) => {
const _defaults = {
params: [],
};
const { sql, params, autorollback } = Object.assign(_defaults, input);
return new Promise((resolve, reject) => {
connection.query(sql, params, (err, resp) => {
if (err && autorollback) {
return resolve(rollback(err));
} else if (err) {
return reject(err);
}
resolve(resp);
});
});
};const rollback = (err) => {
return new Promise((resolve, reject) => {
connection.query('ROLLBACK;', [], (rollbackErr) => {
if (rollbackErr) {
// Fall back to torching the connection
connection.destroy();
console.error(rollbackErr);
}
reject(err);
});
});
};
In this context a ROLLBACK will be initiated once a transaction is sent, preserving the connection rather than having to terminate and re-initiate it every time blocking occurs.
Conclusions
Hopefully, you now have a better idea of how to deal with SQL blocking on Node.js, and more importantly, you understand why this can occur in the first place, even if on a superficial level it seems like these concurrency issues should not really be able to appear in the first place.
This should be just one of the things you look into when examining database performance, integrity, and general maintenance. Knowing the ins and outs of the coding and improving your skills can lead you to deeper benefits than are available just through query optimization alone.
Tip: Share your reusable components between projects using Bit (Github).
Bit makes it simple to share, document, and organize independent components from any project.
Use it to maximize code reuse, collaborate on independent components, and build apps that scale.
Bit supports Node, TypeScript, React, Vue, Angular, and more.
