Luchando contra los interbloqueos en Sql Server

La bases de datos utilizan los bloqueos para asegurar las propiedades ACID de las transacciones. Son un mecanismo imprescindible que tienen una cara oculta, un peaje necesario que muchas veces nos complica la vida: los interbloqueos.


Los interbloqueos son una situación a la que todo desarrollador de aplicaciones que manejan un volumen elevado de datos o de peticiones concurrentes se enfrenta en alguna ocasión. Desde el punto de vista de proceso de desarrollo, el antidotó es simple: hacer pruebas de carga y tener en cuenta las prácticas remendadas para evitar los interbloqueos.


Las recomendaciones para evitar los interbloqueos, que deben ser conocidas por todo desarrollador son:



  • Acceder a los objetos en el mismo orden en los diferentes procesos de nuestra aplicación.
  • Evitar que los usuarios tengan que intervenir durante el proceso de una transacción.
  • Mantener las transacciones lo más cortas y eficientes posible.
  • Elegir índices adecuados.
  • Utilizar el mínimo nivel de aislamiento posible (recordad que Sql Server por defecto utiliza Read Commited como nivel de aislamiento, sin embargo muchas veces será sufiente usar Read Uncommited como nivel de aislamiento).

Pero la tozuda realidad, según mi experiencia, es que por muchas pruebas de carga que hagas y por mucho que sigas las recomendaciones es dificil no sufrir interbloqueos la primera vez que pones tu aplicación en producción. Es muy dificil reproducir en un entorno de laboratorio las condiciones reales a las que se enfrentará nuestra aplicación. El problema es que los interbloqueos afectan seriamente al rendimiento de la aplicación y además se manifiestan a menudo como errores funcionales en la aplicación.


La pregunta que debemos contestar y el mótivo de escribir este post es: una vez que sabemos que estamos sufriendo interbloqueos ¿cómo sabemos en que situación concreta se están producciendo?. Dilucidar que consultas están es vital para deshacer el entuerto. Es aquí donde una característica muy poco conocida del profiler de Sql Server no es de tremenda ayuda.


Entre los numerosísimos eventos que podemos recolectar hay uno especialmente útil a la hora de diagnosticar interbloqueos, el evento Deadlock graph (gráfico de interbloqueo):


Evento deadlock graph


Si creamos una traza del profiler de Sql Server en la que incluyamos este evento, cada vez que se produzca un interbloqueo el profiler capturará toda la información relativa al evento, incluidas las consultas que causaron el interbloqueo y nos la mostrará gráficamente:


Deadlock Graph


En la imagen de encima de estas líneas podéis ver el resultado de probocar un interbloqueo intencionado en la base de datos AdventureWorks.


Otra caracterísca interesante es que el profiler nos permite exportar este evento a un archivo XDL (Deadlock XML file) para, por ejemplo, poder enviarlo por correo.


Extraer datos del evento


¡Espero que estas características del Profiler de Sql Server os sean útiles a la hora de luchar contra los interbloqueos!

9 comentarios en “Luchando contra los interbloqueos en Sql Server”

  1. SQL Server profiler deberia ser la ultima opcion para testear interbloqueos y problemas en un servidor de datos. Lo mejor es administrar usando procedimientos del sistema y vistas dinamicas de admin.

    SQL Server Profiles Constituye una herramienta poco útil cuando se desea llegar diágnósticar a fondo en los problemas, claro sin olvidar que podemos tirar abajo un server en producción con un traza. Sirve para tareas básicas, y dar los primeros pasos en el diagnostico de un server en problemas o realizar cierto testing.

    Saludos,

  2. @Percy: no comparto en absoluto tu opinión.

    ¿Que procedimientos o vistas del sistema te dan información tan detalla como el profiler sobre los bloqueos? Yo no conozco ninguna. sp_locks desde luego no sirve para esto, para una primera aproximación quizás pero no para concretar.

    Sobre las vistas dínamicas ¿cúal tiene información sobre interbloquos? Que yo sepa, ninguna. Evidentemente son una herramienta muy muy potente pero no para esto, hasta donde yo se.

    Desde luego el Profiler puede tirar abajo un servidor, como cuanquier herramienta de traceo, pero hay que hacer las cosas muy muy muy mal para que esto sea así. Desde luego, con el escenario y recogiendo solo los eventos que yo propongo es muy dificil, por no decir imposible.

    ¡Un saludo!

  3. Yo también comparto la opinión de Rodrigo.
    Administro algunas bases de datos bastante grandecitas en tamaño y cargo y el Profiler es lo mejor de lejos en este tipo de escenarios.

  4. Saludos, me podrian ayudar con este tema, necesito saber como implementar el particionamiento de datos y distribuir la data en grupos de archivos en medios fisicos diferentes ya que mi base ya pasa los 120 Gb.

  5. @ivan: Particionar una base de datos de esas dimesiones no es algo que se pueda diseñar así a bote pronto. Depende de tus necesidades, el hardware con el que cuentes y demás.

    Exige un estudio detallado y es algo peliagudo que debe hacerse bien para evitar perdidas de datos y problemas futuros de rendimento.

    Un saludo.

  6. Hola @Percy:
    Que procedimientos usas para ver los interbloqueos. No se queda muy bien si tiras la piedra y luego escondes la mano.
    Saludos a todos.

  7. Saludos,

    1. La herramienta para luchar contra los interbloqueos se llama SQL Profiler, no se en que estaba pensando el amigo cuando sugerió usar la metadata.

    2. Estos son los pasos a revisar:

    a. Verifiquen que tienen los indices correctos, porque si no lo tienen, SQL debe hacer un scan de todas las paginas de la tabla cada vez que hace SELECT, si algun registro de la tabla sufre modificación mientras hace la consulta habrá interbloqueo.

    b. Revisen cada consulta, solo seleccionen lo necesario.

    c. Si no necesitan transacciones o validaciones de integridad en triggers de las tablas donde ocurre el interbloqueo, usen SET TRANSACTION SET ISOLATION LEVEL READ UNCOMMITTED, si la conexión se reutiliza, utilicen SET TRANSACTION ISOLATION LEVEL READ COMMITTED para volverla a su estado original. (PRUEBEN Y ESTEN ALERTAS, TRIGGERS, COLUMNAS CALCULADAS O FUNCIONES PUEDEN LLEGAR A FALLAR).

    d. Si usan Entity Framework utilicen TransactionScope (MSDN para mas info)

    e. Eviten usar Transacciones a nivel de interfaz de usuario.

    f. Las tablas que deban ser de lectura rápida y alta concurrencia, no deben tener triggers, así usar SELECTs bajo SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED será mucho más eficaz y seguro.

    g. No emplear READ UNCOMMITTED si la tabla en cuestión participa en transacciones distribuidas.

    Espero pueda ayudar en algo.

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *