Cómo revisar los bloqueos actuales en una base de datos OracleEscrito por sara wayne | Traducido por xochitl gutierrez cervantes

El bloqueo que se gesta con el programa Oracle protege la integridad de los datos.

lock image by Dwight Davis from Fotolia.com

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).

Nivel de dificultad:Moderada

Otras personas están leyendo

¿Cuál es el propósito de la tecla de bloqueo de cambio en el cambio de velocidad?

Cómo anular el bloqueo de una base de datos en SQL

Instrucciones

1. 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;

2. 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;

3. 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 identifica 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;

Cómo identificar un bloqueo en OracleOracle - Administración

Miércoles, 22 de Julio de 2009 18:00

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

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 session 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 session 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 esta 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 sesion bloqueadas por el usuario 142.

Para matar esa session tenemos que identificar su SERIAL#

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

SID SERIAL#

---------- ----------

142 63166

Ahora si podemos eliminar esa session (No es conveniente estar matando todas las sessiones que

esten bloqueando usuario, debemos siempre de identificar el problema que lo causa, alguna

transaccion 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 esten 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;

Espero les sea de utilidad.

Oracle FormsEste blogAcceso desde aquíEnlaces de Interés

martes, 19 de enero de 2010

¿Las claves foráneas producen bloqueos en Forms?La respuesta es que si no hacemos bien las cosas es SI. Vamos a verlo mejor con un ejemplo con las típicas tablas de formación que utiliza Oracle, DEPT y EMP:

CREATE TABLE dept (deptno NUMBER(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY,dname VARCHAR2(14),loc VARCHAR2(13));

CREATE TABLE emp (empno NUMBER(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,ename VARCHAR2(10),job VARCHAR2(9),mgr NUMBER(4),hiredate DATE,sal NUMBER(7,2),comm NUMBER(7,2),deptno NUMBER(2) CONSTRAINT emp_ref_dept_fkREFERENCES dept(deptno));

Insertamos un departamento:

INSERT INTO dept (deptno, dname, loc)VALUES (23, 'CONTABILIDAD', 'VIGO');

COMMIT;

Ahora insertamos un empleado:

INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES (1234, 'MARIOBROS', 'FONTA', 10,TO_DATE('16/05/1975', 'DD/MM/YYYY'),50000, 2000, 23);

Antes de hacer el commit, al ver los bloqueos veremos que están la tabla EMP y la DEPT bloqueadas.

SELECT lk.sid, lk.TYPE lock_type, lk.lmode,(SELECT o.object_name

FROM dba_objects oWHERE o.object_id = lk.id1) object_nameFROM gv$lock lkWHERE lk.TYPE IN ('TM', 'UL');

Lo que nos devolverá será esto:

SID LO LMODE OBJECT_NAME------ -- --------- ----------------311 TM 3 EMP311 TM 3 DEPT

El bloqueo de la tabla EMP es por el registro que acabamos de insertar pero que todavía no está commitado, pero la tabla DEPT se bloquea por la clave foránea, ya que en el momento de insertar el registro en EMP existía el departamento 23 en DEPT, pero como no hemos comitado el insert en EMP tiene que bloquear el registro del departamento 23 para evitar que otra sesión borre ese registro o modifique el código del departamento.

Hasta aquí todo normal, el problema viene cuando otra sesión quiere modificar el nombre del departamento:

UPDATE deptSET dname = 'FINANCIERO'WHERE deptno = 23;

Con esta update nos dejará modificarlo sin problema, pero si en vez de hacer un update que únicamente modifica el nombre del departamento hacemos este otro (el resultado final de los datos en la tabla serán los mismos):

UPDATE deptSET deptno = 23,dname = 'FINANCIERO',loc = 'VIGO'WHERE deptno = 23;

El registro quedaría con los mismos datos, pero ya tenemos el bloqueo montado ya que le estamos diciendo que actualice el campo DEPTNO, pero la modificación de ese campo afectaría a registros introducidos por otra sesión y que todavía no están comitados, por lo que se quedará bloqueado.

Esta es la explicación del motivo, pero ¿porqué ocurre en Forms?. La respuesta la encontramos en la propiedad "Actualizar Sólo Columnas Cambiadas" a nivel de bloque, por defecto esa propiedad está a "No" con lo que para hacer un update actualizará todos los campos, incluso los que no ha modificado el usuario.

Lo recomendable para evitar bloqueos es poner esa propiedad al valor "Si".

http://comunidadoraclehispana.ning.com/forum/topics/ora-00060-deadlock-detected?commentId=2315969%3AComment%3A79962

ORA-00060: Deadlock detected Publicado por andres felipe styles el agosto 12, 2012 a las 4:24pm en Tecnologias Oracle

Ver discusiones

buen dia

compañeros tengo muy concurrentemente este error en el log de mi base de datos y se que es un bloqueo de tipo x he leido de aumentar mis initrans a 10 pero no he solucionado no se que mas debo de hacer agradeseria su pronta colaboracon gracias

Respuestas a esta discusión

Vínculo permanente Respuesta de Anibal G. Garcia Soto el agosto 13, 2012 a las 11:35am

Andres:

Probablemente ayudaria saber que version y edicion de oracle tienes instalada , en las versiones 10G en adelante el evento de deadlock ( ORA-00060) genera automaticamente un trace file , que se encuentra ubicado en user_dump_dest. El trace file generado indica cual fue la session victima del deadlock y que instrucciones sql eran las involucradas en esta session.

Si este incidente de deadlock ocurre frecuentemente en el sistema , dependiendo de la version de oracle es convieniente utilizar el trace 10027 a nivel 2. En el cual detalla los queries involucrados , eventos de espera , cursores activos, etc.

En todo caso la solucion siempre es cambiar la logica del codigo de la aplicacion , para que en la medida de lo posible intente adquirir los bloqueos de objeto en el mismo orden en todas las sesiones; En algunos casos especiales se puede evitar el evento de deadlock con un simple select for update nowait antes de ejecutar el update.

Si ya tienes el archivo trc , puedes adjuntarlo a la discusion para poder revisarlo.

El deadlock se da porque existe una condición de bloqueo infinito que la base de datos detecta y rompe, es problema de lógica de aplicación casi siempre.

Por ejemplo: usuarios A y B.

t1) A actualiza un registro R1, crea un bloqueo

t2) B actualiza un registro R2, crea un bloqueo

t3) A quiera actualizar el registro R2, espera por el bloqueo de B

t4) B quiere actualizar el registro R1, espera por el bloqueo de A

Como puedes ver A y B esperaran hasta el fin de los días a menos que la base de datos

tome riendas en el asunto y genere el ORA-00060 deadlock.

El ORA-00060 rompe la ultima actualización y libera los recursos para la penultima implicada

( esto seguro el 80% probarlo en test)

y deja savepoint implicito por lo que si tu programa tiene un commit un poco después podrá continuar

solo perdiendo uno de los dos updates del ejemplo anterior eso si tendrá menos registros de los que debería.

A mi se me produce un deadlock, necesito que alguien me ayude a entender lo que me está sucediendo.

A continuación les muestro el trace.

*** 2012-09-10 08:44:54.447

DEADLOCK DETECTED ( ORA-00060 )

[Transaction Deadlock]

The following deadlock is not an ORACLE error. It is a deadlock due to user error in the design of an application or from issuing incorrect ad-hoc SQL. The following information may aid in determining the deadlock:

Deadlock graph:

---------Blocker(s)-------- ---------Waiter(s)---------

Resource Name process session holds waits process session holds waits

TM-0001c83a-00000000 49 85 SX SSX 44 67 SX SSX

TM-0001c83a-00000000 44 67 SX SSX 49 85 SX SSX

session 85: DID 0001-0031-0000036F session 67: DID 0001-002C-00000C7F

session 67: DID 0001-002C-00000C7F session 85: DID 0001-0031-0000036F

Rows waited on:

Session 85: obj - rowid = 0001BF59 - AAAfcCAAqAAAoS9AAA

(dictionary objn - 114521, file - 42, block - 165053, slot - 0)

Session 67: obj - rowid = 00013157 - AAAfchAAqAAAdNsAAA

(dictionary objn - 78167, file - 42, block - 119660, slot - 0)

----- Information for the OTHER waiting sessions -----

Session 67:

sid: 67 ser: 45179 audsid: 74981637 user: 89/SIS_OWNER flags: 0x8000041

pid: 44 O/S info: user: SYSTEM, term: PRO05A, ospid: 2136

image: ORACLE.EXE (SHAD)

client details:

O/S info: user: SYSTEM, term: PRO05A, ospid: 4860:4828

machine: GRUPO_TRABAJO\PRO05A program: Apache.exe

client info: DPCYTFSL.ADMCAT.DNI.29811857

application name: APEX:APPLICATION 2014, hash value=3001996051

action name: validations, hash value=1036917930

current SQL:

UPDATE "SIS_OWNER". "DOC_DOCUMENTOS" SET "DOCUMENTO" = (SELECT BLOB_CONTENT FR

OM WWV_FLOW_FILES WHERE NAME = :B2 ) , "MIMETYPE" = (SELECT MIME_TYPE FROM WWV_F

LOW_FILES WHERE NAME = :B2 ) , "FILENAME" = (SELECT FILENAME FROM WWV_FLOW_FILES

WHERE NAME = :B2 ) , "LAST_UPDATE_DATE" = SYSDATE WHERE ROWID = :B1

----- End of information for the OTHER waiting sessions -----

Information for THIS session:

----- Current SQL Statement for this session (sql_id=3vry6gsju7x8u) -----

UPDATE TRM_TRAMITES SET TRMTRE_ID = :B1 WHERE ID = :B2

----- PL/SQL Stack -----

----- PL/SQL Call Stack -----

object line object

handle number name

4B68560C 4 SIS_OWNER.TRA_TRMTRE

52909EAC 4848 package body SIS_OWNER.TRM_PKG

52909EAC 4635 package body SIS_OWNER.TRM_PKG

52909EAC 5995 package body SIS_OWNER.TRM_PKG

3859D660 2082 package body SIS_OWNER.CATTRM_DPCYTFSL_PKG

46D4EE5C 54 anonymous block

4B5B588C 1815 package body SYS.DBMS_SYS_SQL

4B5B7214 617 package body SYS.WWV_DBMS_SQL

52967F28 4543 package body APEX_040000.WWV_FLOW

52967F28 14940 package body APEX_040000.WWV_FLOW

4B629644 31 anonymous block

Arriba está bastate bien explicado, hay un proceso que bloquea un recurso y otro que quiere ese recurso se genera la primera espera. Luego el otro proceso bloquea el recurso que el primer proceso y quiere el mismo recurso anteriormente bloqueado... esto generaría una espera infinita ya que los dos tienes los recurso cruzados por lo que el sistema decide romper una de las transacciones.

En el trace esta la respuesta a tu problema de deadlock.

Pista[1] Los tipo de lock son TM no TX , significa que estan buscando un lock

sobre un registro que cambio pero necesita validar estructura en pocas

palabras necesita validar que el foreign key se cumpla con respecto a la llave.

Pista[2] las 2 sessiones tienes ya un lock SX(4) y necesitan un lock SSX(5) de

Share a Sub Share Exclusive

Pista[3] Si observas con detalle los objid son diferentes 114521 y el otro es

78167 , verifica que objetos son y los FK entre los mismos deberian tener

indices creados

como identifico los objetos ?

select object_name,owner,object_type from all_objects where object_id in (114521,78167)

como identifico los registros ?

Select * from (tabla que devuelve el 1er query para 11451) where rowid='AAAfcCAAqAAAoS9AAA'

Select * from (tabla que devuelve el 1er query para 78167) where rowid='AAAfchAAqAAAdNsAAA'

Usualmente los deadklocks TM de 4 a 5 son por falta de indices en llaves foraneas y/o el no tener sequence.

El objeto para el id 114521 es un indice sobre 2 campos de una tabla (TRM_TRAMITES).

El objeto para el id 78167 es un indice sobre un campo que es una clave foranea de otra tabla (DOC_DOCUMENTOS).

Las tablas mensionadas anteriormente tienen las sequences creadas y los triggers se encargan de actualizarlas.

Ahora estoy tratando de ver ¿como obtener los datos del recurso TM-0001c83a-00000000?

Anibal G. Garcia Soto dice:

En el trace esta la respuesta a tu problema de deadlock.

Pista[1] Los tipo de lock son TM no TX , significa que estan buscando un lock

sobre un registro que cambio pero necesita validar estructura en pocas

palabras necesita validar que el foreign key se cumpla con respecto a la llave.

Pista[2] las 2 sessiones tienes ya un lock SX(4) y necesitan un lock SSX(5) de

Share a Sub Share Exclusive

Pista[3] Si observas con detalle los objid son diferentes 114521 y el otro es

78167 , verifica que objetos son y los FK entre los mismos deberian tener

indices creados

como identifico los objetos ?

select object_name,owner,object_type from all_objects where object_id in

(114521,78167)

como identifico los registros ?

Select * from (tabla que devuelve el 1er query para 11451) where

rowid='AAAfcCAAqAAAoS9AAA'

Select * from (tabla que devuelve el 1er query para 78167) where

rowid='AAAfchAAqAAAdNsAAA'

Usualmente los deadklocks TM de 4 a 5 son por falta de indices en llaves

foraneas y/o el no tener sequence.

http://comunidadoraclehispana.ning.com/forum/topics/ora-00060-deadlock-detected?commentId=2315969%3AComment%3A79962