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.
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?
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.
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.
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.
This is the strongest level. This level guarantees perfect independence between the different transactions.
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 .
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 .
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 .