Course 21: Transactions in SQL

Oct 3, 2021
Course 21: Transactions in SQL

1. Introduction

A transaction is a set of operations performed on a database, which has an atomic character. During a transaction, the database has no intermediate state, at least seen from outside the transaction. As it has no intermediate state, it cannot be interrupted.

The notion of transaction makes it possible to define a form of simultaneity. Take the example of a ship unloading its cargo in a port. We have two operations to perform to update our database. The first is to remove the cargo from the hold of the boat, and the second is to add that cargo to the warehouse.

These two operations must be carried out simultaneously. If they are not, there is a time when the cargo is both on the ship and in the warehouse, or a time when the cargo is neither in the ship’s hold nor on the harbor quay, according to the order in which the operations are carried out.

From the point of view of the operator who creates a transaction, there is of course no simultaneity: the operations take place one after the other. As part of a transaction, an operator can go through states in which the referential integrity constraints are not respected, which is sometimes essential. He can also decide to go back, and cancel the changes he has made to his database since the start of his transaction.

Transactions take on their full meaning for databases used by several users at the same time, which is generally the case.

2. Isolation of transactions


Transactions were created among other things to manage concurrent changes to a database: how should things be when two operators try to change the same data?


2.1. ACIDity of a transaction

The properties of a transaction were defined in the 1970s by Jim Gray, who brought them together under the acronym ACID : Atomicity, Consistency, Isolation, Durability.


2.1.1. Atomicité

Atomicity designates what we described in the introduction to this chapter. All the operations of a transaction are joint and several, if one fails, then the whole transaction must fail.


2.1.2. Consistency

This means that a transaction must take the database from one consistent state to another consistent state. Consistent means that all the constraints defined on the database data must be checked before and after the execution of any transaction.



2.1.3. Insulation

An isolated transaction means that the modifications they apply to the database while it is running should only be seen by it. Consider the example of a transaction that creates data in the table Marins. Until the transaction is completed, all users who are not in this transaction should not see the data created.

2.1.4. Durability

The durability of a transaction means that once a transaction is completed, and the server has indicated that the modifications have been taken into account, then the data will not be “forgotten” in the event of a major failure (hardware in particular).

2.2. Definition of insulation

Defining the isolation level of a transaction is like relaxing the isolation constraint as defined by Jim Gray. Indeed, this isolation is most often obtained by outright preventing certain reads on the tables being modified, which sometimes has an unacceptable impact on performance.

The isolation of a transaction is guaranteed by the acquisition of locks on the data handled. The isolation level in which a transaction is working actually indicates the type of lock it is allowed to place on the data.

Four isolation levels are defined, which we will examine in detail.



2.2.1. Insulation serializable



This is the strongest level. This level guarantees perfect independence between the different transactions.



2.2.2. Insulation repeatable_read



This level ensures that the reads performed by a selectwill not be modified, unless it selectcontains a clause whereallowing the selection of a range of data. This is the case with the clause where ddnaissance < 1800 and ddnaissance > 1700.

In the case of a transaction repeatable_read, performing such a request twice can result in phantom data, created in another transaction. These data are called phantom reads .



2.2.3. Insulation read_commited



This isolation level means that data handled in the current transaction can be modified by another transaction, and that this modification can be seen once this other transaction has been committed. These read data are called non-repeatable reads .


2.2.4. Insulation read_uncommited



This level of isolation is the loosest of all. It means that a piece of data handled in the current transaction can be modified by another transaction, and that this modification can be seen without this other transaction having been validated. This is called a dirty read .

Leave a Reply

Your email address will not be published. Required fields are marked *