Requêtes incrémentales dans une table contenant une séquence sélectionnée en parallèle

Bonjour

Je suis un simple utilisateur d'une base de donnée qui requête de manière incrémentale, via un traitement périodique, une table particulière contenant plusieurs millions de lignes. Cette table a une séquence qui est également clé primaire, et qui est incrémentée via un trigger à chaque insertion de ligne.

Je fais donc mes requêtes incrémentales de manière assez basique (je ne sais pas s'il y a plus propre), en supposons que la séquence s'appelle ID_SEQ :

• Je stocke le max de la séquence de ma dernière requête incrémentale (ID_SEQ_1).

• Régulièrement je fais un "SELECT max(ID_SEQ) FROM my_table; " (requête qui a l'avantage d'être instantanée) et qui me renvoie donc un ID_SEQ_2.

• Si ID_SEQ_2 > ID_SEQ_1, je lance une requête SELECT avec un certain nombre de critères qui n'ont pas spécialement d'importance pour le pb qui me concerne, sauf celui ci : "ID SEQ > ID_SEQ_1 AND ID SEQ <= ID_SEQ_2" pour ne récupérer que les lignes que je n'ai pas déjà récupérées.

Jusqu'à peu je me disais que tout allait bien, et effectivement je n'avais jamais raté une seule nouvelle entrée. Puis j'ai réalisé en lançant des traitements ingérant chacun un très grand nombre de lignes (de l'ordre du million) que chacun d'entre eux ne faisait le commit qu'à la fin de tous ses insert.
Ainsi dès qu'il y a un lancement de plusieurs traitements en parallèle, il y a un phénomène d'entrelacement des valeurs de la séquence entre chaque traitement, donc rien ne garantit que les valeurs des séquences des nouvelles lignes "commitées" soient supérieures au max de la séquence calculé 1s avant, et donc je peux très bien rater des lignes dans le cas où les séquences s'entrelacent et que je teste le max de la séquence alors qu'un des traitements a commité ses INSERT mais pas les autres.

Alors la solution serait de faire un commit à chaque INSERT, mais j'ai cru comprendre que cela pouvait dégrader les performances et de toute façon je n'ai pas la main sur cette partie du code.

Ainsi ma question est : existe il un moyen sélectionner les nouvelles lignes insérées et comitées dans une table par rapport à un instant précédent dans ce contexte (tout en gardant mes perfos actuelles de quelques ms lorsque j'ai par exemple à récupérer 1000 nouvelles lignes dans ma table de 22M de lignes)?

Merci

Salut #Tony310,

Personnellement je n'arrive pas bien cerné ton souci. Mais ceci dit vu que tu dispose déjà d'une sequence pour alimenter la colonne (clé primaire de ta table),
un déclencheur BEFORE INSERT niveau table doit résoudre l'affaire sans pour autant parcourir toute la table.

Déjà la sequence te retourne l'ancienne valeur incrémentée d'un pas.

Qu'est ce que tu veux dire par là concrètement ?

Citation:

---

•Si ID_SEQ_2 > ID_SEQ_1, je lance une requête SELECT avec un certain nombre de critères qui n'ont pas spécialement d'importance pour le pb qui me concerne, sauf celui ci : "ID SEQ > ID_SEQ_1 AND ID SEQ <= ID_SEQ_2" pour ne récupérer que les lignes que je n'ai pas déjà récupérées.

---

Merci

Une séquence ne garanti qu'une seule chose, l'unicité de sa valeur.
Pas l'ordre d'insertion, ni qu'elle n'ait pas de trous (c'est l'erreur la plus souvent rencontrée avec les séquences).

Partant de là, toute votre logique est erronée.
Il faut que vous travaillez avec un date technique lors de l'insert.

Ensuite, vous devrez indexer cette date puis filtrer dessus.

Bonjour,

Votre problème est un exemple typique d’ignorance du mode d’isolation des transactions utilisé par défaut par Oracle : read commited et vous n’avez pas forcément besoin d’un insert en parallèle pour mettre la méthode en échec. Il suffit pour cela de juste deux sessions qui utilisent la séquence dans l’ordre session 2 suivi de session 1 et d’un mauvais concours de circonstances de type : insert et commit dans session 1, suivi de l’exécution de votre requête avec max et du commit dans la session 2.

Il est possible d’interroger les données via le mécanisme de flash back query mais vous devez tester pour avoir une idée précise sur les performances ainsi de voir avec votre dba si ce mécanisme peut être vraiment envisagé.

Une autre solution pourrais se baser sur une colonne de type timestamp pour enregistrer le moment de création de l’enregistrement et l'utiliser lors de l'extraction des données.

Citation:

---

Votre problème est un exemple typique d’ignorance du mode d’isolation des transactions utilisé par défaut par Oracle : read commited et vous n’avez pas forcément besoin d’un insert en parallèle pour mettre la méthode en échec. Il suffit pour cela de juste deux sessions qui utilisent la séquence dans l’ordre session 2 suivi de session 1 et d’un mauvais concours de circonstances de type : insert et commit dans session 1, suivi de l’exécution de votre requête avec max et du commit dans la session 2.

---

Je reconnais être un novice en matière de base de données, mais je pense que l'exemple que vous donnez est le même que celui que je décris. Enfin vous avez bien compris mon pb en tout cas :)

Citation:

---

Partant de là, toute votre logique est erronée.
Il faut que vous travaillez avec un date technique lors de l'insert

Ensuite, vous devrez indexer cette date puis filtrer dessus. .

---

Citation:

---

Une autre solution pourrais se baser sur une colonne de type timestamp pour enregistrer le moment de création de l’enregistrement et l'utiliser lors de l'extraction des données.

---

Cette colonne existe déjà dans cette table et est indexée mais sauf erreur de ma part cela revient au même, la date d'insertion est prise au même moment que l'incrément de séquence, donc croit de la même manière (et donc je rencontrerai le même pb avec des intervalles de dates qui peuvent "contenir des insert pas encore commités"), ce qu'il me faudrait plutôt c'est une colonne qui indique la date du commit des insert ou update de chaque ligne, qui peut être décalée d'1h par rapport à la date d'insertion, non?
Quoiqu'il en soit je n'ai pas accès à cette partie, je dois m'adapter à ce qu'a fait l'industriel pour venir me greffer sur la base de données afin de faire mes SELECT.

Citation:

---

Il est possible d’interroger les données via le mécanisme de flash back query mais vous devez tester pour avoir une idée précise sur les performances ainsi de voir avec votre dba si ce mécanisme peut être vraiment envisagé.

---

Je vais me renseigner là dessus pour voir si c'est faisable, merci.

Citation:

---

Cette colonne existe déjà dans cette table et est indexée mais sauf erreur de ma part cela revient au même, la date d'insertion est prise au même moment que l'incrément de séquence, donc croit de la même manière (et donc je rencontrerai le même pb avec des intervalles de dates qui peuvent "contenir des insert pas encore commités")

---

C'est vrai mais il y a une astuce. Tom Kyte propose dans un de ses bouquins une solution à ce problème en utilisant pour la date de référence de l'extraction des données, la date la plus ancienne entre la date des transactions actives au moment de l'interrogation et la date système. De cette manière les insertions non encore "commités" (quelle barbarie je viens de commettre :( ) seront prise en compte lors de la prochaine exécution du traitement.

Citation:

---

C'est vrai mais il y a une astuce. Tom Kyte propose dans un de ses bouquins une solution à ce problème en utilisant pour la date de référence de l'extraction des données, la date la plus ancienne entre la date des transactions actives au moment de l'interrogation et la date système

---

Ok je vois merci, par contre ça veut dire qu'on ne colle pas forcément au plus proche, mais en analysant les transactions actives on peut surement peaufiner la chose.
Quoiqu'il en soit je vais être bloqué en fait, pour raison SSI le dba de cette BD ne peut accorder aux utilisateurs les privilèges de lecture dans V$TRANSACTION et V$SESSION :?

Ce qui compte dans ce type de traitement est de ne pas laisser des enregistrements non traités ou les traiter en double, donc du ce point de vue peu importe que vous extrayez tous les enregistrements à 10H00 ou 9H57. Normalement la base source c’est du type OLTP avec beaucoup des transactions courtes. Il n’y a pas d’analyse à faire c’est juste une heure de référence pour l’extraction des enregistrements. Point.

Votre DBA n’a aucun besoin de vous donner l’accès à la table V$TRANSACTION. Il peut tranquillement créer une fonction PL/SQL qui vous renvoie juste la date de référence (date de la plus vielle transaction active ou date système quand il n’y pas de transaction active) et vous donner que le droit d’exécuter cette fonction.

Juste pour dire que j'ai résolu mon pb tout simplement :
1/ lock de la table en exclusive mode
2/ select du max de la séquence
3/ rollback

Le lock restant en attente tant que des transactions sont actives, cela me garantit que le select est fait alors qu'aucune transaction n'est en cours.

Oui si vous vous permettez de verrouiller complétement la table périodiquement.