Contrôle de concurrence multi-versions avec ORACLE
On travaille sur le schéma de relations suivant :
relation PLACE(no integer primary key, nb integer)
relation PARENT(par char(20), enf char(20)) (par, enf) primary key
Le mode par défaut
Une lecture est toujours possible
| T1_lect |
T2_lect |
| ... |
... |
| update place set nb=nb+1 where no=1 |
|
| wait |
|
| |
select nb from place where no=1 |
| |
print(nb) |
| commit |
|
| |
commit |
Deux écritures sur la même information sont sérialisées
| T1_ecr |
T2_ecr |
| ... |
... |
| update place set nb=nb+1 where no=1 |
|
| wait |
|
| |
update place set nb=nb+1 where no=1 |
| commit |
|
| |
commit |
Deux écritures sur la même information dans la même
transaction ne sont pas forcément reproductibles
| T1_lect_n_rep |
T2_lect_n_rep |
| ... |
... |
| select * from parent |
|
| print |
|
| wait |
|
| |
insert into parent |
| |
commit |
| select * from parent |
|
| print |
|
| commit |
|
Cohérence faible : on peut allouer deux fois la même
place
| T1_coh_faible |
T2_coh_faible |
| ... |
... |
| select nb from place where no=1 |
|
| si nb>0 update place set nb=nb-1 where no=1 |
|
| wait |
|
| |
select nb from place where no=1 |
| |
wait |
| commit |
|
| |
si nb>0 update place set nb=nb-1 where no=1 |
| |
wait |
| |
commit |
Dead_lock : une transaction peut attendre une transaction qui l'attend
aussi !
| T1_dead |
T2_dead |
| ... |
... |
| update place set nb=nb+1 where no=1 |
|
| wait |
|
| |
update place set nb=nb+1 where no=2 |
| |
wait |
| update place set nb=nb+1 where no=2 |
|
| |
update place set nb=nb+1 where no=1 |
| commit |
|
| |
commit |
Changements du mode par défaut
Lectures reproductibles
| T1_read_only |
T2_lect_n_rep |
| set transaction read only |
|
| ... |
... |
| select * from parent |
|
| print |
|
| wait |
|
| |
insert into parent values (...) |
| |
commit |
| select * from parent |
|
| print |
|
| commit |
|
Cohérence forte : interdiction de modifier une information
déjà lue
| T1_coh_forte |
T2_coh_forte |
| ... |
... |
| select nb from place where no=1 for update of nb |
|
| si nb>0 wait |
|
| |
select nb from place where no=1 for update of nb |
update place set nb=nb-1 where no=1
finsi |
|
| commit |
|
| |
si nb>0 update place set nb=nb-1 where no=1 |
| |
commit |
Verrouillage explicite :
| T1_lock_lr |
T2_lect_n_rep |
| ... |
... |
| lock table parent in exclusive mode |
|
| select * from parent |
|
| print |
|
| wait |
|
| |
insert into parent values (...) |
| select * from parent |
|
| print |
|
| commit |
|
| |
commit |