Detectar bloqueos en Oracle

En Oracle hay una vista v$lock que nos indica los objetos que se encuentran en bloqueo, el identificador de usuario y sesion y el tipo de bloqueo.

Un join con la tabla dba_objects nos proporciona ademas el nombre y tipo de los objetos bloqueados:


SELECT
decode(L.TYPE,'TM','TABLE','TX','Record(s)') TYPE_LOCK,
decode(L.REQUEST,0,'NO','YES') WAIT,
S.OSUSER OSUSER_LOCKER,
S.PROCESS PROCESS_LOCKER,
S.USERNAME DBUSER_LOCKER,
O.OBJECT_NAME OBJECT_NAME,
O.OBJECT_TYPE OBJECT_TYPE,
concat(' ',s.PROGRAM) PROGRAM,
O.OWNER OWNER
FROM v$lock l,dba_objects o,v$session s
WHERE l.ID1 = o.OBJECT_ID
AND s.SID =l.SID
AND l.TYPE in ('TM','TX');


Existen principalmente dos tipos de bloqueo:
bloqueos de tablas (TM) y
bloqueos a nivel de fila (TX)

Los bloqueos a nivel de tabla son creados cuando se ejecuta una sentencia DML del tipo: update, insert, delete, select ..for update sobre la tabla entera. Por ejemplo:


DELETE from mitabla;
UPDATE mitabla SET campo1 = valor;

Los bloqueos a nivel de fila se crean cuando se ejecutan senencias DML contra un conjunto de registros específicos.

Una consulta sobre esta vista nos permite rapidamente saber que procesos estan bloqueados y si ademas hacemos un join con v$open_cursor podemos ver que consulta es la que se encuentra parada a la espera de que se produzca el desbloqueo para poder ejecutarse. En la consulta siguiente podemos ver las sentencias paradas y el id de proceso que las esta bloqueando.


-- he adaptado la consulta con sys.<nombrevista> pq la lanzaba desde un usuario que no era sys. esta query permite ver los objetos que estan esperando a que termine un bloqueo y la sentencia que quieren ejecutar. el id de proceso nos da la pista de quien esta bloqueando
select /*+ ordered
no_merge(L_WAITER)
no_merge(L_LOCKER) use_hash(L_LOCKER)
no_merge(S_WAITER) use_hash(S_WAITER)
no_merge(S_LOCKER) use_hash(S_LOCKER)
use_nl(O)
use_nl(U)
*/
/* first the table-level locks (TM) and mixed TM/TX TX/TM */
S_LOCKER.OSUSER OS_LOCKER,
S_LOCKER.USERNAME LOCKER_SCHEMA,
S_LOCKER.PROCESS LOCKER_PID,
S_WAITER.OSUSER OS_WAITER,
S_WAITER.USERNAME WAITER_SCHEMA,
S_WAITER.PROCESS WAITER_PID,
'Table lock (TM): '||U.NAME||'.'||O.NAME||
' - Mode held: '||
decode(L_LOCKER.LMODE,
0, 'None', /* same as Monitor */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
'???: '||to_char(L_LOCKER.LMODE))||
' / Mode requested: '||
decode(L_WAITER.REQUEST,
0, 'None', /* same as Monitor */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
'???: '||to_char(L_WAITER.REQUEST))
SQL_TEXT_WAITER
from
V$LOCK L_WAITER,
V$LOCK L_LOCKER,
V$SESSION S_WAITER,
V$SESSION S_LOCKER,
sys.OBJ$ O,
sys.USER$ U
where S_WAITER.SID = L_WAITER.SID
and L_WAITER.TYPE IN ('TM')
and S_LOCKER.sid = L_LOCKER.sid
and L_LOCKER.ID1 = L_WAITER.ID1
and L_WAITER.REQUEST > 0
and L_LOCKER.LMODE > 0
and L_WAITER.ADDR != L_LOCKER.ADDR
and L_WAITER.ID1 = O.OBJ#
and U.USER# = O.OWNER#
union
select /*+ ordered
no_merge(L_WAITER)
no_merge(L_LOCKER) use_hash(L_LOCKER)
no_merge(S_WAITER) use_hash(S_WAITER)
no_merge(S_LOCKER) use_hash(S_LOCKER)
no_merge(L1_WAITER) use_hash(L1_WAITER)
no_merge(O) use_hash(O)
*/
/* now the (usual) row-locks TX */
S_LOCKER.OSUSER OS_LOCKER,
S_LOCKER.USERNAME LOCKER_SCHEMA,
S_LOCKER.PROCESS LOCK_PID,
S_WAITER.OSUSER OS_WAITER,
S_WAITER.USERNAME WAITER_SCHEMA,
S_WAITER.PROCESS WAITER_PID,
'TX: '||O.SQL_TEXT SQL_TEXT_WAITER
from
V$LOCK L_WAITER,
V$LOCK L_LOCKER,
V$SESSION S_WAITER,
V$SESSION S_LOCKER,
V$_LOCK L1_WAITER,
V$OPEN_CURSOR O
where S_WAITER.SID = L_WAITER.SID
and L_WAITER.TYPE IN ('TX')
and S_LOCKER.sid = L_LOCKER.sid
and L_LOCKER.ID1 = L_WAITER.ID1
and L_WAITER.REQUEST > 0
and L_LOCKER.LMODE > 0
and L_WAITER.ADDR != L_LOCKER.ADDR
and L1_WAITER.LADDR = L_WAITER.ADDR
and L1_WAITER.KADDR = L_WAITER.KADDR
and L1_WAITER.SADDR = O.SADDR
and O.HASH_VALUE = S_WAITER.SQL_HASH_VALUE


En este ejemplo tenemos la tabla SMT_TESTCASE bloqueada con una sentencia:

SELECT * FROM SMT_TESTCASE FOR UPDATE

(y no hemos realizado el commit), asi que cualquier sentencia posterior que intente acceder a la tabla se queda parada (es este caso se paran las dos sentencias DELETE from hasta que la tabla bloqueada ya no este en situacion de bloqueo).

Esta ultima consulta es util porque podemos anotar el id del proceso bloqueante y ejecutando la primera consulta con el id de proceso averiguar que objeto (SMT_TESTCASE) y de que tipo (TABLE) se encuentra bloqueado.

Comentar que muchas situaciones de bloqueo se producen porque podemos tener el cliente (por ejemplo el Toad) sin activar la opcion de «autocommit». Asi, que al salir es cuando se nos indica que hagamos el «commit» o «rollback», pero si ejecutamos una sentencia DDL sin hacer un «commit» a continuacion podemos provocar una situacion de bloqueo a otro proceso que intente actualizar la tabla o filas bloqueadas.

Mas informacion en:

Oracle Locking Survival Guide