What is a transaction in a database?

When you make a query inside a database (SQL or NoSQL, it doesn't matter), that query will be executed, the database will be modified (or not) and possibly return some data to you.

If ever, for X or Y reason, your database encounters a problem (software, system, hardware, or of external origin) at that precise moment, you will then have to fix the incident before you can continue to use your data with the last known state.

Let's say before your last query, since the incident prevented the query from finishing its execution.

Theoretically (excluding data loss due to the incident), your program can be restarted and function normally.

Now let's imagine the same scenario, but on a more critical application such as a bank transfer system.

The scenario

By simplifying the transfer system as much as possible, let's say that each money transfer requires at least two operations:

• The withdrawal of the money sent from the debtor account
• The addition of the money received to the creditor account

These two successive operations, represented at the software level by two separate queries to the database, introduce a possibility of losing the integrity of the data contained in the database.

Let's imagine again that our database server experiences an incident during the operation, there are two possibilities:

• Either one of the accounts received money but no one was debited, so the system has virtually created money.
• Or one of the accounts was debited but the money was never received, it has disappeared.

It is unthinkable for a financial management system (but not only) to leave the door open to such a risk, which is why the majority of database management systems implement a feature to address this problem: transactions.

Transactions

To understand this mechanism, we need to quickly go back over another, more general concept: atomicity.

The origin of the word "atom" means "indivisible", and in computer science we talk about atomic operations when several operations are carried out sequentially but inseparably.

In our previous example, this is exactly the kind of operations (atomic) that we would need, so that the debit and the credit cannot exist without one another.

Well, to carry out a set of atomic operations in a database, we are going to create a transaction that will ensure that data integrity is preserved even in the event of an incident.

Commit and rollback

To make it easier to understand, we will break down the creation of a transaction and the queries it contains into several steps:

• Creation and configuration of a new transaction
• Creation of a full or partial copy of the current state of the database (snapshot) reserved for the transaction
• Execution of several atomic operations within the transaction, on the snapshot's data

And depending on the outcome of the execution of the operations, we have two options:

• If everything went well, then we will ask to "commit" the result of the operations, the snapshot will then be "merged" with the database.
• If a problem occurred, then we will ask for a rollback, the snapshot will be deleted and none of the operations will have been executed on the database.

Of course, the actual implementation of transactions may differ somewhat from this way of working, but the general principle is the same, and you will therefore understand its crucial importance in data management.

To go further

For the curious ones among you who are wondering how several concurrent transactions can be orchestrated and not step on each other's toes, I recommend this article (in English) which explains the different strategies put in place quite well: