Juan Andres Mercado Oracle Blog – IT Buenos Aires

Este pequeño post lo agrego para Milton (Ecuador) que me preguntaba como podia armar un script para consultar sessiones que estuvieran con bloqueos esperando a que otro usaruario termine la operación.

Lo que le había occurido, en una prueba de Java con Oracle, es que la aplicación se hangeo y para poder ejecutar la prueba desde la contingencia de su aplicación debía primero truncar la tabla.

Al intentar hacerlo le daba el error:

SQL> truncate table TEST.VOL_MONEY_CAP ;
truncate table TEST.VOL_MONEY_CAP
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

Este error ocurre cuando se bloquea un/unos registro/s mediante:

• Las sentencias SQL “SELECT” especificados como “NO WAIT” o “FOR UPDATE NOWAIT”.
• Que una operación SQL de tipo DDL fue blockeada.

Una solución podría ser haciendo COMMIT o ROLLBACK en la sesión de bloqueo.

Entendamos un poco lo que estamos haciendo.

Yo utilizo para la query que se demuestra al pie la vista v$lock.

De acá podemos decir que los tipos de lockeos son:

• TM – DML enqueue
• TX – Transaction enqueue
• UL – User supplied

Para encontrar que proceso esta blokeados usamos la query.

SQL> select * from v$lock where request!=0; 

Y como el blockeo se produce cuando quiero realizar un truncate, lo que hago es revisar en la tabla dba_dml_locks.

SQL> select mode_held from dba_dml_locks where owner=’TEST’; 
MODE_HELD
————-
Row-X (SX)
Row-X (SX)
Row-X (SX)

Ahora puedo listar los blockeos que ocurren en este momento en mi base de datos.

Y podemos observar por medio de los campos LMODE y REQUEST de que tipo es :

• none
• null (NULL)
• row-S (SS)
• row-X (SX)
• share (S)
• S/Row-X (SSX)
• exclusive (X)

Ejecutemos la prueba.

SQL>
set linesize 130
set pages 100
col username format a20
col sess_id format a10
col object format a25
col mode_held format a10
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 /SQL> SQL> SQL> SQL> SQL> SQL> 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

USERNAME   SESS_ID  OBJECT                          OBJECT_TYPE        STATUS  MODE_HELD
——————–    ———————  —————————————————————————————   —————————————————  ————    —————————
AP (jcaps) 48,367   TEST.VOL_MONEY_CAP              TABLE Not Blocking Row-X   (SX)
AP (jcaps) 48,367   TEST.VOL_MONEY_CAP     	    TABLE Not Blocking Row-X   (SX)
AP (jcaps) 145,123  TEST.VOL_MONEY_CAP_HISTORICA    TABLE Not Blocking Row-X   (SX)
AP (jcaps) 160,339  TEST.VOL_MONEY_CAP 	            TABLE Not Blocking Row-X   (SX)
AP (jcaps) 160,339  TEST.VOL_MONEY_CAP 	            TABLE Not Blocking Row-X   (SX)

SQL>

Ahora que sabemos que ocurrio y desde que cliente se estan conectando podemos tomar las medidas adecuadas del caso.