Node.js MySQL Transaction: a step-by-step tutorial with a real-life example

Geshan Manandhar
Level Up Coding
Published in
9 min readMar 16, 2021

--

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.

--

--

Senior Software Engineer, Agile follower. Technologist, Google Developer Expert. Blogging at geshan.com.np