Este error suele aparecer cuando existen bloqueos esperando a que otro usuario termine una operación, para poder realizar la suya. Uno de los más comunes que suelen suceder es cuando se hacen “truncate” o “drop” de tablas y no nos deja hacerlo porque las tabla/s están bloqueada/s por otros procesos de ese mismo u otros usuarios.
En el ejemplo que explico seguidamente el problema se produjo porque al llenarse un tablespace (datafile al 100%) realizando unos “inserts” en una tabla, el proceso se quedó bloqueado hasta poder hacer la inserción. En mi caso, como podía volver a lanzar el proceso sin problema, y no quería ampliar más la ocupación del tablespace afectado. Decidí matar los procesos que estaban bloqueados y volver a lanzar la ejecución de los inserts que provocaron por error que se llenara el tablespace.
En este ejemplo sólo se va a tratar el problema del bloqueo, no se va a tener en cuenta el problema de la falta de espacio del tablespace, porque eso fue debido a otra causa, pero que fue, en parte el causante del error que os voy a explicar y cuya solución veréis en esta entrada de blog.
Entro en detalle:
Al intentar hacer un truncate de una tabla salta el error:
ORA-00054: recurso ocupado y obtenido con NOWAIT especificado o timeout vencido
SQL> truncate table <mi_tabla> ; truncate table <mitabla> * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified
Esto ocurre porque se ha bloqueado uno o varios registros mediante setencias SQL. Select´s especificados como “NO WAIT” o “FOR UPDATE NOWAIT” o por una operación DDL que fue bloqueada. La solución podía pasar por hacer el commit o rollback. El commit no funcionó porque no hay espacio en el tablespace. El rollback no se probó, porque preferí matar la sesión que estaba provocando el error tal y como explico seguidamente.
En Oracle hay una vista llamada v$lock que nos indica los objetos que se encuentran en bloqueo, el identificador de usuario, sesion y el tipo de bloqueo. Una join con la tabla dba_objects nos proporcionará ademas el nombre y tipo de los objetos bloqueados. La consulta seria como sigue:
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','UL');
Existen varios tipos de bloqueo (TM,TX,UL):
TM – DML enqueue. Bloqueos a nivel de tabla. 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 <mi_tabla>; TRUNCATE <mi_tabla>; UPDATE <mi_tabla> SET campo1 = valor;
haciendo un “delete” fue el error en este ejemplo. Por tanto tenía un bloqueo a nivel de tabla.
TX – Transaction enqueue. Bloqueos a nivel de fila. Los bloqueos a nivel de fila se crean cuando se ejecutan senencias DML contra un conjunto de registros específicos.
UL – User supplied. Bloqueos a nivel de usuario.
Para solucionar el error:
Nos conectaremos como system a Oracle y ejecutaremos la siguiente consulta para ver si existe algún bloqueo:
SQL> show user USER is "SYSTEM" SQL> select * from v$lock where request!=0;
Si hacemos una join con v$open_cursor podremos 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 esperando a que termine un bloqueo, la sentencia que quieren ejecutar y el id de proceso que las está 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
/
Pero sino devuelve nada, que era lo que me pasaba a mi, y como el error me lo generaba un delete o un truncate, tuve que ejecutar la siguiente consulta para ver que era lo que tenía que “matar”.
SQL> SELECT mode_held FROM dba_dml_locks where OWNER='<mi_usuario>'; MODE_HELD ------------- Row-X (SX) Row-X (SX) Row-X (SX)
Seguidamente podía listar los bloqueos que ocurrían en ese momento en la base de datos.Y por medio de los campos LMODE y REQUEST saber de que tipo son :
none
null (NULL)
row-S (SS)
row-X (SX)
share (S)
S/Row-X (SSX)
exclusive (X)
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
/
Sale algo como … 
Intento matarlo …
SQL> alter system kill session '215,2774'; alter system kill session '215,2774' * ERROR at line 1: ORA-00031: session marked for kill
Pruebo entonces con immediate y si me funciona.
alter system kill session '215,2774' immediate;
Pero si lo anterior fallase buscaría por medio de esta SQL el SPID que es el número de proceso de Linux/unix que usaría para matar el proceso a nivel de S.O.
SELECT s.sid, p.spid, s.osuser, s.program FROM v$process p, v$session s WHERE p.addr = s.paddr;
La consulta anterior me muestra todos los procesos, buscaría el SID del proceso que quiero matar y me anotaría el campo SPID que es el que definitivamente usaré para matar el proceso. Pero si quiero sólo buscar el SID de la que quiero matar, ejecutaría:
SELECT s.sid, p.spid, s.osuser, s.program FROM v$process p, v$session s WHERE p.addr = s.paddr and s.sid = '215'; --> SID basado en mi ejemplo
Para saber cual es el SPID busco con el campo SID que me salia en la primera consulta. Es el primer valor antes de la “,” que uso en el” alter kill session …”
Para matar el proceso a nivel de S.O en mi caso un Linux. Se hace así:
Conectado como root al linux …
# ps -ef |grep <SPID> --> Donde SPID es el número de proceso # kill -9 <SPID>