Objetos bloqueados en una base de datos (2)

Esta nota es continuación del anterior post Objetos bloqueados en una base de datos (1). Así pretendo dejar zanjado el tema referente a la información almacenada en la tabla V$LOCK.

Tipos de locks y las columnas ID1 e ID2

En nuestro ejemplo de la nota anterior, ya sabemos que nuestro lock es un lock producido por una sentencia DML (sentencias de manipulación de datos como select, insert, update, delete, etc), pero esto es porque fuimos nosotros los que ejecutamos la sentencia bloqueadora. Evidentemente este no va a ser siempre el caso, ya que una base de datos es normalmente compartida por multitud de usuarios. Afortunadamente, podemos encontrar la información que necesitamos en la tabla V$LOCK.

El primer lugar donde mirar es en la columna TYPE. Existen docenas de tipos de locks pero la mayoría son tipos de locks del sistema. Los locks del sistema aparecen normalmente durante un tiempo muy breve y generalmente no es muy útil intentar poner a punto (tunear) la librería caché y otros elementos del sistema mirando en V$LOCK.

Sólo hay tres tipos de locks de usuario, TX, TM y UL.

   1. UL son locks definidos por el usuario (user-defined lock), estos locks se definen utilizando el paquete DBMS_LOCKS.
   2. Los TX son locks de transacciones sobre registros, el bloqueo se produce una vez por cada transacción que cambia el dato, sin importar cuantos objetos son cambiados durante la transacción. Las columnas ID1 e ID2 apuntan al segmento de rollback y a las entradas de la tabla de transaciones para dicha transacción.
   3. Los TM son locks producidos por sentencias DML. El bloqueo se produce una vez para cada objeto que está siendo actualizado. La columna ID1 identifica el objeto que está siendo modificado.

Nivel de exclusividad de un lock

Se puede optener más información sobre los locks TM y TX con sólo mirar a las columnas LMODE y REQUEST, ambas utilizan el mismo formato numérico para indicar el nivel de exclusividad de un lock: desde 0 para indicar que no hay lock, hasta 6 para indicar que se trata de un lock exclusivo. Cualquier sesión debe tener un lock TX exclusivo para poder actualizar datos; LMODE valdrá 6. Si no se puede conseguir un lock exclusivo porque alguno de los registros que se quieren cambiar está ya bloqueado por otra sesión, entonces se solicitará un lock TX exclusivo; LMODE valdrá 0, ya que no hay un lock todavía, y REQUEST valdrá 6. Esto se puede ver en los registros de la tabla V$LOCK de nuestra nota anterior y que vuelvo a poner a continuación:

ADDR     KADDR    SID TY ID1    ID2    LMODE REQUEST CTIME BLCK
-------- -------- --- -- ------ ------ ----- ------- ----- ----
B765B198 B765B1AC 779 TM 6442   0      3     0       11543 0
01AA2F00 01AA2FCC 611 TX 262160 653305 6     0       54537 1
B6047A58 B6047A68 699 TX 262160 653305 0     6       54535 0
...............................................................

En estos registros se puede ver que las columnas ID1 e ID2 de la sesión 2, que es la que está solicitando el lock TX (LMODE=0, REQUEST=6), apuntan al segmento de rollback y a las entradas de la transacción de la sesión 1. Esto es lo que nos permite determinar que sesión 1 bloquea a la sesión 2.

Es posible también ver locks TX en modo 4, modo compartido. Si un bloqueo que contiene registros que van a ser actualizados no tiene ninguna entrada ITL (Interested Transaction List), entonces las sesión toma un lock TX en modo 4 mientras espera por la entrada ITL. Si un objeto queda frecuentemente bloqueado por locks TX modo 4, esto signfica que probablemente haya que incrementar el INITRANS para dicho objeto.

Los locks TM son generalmente requeridos y tomados en modos 3 (shared-row exclusive) y 6. Las sentencias DDL (sentencias de definición de objetos como revoke, grant, alter, etc) requieren un lock TM exclusivo (6), aunque hay excepciones como el comando CREATE TABLE que no requiere ningún lock TM, ya que no necesita bloquear ningún objeto porque el objeto en cuestión todavía no existe. Las sentencias DML (sentencias de manipulación de datos como select, insert, update, delete, etc), sin embargo, requieren un lock TM en modo 3, así se puede ver en los registros que seleccionamos anteriormente en la tabla V$LOCK (ver arriba), los locks TM son de nivel 3 y por tanto son locks generados por sentencias DML.

Como identificar el objeto bloqueado

Ahora que sabemos que cada lock TM apunta a un objeto bloqueado, podemos utilizar la columna ID1 para identificar el objeto.

SQL> select object_name from dba_objects
2* where object_id=6442;

OBJECT_NAME
-------------
TEST_BLOQUEO

A veces conocer el objeto bloqueado es suficiente pero es posible obtener más información todavía. Veamoslo a continuación.

Como identificar el registro bloqueado

Podemos obtener esta información desde la tabla V$SESSION mirando los datos de la sesión bloqueada. En nuestro ejemplo la sesión bloqueada era la que tenía SID 699.

SQL> select row_wait_obj#, row_wait_file#,
2* row_wait_block#, row_wait_row#
3* from v$session where sid=699 ;

ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
------------- -------------- --------------- -------------
         6442             16          111507             0

Estos cuatro datos son los cuatro componentes de un ROWID extendido. Ahora podemos utilizar el paquete DBMS_ROWID, en concreto la función ROWID_CREATE, para obtener el ROWID real:

SQL> select do.object_name,
2* dbms_rowid.rowid_create ( 1,
3* ROW_WAIT_OBJ#, ROW_WAIT_FILE#,
4* ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
5* from v$session s, dba_objects do
6* where sid=699
7* and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;

OBJECT_NAME    DBMS_ROWID.ROWID_C
-------------- ------------------
TEST_BLOQUEO   ABAxnHBBWACCv0yACC

Y, por supuesto, esto nos permite obtener la información del registro directamente:

SQL> select * from test_bloqueo
2* where rowid='ABAxnHBBWACCv0yACC' ;

TEST BLOQUEO
---- ----------
1    Hola

Conclusión

Hemos visto que podemos identificar que sesión está bloqueando a otra, el objeto que está bloqueado y hasta el registro individual que está bloqueado. Espero que hayáis aprendido un poquito acerca del uso que podemos dar a la tabla V$LOCK.