SELECT FOR UPDATE SKIP LOCKED ORA-02014 [Résolu]

[Moostiq]

Bonjour,
Je suis en Oracle 11g.
J'utilise une table UTILISATEUR_ORA comme pool de connexion (couple login/mot de passe).
Un webservice devrait appeler la fonction ci dessous afin de retourner l'id d'une seule ligne de cette table telle que la ligne soit la plus ancienne (attr UTO_DATE_SATUT) et dans l’état 'LIBRE' (attr UTO_STA). ou -1 si il n'y a plus de ligne correspondante (plus de place [pour le moment]dans le pool de connexion)

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
 
-- -------------------------------------------------------------
-- Tentative de revervation d'un couple login/mot de passe dans le pool (UTILISATEUR_ORA)
-- -------------------------------------------------------------
FUNCTION Reserve_UOC(p_id_java IN NUMBER)
RETURN NUMBER IS 
PRAGMA AUTONOMOUS_TRANSACTION;
--Retour -1 en cas d'erreur sinon la clé de la table UTO_SEQ
-- compte INTEGER;
v_retour NUMBER(38,0);
v_session_id NUMBER;
v_UOC_trouve BOOLEAN;
v_UOC_dispo UTILISATEUR_ORA%ROWTYPE;
 
CURSOR CUR_UOC_LIBRE_PLUS_VIEUX IS
SELECT * 
FROM (
SELECT * FROM  UTILISATEUR_ORA 
WHERE UTO_STA='LIBRE' 
ORDER BY UTO_DATE_STATUT ASC) WHERE rownum=1
FOR UPDATE SKIP LOCKED;
 
BEGIN
  v_UOC_trouve:=FALSE;
  v_retour:=-1;
	/* On recupere l'identificateur de session */
	SELECT USERENV('SESSIONID') INTO v_session_id FROM dual;
	/* On scanne tous les tuples en etat LIBRE du plus ancien au plus recent */
	OPEN CUR_UOC_LIBRE_PLUS_VIEUX;
	LOOP
		FETCH CUR_UOC_LIBRE_PLUS_VIEUX INTO v_UOC_dispo;
		EXIT WHEN v_UOC_trouve=TRUE OR CUR_UOC_LIBRE_PLUS_VIEUX%NOTFOUND;
		--DBMS_OUTPUT.PUT_LINE('Nombre Ligne Utilisateur_ORA dispo:*'||CUR_UOC_LIBRE_PLUS_VIEUX%ROWCOUNT || '*.');
		--DBMS_OUTPUT.PUT_LINE('Utilisateur trouvé:*'||v_UOC_dispo.UTO_USR_GPL||'*');
		v_UOC_trouve:=TRUE;
    UPDATE UTILISATEUR_ORA 
    SET UTO_STA='RESERVE', UTO_DATE_STATUT=SYSDATE, UTO_ID_ORA=v_session_id, UTO_ID_THREAD=p_id_java
		WHERE CURRENT OF CUR_UOC_LIBRE_PLUS_VIEUX;
		v_retour:=v_UOC_dispo.UTO_SEQ;
	END LOOP;	
	CLOSE CUR_UOC_LIBRE_PLUS_VIEUX;
 
	IF v_UOC_trouve=FALSE THEN
		COMMIT;
    RETURN -1;
   ELSE 
    COMMIT;
    RETURN v_retour;
	END IF;
END Reserve_UOC;

Après tentative de test de la requête du curseur (qui doit me ramener l'id de cette fameuse ligne [+ ancienne et statut libre]), j'obtiens l'erreur :

Code :

1
2
3
4
 
ORA-02014: SELECT FOR UPDATE depuis vue imposs. avec fonctions DISTINCT, GROUP BY, etc
02014. 00000 -  "cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc."
Erreur à la ligne 2, colonne 6

Qu'ai - je fais pour mériter cela ?

Et surtout comment obtenir un curseur qui ne fetch qu'une seule ligne selon les conditions : la plus ancienne et le statut à LIBRE ?
Car FOR UPDATE SKIP LOCKED devrait ne me retourner qu'une ligne non verrouillée ET correspondant à mes conditions citées ci-dessus, non ?

Merci pour votre aide

[mnitu]

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
 
 UPDATE UTILISATEUR_ORA 
    SET UTO_STA='RESERVE', 
        UTO_DATE_STATUT=SYSDATE, 
        UTO_ID_ORA=USERENV('SESSIONID'), 
        UTO_ID_THREAD=p_id_java
  WHERE UTO_STA='LIBRE' 
    AND <condition_enregistrement_unique>
  Returning UTO_SEQ INTO l_retour;
--
  IF SQL%NOTFOUND Then
    l_retour = -1;
  End IF;

Mettez à la place de <condition_enregistrement_unique> vos critères qui permetront d'identifier un unique enregistrement.

[Moostiq]

Bonjour mnitu et merci pour votre réponse,
Mais n'y aura - t il pas de problèmes de 'lost_update' si plusieurs threads fonds appelle à la fonction Reserve_UOC dans un même laps de temps ?

En fait, en parallele de cette fonction j'ai un trigger on logon database qui fait un update sur UTILISATEUR_ORA (et crée un job pour modifier le mot de passe aleatoire) tel que :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
  v_ddl_cmd:='ALTER USER '||v_username||' IDENTIFIED BY '||v_new_passwd||'';
  /* Synchrone */  
  sys.DBMS_SCHEDULER.CREATE_JOB (
   job_name           =>  'job_trg_log_on',
   job_type           =>  'PLSQL_BLOCK',
   job_action         =>  'BEGIN PKG_OTP.execute_alter_user(''' || v_ddl_cmd || '''); END;',
   start_date         =>  NULL,
   repeat_interval    =>  NULL,
   auto_drop          =>  TRUE,
   enabled            =>  TRUE,
   comments           =>  'trg_log_on job');
  sys.DBMS_SCHEDULER.RUN_JOB (
   'job_trg_log_on',
   TRUE);-- use_current_session TRUE => synchrone 
   -- SELECT job_name, LOG_DATE, operation, status FROM USER_SCHEDULER_JOB_LOG;
   -- "REASON="manual slave run"
   -- is not an error message - it just says that this run was run manually in a foreground session and not in the background by a job slave (which is the normal case).
 
 L_Requete:='UPDATE UTILISATEUR_ORA SET UTO_PWD_GPL=:a, UTO_DATE_STATUT=:b, UTO_STA=:c WHERE UTO_USR_GPL=:d';
 Execute immediate L_Requete USING v_new_passwd, v_date_connexion, 'LIBRE', v_username;
 
  COMMIT;

[mnitu]

Envoyé par Moostiq

Bonjour mnitu et merci pour votre réponse,
Mais n'y aura - t il pas de problèmes de 'lost_update' si plusieurs threads fonds appelle à la fonction Reserve_UOC dans un même laps de temps ?
...

Il peut y avoir un update restart mais pas de lost update.