Lost update

The lost updated (in English lost update ) is a type of error which can appear in Informatique when several accesses in writing to shared information take place in parallel. When two transactions modify same information, the modifications of the first can be covered immediately by those with the second if no precaution is taken against this problem; it is said that the updates carried out by the first transaction were lost .

This difficulty can arise independently of the form which takes the information shared, that they are in a file, in a table of Database or in shared Mémoire between several Thread S. This problem is also distinct from that of the corruption of the data which can intervene in the event of bad synchronization of the writings, although the solutions with the two problems often use the same basic mechanisms.

Readings and writings without interaction with the user

Example

The computing system of a cinema (where the places are not numbered) stores the number of tickets already sold for the meeting. 100 tickets were already sold, and the case n° 2 is selling three others of them. At the same time, the case n° 1 records the refunding of five tickets, which must thus be withdrawn total. The system of the case n° 1 thus withdrawn 5 on the whole of the number of tickets (100) and thus registers 95 in database. Immediately after, the case n° 2 records its own transaction and adds 3 to the number of tickets that itself memorized , that is to say 103, and registers the result in base. The first update is thus lost, and the end result is false (103 instead of 98 indeed occupied places).

Atomic modifications

The simplest solution with the problem of the lost update is not to cut out the operations of reading and writing as one did in the pseudo code of the cases of the preceding example; in the place, one will use an atomic operation which carries out the reading and the writing, for example, in a database SQL:

UPDATE rooms SET nb_places = nb_places + 5 WHERE numero_salle = 4;

Locking of the data

The solution of the atomic modifications does not apply when the program customer of a DBMS has the right to make complex decisions according to the data which it reads before carrying out his writings. To solve the problem of the update lost in this context, it is necessary to use a mechanism of locking data, which will prohibit with more than one program at the same time to modify the shared data. The primitive mechanisms of locking relevant are the following:

the bolt divided can be held simultaneously by an arbitrary number of transactions;
the exclusive bolt , as its name indicates it, can be held only by only one transaction at the same time; moreover, it is also exclusive compared to the divided bolts, i.e. a divided bolt and an exclusive bolt cannot exist simultaneously on the same site of data.

These mechanisms are provided by all the usual systems of data:

for the files, by the bolts of the Operating system;
for the shared Memory, by the semaphore S (the technique is described in Problème of the readers and the writers);
for the databases, by specific orders such as the order SQL LOCK TABLE.

In the case of the nelles databases transaction, the problem of the lost update is more often taken charges some directly by the DBMS, as it further will be seen.

Here course of the transactions of the example above, when an exclusive bolt is used:

Levels of insulation in databases SQL

The principal disadvantage of the method of the bolts is that it is discrétionnaire : it rests on goodwill transactions participating to acquire and slacken the bolts at the good moment, or else lost updates, even of the more serious problems like the corruption of the data, are likely to occur. Moreover, the exclusive bolt constitutes a potential bottleneck , because it must be held during the time which calculations of the transaction and the communications with the base last. It is undoubtedly not a problem in a cinema, but can become it for more complex systems in particular if they are distributed in several sites throughout the world.

Standard SQL92 defines a functionality known under the name of levels of insulation , which consists in for the database treating side waiter the problem of the lost update. For the algorithm of the cases of ticketting evoked above, a level of insulation “reading répétable” ( repeatable read ) is enough. Here orders SQL necessary for PostgreSQL, which does not have a level of insulation “reading répétable” (one thus uses “sérialisable” in the place, who is stronger):

BEGIN WORK SET TRANSACTION INSULATION LEVEL SERIALIZABLE SELECT nb_places FROM rooms WHERE no_salle = 4 -- Here the calculation of the number of remaining places intervenes -- by the case UPDATE nb_places SET nb_places = 98 WHERE no_salle = 4 END WORK

The database server can choose to translate the order SET TRANSACTION INSULATION SERIALIZABLE LEVEL by obtaining an exclusive bolt on the whole of the database; one finds oneself then in the situation of the preceding paragraph. It is only done PostgreSQL; more skilfully, this one lets the readings (orders SELECT) be carried out in parallel, and rejects the writings (by causing a Rollback transaction) if a situation of lost update is detected. In this way, PostgreSQL allows transactions relating to different data to be carried out completely in parallel, and prohibits the updates lost without having to make confidence with the customers so that they manage subtleties of locking correctly; the disadvantage is that there exists an arbitrary risk of rollback , and that the case must thus be ready to repeat its transaction (several times so necessary) until it makes a success of.

Readings and writings in the presence of interactions with the user

The idea to repeat the transaction several times is typically not acceptable when it implies a human user: indeed, this one should start again all its work of seizure each time. The solutions are the following ones:

to return to the mechanism of the exclusive bolts, in which case a second device should be envisaged so that the bolt is not held in an abusive way (for example during all the pause of midday!). It can be a time limited and known in advance to finish the transaction, or the possibility for an administrator “of breaking” the bolt manually;
to inform the user whom a writing had place while he worked at the end of the transaction (and if possible front), and to propose an assistance for to him to amalgamate its own work with the modifications which have occurred previously. The modern systems of Management of version, for example, carry out this semi-automatically operation and require a manual intervention only in the event of conflict.

See too

Random links: