insertion, violation de contraint et commit

Bonjour à tous,

Voila mon petit problème j'ai une application qui tourne sur plusieurs poste et qui alimente une même base (oracle 10g)

le principe est simple:
à partir de fichier, l'application doit créer des "rubriques" dans la table rubriques
si une rubriques existe déjà, on met juste à jour un champ dt_maj

or la création d'une rubrique peut mettre bcp de temps (plusieurs traitement à effectuer avant de commiter)

voila donc ce qui se passe

1- appli1 du poste1 crée rubrique1 (sans commit) et continue son traitement, si tous les traitement sont ok, on commit la transaction sinon on fait un rollback

2- juste aprés appli2 du poste2 recoit l'ordre de créer rubrique2, mais comme appli1 n'a pas commité, appli2 essai alors de créé rubrique2 (au lieu de la mettre à jour) mais celui-ci recoit une erreur ORA-00001: unique constraint violated

3- à cause de l'erreur, appli fait un rollback

4- appli1 a fini son traitement et commit (si pas d'erreu sinon rollback)

Premièrement est ce normal de recevoir l erreur de violation de contraint alors que la premiere appli n'a pas commité
Secondo Que peut on proposer pour ce type de problèmatique ?


Merci de votre aide

En fait, je me pose la question de savoir si il y'a pas un problème de conflit d'insertion entre ces deux application vu que pour chaque appli entre le début de la transaction et le commit, il peu se passer 4/5 minute

Merci pour votre aide prochaine

Pour répondre précisément à vos questions, il faudrait savoir comment sont gérées les colonnes qui composent la clé primaire ou unique qui provoquent l'erreur: ces valeurs à insérer sont-elles entrées par l'utilisateur, calculées par le code applicatif et si oui comment ? Essayez de poster un scénario SQL complet avec les 2 sessions qui simule ce qui ce passe dans l'application: ceci devrait permettre de comprendre ce qui ce passe exactement.

Votre problème ressemble à un problème classique de non utilisation d'une séquence qui permet de générer un numéro dont Oracle est censé garantir l'unicité pour être utilisé comme clé primaire ou clé unique. Voir le tutoriel http://fadace.developpez.com/oracle/sequences/.

Bonjour,

En première analyse, je pense que le scénario suivant se produit :

La base est dans le niveau d'isolation READ_COMMITED (mode par défaut).

dans ce mode :
A fait un insert des valeurs (KEY1,VALUE1) mais ne commit pas.
B fait un insert des valeurs (KEY1,VALUE2) car l'ordre select sur KEY1 ne retourne rien. En effet dans le mode READ_COMMITTED, seule les transactions committées sont prises en compte. Pour B, la clé KEY1 est donc dispo La transaction n'est pas rejetée car A n'a toujours pas commité. La transaction B est en fait en attente du commit/rollback de A. Si A rollback, la transaction B va être acceptée si A commit, la transaction va être rejetée.
La suite, tu la connais : A commit et donc la transaction de B est rejetée à juste titre.

La réponse à ta première question est donc que la transaction B est rejetée mais seulement après le commit de A. Elle est mise en attente tant que A n'a pas commité.

Tu peux tenter de verouiller les enregistrements de ta table par

Code:

---

select * from table for update

---

Dans ce cas de figure, lorsque A effectue sa mise à jour, toutes les autres transactions (même les ordres select) sur la table sont mises en attente du commit/rollback. Dans ce cas de figure lorsque B va effectuer le select , il verra la clé KEY1 (car l'ordre select sera en attente du commit) et effectuera donc un update plutôt qu'un insert.

Bonjour et bonne année tous

mongolic, c'est exactement ça mon problème et ton jeu de test explique mon problème

Cependant la solution avec le select for update ne fonctionne pas, car il ne bloque pas les select comme tu le dis et c'est bien dommage car ça aurait résolu mon problème

Bonjour,

Citation:

---

Envoyé par elkamaro

1- appli1 du poste1 crée rubrique1 (sans commit) et continue son traitement, si tous les traitement sont ok, on commit la transaction sinon on fait un rollback

2- juste aprés appli2 du poste2 recoit l'ordre de créer rubrique2, mais comme appli1 n'a pas commité, appli2 essai alors de créé rubrique2 (au lieu de la mettre à jour) mais celui-ci recoit une erreur ORA-00001: unique constraint violated

---

Par "n'a pas commité" tu veux dire que appli1 a rollbacké, c'est bien ça ?
Dans ce cas, il suffit de tester l'erreur ORA-00001 et dans ce cas retourner en 1
Et là appli2 va lui-même créer la rubrique puisque appli1 aura rollbacké

Mais tout ce scénario peut proser des problèmes de performances: toutes les transactions concurrents vont être sérialisées sur la rubrique.

Cordialement,
Franck.

Comment générez-vous vos clefs ?
Avec des séquences ou avec des select max(KEY) from MaTable ?

elkamaro, es tu sûr de faire le select sur toutes les lignes de la table (sans clause where):

Code:

---

select * from table for update

---

Lorsque tu fais un select for update, oracle vérouille les enregistrements retournés par le select. Ce que je te propose est de faire un verouillage sur toute la table avant de faire ta tambouille :

1 select * from table for update -- verouillage de la table
2 select * from table where code ='KEY' --test d existence pour decision : insert ou update
3 ordre insert ou update
3 commit -- ce qui libère tous les enregistrements de la table

mongolic,
un select for update va vérouiller tous les enregistrements existants, mais ne va pas protéger d'un insert en cours. C'est différent d'un lock table.
Il n'y a qu'un insert qui peut bloquer un autre insert (de la même valeur, et parce qu'il y a un index unique -> c'est alors l'entrée d'index qui est vérouillée)

elkamaro,
tu peux peut-être regarder la fonction dbms_lock pour faire un vérouillage un peu plus 'custom'.

Cordialement,
Franck.

ok pour ton explication, je ne suis pas spécialiste et je me trompe peut-être. Mais je ne vois pas pourquoi çà ne marcherait pas :
Si une transaction est en cours, cela veut dire qu'il y a eu un select * for update en cours,. Donc toute autre transaction, ne passera pas l'étape 1 puisque le select * for update sera mis en attente par celui de la 1ere transaction.
Est ce que je me trompe ?

Citation:

---

Envoyé par mongolic

Est ce que je me trompe ?

---

Ca marche sauf si la table est vide au départ... Mais je trouve ca un peu tiré par les cheveux: au lieu de vérouiller une ressource, on en vérouille plein (chaque enregistrement de la table) qui ne sont pas concernées par la transaction.
Et vérouiller, c'est modifier le bloc, donc générer de l'undo et du redo.

Je préfère vérouiller sur l'insert puisque c'est vraiement là dessus qu'il faut sérialiser.

Je ne savais pas que le verrouillage modifiait les blocs. :mrgreen:

Citation:

---

Envoyé par mongolic

ok pour ton explication, je ne suis pas spécialiste et je me trompe peut-être. Mais je ne vois pas pourquoi çà ne marcherait pas :
Si une transaction est en cours, cela veut dire qu'il y a eu un select * for update en cours,. Donc toute autre transaction, ne passera pas l'étape 1 puisque le select * for update sera mis en attente par celui de la 1ere transaction.
Est ce que je me trompe ?

---

Bonjour Mongolic

En fai je ne suis pas du tout parti dans ta direction pour la simple raison qu'un select * for update sur ma table prendrait énormément de temps vu sa taille.

Merci quand meme pour tes explication qui pourrait servir a d'autres fin

pas de soucis, ce n'était pas une solution satisfaisante

Si tu peux juste nous dire comment tu as fait finalement

Citation:

---

Envoyé par mongolic

pas de soucis, ce n'était pas une solution satisfaisante

Si tu peux juste nous dire comment tu as fait finalement

---

Bonjour Mongolic

en fait j'ai pas encore résolu le probleme car je cherche une solution robuste car il s'agit d'une grosse base de prod

donc pour l'instant j'essai de creuser les pistes de pachot

Mais sinon la solution que j'ai poposer actuellement c'est de travailler sur l'erreur ora-00001 qui est retourné
a savoir des qu'on a cet erreur on relance la création de la rubrique, et du coup comme celle ci existera, elle sera uniquement mise à jour

En fait pour mieu comprendre mon probleme, voila ce qui se passe

lorsque l'appli1 fait une insertion de rubrique dans la table rubrique1, un trigger se declenche pour faire une insertion dans rubrique2

voici le trigge

Citation:

---

Envoyé par code

TRIGGER rubrique2
AFTER INSERT ON rubrique1 FOR EACH ROW
DECLARE
trouve BOOLEAN;
le_rowid ROWID;
dt DATE;

BEGIN
trouve := FALSE;

BEGIN
SELECT id ,dt
INTO le_rowid , dt
FROM rubrique2
WHERE id = :new.id
;
trouve := TRUE;

EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
WHEN OTHERS THEN RAISE;
END;

IF ( trouve )
THEN


IF ( :new.dt >= dt )
THEN

UPDATE rubrique2
SET dt = :new.dt
WHERE id = le_rowid
;
END IF;

ELSE

INSERT INTO rubrique2
(
id
, dt
)
VALUES(
:new.id
, :new.dt
);
END IF;

EXCEPTION
WHEN OTHERS THEN RAISE;
END;
/

---

Pour comprendre ce trigger (simple :) ), lorsque le select renvoi une réponse, un update est fait => dans ce cas aucun problème de concurence car l'update lock l'enregistrement et ne le délock pas tant tant que le commit n'est pas passé

par contre quand le select ne renvoit rien, un insert est alors effectué et dans ce cas deux cas de figure se présente :

- l'enregistrement à inserer n'existe pas et dans ce cas tout se passe bien
- une autre appli a lancer un insert du meme enregistrement et a commiter. Dans ce cas mon insert (ci-dessus) va planté car accés a concurrence et violantion de contrainte.

Il faudrait donc trouver un moyen d'être sur de ne pas lancer un insert d'un enregistreement qui est deja encours d'insertion.

Pour ce faire, moi je proposait de mieu gerer les exception dans le trigger à savoir, sur une exception de contrainte(ora-00001), de repartir tout au début du trigger au select et tout redérouler. Ainsi, le trigger ne fera plus un insert mais un update puisque l'enregistrement existe déjà

Qu'en pensez vous

Citation:

---

Qu'en pensez vous

---

Je pense que c'est la bonne solution. Sauf si l'exception ORA-00001 est trop frequente, mais je ne pense pas.

Le premier select je ferais un select for update, pour vérouiller l'enregistrement à partir de là. Sinon, imagines qu'une transaction vienne à faire un delete de la rubrique entre ce select et l'update: tu pers la rubrique.

Ou mieux: faire directement l'update et tester SQL%ROWCOUNT pour savoir s'i la rubrique existait ou pas.

Cordialement,
Franck.

Citation:

---

Envoyé par pachot

Je pense que c'est la bonne solution. Sauf si l'exception ORA-00001 est trop frequente, mais je ne pense pas.

Le premier select je ferais un select for update, pour vérouiller l'enregistrement à partir de là. Sinon, imagines qu'une transaction vienne à faire un delete de la rubrique entre ce select et l'update: tu pers la rubrique.

Ou mieux: faire directement l'update et tester SQL%ROWCOUNT pour savoir s'i la rubrique existait ou pas.

Cordialement,
Franck.

---

Pachot

le select for update n'est pas nécessaire dans notre cas, car aucun ordre de delete n'est lancé (on ne supprimer jamais rien dans ces tables)

secondo je ne peux faire directement un update car je dois d'abord récupérer l'enregisterment pour savoir si sa date (dt) et inférieur à la nouvelle (seulement dans ce cas je fais un update, si la date est inférieur je ne fais pas d'update)


Je te remercie, je vais proposer uniquement cette solution de gestion d'exception et on verra ce qu'il vont dire (les développeurs)


Merci à tous