Cómo Identificar Un Bloqueo en Oracle

Cómo identificar un bloqueo en Oracle
Con este ejemplo, puedes fácilmente identificar un bloqueo en tu base de datos

Oracle vía comandos. Existen 3 vistas que pueden ayudarnos a identificar un
bloqueo en oracle. La manera más fácil de explicar esto es con un ejemplo.
Primero creemos una tabla donde vamos a realizar nuestras pruebas.
SQL> CREATE TABLE test (uno INTEGER, dos VARCHAR2(10));
Table created.
Ahora vamos a insertar algunos registros de prueba y ejecutamos commit.
INSERT INTO test VALUES(1,'AAAAAAAAAA');

INSERT INTO test VALUES(2,'BBBBBBBBBB');
INSERT INTO test VALUES(3,'CCCCCCCCCC');
INSERT INTO test VALUES(4,'DDDDDDDDDD');
INSERT INTO test VALUES(5,'EEEEEEEEEE');
SQL> commit;
Commit complete.
Identificamos nuestro SID con el cual estamos bloqueando la tabla.
SQL> select sys_context('USERENV','SID') SID from dual;
SID
-----------------------
142
Colocamos un bloqueo exclusivo en la tabla.
SQL> LOCK TABLE test IN EXCLUSIVE MODE;
Table(s) Locked.
Abrimos otra sesion en oracle, identificamos nuestros sid y procedemos a

actualizar la tabla.
SQL> select sys_context('USERENV','SID') SID from dual;
SID
-----------------------
119
SQL> UPDATE test SET dos = 'XXXXXXXXXX' WHERE uno = 2;
Al terminar de ejecutar el update nuestra sesión se va a quedar esperando,

esto es debido a que ya tenemos bloqueada la tabla por medio de un bloqueo
exclusivo. Supongamos que nos llama un usuario y nos dice que su sesión no
responde. En este caso primero debemos identificar si existe un bloqueo en la
base de datos, para eso podemos hacer un select sobre la vista DBA_LOCK, y
si encontramos una session con un valor de Blocking en la columna
BLOCKING_OTHERS entonces existe un bloqueo a otro usuario.
SQL> SELECT SESSION_ID,LOCK_TYPE, MODE_HELD FROM dba_locks

WHERE BLOCKING_OTHERS = 'Blocking';
SESSION_ID LOCK_TYPE MODE_HELD

---------- -------------------------- --------------------------------
--------
142 DML Exclusive
Para ver solo el ID del bloqueo.
SQL> SELECT * FROM DBA_BLOCKERS;
HOLDING_SESSION
---------------
142
Para ver a quien está siendo bloqueado
SQL> SELECT * FROM DBA_WAITERS;
WAITING_SESSION HOLDING_SESSION LOCK_TYPE MODE_HELD MODE_REQUESTED

--------------- --------------- ---------- ---------- ---------------
119 142 DML Exclusive Row-X (SX) 81714
En este podemos ver que existen una sesión bloqueadas por el usuario 142.
Para matar esa sesión tenemos que identificar su SERIAL#
SQL> SELECT SID,SERIAL# FROM V$SESSION WHERE SID = 142
SID SERIAL#
---------- ----------
142 63166
Ahora si podemos eliminar esa sesión (No es conveniente estar matando todas
las sesiones que estén bloqueando usuario, debemos siempre de identificar el
problema que lo causa, alguna transacción abierta dejada por algún
desarrollador, un error en la aplicación, etc.)
SQL> ALTER SYSTEM KILL SESSION '142,63166';
System altered.
Si revisamos nuevamente, ya no vamos a encontrar registros que estén

bloqueando.
SQL> SELECT * FROM DBA_WAITERS
no rows selected
Quisiera anexar algunos queries más que también nos muestran los bloqueos:
select l1.sid, ' IS BLOCKING ', l2.sid
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2;
Para RAC podemos usar el siguiente
SELECT substr(DECODE(request,0,'Holder: ','Waiter: ')||sid,1,12) sess,

id1, id2, lmode, request, type, inst_id
FROM GV$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM GV$LOCK WHERE request>0)
ORDER BY id1, request;
Cómo revisar los bloqueos actuales en una base de datos Oracle
El programa Oracle utiliza una función de bloqueo para garantizar que más de una función
actualice los datos a la vez y, de esta manera, mantener la integridad de la información. El
bloqueo ocurre en muchos niveles, incluyendo las filas, tablas y espacios en tablas. El bloqueo a
nivel de usuario, el comando "commit" hace que los cambios sean permanentes y elimina el
bloqueo. El comando "rollback" revierte los cambios antes de liberar el bloqueo. El programa
Oracle registra la información del bloqueo en una tabla del sistema, el cual se denomina
"v$lock" y lo podrás consultar usando el Lenguaje de consulta estructurado (SQL).
Paso 1
Revisa la tabla Oracle "v$lock" para bloquear la información. Utiliza la opción "Seleccionar"
para ver "sid", "type", "lmode" y "request". Sid representa el número ID de sesión e identifica
la sesión que posee el bloqueo.
select sid, type, lmode, request from v$lock;
Paso 2
Une la tabla Oracle "v$session" a la tabla "v$lock" para determinar quién está bloqueando la
base de datos. Une la sesión sid (sesión ID) para encontrar el nombre de usuario.
select s.username, s.sid, l.type, l.lmode, l.request from v$lock l, v$session s where l.sid = s.sid;
Paso 3
Consulta la tabla "v$locked_object" para obtener más información sobre lo que la está
bloqueando. Une la tabla "dba_objects" para el nombre y el tipo. El nombre del objeto indica
que está bloqueado y las columnas del nombre de usuario identifican a quién ha creado el
bloqueo.
select oracle_username os_user_name, locked_mode, object_name, object_type from

v$locked_object lo,dba_objects do where lo.object_id = do.object_id;
ORACLE, ver tablas bloqueadas

Este query lista las tablas con bloqueos
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');
Sesiones Bloqueadas en Oracle

REVISAR SESIONES BLOQUEDAS EN ORACLE
Muchas veces debido a la codificación de nuestras aplicaciones se presentan casos en los que
un segundo usuario se queda esperando mucho tiempo a que finalice una transacción porque
un primer usuario ya tiene bloqueada una o varias tablas que el segundo requiere.
¿Cómo saber que sesiones están bloqueando y cuales están esperando?
1. Qué usuarios están creando bloqueos?
SELECT Session_Id, Lock_Type, Mode_Held
FROM Dba_Locks
WHERE Blocking_Others = 'Blocking';
Con esta sentencia sabré el Id de la sesión que me está ocasionando un bloqueo. Pero quizás
quiera saber también quienes están esperando. Los resultado de esta consulta son algo así:
Esto quiere decir que la sesión 171 tiene elementos bloqueados
2. Qué usuarios están esperando a que otra sesion desbloquee recuersos?
SELECT * FROM Dba_Waiters;
Los resultados de esta consulta son como este:
Me muestra que la sesión 69 está esperando a que la 171 libere algún recurso.
Sin embargo estos resultados me dicen poco, tendría que ingresar a ver las sesiones para
saber quiénes son estos usuarios y qué están haciendo, pero podemos mejorar el query
3. Qué recursos están usando quienes bloquean y esperan?
SELECT l.Sid, l.Id1, l.Id2, l.Lmode, o.Owner Object_Owner,

o.Object_Name,
s.Username, s.Osuser, s.Machine, s.Wait_Class,

s.Seconds_In_Wait
FROM Sys.All_Objects o, V$lock l, V$session s, Dba_Waiters d
WHERE (l.Sid = d.Waiting_Session OR l.Sid = d.Holding_Session)
AND l.Type = 'TM'
AND o.Object_Id = l.Id1
AND l.Sid = s.Sid
ORDER BY l.Id1;
El query nos dará este resultado:
En la columna Object_Name nos dice cuál es el recurso usado o esperado, como está
ordenado por el número de apariciones en el resultado, podemos saber cuáles son las que
están bloqueadas. El OSUSER es el usuario de sistema operativo y Machine el nombre del
equipo con el que el usuario se logueó, así podremos identificar mejor a los actores en este
asunto. Seconds_in_Wait me dice el tiempo que lleva esperando (el que está en espera) y la
última columna es simplemente la cuenta con la que estoy ordenando el resultado.
Una vez finalizada alguna de las dos sesiones en conflicto el resultado de los 3 querys debe
estar vacío.
Encontrar y matar sesiones bloqueadas

El bloqueo de sesiones se produce cuando uno o más sesiones sostienen un bloqueo
exclusivo sobre un objeto y no lo libera antes de que otra sesión quiera actualizar los
mismos datos. Esto bloqueará el segundo hasta que el primero ha hecho su trabajo.
Desde el punto de vista del usuario lo que verá será como la aplicación se cuelga por
completo y estará a la espera hasta que la primera sesión libere su bloqueo. A
menudo se tiene que identificar estas sesiones con el fin de mejorar su aplicación para
evitar el mayor número de sesiones de bloqueo posible.
Podemos encontrar las sesiones bloqueadas con el siguiente script el cual esta
buscando los objetos que empiecen con MTL.
select a.object_name, b.ORACLE_USERNAME, b.SESSION_ID
from all_objects a, v$locked_object b
where a.OBJECT_ID = b.OBJECT_ID

and a.object_name like 'MTL%'
order by session_id asc
Ejecutando en base de datos nos saldrá lo siguiente:
Ahora si queremos matar una sesión de una de las tablas podemos realizar lo
siguiente:
DECLARE
CURSOR c IS
SELECT c.owner,
c.object_name,
c.object_type,
b.SID,
b.serial#,
b.status,
b.osuser,
b.machine
FROM v$locked_object a, v$session b, dba_objects c
WHERE b.SID = a.session_id AND a.object_id = c.object_id
and c.object_name in ('MTL_SYSTEM_ITEMS_INTERFACE');
c_row c%ROWTYPE;
l_sql VARCHAR2(100);
BEGIN
OPEN c;
LOOP
FETCH c INTO c_row;
EXIT WHEN c%NOTFOUND;
l_sql := 'alter system kill session

'''||c_row.SID||','||c_row.serial#||'''';
EXECUTE IMMEDIATE l_sql;
END LOOP;
CLOSE c;
END;
Una ves ejecutado la sesión ha sido eliminada y la tabla ya no estara bloqueada.
Desbloquear tablas en oracle

Como desbloquear tablas u objetos en Oracle.
Lo primero es validar que efectivamente existe un bloqueo, reemplazamos el

USUARIO_ORACLE por su usuario
select mode_held from dba_dml_locks where owner='USUARIO_ORACLE';
El siguiente paso es obtener la sesión que se quedo bloqueada
select oracle_username || ' (' || s.osuser || ')' username
, s.sid || ',' || s.serial# sess_id

, owner || '.' || object_name object
, object_type
, decode( l.block
, 0, 'Not Blocking'
, 1, 'Blocking'
, 2, 'Global') status
, decode(v.locked_mode
, 0, 'None'
, 1, 'Null'
, 2, 'Row-S (SS)'
, 3, 'Row-X (SX)'
, 4, 'Share'
, 5, 'S/Row-X (SSX)'
, 6, 'Exclusive', TO_CHAR(lmode)) mode_held
from v$locked_object v
, dba_objects d
, v$lock l
, v$session s
where v.object_id = d.object_id
and v.object_id = l.id1
and v.session_id = s.sid
order by oracle_username
, session_id;
Ahora que ya se tiene la sesión se ejecuta el siguiente script para terminarla
alter system kill session '56983';
Si falla al termiarla se intenta forzarla
alter system kill session '56983' IMMEDIATE;
Si falla terminar la sesión, debemos terminar la tarea desde una consola. Obtenemos el
SID con la siguiente consulta:
SELECT m.sid, m.spid, m.osuser, m.program FROM v$process p, v$session m WHERE

m.addr = m.paddr;
Y ejecutamos el comando en la consola
kill -9 58623

Cómo Identificar Un Bloqueo en Oracle

Cargado por

Copyright:

Formatos disponibles

Cómo Identificar Un Bloqueo en Oracle

Cargado por

Información del documento

Título original

Derechos de autor

Formatos disponibles

Compartir este documento

Compartir o incrustar documentos

Opciones para compartir

¿Le pareció útil este documento?

¿Este contenido es inapropiado?

Copyright:

Formatos disponibles

Cómo Identificar Un Bloqueo en Oracle

Cargado por

Copyright:

Formatos disponibles

Cómo identificar un bloqueo en Oracle

Con este ejemplo, puedes fácilmente identificar un bloqueo en tu base de datos

SQL> CREATE TABLE test (uno INTEGER, dos VARCHAR2(10));

Ahora vamos a insertar algunos registros de prueba y ejecutamos commit.

INSERT INTO test VALUES(1,'AAAAAAAAAA');

Identificamos nuestro SID con el cual estamos bloqueando la tabla.

SQL> select sys_context('USERENV','SID') SID from dual;

Colocamos un bloqueo exclusivo en la tabla.

SQL> LOCK TABLE test IN EXCLUSIVE MODE;

Abrimos otra sesion en oracle, identificamos nuestros sid y procedemos a

SQL> select sys_context('USERENV','SID') SID from dual;

SQL> UPDATE test SET dos = 'XXXXXXXXXX' WHERE uno = 2;

Al terminar de ejecutar el update nuestra sesión se va a quedar esperando,

SQL> SELECT SESSION_ID,LOCK_TYPE, MODE_HELD FROM dba_locks

SESSION_ID LOCK_TYPE MODE_HELD

Para ver solo el ID del bloqueo.

SQL> SELECT * FROM DBA_BLOCKERS;

Para ver a quien está siendo bloqueado

SQL> SELECT * FROM DBA_WAITERS;

WAITING_SESSION HOLDING_SESSION LOCK_TYPE MODE_HELD MODE_REQUESTED

Para matar esa sesión tenemos que identificar su SERIAL#

SQL> SELECT SID,SERIAL# FROM V$SESSION WHERE SID = 142

SQL> ALTER SYSTEM KILL SESSION '142,63166';

Si revisamos nuevamente, ya no vamos a encontrar registros que estén

SQL> SELECT * FROM DBA_WAITERS

Para RAC podemos usar el siguiente

SELECT substr(DECODE(request,0,'Holder: ','Waiter: ')||sid,1,12) sess,

Cómo revisar los bloqueos actuales en una base de datos Oracle

select sid, type, lmode, request from v$lock;

select oracle_username os_user_name, locked_mode, object_name, object_type from

ORACLE, ver tablas bloqueadas

Sesiones Bloqueadas en Oracle

¿Cómo saber que sesiones están bloqueando y cuales están esperando?

1. Qué usuarios están creando bloqueos?

SELECT Session_Id, Lock_Type, Mode_Held

WHERE Blocking_Others = 'Blocking';

Esto quiere decir que la sesión 171 tiene elementos bloqueados

2. Qué usuarios están esperando a que otra sesion desbloquee recuersos?

SELECT * FROM Dba_Waiters;

Los resultados de esta consulta son como este:

SELECT l.Sid, l.Id1, l.Id2, l.Lmode, o.Owner Object_Owner,

s.Username, s.Osuser, s.Machine, s.Wait_Class,

FROM Sys.All_Objects o, V$lock l, V$session s, Dba_Waiters d

WHERE (l.Sid = d.Waiting_Session OR l.Sid = d.Holding_Session)

AND l.Type = 'TM'

AND o.Object_Id = l.Id1

AND l.Sid = s.Sid

El query nos dará este resultado:

Encontrar y matar sesiones bloqueadas

select a.object_name, b.ORACLE_USERNAME, b.SESSION_ID

from all_objects a, v$locked_object b

where a.OBJECT_ID = b.OBJECT_ID

order by session_id asc

Ejecutando en base de datos nos saldrá lo siguiente:

FROM v$locked_object a, v$session b, dba_objects c

WHERE b.SID = a.session_id AND a.object_id = c.object_id

and c.object_name in ('MTL_SYSTEM_ITEMS_INTERFACE');

FETCH c INTO c_row;

EXIT WHEN c%NOTFOUND;