La Force d'Oracle pour revenir en HAUT à N lignes avec SKIP VERROUILLÉ
Il y a un quelques questions sur la façon de mettre en œuvre une file d'attente-comme la table (verrouiller des lignes, la sélection d'un certain nombre d'entre eux, et le fait de sauter actuellement verrouillé lignes) dans Oracle et SQL Server.
Comment puis-je garantir que j'ai récupérer un certain nombre (N
) lignes, en supposant qu'au moins N
lignes éligibles?
De ce que j'ai vu, Oracle s'applique la WHERE
prédicat avant de déterminer quelles lignes à ignorer. Cela signifie que si je veux tirer une ligne d'une table, et deux threads simultanément exécuter le SQL, on ne va le recevoir de la ligne et de l'autre un jeu de résultats vide (même si il y a plusieurs lignes).
C'est contraire à la façon dont SQL Server s'affiche pour gérer la UPDLOCK
, ROWLOCK
et READPAST
indicateurs de verrou. Dans SQL Server, TOP
apparaît comme par magie à limiter le nombre d'enregistrements après avec succès la réalisation de serrures.
Note, deux articles intéressants ici et ici.
ORACLE
CREATE TABLE QueueTest (
ID NUMBER(10) NOT NULL,
Locked NUMBER(1) NULL,
Priority NUMBER(10) NOT NULL
);
ALTER TABLE QueueTest ADD CONSTRAINT PK_QueueTest PRIMARY KEY (ID);
CREATE INDEX IX_QueuePriority ON QueueTest(Priority);
INSERT INTO QueueTest (ID, Locked, Priority) VALUES (1, NULL, 4);
INSERT INTO QueueTest (ID, Locked, Priority) VALUES (2, NULL, 3);
INSERT INTO QueueTest (ID, Locked, Priority) VALUES (3, NULL, 2);
INSERT INTO QueueTest (ID, Locked, Priority) VALUES (4, NULL, 1);
En deux sessions distinctes, exécuter:
SELECT qt.ID
FROM QueueTest qt
WHERE qt.ID IN (
SELECT ID
FROM
(SELECT ID FROM QueueTest WHERE Locked IS NULL ORDER BY Priority)
WHERE ROWNUM = 1)
FOR UPDATE SKIP LOCKED
Noter que la première renvoie une ligne, et la deuxième session ne renvoie pas une ligne:
Session 1
ID ---- 4
Session 2
ID ----
SQL SERVER
CREATE TABLE QueueTest (
ID INT IDENTITY NOT NULL,
Locked TINYINT NULL,
Priority INT NOT NULL
);
ALTER TABLE QueueTest ADD CONSTRAINT PK_QueueTest PRIMARY KEY NONCLUSTERED (ID);
CREATE INDEX IX_QueuePriority ON QueueTest(Priority);
INSERT INTO QueueTest (Locked, Priority) VALUES (NULL, 4);
INSERT INTO QueueTest (Locked, Priority) VALUES (NULL, 3);
INSERT INTO QueueTest (Locked, Priority) VALUES (NULL, 2);
INSERT INTO QueueTest (Locked, Priority) VALUES (NULL, 1);
En deux sessions distinctes, exécuter:
BEGIN TRANSACTION
SELECT TOP 1 qt.ID
FROM QueueTest qt
WITH (UPDLOCK, ROWLOCK, READPAST)
WHERE Locked IS NULL
ORDER BY Priority;
Noter que les deux sessions de retour d'une autre ligne.
Session 1
ID ---- 4
Session 2
ID ---- 3
Comment puis-je obtenir un comportement similaire dans Oracle?
- Je vais vous donner la prime à quelqu'un qui peut me donner une simple réponse de Gary Myers avec son curseur, comme je veux oublier le curseur en tant que bien, tout comme l'OP
Vous devez vous connecter pour publier un commentaire.
"À partir de ce que j'ai vu, Oracle s'applique la OÙ le prédicat avant de déterminer quelles lignes à ignorer."
Yup. C'est la seule voie possible. Vous ne pouvez pas sauter une ligne à partir d'un jeu de résultats jusqu'à ce que vous avez déterminé le jeu de résultats.
La réponse est tout simplement de ne pas limiter le nombre de lignes retournées par l'instruction SELECT. Vous pouvez toujours utiliser le FIRST_ROWS_n allusions directes à l'optimiseur que vous ne serez pas en saisissant l'ensemble de données.
Le logiciel de l'appel de la SÉLECTIONNER convient de sélectionner uniquement les n premières lignes. En PL/SQL, il serait
REF CURSOR
(si c'est encore possible). Mais comme l'OP, je suis plus à la recherche d'un SQL-Server-comme solution dans un seulSELECT
déclarationLa solution Gary Meyers posté est tout ce que je peux penser, bref de l'aide AQ, qui fait tout pour vous, et bien plus encore.
Si vous voulez vraiment éviter le PLSQL, vous devriez être en mesure de traduire le PLSQL en Java appels JDBC. Tout ce que vous devez faire est de préparer la même instruction SQL, exécuter, puis continuer à le faire seule ligne extrait (ou N de ligne extrait).
La documentation d'Oracle à http://download.oracle.com/docs/cd/B10501_01/java.920/a96654/resltset.htm#1023642 donne une idée de comment faire cela au niveau de l'instruction:
De sorte que vous pouvez coder quelque chose en Java qui ressemble à quelque chose comme (en Pseudo-code):
Mise à JOUR
Sur la base des commentaires ci-dessous, une suggestion a été faite d'utiliser ROWNUM pour limiter les résultats reçus, mais qui ne fonctionne pas dans ce cas. Prenons l'exemple:
Maintenant, nous avons une table avec 10 lignes. Notez que j'ai bien inséré les lignes dans l'ordre inverse, la ligne contenant 10 est premier, 9 etc.
Dites que vous voulez que les 5 premières lignes, commandé croissant - c'est à dire de 1 à 5. La première est: est-ce
Qui donne les résultats:
Qui est clairement faux, et c'est une erreur de presque tout le monde fait! Chercher à l'expliquer plan pour la requête:
Oracle exécute le plan à partir de la base de l'avis que le filtre sur rownum est effectuée avant le tri, Oracle prend les lignes dans l'ordre qu'il trouve (de l'ordre qu'ils ont été insérés ici { 10, 9, 8, 7, 6}), s'arrête après 5 lignes, puis trie l'ensemble.
Donc, pour obtenir le premier 5 vous avez besoin de faire le tri en premier et ensuite l'ordre à l'aide d'une ligne de vue:
Maintenant, pour finalement en arriver au point - pouvez-vous mettre une pour la mise à jour de sauter verrouillé à la bonne place?
Cela donne une erreur:
Essayant de déplacer le pour mise à jour dans la vue donne une erreur de syntaxe:
La seule chose qui fonctionne est la suivante, qui DONNE LE MAUVAIS RÉSULTAT:
En effet, si vous exécutez cette requête dans la session 1, puis exécutez-le à nouveau dans la deuxième session, session permettra aux lignes nulles, ce qui est vraiment vraiment mal!
De sorte que pouvez-vous faire? Ouvrir le curseur et de récupérer le nombre de lignes que vous voulez à partir d'elle:
Si vous exécutez le bloc dans la session 1, il sera imprimée, '1' comme il a obtenu un blocage de la première ligne. Puis exécutez-le à nouveau dans la session 2, et il apparaîtra à l'impression '2' comme il sauté la ligne 1 et a la prochaine gratuit.
Cet exemple est en PLSQL, mais à l'aide de la setFetchSize en Java, vous devriez être en mesure d'obtenir le même comportement.
setFetchSize(10)
, mais en limitant avecROWNUM <= 10
. Qui a le même effet, sauf que la priorité de la commande est assez inutile lors de l'utilisation deROWNUM
. Commettre après le réglage du verrouillage de l'indicateur fait l'astuce, en effet, mais ce n'est pas très agréable de s'engager dans le milieu d'un EJB SessionBean activités de manutention de la logique. Et il n'est toujours pas à l'échelle, c'est à dire la probabilité qu'un deuxième processus est exécuté leFOR UPDATE SKIP LOCKED
requête avant le premier commis est encore trop élevé... Mais je suppose que c'est la meilleure solution à court de l'aide d'AQ...Oracle SQL developer Version 17.4.0.355
.select for update
sur l'ensemble de la table d'environ 1 million de lignes. Le premier seulement quelques centaines de lignes récupérées* est verrouillé. (* récupérés: ceux qui ont montré dans mon sql developer Résultat de la Requête de l'onglet)Dans votre première session, lors de l'exécution:
Votre sélection interne tenter de récupérer uniquement id=4 et le verrouiller. C'est un succès parce que cette seule ligne n'est pas encore verrouillé.
En seconde session, votre intérieur sélectionner essaie de les attraper SEULEMENT id=4 et le verrouiller. Ce n'est pas un succès parce que la ligne est toujours verrouillé par la première session.
Maintenant, si vous avez mis à jour la "verrouillé" dans la première session, la prochaine session de l'exécuter sélectionnez saisira id=3.
Fondamentalement, dans votre exemple, vous êtes en fonction sur un drapeau qui n'est pas définie. Pour utiliser votre verrouillé drapeau, tu veux sans doute dire de faire quelque chose comme:
Vous pouvez ensuite utiliser votre select for update sauter verrouillé déclaration depuis votre verrouillé drapeau est maintenu.
Personnellement, je n'aime pas toutes les mises à jour des drapeaux (votre solution peut exiger d'eux pour quelque raison que ce soit), alors je serais probablement juste essayer pour sélectionner l'Id je veux mettre à jour (par tous les critères) dans chaque session:
sélectionnez * à partir de queuetest où ... pour la mise à jour de sauter verrouillé;
Par exemple (dans la réalité, mes critères ne serait pas basée sur une liste d'id, mais queuetest table est trop simpliste):
sess 1: select * from queuetest où
id in (4,3) pour la mise à jour de sauter verrouillé;
sess 2: sélectionnez * à partir de queuetest où
id in (4,3,2) pour la mise à jour de sauter verrouillé;
Ici sess1 serait un lock-4,3 et sess2 verrouille uniquement 2.
Vous ne pouvez pas, à ma connaissance, faire un top-n ou de l'utilisation group_by/order_by etc dans le sélectionner pour l'instruction de mise à jour, vous obtiendrez un ORA-02014.
Ma solution est d'écrire une procédure stockée comme ceci:
C'est simple exemple - retour en HAUT de la PREMIÈRE non bloqué ligne. Pour récupérer les N premières lignes - remplacer les récupérer dans une variable locale ("je") avec boucle de fetch dans la table temporaire.
PS: le retour du curseur est à hibernate de l'amitié.
J'ai rencontré ce problème, nous passons beaucoup de temps à résoudre. Certains utilisent
for update
for update skip locked
, oracle 12c, une nouvelle méthode est d'utiliserfetch first n rows only
. Mais nous utilisons oracle 11g.Enfin, nous avons essayer cette méthode, et a trouvé fonctionne bien.
Je wirte dans le bloc-notes, donc peut-être quelque chose de mal, vous pouvez la modifier comme une procédure ou d'autre.
Tout d'abord merci pour les 2 réponses ..beaucoup Appris d'eux.J'ai testé le code suivant et après l'exécution de Practicedontdel.java la principale méthode ,j'ai trouvé que les deux classes imprime des lignes différentes à chaque fois. S'il vous plaît laissez-moi savoir, en tout cas, ce code peut échouer.(P. S : merci à débordement de pile)
Practicedontdel.java:
Practisethread.java: dans run():