[LOCK MONITOR] ROWID vide

Bonjour,

J'utilise un base de donnees Oracle 9.2.0.1.

J'ai decouvert recemment dans la console OEM l'outil : Lock Monitor qui permet de voir tous les verrous.

C super mais je vois bien tous les verrous pose mais la seule colonne qui m'interresse est vide.

En effet lorsque je pose un verrou sur un enregistrement d'une table la colonne ROWID est vide ce qui fait que je ne peux pas savoir sur quel enregistrement de cette table est pose le verrou.

QQ un peut m'aider

Merci

Meme sous Grid Control 10g la colonne est vide. Je pense que l'information est reservee pour un usage future.

Je n'ai pas trouve cette faculte sous Raptor non plus. Les corrections sont les bienvenus.

Bonsoir,

En fait cette information n'est renseigne que lorsque un autre utilisateur tente de poser un verrou sur ce meme enregistrement, pour pouvoir trouver cette enregistrement grace au package dbms_rowid grace a la fonction ROWIS_TO_.... (sais plus) qui permet de transformer le rowid de type 0004.0008.000C en AAAAeAAAxA (qq chose comme ca) mais pourquoi cette information n'est elle pas renseigne des la pose du premier enregistrement. Y a t il un parametre a renseigne ? Ceci pourrais nous informer directement sur l'utilisateur qui a poser un verrou sur un enregistrement, pour repondre a la question :
Comment trouver qui a pose un enregistrement sur un verrou ?


DeZ

Je ne suis pas un spécialiste de la version 9 mais dans la version 8, les locks ne sont pas toujours affecté au ROWID mais plus par BLOCK.

Est ce que qq peut confirmer??
Parceque la je ne suis pas sur de moi

Merci

Les verrous sont poses au niveau ligne. Je pense que ceci est vrai depuis la version Oracle V5 (au moins).

Effectivement sous EM, je peux voir la colonne ROWID renseignee lorsqu'une seconde session essaye de verrouiller les enregistrement deja verrouilles par une premiere session. Toutefois tous les verrous ne sont pas affiches, n'est apparement affiche que le premier verrou du meme type sur le meme object (je viens de faire le test sous EM10g avec 150.000 verrous et un seul rowid est affiche).

ok.

En fait c sous Lock Monitor de OEM 9i que je vois la colonne ROWID renseigne lorsque une session essaye de verrouiller un enregistrement (une ligne) deja verrouiller par une autre session.

Lorsqu'une session tente de verrouiller un enre deja verrouillé, cette session soit se mets en attente ou soit redonne la main aussitot et ne peut pas faire de modif sur l'enre mais comment renvoyer un message a cette session pour dire c tel session qui a deja pose un verrou.

Comment trouver la session qui verrouille un enre ?

J'ai un enregistrement verrouiller coment trouver la session associer ?

C facile de voir que tel session verrouille tel table, comment dire que telle session verrouille tel enre ?

?.........

J'ai beau faire toutes les tables SYS impossible trouver.

Ci joint surement un indice mais comprends pas :

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 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330

Doc ID: Note:132629.1 Subject: How to know which row is locked by what user. Type: TROUBLESHOOTING Status: PUBLISHED   Content Type: TEXT/X-HTML Creation Date: 16-JAN-2001 Last Revision Date: 28-JUL-2005     PURPOSE -------   The following programs will attempt to pinpoint all the locked rows of a table and tell who has the lock on each of them.   SCOPE & APPLICATION -------------------   So far this is the only known automatic method to obtain who is locking which row. This is very slow and is NOT bullet-proof. It might block for a short moment other concurrent sessions accesing the table in question. It has been tested only in Unix but should work on any O.S. It requires to be able to compile Pro*C Programs. Due to all the PL/SQL features used, the programs require Oracle RDBMS 8.1.5 or greater.   The DB users executing the Programs need to have execution privileges on the dbms_pipe package, the 'alter system' privilege and access to v_$session and v_$lock; Ej: grant execute on dbms_pipe to test; grant alter session to test; grant select on V_$SESSION to test; grant select on V_$lock; to test;   HOW IT WORKS ------------ The basic idea is to first locate all the rows that are locked and then find out who is locking it. To first locate all the locked rows a cursor is build which will try to lock each row in the table. (Select for update nowait) If it success then that row is not locked by anyone. If it fails it will fail with an ora-54 (See Note 18245.1) which its trapped with the use of an exception. This means this row is being locked.   Now that we know what rows are locked then we proceed to try to find who has the lock on it.   If we try to lock that row with a select for update the session will remain on a waiting state until it is freed. The information that this session is being blocked by another is found in the v$lock under the column request. This line identifies which enqueue is being used in column id1. In this enqueue are all the requests to modifiy the same resource in this case the same row. Since only one person can modify the same row at the same time there will be a row in v$lock which will have in the block column greater than 0. This column tell us how many sessions are waiting on the same enqueue. In this row we will find the Session ID (SID column) which we can look up in v$session giving us the name of the owner of the lock.   The problem here is that we can't wait on a lock and do the query to find out who is blocking us at the same time with the same session, so we use another session to do the lock and wait on it until we obtain all the info. Then, that other session can release the lock and we can proceed to the next row.   There are two programs in this article. A PL/SQL Procedure and a Pro*C Program.   The PL/SQL will find all the locked rows and pass the instruction to the Pro*C to try to put the lock on one of those rows. After we obtain the info the PL/SQL will kill the session of the Pro*C which will reconect waiting for the next instruccion.     HOW TO USE IT -------------   Modify the Pro*C with the proper user and password and compile it. If you name the program 'lock' A suggestion for compiling it is :   make -f demo_proc.mk EXE=lock OBJS=lock.o build PROCFLAGS="SQLCHECK=SEMANTICS userid=test/test"   In a seperate telnet session run the program.   Connect to a session with access to the table in question. issue a SET SERVEROUTPUT ON issue locksmith('with_your_table');   It is indispensable for the Pro*C program to run before the PL/SQL.   The Result will be like this :   Pro*C on telnet 1 ----------------- $ lock Connecting. Waiting. Executing select 1 from EMP where rowid ='AAADWQAABAAAVrrAAJ' for update Connecting. Waiting. Executing select 1 from EMP where rowid ='AAADWQAABAAAVrrAAN' for update Connecting. Waiting. Exiting.   PL/SQL on telnet 2 ------------------ SQL> Set serveroutput on SQL> exec locksmith('EMP'); Row AAADWQAABAAAVrrAAJ locked by SCOTT SID(15) Row AAADWQAABAAAVrrAAN locked by BRUCE SID(9)   PL/SQL procedure successfully completed.     ERROR MESSAGES -------------- If the message 'failure probably due to desyncronization' appears at some point, kill the Pro*C program and reinitialize it before running the PL/SQL again. This message also appears if the PL/SQL was executed before the Pro*C Program.   RELATED DOCUMENTS -----------------   PL/SQL User's Guide and Reference Release 8.1.5 Oracle8 Server Reference Release 8.1.5   SOURCE CODE ------------   Pro*C -----   #include <stdio.h> #include <string.h> EXEC SQL include sqlca;   EXEC SQL begin declare section; char *uid = "TEST/TEST"; /* User/password to connect to Oracle,   Modify accordingly*/ int status; /* Return value for dbms_pipe.send_message   and dbms_pipe.receive_message */ varchar command[20]; /* Command to execute */ varchar value[2000]; /* SQL statement */ int audsid; /* The Audit Session Id to identify myself */     EXEC SQL end declare section;   /* This is the error handler for connecting to Oracle. If we failed on the   connection attempt, we need to exit the program. */ void connect_error() {   char msg_buffer[512]; int msg_length; int buffer_size = 512;   EXEC SQL whenever sqlerror continue; sqlglm(msg_buffer, &buffer_size, &msg_length); printf("Error while connecting:\n"); printf("%.*s\n", msg_length, msg_buffer); printf("Quitting.\n"); exit(1); }     main() {   EXEC SQL EXECUTE begin dbms_pipe.purge('LockSmith'); dbms_pipe.purge('Lock'); end; END-EXEC;   while (1) { EXEC SQL whenever sqlerror do connect_error(); printf("Connecting.\n"); EXEC SQL connect :uid; EXEC SQL EXECUTE begin dbms_pipe.purge('LockSmith'); select userenv('SESSIONID') into :audsid FROM DUAL; dbms_pipe.pack_message(:audsid); :status := dbms_pipe.send_message('LockSmith'); end; END-EXEC;   EXEC SQL whenever sqlerror continue;   printf("Waiting.\n");   EXEC SQL EXECUTE begin :status := dbms_pipe.receive_message('Lock'); if :status = 0 then dbms_pipe.unpack_message(:command); end if; end;   END-EXEC;   if (status == 0) { command.arr[command.len] = '\0'; if (!strcmp((char *)command.arr, "STOP")) { printf("Exiting.\n"); exit(0); } else { EXEC SQL EXECUTE begin dbms_pipe.unpack_message(:value); end; END-EXEC; EXEC SQL whenever sqlerror continue; value.arr[value.len] = '\0'; printf("Executing %s\n",value.arr); EXEC SQL EXECUTE IMMEDIATE :value; status = sqlca.sqlcode; EXEC SQL ROLLBACK WORK; } } } }     PL/SQL ------   create or replace procedure locksmith(table_name varchar2) is type tabcurtype is ref cursor; type tabrowstype is table of varchar2(50) index by binary_integer; table_cursor tabcurtype; rowid_table tabrowstype; row_id rowid; status number; aud_sid number; test_sid number; test_serial number; locker_sid number; locker_name varchar2(1000); message varchar2(1000);   resource_busy exception; pragma exception_init(resource_busy,-54);   begin rowid_table(0):='0'; dbms_output.enable(1000000); open table_cursor for 'select rowid from '||table_name; loop begin fetch table_cursor into row_id; exit when table_cursor%notfound; savepoint one_register; execute immediate 'select 1 from '||table_name ||' where rowid =:r for update nowait' using row_id; exception when resource_busy then rowid_table(rowid_table.last+1):=row_id; end; rollback to savepoint one_register; end loop; close table_cursor;   for r in rowid_table.first+1 .. rowid_table.last loop status:=dbms_pipe.RECEIVE_MESSAGE('LockSmith',1); if status=0 then dbms_pipe.unpack_message(aud_sid); begin select a.sid,a.serial# into test_sid,test_serial from v$session a where a.audsid=aud_sid; dbms_pipe.pack_message('select 1 from '||table_name ||' where rowid ='''||rowid_table(r)||''' for update'); status:=dbms_pipe.send_message('Lock');   status:=0; loop begin select sid,username into locker_sid,locker_name from v$session c where c.sid =( select sid from v$lock a where a.block>0 and a.id1 =(select b.id1 from v$lock b where b.request=6 and b.sid=test_sid ) ); exit; exception when no_data_found then status:=status+1; exit when status>100; end; end loop; if status=<101 then execute immediate 'alter system kill session ''' ||to_char(test_sid)||','||to_char(test_serial)||''''; dbms_output.put_line('Row '||rowid_table(r)||' locked by ' ||locker_name||' SID('||to_char(locker_sid)||')' ); else dbms_output.put_line('Row '||rowid_table(r)||' was locked and released.' ); end if; exception when no_data_found then null; end; else dbms_output.put_line('failure probably due to desyncronization'); exit; end if; end loop; dbms_pipe.pack_message('STOP'); status:=dbms_pipe.send_message('Lock'); end; /   KEYWORDS -------- v$lock v$session lock row enqueue ora-54 resource busy nowait     .

---

Interessant et genial petit bout de code, compose d'un poseur de verrou (l'executable Pro*C) et d'un detecteur de verrou (PL/SQL).
La description est assez claire. En gros, le PL/SQL va determiner la liste des lignes verrouillees puis, pour chaque ligne, envoyer par un pipe au Proc*C les details du record a verrouiller. Le Proc*C va alors tenter de verrouiller le record en question, permettant en PL qui tourne toujours et en parallel de detecter l'attente subie par le Proc*C, en d'enregistrer au passage les details sur la session qui possede le verrou. Le PL passera ensuite a l'enregistrement suivant en ayant au prealable tue la session du Proc*C qui immediatement, se reconnectera en attente d'une nouvelle instruction de verouillage.

Sauriez vous comment l'utiliser, la compiler ?

Je n'ai jamais fais de Proc*C

Merci

Pour le Pro*C, il suffira de copier le contenu du fichier dans un fichier lock.pc, et, par example en 10gR1, le placer dans le repertoire $ORACLE_HOME/precomp/demo/proc.

Ensuite le compiler avec les instructions figurant dans la note:

Code:

---

1 2	make -f demo_proc.mk EXE=lock OBJS=lock.o build PROCFLAGS="SQLCHECK=SEMANTICS userid=scott/tiger@ora10103"

---

Prendre garde a remplacer le schema et password, en dur dans le proc et dans les instructions ci-dessus de precompilation. Donner aussi tous les grants qui vont bien au schema (dbms_pipe, v$session, v$lock), sinon, la precompilation retournera une erreur.

Il y avait quelques typo dans le Proc*C. Voici le mien rapidemment corrige:

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

#include <stdio.h> #include <string.h> #include <setjmp.h> #include <stdlib.h> #include <sqlcpr.h> #include <string.h> #include <sqlca.h>   /* ##EXEC SQL include sqlca; */   EXEC SQL begin declare section; char *uid = "SCOTT/TIGER"; /* User/password to connect to Oracle,   Modify accordingly*/ int status; /* Return value for dbms_pipe.send_message   and dbms_pipe.receive_message */ varchar command[2000]; /* Command to execute */ varchar value[2000]; /* SQL statement */ int audsid; /* The Audit Session Id to identify myself */     EXEC SQL end declare section;   /* This is the error handler for connecting to Oracle. If we failed on the   connection attempt, we need to exit the program. */ void connect_error() {   char msg_buffer[512]; int msg_length; int buffer_size = 512;   EXEC SQL whenever sqlerror continue; sqlglm(msg_buffer, &buffer_size, &msg_length); printf("Error while connecting:\n"); printf("%.*s\n", msg_length, msg_buffer); printf("Quitting.\n"); exit(1); }     main() {   EXEC SQL EXECUTE begin dbms_pipe.purge('LockSmith'); dbms_pipe.purge('Lock'); end; END-EXEC;   while (1) { EXEC SQL whenever sqlerror do connect_error(); printf("Connecting.\n"); EXEC SQL connect :uid; EXEC SQL EXECUTE begin dbms_pipe.purge('LockSmith'); select userenv('SESSIONID') into :audsid FROM DUAL; dbms_pipe.pack_message(:audsid); :status := dbms_pipe.send_message('LockSmith'); end; END-EXEC;   printf("Current session %d.\n",audsid); printf("Waiting.\n");   EXEC SQL EXECUTE begin :status := dbms_pipe.receive_message('Lock'); if :status = 0 then dbms_pipe.unpack_message(:command); end if; end;   END-EXEC;   if (status == 0) { command.arr[command.len] = '\0'; if (!strcmp((char *)command.arr, "STOP")) { printf("Exiting %s.\n",command.arr); printf("Exiting.\n"); exit(0); } else { printf("Executing %s.\n",command.arr); EXEC SQL whenever sqlerror continue; EXEC SQL EXECUTE IMMEDIATE :command; status = sqlca.sqlcode; EXEC SQL ROLLBACK WORK; } } } }

---

Il en etait de meme sur le package, ci-dessous revue a la grosse:

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

CREATE OR REPLACE PROCEDURE locksmith(table_name varchar2) IS   TYPE tabcurTYPE is ref cursor; TYPE tabrowsTYPE is table of varchar2(50) index by binary_integer;   table_cursor tabcurTYPE; rowid_table tabrowsTYPE; row_id rowid; status number; aud_sid number; test_sid number; test_serial number; locker_sid number; l_command varchar2(4000); locker_name varchar2(1000); message varchar2(1000);   resource_busy exception; pragma exception_init(resource_busy,-54);   BEGIN dbms_output.enable(1000000);   open table_cursor for 'select rowid from '||table_name;   loop begin fetch table_cursor into row_id; exit when table_cursor%notfound;   savepoint one_register; execute immediate 'select 1 from '||table_name ||' where rowid =:r for update nowait' using row_id; exception when resource_busy then rowid_table(rowid_table.COUNT +1):=row_id; end; rollback to savepoint one_register; end loop; close table_cursor;   for r in 1..rowid_table.COUNT loop   status:=dbms_pipe.receive_message('LockSmith',1); if status=0 then dbms_pipe.unpack_message(aud_sid); begin select a.sid ,a.serial# into test_sid ,test_serial from v$session a where a.audsid=aud_sid;   l_command := 'select 1 from '||table_name ||' where rowid ='''||rowid_table(r)||''' for update'; dbms_pipe.pack_message(l_command);   status:=dbms_pipe.send_message('Lock');   loop begin select sid ,username into locker_sid ,locker_name from v$session c where c.sid = (select sid from v$lock a where a.block >0 and a.id1 IN (select b.id1 from v$lock b where b.sid=test_sid));   exit;   exception when no_data_found then status:=status+1; exit when status>500; end; end loop;   if status<=501 then BEGIN execute immediate 'alter system kill session ''' ||to_char(test_sid)||','||to_char(test_serial)||''' IMMEDIATE'; EXCEPTION WHEN OTHERS THEN NULL; END; dbms_output.put_line('Row '||rowid_table(r)||' locked by ' ||locker_name||' SID('||to_char(locker_sid)||')' ); else dbms_output.put_line('Row '||rowid_table(r)||' was locked and released.' ); end if;   exception when no_data_found then dbms_output.put_line('Error during kill session'); null; end; else dbms_output.put_line('failure probably due to desyncronization'); exit; end if; end loop; dbms_pipe.pack_message('STOP'); status:=dbms_pipe.send_message('Lock'); end; /

---

Execution du C

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

$ ./lock Connecting. Current session 253. Waiting. Executing select 1 from T where rowid ='AAAMYhAAEAAAAFlAAA' for update. Connecting. Current session 254. Waiting. Executing select 1 from T where rowid ='AAAMYhAAEAAAAFlAAB' for update. Connecting. Current session 255. Waiting. Executing select 1 from T where rowid ='AAAMYhAAEAAAAFlAAC' for update. Connecting. Current session 256. Waiting. Executing select 1 from T where rowid ='AAAMYhAAEAAAAFlAAD' for update. Connecting. Current session 257. Waiting. Exiting STOP. Exiting.

---

Execution du PL/SQL

Code:

---

1 2 3 4 5 6 7 8

SQL> execute locksmith('T'); Row AAAMYhAAEAAAAFlAAA locked by SCOTT SID(148) Row AAAMYhAAEAAAAFlAAB locked by SCOTT SID(148) Row AAAMYhAAEAAAAFlAAC locked by SCOTT SID(148) Row AAAMYhAAEAAAAFlAAD locked by SCOTT SID(148)   PL/SQL procedure successfully completed.

---

Hope this helps.

Les verrous niveaux lignes sont réalisés à travers un byte de verrou dans chaque entête de données d'une ligne et dans les listes interested transaction (ITL) dans chaque bloc de données ou d'index.

Quand une ligne est verouillé, le byte de verrous pointe dans l'entrée ITL qui peut contenir ou ne pas contenir des informations concernant l'etat du verrou (active/inactive)

Toutes les entrée d' une transaction pointent vers l'entrée de la table de transaction dans l'entête du segment undo qui detrmine si la transaction à été validé ou non et si les verrous lignes sont actives ou inactives.

Autre chose oracle stocke les verrous lignes dans les blocs de données eux même et non pas dans des tables de verrous dans le SGA

Bonjour,

Désolé de repondre si tard mais j'ai essaye de comprendre qq chose a votre explication, mais tres franchement ..... je suis novice (pas DBA confirmé)

En fait ce que je desires c tout bete, lorsqu'un enregistrement est verrouille par un user est il possible de savoir quel user a verrouille cette enregistrement soit par son ID ou par son ROWID ?

Merci

PS : d'apres votre ceci n'est pas possible ?

qui peut contenir ou ne pas contenir des informations concernant l'etat du verrou
et non pas dans des tables de verrous dans le SGA