Detectar enq: TX - row lock contention | ExpoDBA

Detectar enq: TX - row lock contention

Detectar enq: TX - row lock contention

Es muy común que los usuarios de base de datos (desarrolladores principalmente) reclamen al DBA de turno que tienen una tabla bloqueada: ¿Quien bloquea mi tabla? Ahi es donde es necesario aclarar el concepto de LOCK en Bases de datos y en particular Oracle. El LOCK es un comportamiento normal de la base de datos para poder asegurar que las transacciones se realizan cumpliendo con las características ACID (Atomicity, Consistency, Isolation, Durability). La expresión mínima del lock en bases de datos transaccionales como Oracle es a nivel de fila, pero tambén hay locks a nivel de tabla. Además los locks se categorizan en Shared o Exclusive. Cuando una transacción inicia, realiza una serie de DML sobre distintas tablas. La misma va generando locks sobre los registros que modificó para asegurar que otro que quiera modificarlos sepa que tiene que esperar a que esta transacción termine. Cuando la transacción termina (commit o rollback) se liberan los registros modificados, y puede acceder otra transacción. Este comportamiento es el esperado y es correcto que ocurra este lock. El problema se encuentra cuando otra sesion necesita adquirir un lock exclusivo sobre el mismo registro y no puede. En este caso la segunda sesión queda esperando en el evento enq: TX - row lock contention hasta que la primer transacción termine. Ej: Tengo una tabla de saldos que tiene un unico registro con una unica columna saldo de $10 t Session A Session B Notas 1 Update saldos set saldo = saldo -2;     A realiza resta $2 del saldo actual dejandolo en $8. Como no ejecutó un commit se mantiene un lock sobre esta row.  2 select saldo from saldos; Saldo --------- 8 select saldo from saldos; Saldo --------- 10 Como la transacción de A está Aislada A ve el resultado de su transacción, mientra B sigue viendo el valor original. Esto se mantendrá así hasta que A termine su transacción y los 2 vean lo mismo. 3   Update saldos set saldo = saldo -3;  B quiere sacar $3 de los 10 que consultó, pero esta row está siendo bloqueada por A por lo tanto queda esperando con el evento enq: TX - row lock contention hasta que la transacción se libere. El usuario B piensa que después de generar su transacción quedarán $7 en la cuenta.... (pero ya sabemos que no será así). 4 commit;     El usuario A termina su transacción, por lo tanto se libera el bloqueo sobre la fila. Entonces se ejecuta el update pendiente del usuario B. 5   select saldo from saldos; Saldo --------- 5 Cuando B consulta la fila ve que el resultado de su transacción es 5 (porque ya ejecutó A). Si hace commit, queda el 5, si hace rollback queda el 8 que dejó A. En este caso B está generando un lock sobre la fila con la intensión de que su transacción quede resguardada de cualquier otro usuario que quiera alterar su valor. Este lock se elimina cuando B hace commit. 6   commit;  Se libera el lock generado por B.   Si ven en una base sesiones esperando por el evento enq: TX - row lock contention es porque esa sesión está esperando porque otra sesion termine su transacción para poder trabajar, por lo tanto el problema no es el LOCK en sí, sino que el verdadero problema es que hay una sesión en ENQUEUE y está demorando su ejecución. Así que para responder la pregunta ¿Quien bloquea mi tabla? tenemos varias herramientas:
  • Si hay sesiones en enqueue podemos ejecutar el script Locks (Esperas y Bloqueadores) que nos dirá quien es el que genera el lock y que está haciendo además de todas las sesiones que están esperando porque se libere ese lock.
  • Si simplemente queremos ver quien tiene locks sobre una tabla (independientemente de si hay enqueues) podemos ejecutar Identificar quien bloquea un objeto (ORA-00054) que nos mostrará los datos de la sesion que genera locks sobre el objeto que indiquemos.
Hay miles de formas más, pero estas son algunas de las más útiles y fáciles de ejecutar. También hay problemas de locks mucho más complejos como de library cache, o problemas generados por contención de bloque (ver nota), pero como primera aproximación este post servirá a varios. Cualquier duda o consulta, dejen sus comentarios.