When we work with databases, we usually think that the operations we perform against them are performed one by one independently, but this causes several problems: how do we guarantee that the data is consistent if, for example, we update the table with two modification operations happening at the same time? What happens when there are errors in any operation and those errors propagate leaving the database in an unsatisfactory state? It is for these types of situations that transactions were designed.

Transactions are the minimum unit of execution of database operations, and consist of a series of one or more operations, which are executed in isolation between each transaction. Transactions comply with ACID properties, which guarantee that the database will be in a correct state after each transaction.

ACID Properties

• AtomicityAll changes are performed as if they were a single operation. For example, if there are three updates in a transaction, all three must be executed; if any of them fails, none of the three operations will be performed.
• ConsistencyThe database remains in a consistent state before and after execution after each transaction, i.e. the rules defined in the database continue to be followed after each transaction.
• Isolation (isolation): each transaction is executed independently from the rest of the transactions, so that the transactions only see the final states of the transaction executions and not the intermediate states.
• DurabilityThe state of the database after the execution of transactions persist over time and do not disappear, even when there is a system failure.

These transactions are transparent to the user, i.e., the user does not know that several operations are being executed as if they were one, but sees how the database update is executed and that everything continues to work correctly.

How developers can take advantage of transactions

Let's put a practical example in which we can use the transactions. Let's imagine that we want to update the email of a user in the database and at the same time we want to keep a record specifying that the user has been updated on such and such a day at such and such a time. If there has been any failure we do not want this record to be created, so we will consider that this operation has to be atomic, that is to say, if one of the two operations fails, the user does not have to be updated nor the record created.

When we work with databases we usually do it from different approaches, but the most common ones are connecting to the database and executing queries directly, or through libraries. When we connect to the database and execute a query we can indicate that we are going to do it in a transaction; for example with PostgreSQL we do this by means of the command BEGIN

BEGIN;
UPDATE users SET email = 'test@test.org' WHERE id = 1;
INSERT INTO logs VALUES(4, 1, 'user_updated');
COMMIT;

With the order COMMIT we indicate the end of the transaction and both operations will be executed in a single transaction. In this way we explicitly create the transaction ourselves and have full control over which operations are performed in the transaction.

Another approach is to perform transactions through the code in order to have that business logic in our code and not be dependent on the database. When, for example, we develop an endpoint that updates the money in a user's account, it is quite common that as developers we think that simply calling the service that updates that entity (for example using the Repository Pattern) is enough for what we need.

async save(user: User): Promise<void> {
    await this.repository.save(user);
  }

Now, we want that in the same way that we have made the transaction in PostgreSQL we can make the transaction of creating a record when the user is updated. Many current ORMs such as Doctrine o TypeORM allow several operations to be executed in one transaction. In the case of TypeORM by default when an update is made a transaction is created that executes that operation in a single transaction, but we can use the Query Runner that provides TypeORM to indicate that we are going to initiate a transaction.

const queryRunner = this.datasource.createQueryRunner();
await queryRunner.startTransaction();

With the function startTransaction() we are indicating to the Query Runner that everything after it will be counted as a transaction. For example, let's imagine that we want to update a user and, in addition, add a record that the user has been updated in another separate table. To do this we can do the following:

const queryRunner = this.datasource.createQueryRunner();
await querRunner.startTransaction();
try {
  await queryRunner.manager.save(User, user);
  const log = new Log(user.id, 'user created');
  await queryRunner.manager.save(Log, log);
  await queryRunner.commitTransaction();  
}
...

What are we doing in this piece of code? The first thing is that we indicate that we are going to initiate a transaction with startTransaction, In this way, everything we do will be considered as a single transaction until we indicate that we are going to end the transaction. With Query Runner we can use the Manager from TypeORM indicating that we are going to save the user in the database. Once the user has been updated we create a Log indicating the operation we have just performed, and we insert in the database the Log. Finally, with the commitTransaction() we indicate that all of these operations are commitearan in a transaction.

In the code we have put these operations in a try-catch. Why? So that we can handle errors and what we want to do when there has been an error. ORMs like Doctrine do an automatic rollback when they detect an error, but in TypeORM we have to explicitly indicate it.

...
} catch (error) {
  await queryRunner.rollbackTransaction();
} finally {
  await queryRunner.release();
}

The method release() is needed in TypeORM to indicate that the query runner we have created has to close the connection it creates.

Conclusions on database transactions

Transactions allow us to have more control when we want to perform several operations on the database and we want the database to remain in a state that complies with the business rules. In addition, they help us to manage that, in case of errors occurring in the database operations, the database remains in a state that does not compromise the correct operation of the application.

Knowing how they work and using them in a way that suits our needs allows us to take advantage of all the power they provide, thus ensuring that the database will always be in a correct state after several operations dependent on each other.

Want to learn more about development and programming? If you didn't read our last article, you can find it here. here.