Discussion :

[tintinux]

Bonjour

Sous Oracle 11, sans utiliser de curseur, donc uniquement des SELECT.

Je récupére les rangées d'une table qui ne sont pas verrouillées, en les verrouillant toutes, ainsi :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT ROWNUM, ID FROM MaTable FOR UPDATE SKIP LOCKED ;

-- Avec un WHERE pour de vrai, évidemment.

Mais comment récupérer et surtout ne verrouiller que la première rangée ?

Pas de TOP 1 ou de LIMIT 1 comme sous Sql-server ou MySql... peut-être sous Oracle 12, mais non envisageable pour l'instant.

Un test sur ROWNUM ne va pas, parce que ROWNUM est incrémenté même pour les rangées "skippées".
Si la première est verrouillée, donc non listée, le premier ROWNUM retourné sera 2.

Et apparemment on ne peut pas mettre le FOR UPDATE ou le SKIP LOCKED dans une sous-requête du genre de celle-ci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT ROWNUM,ID FROM MATABLE
WHERE ID = (SELECT MIN(ID) FROM MATABLE FOR UPDATE SKIP LOCKED)
FOR UPDATE SKIP LOCKED

Est-ce que vous auriez une idée pour y parvenir quand même ?

Merci !

[mnitu]

J'ai des bonnes et des mauvaise nouvelles pour vous.

Déjà peu importe que dans Oracle 12c il y une autre syntaxe pour implémenter Top ou Limit si vous creusez un peu vous allez découvrir que Oracle s'arrange pour utiliser le vieux code qui marche! Et cela c'est assez raisonnable: pourquoi ne pas utiliser en interne le code qui fonctionne déjà c'est-à-dire les fonctionnes de fenêtrage et inventer la rue à chaque fois ?

Deuxième c'est la bonne nouvelle: la cluse FOR UPDATE SKIP LOCKED ne fonctionne pas de la même manière que la clause FOR UPDATE tout court et cela précisément du point de vue que vous intéresse: la deuxième applique les verrous à l'ouverture du curseur mais l'autre seulement au moment du fetch! Autrement dit: votre Select ... For Update Skip Row pourrait impacter disons 10 enregistrements de la table A_Table mais si vous obtenez une seule, en faisant un seul fetch d'un seul enregistrement alors il y a que c'est enregistrement qui sera verrouillé.

Troisième c'est la mauvaise nouvelle: Oracle vous recommande une autre solution

Oracle recommends that you use the Oracle Streams Advanced Queuing APIs instead of directly using the SKIP LOCKED functionality. Refer to Oracle Streams Advanced Queuing User's Guide for more information.

Bon courage pour la suite.

[tintinux]

Bonjour et merci pour la réponse, même s'il n'y a guère de bonne nouvelle.

Si vous creusez un peu vous allez découvrir que Oracle s'arrange pour utiliser le vieux code qui marche!

Je ne comprends pas ce que tu veux dire, mais c'est pas grave, vu que je ne peux pas utiliser Oracle 12.

la clause FOR UPDATE SKIP LOCKED ne fonctionne pas de la même manière que la clause FOR UPDATE tout court

Peut-être avec un curseur, mais je ne peux pas en utiliser, comme je l'ai dit.
Avec un simple SELECT, je ne vois aucun moyen de me limiter à la première rangée, et bien sûr tout ce qui est retourné est verrouillé.

Oracle recommends that you use the Oracle Streams Advanced Queuing APIs

Ah oui, et comment on fait quand on ne peux appeler qu'une instruction SELECT ?
Si je ne peux utiliser un curseur, je ne peux évidemment encore moins appeler une API.

[skuatamad]

Vous devriez détailler un peu ce que vous souhaitez faire car en l'état je ne comprends pas pourquoi vous parler de TOP 1 ou LIMIT 1 associé avec SKIP LOCKED.

Est ce que vous souhaitez obtenir la 1ere ligne non locked de votre table ?

TOP 1 ou LIMIT 1, ce code en Oracle avec rownum ou les fonctions analytiques :
Top-N Queries

Vous semblez utiliser un outil qui vous limite à une zone requête SQL SELECT.
Peut être que vous pouvez créer une fonction qui renvoie une collection et que vous pouvez appelez ensuite dans un select.
Cette fonction se chargera de ne fetch qu'une ligne, la première non locked.
Table Functions, Part 1: Introduction and Exploration

[EDIT] En fait ça n'est pas possible d'utiliser for update dans une fonction appelée depuis un select...
Donc ma proposition ci-dessus ne convient pas.

[mnitu]

Je voudrais toute simplement dire qu'Oracle 12c implémente la nouvelle fonctionnalité via les mêmes fonctions de fenêtrage qui sont disponible depuis la version 9. Or il s'avère que ces fonctions de fenêtrage vous interdisent d'utiliser for update, skip locked ou pas.

Je ne sais pas ce que comprenez par "avec curseur" mais sachez que n'importe quelle requête SQL fonctionne via un curseur! Il est vrai que la notion de curseur chez Oracle couvre pas mal des choses mais vous devez clarifier les notions que vous utilisez. A ce que je comprends vous utilisez le terme de curseur dans le sens du langage PL/SQL comme étant un identifiant d'une requête SQL propre à ce langage.

Et cela nous permet peut être d'avancer: comme indiqué le Select for Update Skip locked verrouille les enregistrements qu'au moment du fetch de ces enregistrements. Cela reviens à dire que si via votre outil ou langage de programmation préféré vous pouvez contrôler le nombre des enregistrements ramènes (fetch) alors vous avez la solution. Cela est possible facilement dans la plupart des langages de développement comme java, .net, C#, ruby, etc.

Dans le cas contraire, c'est à dire un langage/outil qui ne donne aucun contrôle au programmeur à ce niveau vous devez développez votre solution maison comme suggérée par skuatamad or autres bricolages maison comme gérer une colonne statut sur la table en question.

[tintinux]

Bonjour et merci pour vos précisions.

@mnitu

Effectivement, je réduisais le terme de curseur à des requêtes PL/SQL qui font un CREATE CURSOR.

Mon programme (Delphi ou Lazarus, avec AnyDac pour l'accès aux bases) ouvre une Query définie avec un premier SELECT [...] FOR UPDATE en retournant la première rangée.
Pour test, je stoppe le programme à ce stade en debug, sans explicitement lire les rangées suivantes (ce qui se ferait avec Query.Next).

Avec un autre client Oracle (TOAD) je fais une seconde requête : SELECT [...] FOR UPDATE SKIP LOCKED

Si la première Query appelée par programme est assimilée à un curseur, elle ne devrait donc pas verrouiller les rangées au delà de la première, et la seconde requête devrait donc retourner toutes les autres rangées.
Or, ce n'est pas le cas : la seconde requête ne retourne rien avec un SKIP LOCKED, et reste en attente sans lui, donc les rangées sont bien verrouillées au delà de la première.

Il est probable que la Query appelée par programme lise et donc verrouille toutes les rangées dès son ouverture, et je ne pense pas avoir la main là dessus. Elle ne fonctionne donc pas comme un curseur au sens que tu lui donnes.

--

D'où la nécessité de limiter le premier SELECT à une seule rangée...

@skuatamad, il faut relire mon message initial où j'ai écrit :"comment récupérer et surtout ne verrouiller que la première rangée ?"et où j'ai aussi précisé que ROWNUM ne pouvait pas être utilisé parce que le comptage inclut les rangées "skippées", donc commence à 2 dans la seconde requête SELECT ROWNUM,[...] FOR UPDATE SKIP LOCKED. Je ne sais pas comment déterminer la première.