Desbloquear un objeto de la base de datos

La escena es universal: intentas hacer un DROP, un ALTER o un TRUNCATE y la sentencia se queda colgada, sin error y sin explicación, minutos u horas. En algún lugar, otra sesión mantiene un candado sobre ese objeto. Oracle no avisa: simplemente espera —de forma predeterminada, para siempre— a que el candado se libere. Localizar al culpable y decidir qué hacer con él es una de esas tareas que distinguen a quien administra del que solo ejecuta.

Quién bloquea a quién

La vista dinámica V$LOCKED_OBJECT, unida al diccionario y a las sesiones, da la respuesta completa en una sola consulta:

SELECT o.object_name,
       o.object_type,
       l.session_id,
       s.serial#,
       s.username,
       s.machine,
       s.program,
       s.status,
       s.last_call_et / 60 AS minutos_inactivo,
       l.locked_mode
FROM   v$locked_object l
JOIN   dba_objects  o ON o.object_id = l.object_id
JOIN   v$session    s ON s.sid = l.session_id
ORDER  BY o.object_name, s.last_call_et DESC;

La columna LOCKED_MODE cuenta la dureza del candado: modo 2 (row share) y 3 (row exclusive) son bloqueos de DML normales, compatibles entre sesiones; el modo 6 (exclusive) es el de las operaciones DDL y el que detiene el mundo. Un SELECT ... FOR UPDATE deja modo 3; un DELETE sin commit sobre miles de filas, también; el DDL pendiente espera a que todos los modos caigan.

El caso más común: la transacción abierta

Nueve de cada diez bloqueos son una transacción que empezó y nunca terminó: un cliente SQL con DELETE ejecutado y sin COMMIT, un proceso batch muerto a medias, una aplicación con autocommit desactivado. La pista está en la unión con la transacción:

SELECT s.sid, s.serial#, s.username, s.machine,
       t.start_time,
       s.module
FROM   v$transaction t
JOIN   v$session s ON s.taddr = t.addr
ORDER  BY t.start_time;

Una transacción iniciada hace tres horas desde la máquina de alguien de contabilidad cuenta sola la historia. Antes de nada, pregunta: muchas veces basta con que la persona haga commit o rollback en su ventana olvidada, y el candado desaparece solo.

Cuando no queda otra: terminar la sesión

-- matar la sesion bloqueante (datos de la consulta anterior)
ALTER SYSTEM KILL SESSION '142, 39107' IMMEDIATE;

-- si no termina (transaccion larga en rollback):
ALTER SYSTEM DISCONNECT SESSION '142, 39107' IMMEDIATE;

La diferencia entre ambos: KILL marca la sesión para terminar y deja que su proceso haga el rollback pendiente —que puede tardar tanto como la transacción—, mientras que DISCONNECT ... IMMEDIATE corta también la conexión del cliente. En cualquier caso, el rollback se hace siempre: matar la sesión no evita el trabajo de deshacer, solo lo saca de tu vista.

Bloqueos esperados en cargas y mantenimiento

Los bloqueos esperados también existen: una carga masiva con /*+ APPEND */ toma modo exclusivo sobre la tabla y todo lo demás espera —es diseño, no accidente. Para las operaciones de mantenimiento hay dos adiciones útiles:

-- DDL que espera como mucho 60 segundos
ALTER TABLE facturas ADD (observaciones VARCHAR2(200));
-- version no bloqueante en ediciones recientes:
-- LOCK TABLE ... IN EXCLUSIVE MODE WAIT 60;

-- ver que sentencia exacta mantiene la sesion
SELECT s.sid, q.sql_text
FROM   v$session s
JOIN   v$sql q ON q.sql_id = s.sql_id
WHERE  s.sid = 142;

El join con V$SQL identifica la sentencia exacta que sostiene el candado, el detalle que convierte una acusación vaga en un informe. Con ese dato, la conversación con el dueño del proceso bloqueante suele resolverse en dos minutos.

Prevención: cortar el problema de raíz

• Transacciones cortas: en aplicaciones, abrir tarde y cerrar pronto; un commit por unidad de trabajo, no uno por hora.
• Timeouts de bloqueo DML: ALTER SESSION SET DDL_LOCK_TIMEOUT = 60; (11g+) convierte la espera eterna de un DDL en una espera con límite.
• Vigilancia: la consulta de bloqueos combinada con las de sesiones del diccionario forma el health-check diario de cualquier esquema con cargas.

Los bloqueos también aparecen alrededor del borrado con papelera —un DROP esperando a otro candado— y comparten escenario con el agotamiento de procesos del ORA-12516: mismos protagonistas, distinta víctima.