Transactions are one of the important features of a relational database. In this Node.js MySQL transaction walkthrough, we will look into how it can help you achieve data consistency pushing the difficult stuff to the database level. Let’s get started!

What is a database transaction

The simplest way to define a relational database transaction is “all or nothing”. You can run a sequence of SQL queries, for example, you can run 4 SQL queries. If there is an issue in the third one the whole thing can be rolled back reverting the state of the data to before any queries were run.

Wikipedia says:

A database transaction symbolizes a unit of work performed within a database management system (or similar system) against a database and treated in a coherent and reliable way independent of other transactions. A transaction generally represents any change in a database.

A classic example is a money transfer between two bank accounts. Either the operation should happen in full or not happen at all. The data should not be in an interim state where the money is deducted from the transferor's account but not credited to the transferee’s account.

Importance of locks and Isolation levels

With database transactions, our aim is to have data consistency intact. In relation to the data consistency, database row locks and isolation levels are always part of it. The simplest way to understand database isolation is the ability of the database to allow a transaction to execute when there are no other concurrently running transactions that may be competing for the same rows/resources.

To allow good isolation, locking comes into play. So we could lock some rows of a table, update them and then commit it. While the rows are being updated, we can stop other queries from reading these rows. If we allow reading of uncommitted rows/data it is referred to as dirty reads.

Of course, this is not an easy topic to describe in like 100 words, so I would really recommend reading this great piece on database isolation and locking. Below is the summary of the 4 isolation levels from Wikipedia:

The default transaction isolation level for MySQL is Repeatable read as per its docs. We would be using a different isolation level in our example.

This takes us to why relational databases are so strict about transaction and data consistency. It is because of ACID compliance as discussed in the next part:

ACID compliance

ACID stands for Atomicity Consistency Isolation Durability which guarantees that database transactions are processed reliably. Essential SQL given the classic example of bank transfer to explain ACID. In the example it elaborates:

Atomic means either the whole bank transfer would go through or nothing would happen. Consistency ensures audit trail, meaning in the case of transaction rollback both accounts will be restored to original balances.

Furthermore, the post describes:

Isolation makes sure other banking transactions don’t affect the outcome of this particular balance transfer. Other competing transactions would wait for this transaction to complete. Finally, Durability means once the transaction is saved or committed, it will be available (not lost) even in the case of a power outage or system crash. Basically, saved safely to disk.

You can read more about it on this BMC blog too.

MySQL in particular is with the InnoDB engine says it adheres closely to the ACID model. But, needless to say, there are different opinions about it also because of the old MyISAM engine which is non-transactional but it is not the default engine nowadays.

Along the same lines, you might want to read a bit more about CAP Theorem - Consistency, Availability, and Partition Tolerance. How you can only have 2 out of those three unless you believe Google Spanner has all 3 of them.

Reading a bit more about BASE - Basically Available Soft state, Eventual Consistency) will help to understand the “speed” of NoSQL databases and tradeoffs they take. To scratch the surface, I will leave you with this ingenious cartoon about Eventual Consistency, probably by Mykola:

Back to the main thing, let’s dive into the code or Node.js MySQL transaction example.

Node.js MySQL transaction example

We will take the example of a special kind of online shop for this tutorial. The shop is an imaginary government-run online shop that sells essential items because of COVID created shortage only to pensioners. We will have 2 tables sales_order and product (has stock count too) for this MySQL transaction illustration. Below are the rules of this fictitious shop:

1. It only sells toilet paper, rice, pasta, chicken breast, and hand sanitizer. Just 5 items in total.
2. You can only buy a maximum of 1 of each of the above items in one order (per day/per person). This is done to make the logic very simple.
3. All prices are stored in cents and displayed in dollars for simplicity and easier calculation.
4. We are not concerned with the customer or payment data for this overly simple example, in real life those things would be crucial.

With the above rules to simplify the transaction, in the next steps, we will create the tables, some records and write the code for the Node.js MySQL transaction illustrations.

Node.js MySQL transaction tables

With the above assumptions let’s create the two over-simplified tables, product and sales order as below:

CREATE TABLE `product` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `sku` char(6) COLLATE utf8_unicode_ci NOT NULL,
  `price` int(11) NOT NULL COMMENT 'in cents',
  `quantity` int(11) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `sku` (`sku` ASC) VISIBLE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `sales_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `items` text COLLATE utf8_unicode_ci NOT NULL,
  `total` int(11) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

These are 2 distinct tables without any relationship. As this is a super simple example, sales_order will have the items column which is a comma separated values of product names. Let’s add these 5 items with 500 quantity for each of them as below:

INSERT INTO `product` (`id`, `name`, `sku`, `price`, `quantity`) VALUES
(1, 'Toilet paper 10 pack', 'TP0001', 750, 1),
(2, 'Rice 1 Kg', 'RI0002', 140, 500),
(3, 'Pasta 500 g', 'PA0003', 260, 500),
(4, 'Chicken Breast 1 Kg', 'CB0004', 1200, 500),
(5, 'Hand Sanitizer', 'HS0005', 300, 500);

We have created the 5 products we sell. Each of them have 500 quantities except toilet paper. We will use toilet paper later to test the out of the stock scenario.

Node.js MySQL transaction code

Before writing code, let's analyze the course of action. This is very important and one of the skills I see lacking in junior engineers. First solve the problem, then write code is one of the most important coding tips. The steps for solving this problem are as the following:

1. We will set the isolation level to “READ COMMITTED”
2. Start the transactions
3. Lock the rows by SKU, as SKU is unique.
4. Read the rows for stock
5. Calculate the order total and items
6. Insert the order in the order table
7. Update the product to deduct the quantity by 1.
8. Commit the transaction
9. If there is an issue between step 3-8, it will rollback the whole transaction

Below is our order service with the create order function which has the Node.js MySQL transaction:

const mysql = require('mysql2/promise');
const config = require('./config');

async function createOrder() {
  const items = ['RI0002', 'CB0004']
  const connection = await mysql.createConnection(config.db);
  await connection.execute('SET TRANSACTION ISOLATION LEVEL READ COMMITTED');
  console.log('Finished setting the isolation level to read committed');
  //set wait timeout and lock wait timeout as per need.
  await connection.beginTransaction();
  try {
    await connection.execute('SELECT id, name FROM product WHERE sku IN (?, ?) FOR UPDATE', items);
    console.log(`Locked rows for skus ${items.join()}`);
    const [itemsToOrder,] = await connection.execute('SELECT name, quantity, price from product WHERE sku IN (?, ?) ORDER BY id', items);
    console.log('Selected quantities for items');
    let orderTotal = 0;
    let orderItems = [];
    for (itemToOrder of itemsToOrder) {
      if (itemToOrder.quantity < 1) {
        throw new Error(`One of the items is out of stock ${itemToOrder.name}`);
      }
      console.log(`Quantity for ${itemToOrder.name} is ${itemToOrder.quantity}`);
      orderTotal += itemToOrder.price;
      orderItems.push(itemToOrder.name);
    }
    await connection.execute(
      'INSERT INTO sales_order (items, total) VALUES (?, ?)', 
      [orderItems.join(), orderTotal]
    )
    console.log(`Order created`);
    await connection.execute(
      `UPDATE product SET quantity=quantity - 1 WHERE sku IN (?, ?)`,
      items
    );
    console.log(`Deducted quantities by 1 for ${items.join()}`);
    await connection.commit();
    const [rows,] = await connection.execute('SELECT LAST_INSERT_ID() as order_id');
    return `order created with id ${rows[0].order_id}`;
  } catch (err) {
    console.error(`Error occurred while creating order: ${err.message}`, err);
    connection.rollback();
    console.info('Rollback successful');
    return 'error creating order';
  }
}

(async function testOrderCreate() {
  console.log(await createOrder());
  process.exit(0);
})();

You can find the code in this open-source GitHub repository if you want to give it a quick spin.

How the MySQL transaction code works

Time to see how the code is organized:

• It starts with requiring the MySQL2 library which we installed from NPM with npm install --save mysql2 and the config with credentials to our MySQL server in the config.js file.
• We have a createOrder function which does all the heavy lifting.
• It starts by creating a connection to the database, we will use this connection to do the transaction with all our related queries.
• Then we set the isolation level to be READ COMMITTED which means, other transactions can read the locked rows only after the current locking transaction has committed/rolled back.
• Consequently, we start the transaction with beginTransaction
• After that, we lock the rows with the SKUs for items in line no 5, those are RI0002 and CB0004.
• Subsequently, we get the name, quantity, and price for the same products.
• Then, we loop through each item to see if it has stock (quantity is not less than 1), if it doesn’t have stock we throw an error that rolls back the transaction else it will carry on.
• After that we compute the order total and push the name to orderItems array
• Consequently, we insert a row on the sales_order table with the computed order total and join the names of the orderItems with a , comma.
• Then, we deduct the quantity by 1 for the items of the order (remember the 1 item per order rule)
• Finally we commit the transaction to seal the whole process.
• If there is any issue in the transaction we have a rollback in the catch part.
• We have a testOrderCreate function that just calls the createOrder and exits the process.

Let’s test our code and see that it does what we are intending to do.

Testing Node.js MySQL transaction code

We can run two concurrent and competing transactions but as our isolation and lock levels are optimal one will wait for the other to complete. If it was an API we could have got 2 or more requests for the same items at the exact same time (millisecond or microsecond), still, it would be handled well by our code. One of the easiest ways to test our code which is just a script at this point in time is with Tmux.

Tmux also known as Terminal Multiplexer is like a window manager within your terminal.
We can split the terminal into multiple “panes”. We can also propagate the same command in multiple panes with the synchronize panes setting. We will use the same synchronize panes “on” setting to run the command in 2 panes simulating 2 competing transactions as seen below:

As we have seen, the above pane was executed first as it read the quantities to be 484 and placed the order with id 17. Then the lower pane read the rows from the product table after the lock was released by the commit action of the above transactions. It read the quantity of both Rice and Chicken breast as 483 and the order id was 18 for this one. Next, let’s see what happens if 2 people want to place an order for the last item at the same time, logically one should get out of stock message.

Transaction to tackle the out of stock problem

You might know in early Mar 2020, people were going crazy about toilet paper. To a point that they resorted to fighting and hair-pulling for the last toilet paper roll. We will reenact this fight virtually where no-one gets hurt and we don’t need to involve any law enforcement officials.

As our shop is online, the fight will be on who hits the “Pay Now” button first :). The quantity of toilet paper is 1. Even if two people hit it at the same time database row locking and right isolation levels will put any arguments at bay, how? With the right isolation and locking, for the place order transaction, one of the competing transactions will wait while the other is successful. So the waiting ones will get an “Out of stock error” like below:

You can view the code change we made for this last toilet paper roll in the stock example in this pull request. The person placing the order on the screen below got it and the unlucky person on the top screen got an out of the stock message. Another way to test it can be with promise.allSettled. I leave that exploration to you :).

This sane behavior of the systems is possible as the rows were locked in time and the other competing transaction was waiting. When the first transaction finished it had set the quantity to 0 so the transaction that was second read 0 quantity resulting in a valid out of the stock message.

Conclusion

Locking, isolation levels, and transactions are very important and useful in a Relational database context.

If you have to write a sequence of SQL queries that write data or more than one SQL query involves writing data to more than one table chances are you will need a database transaction to keep the data integrity intact.

Use transactions well and hopefully you will stay out of database deadlocks :), Keep coding!