Discussion :

[bruno270579]

Bonsoir,
Dans le cadre de mon boulot, je dois organiser la migration de mon application d'Oracle 10 vers Oracle 11 et quelques queries ont la syntaxe

select ... where rownum < 50 for update nowait skip locked.

Le but de cette instruction est de pouvoir faire du traitement en parallèle sur plusieurs noeuds applicatifs (le 1er noeud traite les 50 premiers records, le noeuds 2 les 50 suivants mais en même temps ...).
Cependant, cette combinaison de mot clefs n'est plus supportée par Oracle 11 (mais bien en Oracle 10).
D'après des premiers tests, il semble que même en Oracle 10, le comportement n'est pas celui attendu (mais ça doit encore être confirmé demain), les 1er tests montrent qu'à cause du rownum, la première instance lock bien 10 records, mais les instances suivantes ne lock rien, Oracle lit les données, mais ne sait pas locké et donc ne retoune aucune donnée.

Auriez vous une solution pour résoudre ce problème?
Faire en sorte qu'en cas de select for update sur 2 noeuds applicatifs, les 2 noeuds puissent locker des données?

Merci d'avance

[skuatamad]

Cependant, cette combinaison de mot clefs n'est plus supportée par Oracle 11 (mais bien en Oracle 10).

Es tu sûr ? je dirais plutôt que c'est le contraire, en 10G cette fonctionnalité existait mais n'était pas documentée, et depuis la 11G elle l'est.
Cependant la doc n'est pas très explicite (rechercher SKIP dans le lien de la doc):

SKIP LOCKED

SKIP LOCKED is an alternative way to handle a contending transaction that is locking some rows of interest. Specify SKIP LOCKED to instruct the database to attempt to lock the rows specified by the WHERE clause and to skip any rows that are found to be already locked by another transaction. This feature is designed for use in multiconsumer queue environments, such as Oracle Streams Advanced Queuing. It enables queue consumers to skip rows that are locked by other consumers and obtain unlocked rows without waiting for the other consumers to finish. 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.

Note on the WAIT and SKIP LOCKED Clauses
If you specify WAIT or SKIP LOCKED and the table is locked in exclusive mode, then the database will not return the results of the SELECT statement until the lock on the table is released. In the case of WAIT, the SELECT FOR UPDATE clause is blocked regardless of the wait time specified.

As tu déjà lu ces 2 très bon liens sur le sujet :
Asktom - 11g FOR UPDATE SKIP LOCKED", version 11.1.0.7
Rob Van Wijk - FOR UPDATE SKIP LOCKED

A l'époque j'avais mené quelques tests pour comprendre le focntionnement (mais je n'ai pas d'expérience de cette fonctionnalité en prod).
Setup du test :

Code :

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

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
SQL> drop table t                                                                                                                      
  2  /                                                                                                                                 
 
Table dropped.                                                                                                                         
 
SQL> create table t as                                                                                                                 
  2  select level id, case when mod(level,5) = 0 then 'YES' else 'NO' end as flag                                                      
  3  from dual
  4  connect by level < 20
  5  /
 
Table created.
 
SQL> 
SQL> create or replace procedure p_lock (p_flag t.flag%type)
  2    as
  3    l_id number;
  4    c sys_refcursor;
  5  begin
  6    for c in (select id from t where flag = 'NO' for update skip locked) loop
  7      dbms_output.put_line('id locked = '||c.id);
  8      -- perform some big traitement
  9      dbms_lock.sleep(2);
 10      update t set flag = p_flag where id = c.id;
 11    end loop;
 12  end;
 13  /
 
Procedure created.
 
SQL>

Je lance la procédure pour traiter les lignes à NO dans la session 1 avec 'S1'.
A la fin de la proc je commit.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SQL> exec p_lock('S1');
id locked = 1
id locked = 2
id locked = 4
id locked = 7
id locked = 9
id locked = 12
id locked = 14
id locked = 17
id locked = 19
 
PL/SQL procedure successfully completed.
 
SQL> commit;
 
Commit complete.

En parallèle je lance la procédure dans une session 2 et j'update les lignes à NO avec 'S2'
A la fin de la proc je commit.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SQL> exec p_lock('S2');
id locked = 3
id locked = 6
id locked = 8
id locked = 11
id locked = 13
id locked = 16
id locked = 18
 
PL/SQL procedure successfully completed.
 
SQL> commit;
 
Commit complete.
 
SQL>

Et voici le résultat dans la table T avec un traitement en parallèle avec SKIP LOCK :

Code :

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

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
SQL> select* from t;
 
        ID FLA
---------- ---
         1 S1
         2 S1
         3 S2
         4 S1
         5 YES
         6 S2
         7 S1
         8 S2
         9 S1
        10 YES
        11 S2
 
        ID FLA
---------- ---
        12 S1
        13 S2
        14 S1
        15 YES
        16 S2
        17 S1
        18 S2
        19 S1
 
19 rows selected.
 
SQL>

Bon, ça n'est qu'un rapide test et je n'ai pas testé la combinaison NOWAIT avec SKIP LOCKED (qui me semble inutile), mais j'aurais tendance à penser que le rownum est inutile, il faut laisser Oracle gérer le passage de ligne en ligne entre les 2 sessions et non le forcer à gérer 50 lignes par 50 lignes.

Tout autre retour d'expérience sur le sujet sera le bien venu

[edit] N'aurais tu pas besoin de creuser l'Advanced Queing dont il est question dans le lien vers Asktom ?

[bruno270579]

Bonjour et merci pour ta réponse,
en effet, je suis sur que la combinaison for update nowait skip locked n'est pas supportée en oracle 11.
Hier, j'ai discuté avec un dba de ma société et il m'a conseillé une solution qui ressemble comme 2 goûtes d'eau à la tienne.
Le seul problème, c'est que je développe sur une application java et que modifier les queries existantes pour les passer en cursor, ça ne sera pas immédiat et que mon chef ne sera pas content

je vais tout de même parcourir les différents liens que tu m'as fournis.

J'ai un peu réfléchis hier et j'ai peut être une possibilité pour contourner mon problème :
1) En db, je crée une table paramètre dans laquelle je stocke des rownum temporaire (valeur initiale 0)
2) Dès qu'une query veut faire du locking, j'incrémente mon paramètre de 10 (par exemple) et je lock les records avec rownum < 10.
3) Si une 2eme query veut faire du locking, même principe, j'incrémente le paramètre de 10 (il est à 20) et je fais un lock sur les 20 premiers record (cette query verra donc 10 record (les 20 - les 10 locké au point 1)).
4) A la fin de chaque query, je diminue la valeur du paramètre de 10), comme ça, pas de risque qu'une query doive traiter trop de données.

C'est tordu, mais ça peut passer je crois.

Aujourd'hui, je compte de toute manière tester le comportement de mon application en Oracle 10, afin de voir exactement les effets du select for update nowait skip locked.
Et si c'est le même effet que le skip locked en Oracle 11, alors je supprime juste le nowait et je suis tranquille.