jump to navigation

Detectar bloqueos en Oracle febrero 19, 2007

Posted by superpiwi in Java.
trackback

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');

bloqueo01.jpg

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

bloqueo02.jpg

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

Comentarios»

1. NachoProy - febrero 26, 2007

Buenísimo, hombre yo suelo ver en mi curro los bloqueos por medio del Enterprise manager pero claro no siempre lo tenemos a mano y es bueno saber como verlo sin necesidad de esta herramienta.
Tienes dos blogs cojonudos y muy interesantes voy a tener que añadirte a mi blogroll

2. superpiwi - febrero 26, 2007

Muchas gracias. Lo de controlar las sesiones, estado de la base de datos, etc tambien lo puedes ver con el toad en modo dba, pero claro, como tu dices no siempre lo tienes a mano. Esto mas lo comentaba para que tambien se pudiera detectar la situacion de bloqueo desde codigo con tu propio programa, sin embargo estas consultas son un poco lentas.

3. Antón María Rodríguez Yuste - noviembre 13, 2007

[…] La fuente es esta. Posted in Bases de datos || || Convertir a PDF […]

4. Martín_ARG - diciembre 6, 2007

Muy buena la query. Sugiero este Hint para acelerarla un poco:

SELECT /*+ FULL(s) PARALLEL(l, 5) */
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’);

Saludos.

5. Rolando Antonio - febrero 21, 2008

muy bueno me sirve de mucho porque siempre existe la posibilidad que haya lentitud en oracle o que los usuarios no puedan trabajar por bloqueo de sesiones

gracias

6. Super_jaime - marzo 6, 2008

Muchas gracias, no os podéis imaginar cuanto me habéis ayudado.

Un saludo y seguir así.

7. Julepe - abril 2, 2008

Un saludo, muchas gracias por tus importantes apuntes. He visto que la vista v$lock tiene un campo llamado BLOCK, el cuál a veces es usado para filtrar la información de bloqueos. Podrías explicarme para que sirve dicho campo?
Mil gracias por tu valiosa ayuda

8. krlos - junio 10, 2008

Es muy interesante todo lo expuesto pero mi problema es al contrario ya que lo que quiero es tener una tabla bloqueada, os cuento mi inquietud.
Tengo un cluster montado en dos servidores unix, en el cluster tengo montado el motor de oracle, el tema es que necesito tener bloqueada una tabla para que funcione una aplicación, el problema surge cuando se balancea la instancia de un servidor a otro, en ese momento oracle desbloquea la tabla y por tanto la aplicación falla y es necesario pararla y volver a arrancarla, no es fácil controlar el balanceo de cluster al ser transparente para el usuario, por lo que necesito que sea automático el bloqueo de dicha tabla aunque cambie de miembro. ¿Como puedo hacer para que siga bloqueada una tabla después que una instancia de oracle cambie de miembro en un cluster.? Espero vuestra ayuda. Muchas gracias.

9. R - julio 10, 2008

Prueba con esto:

LOCK TABLE IN EXCLUSIVE MODE

ó

LOCK TABLE IN SHARE MODE

10. Tux Dueñas - noviembre 11, 2008

Hola amigos.. muy interesante lo que han escrito.. no se si tienen alguna documentacion que me pudiera explicar un poco mas como comprender los bloqueos y los datos que muestra el query..

Les agradeceria mucho..

11. Roberto - noviembre 21, 2008

Yo tengo ahorita un problema en que utilizan tuxedo en una aplicacion y me esta bloqueando a nivel de tabla y anivel de registro, necesito detectar que esta bloqueando y que tablas me esta bloqueando, con este query puedo verificarlo,

12. borja - octubre 7, 2009

Buenas a todos, me gustaría saber si alguien sabe cómo desbloquear estas tablas sin matar la sesión, ya que necesito tenerla abierta para próximas operaciones.

Muchas gracias a todos.

13. Kirk - diciembre 30, 2009

Saludos a todos,estoy ingresando a este ambiente y en la empresa donde estoy manejan una aplicacion que esta provocando bloqueos , es demasiado engorroso pero quisiera saber luego de haber diagnositicado el objeto o quien bloquea ,que paso sigue , como debo proceder o como manejar esta situacion. Si es por la ofrma de programar en el caso se hacen procesos que si terminan satisfactorios entonces hacen commit.

14. Jama - febrero 24, 2010

Amigos una consulta, y como desbloqueo una fila de una tabla, parece que algun usuario me esta bloqueando una fila y quiero botarla ,,, gracias

Saludos, Jorge

15. Lucas Torres - marzo 31, 2011

Barbaro! Me sirvió

16. Lucas Torres - marzo 31, 2011

hay nuevo material sobre este tema?

17. Jose - junio 8, 2011

gente, queria preguntar si alguien conoce de algun script que me de un informe al dia de un top de las 10 querys que mas se ejecutan en el dia y consumen mas memoria

18. Freddy M - junio 17, 2011

Buenas tardes…
esta muy interesante los querys y muy buenos… para poder controlar los bloqueos…
yo tengo una consulta…
trabajo con una base de datos que se divide en 2 bases…
la idea es administrar el recurso de la base de datos …para no saturarla con procesos diarios…
por lo tanto la mitad de usuarios entran a una base de datos y la otra mitad a la otra…
pero en el toad… reviso si hay alguien bloqueando… solo me aparece una base de datos… me han dicho que utilizando un query puedo ver el bloqueo sea una base u otra…
si tienen alguna idea… seria genial… visto el query anterior creo que si se puede … solo es buscar el como…
Gracias… mi correo is fmucr69@hotmail.com

19. Rubén - julio 29, 2011

Hola a todos, tengo una consulta y de favor me gustaría me ayudaran. Por mi novatez una sesión del TOAD la bloquee por ingresar en diferentes ocasiones la contraseña equivocada…me podrían indicar de qué manera la puedo desbloquear? ya tengo el user y pass correctos pero ya no puedo ingresarlos.
Les doy gracias de antemano por la atención. Saludos!
Rubén.

20. Pablo - octubre 5, 2011

excelente!!!
gracias

21. Laura - diciembre 22, 2011

Gracias!! Me ha servido bastante.

22. Blackad_tkd - enero 2, 2012

Me pueden explicar más a detalle que es cada uno de los datos que muestra, y bueno ya me dijo que tabla está bloqueada, y ahora ?? como la desbloqueo, lo siento soy inexperto en este tema..
Saludos…

23. jorge70 - enero 27, 2012

maravilloso, sus comentarios, gracias.

24. David Aguilar Illescas - marzo 12, 2012

Informacion utilisima!

25. Cristian - marzo 30, 2012

Mis más sinceros agradecimientos, muy útiles las consultas.

Darío R. - diciembre 14, 2012

Excelente, nos ha servido mucho. Tengo una consulta, como obtengo el usuario que está produciendo el bloqueo???

Gracias.

26. mauro - mayo 8, 2013

nunca respondieron la pregunta de 13. Kirk – diciembre 30, 2009
.. yo estoy en la misma situacion

27. Max - mayo 9, 2013

Hola, si no tengo privilegios de sysdba, hay alguna forma de realizar el desbloqueo ?

28. joaquin - febrero 16, 2015

BUENOS DIAS COMO PUEDO SABER SI UNA EMPRESA SIGUE CON LAS CUENTAS BLOQUEADAS


Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: