DBMS_LOCK vs LOCK TABLE

Version imprimable

22/02/2007, 10h10
aemag

DBMS_LOCK vs LOCK TABLE

Bonjour,
c'est quoi la différence entre dbms_lock et lock_table?
aussi, est ce qu'il y a moyen de locker sur des enregistrements particuliers, sachant que ces enregistrement proviennent d'un jointure entre plusieurs tables?
22/02/2007, 10h24
pifor

DBMS_LOCK est un package permettant d'utiliser directement les services de verrouillage sans que les verrous soient forcémént liés à une table ou une ligne dans une table.

Le seuil moyen de verrouiller explicitement des lignes dans des tables est d'utiliser la clause FOR UPDATE de la commande SELECT: la clause OF permet de restreindre le verrouillage à un sous-ensemble de tables (sinon toutes les lignes retournées par le SELECT de toutes les tables concernées sont verrouillées):

http://download-uk.oracle.com/docs/c...2.htm#i2130052
22/02/2007, 10h45
aemag
Merci pour ta réponse, seulement j'ai une requête de ce type:
Code:

1 2 3 4 5 6 7 select cold1, cold2 into did,o_ip from (select d.cold1, d.cold2 from table1 p, table2 d where d.cold3=p.col1 and p.col1=parametre order by d.cold1 ) where rownum = 1 for update;
et je veux faire le lock sur cold1 et cold2 de la table Table2.
est ce que le for update marche toujours dans ce cas?
merci
AEMAG
22/02/2007, 10h55
orafrance

un update d'une vue... m'est avis que ça va pas trop bien marcher :koi:
22/02/2007, 11h03
aemag

oui, ça ne marche pas du tout, en plus j'ai oublié qu'on ne fait pas un update for dans c conditions :oops:

Code:

ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.

quelqu'un voit une piste pour ce que je veux faire?
merci
22/02/2007, 11h10
orafrance

en passant par un curseur probablement... là tu sélectionnes une colonne de chacune des tables alors évidemment Oracle ne sait pas quoi locker :?

D'ailleurs, ordonné sur une colonne qui n'est pas de la table à mettre à jour c'est pour le moins curieux :koi:

Si on veut être sûr de verrouiller le plus tôt possible sans attendre que UPDATE pose le verrou, il y a une solution en utilisant le rowid de la ligne concernée:

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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
 
SQL> select * from v$version;
 
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
PL/SQL Release 10.1.0.2.0 - Production
CORE    10.1.0.2.0      Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production
 
SQL>
SQL> drop table t1;
 
Table dropped.
 
SQL> drop table t2;
 
Table dropped.
 
SQL>
SQL> create table t1(x1 int, y1 int);
 
Table created.
 
SQL> create table t2(x2 int, y2 int);
 
Table created.
 
SQL>
SQL> insert into t1 values(1,1);
 
1 row created.
 
SQL> insert into t2 values(1,2);
 
1 row created.
 
SQL> insert into t2 values(1,3);
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL>
SQL> select * from t1;
 
        X1         Y1
---------- ----------
         1          1
 
SQL> select * from t2;
 
        X2         Y2
---------- ----------
         1          2
         1          3
 
SQL>
SQL> --
SQL> -- OK
SQL> --
SQL>
SQL> select at1.x1, at1.y1
  2   from  t1 at1, t2 at2
  3   where at1.x1 = at2.x2 and at2.y2 = 3
  4   for update of at1.x1
  5   order by at1.x1;
 
        X1         Y1
---------- ----------
         1          1
 
SQL>
SQL> commit;
 
Commit complete.
 
SQL>
SQL> --
SQL> -- KO
SQL> --
SQL>
SQL> select x1, y1
  2  from
  3  (select at1.x1, at1.y1
  4   from  t1 at1, t2 at2
  5   where at1.x1 = at2.x2 and at2.y2 = 3
  6   order by at1.x1
  7  )
  8  where rownum  = 1 for update;
(select at1.x1, at1.y1
*
ERROR at line 3:
ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.
 
 
SQL>
SQL>
SQL>
SQL> --
SQL> -- KO ?
SQL> --
SQL>
SQL> select x1, y1
  2  from
  3  (select at1.x1, at1.y1
  4   from  t1 at1, t2 at2
  5   where at1.x1 = at2.x2 and at2.y2 = 3
  6   for update of at1.x1
  7   order by at1.x1
  8  )
  9  where rownum  = 1;
 for update of at1.x1
 *
ERROR at line 6:
ORA-00907: missing right parenthesis
 
 
SQL>
SQL> --
SQL> -- OK
SQL> --
SQL>
SQL> declare
  2  v_r1 rowid;
  3  v_x1 number;
  4  v_y1 number;
  5  begin
  6  select r1, x1, y1
  7  into v_r1, v_x1, v_y1
  8  from
  9  (select at1.rowid r1, at1.x1, at1.y1
 10   from  t1 at1, t2 at2
 11   where at1.x1 = at2.x2 and at2.y2 = 3
 12   order by at1.x1
 13  )
 14  where rownum  = 1;
 15  --
 16  select x1, y1 into v_x1, v_y1
 17  from t1 where rowid = v_r1 for update;
 18  end;
 19  /
 
PL/SQL procedure successfully completed.
 
SQL> show errors
No errors.
SQL>
SQL>
SQL>

22/02/2007, 11h17
aemag

Citation:

D'ailleurs, ordonné sur une colonne qui n'est pas de la table à mettre à jour c'est pour le moins curieux

je ne vois pas ce que tu veux dire? c'est bien la table2 qui est à mettre à jour, et cold1 fait partie de la table 2!
sinon, pour les cursor, je les ai bien évidement utiliser, seulement j'ai un gros problème de performances... et comme je ne ve séléctionner que le premier enregistrement, j'ai trouvé que finalement le curseur n'est pas une bonne solution.
merci
AEMAG

je crois que le mieux est de vous exposer le problème.
je travaille avec la section critique suivante :
Code:

1 2 3 4 5 6 7 8 SELECT cold1, cold2 INTO did,o_ip FROM (SELECT d.cold1, d.cold2 FROM table1 p, table2 d WHERE d.cold3=p.col1 AND p.col1=parametre ORDER BY d.cold1 ) WHERE rownum = 1 ; update table2 set cold3= qqchose where cold1 = did;
mon problème est le suivant: si deux utilisateurs accèdent à la même section critique en même temps, le premier fait le select, et le second aussi, fatalement, ils auront le meme did et o_ip en sortie, et finalement, les deux feront le meme update sur les le même enregistrement. ce qui ne m'arrange pas.
maitenant, quand je fait un lock table sur table2, le problème est résolu, mais j'ai par contre des problèmes de performances.
qq'un a déjà voulu faire la même chose ou voit une solution? ça fait un certain temps que je tourne en rond
Merci
AEMAG

22/02/2007, 11h27
pifor

Pourquoi est-ce la solution avec le 2ième SELECT et le rowid ne fonctionne pas ?

Citation:

Pourquoi est-ce la solution avec le 2ième SELECT et le rowid ne fonctionne pas ?
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 SQL> declare 2 v_r1 rowid; 3 v_x1 number; 4 v_y1 number; 5 begin 6 SELECT r1, x1, y1 7 INTO v_r1, v_x1, v_y1 8 FROM 9 (SELECT at1.rowid r1, at1.x1, at1.y1 10 FROM t1 at1, t2 at2 11 WHERE at1.x1 = at2.x2 AND at2.y2 = 3 12 ORDER BY at1.x1 13 ) 14 WHERE rownum = 1; 15 -- 16 SELECT x1, y1 INTO v_x1, v_y1 17 FROM t1 WHERE rowid = v_r1 FOR UPDATE; 18 end; 19 /
supose que tu as deux utilisateur en meme temps qui accèdent à cette section, le premier fait le premier select et lache la main, le second fait aussi le premier select, dans ce cas, les deux utilisiteurs ont le même enregistrement en sortie, et par conséquand, le même rowid, ce qui n'est pas bon non plus dans mon cas.
AEMAG

22/02/2007, 11h40
aemag

dans la cas ou j'utiliserais DBMS_LOCK, quel mode serait le plus adapté à mon cas?
Merci
AEMAG
22/02/2007, 11h44
pifor

Citation:

les deux utilisiteurs ont le même enregistrement en sortie, et par conséquand, le même rowid, ce qui n'est pas bon non plus dans mon cas

Dans ce cas là il faut peut-être utiliser l'option NOWAIT de FOR UPDATE: si le verrou est déjà pris, l'appelant reçoit une exception ORA-00054 à traiter par un ROLLBACK et il faut recommencer la transaction en refaisant la lecture: s'il obtient alors le verrou, cela veut dire que la première transaction a fait COMMIT (avec mise à jour) ou ROLLBACK (sans mise à jour) et si la première transaction a fait COMMIT, il verra alors la mise à jour de la première transaction (mais uniquement s'il y a bien eu ROLLBACK après ORA-00054).
22/02/2007, 11h46
orafrance
Citation:

Envoyé par aemag

je ne vois pas ce que tu veux dire? c'est bien la table2 qui est à mettre à jour, et cold1 fait partie de la table 2!
sinon, pour les cursor, je les ai bien évidement utiliser, seulement j'ai un gros problème de performances... et comme je ne ve séléctionner que le premier enregistrement, j'ai trouvé que finalement le curseur n'est pas une bonne solution.
merci
AEMAG

c'est moi qui me suis trompé ;)

Essaye :
Code:

1 2 3 4 5 6 7 SELECT * FROM ( SELECT cold1, cold2 INTO did,o_ip FROM table2 WHERE cold3 IN ( SELECT col1 FROM table1 WHERE col1=parametre) ORDER BY 1) FOR UPDATE;
Là tu sélectionnes uniquement table2 :)
22/02/2007, 11h54
aemag

non, ça ne marche pas, j'ai toujours le même message d'erreur, en plus le order by c sur cold1 en non col1 ;)
merci en tout cas
22/02/2007, 12h04
pifor

Ce problème est connu sous le nom de "lost update": il existe plusieurs façons de le résoudre: je recommende de lire toute la longue discussion suivante sur AskTom: http://asktom.oracle.com/pls/asktom/...30562552526857

problème de Dbms_lock

Bonjour,
j'exécute les deux scripts suivants :

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
declare
  v_result     number;
  v_lockhandle varchar2(200);
begin
 
  dbms_lock.allocate_unique('control_lock', v_lockhandle);
 
  v_result := dbms_lock.request(v_lockhandle, dbms_lock.ss_mode);
 
  if v_result <> 0 then 
    dbms_output.put_line(
           case 
              when v_result=1 then 'Timeout'
              when v_result=2 then 'Deadlock'
              when v_result=3 then 'Parameter Error'
              when v_result=4 then 'Already owned'
              when v_result=5 then 'Illegal Lock Handle'
            end);
  end if;
 
  insert into lock_test values ('started', sysdate);
  dbms_lock.sleep(5);
  insert into lock_test values ('ended'  , sysdate);
 
  commit;
 
end;
/

et ensuite

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
declare
  v_lockhandle varchar2(200);
  v_result     number;
begin
 
  dbms_lock.allocate_unique('control_lock', v_lockhandle);
 
  v_result := dbms_lock.release(v_lockhandle);
 
  if v_result <> 0 then 
    dbms_output.put_line(
           case 
              when v_result=1 then 'Timeout'
              when v_result=2 then 'Deadlock'
              when v_result=3 then 'Parameter Error'
              when v_result=4 then 'Already owned'
              when v_result=5 then 'Illegal Lock Handle'
            end);
  end if;
 
end;
/

j'ai deux questions :
- pourquoi quand je fait select * from dbms_lock_allocated j'obient toujours 'control_lock' dans les données alors que j'ai déjà fait un release?
- j'obitent des fois comme code de retour de dbms_lock.release ou dbms_lock.request la valeur 4, ce qui signifie : "Already own lock specified by id or lockhandle". comment corriger le problème surtout que je suis toujours dans la même section?
merci
AEMAG

22/02/2007, 16h00
orafrance

continuons dans cette discussion ;)
22/02/2007, 16h29
aemag

ok, merci
22/02/2007, 16h30
aemag

merci pifor pour ta réponse, c'est de ça dont j'ai besoin