none
Controlar interbloqueos

    Pregunta

  • Buenas tardes,

    Tengo un procedimiento que devuelve un set de datos, el procedimiento incluye varios UNIONS y un inner join a una función entre otras cosas.

    Cada vez que se ejecuta me lanza el siguiente mensaje:

    Transaction (Process ID 126) was deadlocked on lock resources with another

    process and has been chosen as the deadlock victim. Rerun the transaction.


    También quiero mencionar que hay ocasiones en que el procedimiento se ejecuta de manera correcta, a qué se debe este error?

    Saludos,


    Carlos Márquez
    San Pedro Sula
    Honduras

    martes, 10 de octubre de 2017 17:05

Respuestas

Todas las respuestas

  • Hola que tal. 

    Lo que está ocurriendo en el error que envías, es que el proceso ha sido cancelado por esta involucrado en un deadlock o abrazo mortal.

    Este mecanismo  ocurre cuando el motor de SQL Server detecta que un Proceso A esta realizando un acceso exclusivo sobre un objeto X, y al mismo tiempo quiere acceder en forma exclusiva a un objeto Y.

    En este punto también existe un Proceso B, que ya posee un acceso exclusivo en el objeto Y, y quiere acceder en forma exclusiva al objeto X.

    En consecuencia ninguno de los proceso podría concluir, y para resolver la situación el motor de SQL Server decide cancelar alguno de ellos para liberar la situación.

     El problema, es exclusivamente un tema de la lógica de programación de los procesos, que termina produciendo esta situación. 

    Para determinar esto, hay una guía en la documentación oficial, que entre otras cosas activa temporariamente unos Trace Flags, que hacen que al momento de ocurrir estas situaciones, se graba toda la información de los procesos en el Errorlog del motor para que se puedan analizar los procesos que tuvieron deadlocks.

    Ref: https://technet.microsoft.com/en-us/library/ms178104(v=sql.105).aspx

    Espero haber sido claro, pero la solución radica en que puedas ver que procesos están involucrados, y alterar la lógica de acceso exclusivo a los objetos y no producir deadlocks.

    Saludos.


    Mariano K.

    martes, 10 de octubre de 2017 18:18
  • Hola Marianok,

    De qué forma podría alterar la lógica de acceso exclusivo?

    Saludos,


    Carlos Márquez
    San Pedro Sula
    Honduras

    miércoles, 11 de octubre de 2017 23:11
  • Una forma de hacerlo, es utilizar transacciones más atómicas, es decir que involucren de a un objeto a la vez, liberando los accesos exclusivos. Y no utilizando varios objetos en la misma transacción.

    Otra opción puede ser setear diferentes niveles de Aislamiento para las operaciones, con la opción SET ISOLATION LEVEL. Pero eso requiere un análisis muy riguroso, para evitar problemas de inconsistencia. Te diría que esta opción, sería la que menos utilizaría.

    Si todo esto no es posible, ya requiere analizar mas el codigo ver otras alternativas.

    Saludos.


    Mariano K.

    jueves, 12 de octubre de 2017 14:14
  • Hola, Mariano K.

    Sí, por ahora es lo que he intentado, utilizar una transacción:

    Begin Transaction
    --Query
    Commit

    Por ahora no me ha lanzado el error de deadlock.

    Me sugiere configurar algun tipo de isolación o puedo usar el que trae por defecto?

    Saludos,


    Carlos Márquez
    San Pedro Sula
    Honduras

    jueves, 12 de octubre de 2017 16:09
  • Carlos, cambiar el nivel aislamiento es algo valido pero requiere saber con mucha precisión la volatilidad de los datos al momento de realizar las transacciones. 

    A priori, yo trabajaría siempre con el defecto de Read Commited.

    Saludos!


    Mariano K.

    jueves, 12 de octubre de 2017 16:15
  • Hola, Mariano K.

    Pasa que ahora mismo volvió a "tronar", me puede recomendar otro nivel de aislamiento?


    Carlos Márquez
    San Pedro Sula
    Honduras

    jueves, 12 de octubre de 2017 16:37
  • Hola, Mariano.

    El mensaje de error me lanza el problema en la Linea 17. pero justamente la línea 17 es el begin del procedimiento.

    El transaction tengo que ejecutarlo fuera del SP, es decir:

    Begin transaction

    Llamo procedimiento

    Commit.

    O la transaction tiene que ir dentro del SP o da igual?

    Saludos,


    Carlos Márquez
    San Pedro Sula
    Honduras

    jueves, 12 de octubre de 2017 17:03
  • SI no existe obstrucción cuanto tiempo tarda tu proceso en ejecutarse y que mas se ejecuta en el sistema.
    jueves, 12 de octubre de 2017 22:08
  • En tu código, haz que las actualizaciones e inserciones se hagan en el mismo orden.  

    Como norma general que los bloqueos se produzcan lo mas tarde posible, y los commits lo antes posible 

    Adicionalmente puedes jugar con el comando set deadlock_priority y así en caso de bloqueo decidir quien cae.


    Comparte lo que sepas, aprende lo que no sepas (FGG)
    portalSQL
    El rincón del DBA

    viernes, 13 de octubre de 2017 6:04
    Moderador
  • Hola a todos,

    Mediante el deadlock report he obtenido esto:

    El primer procedimiento sí es el que yo ejecuto: exec sp_stock_xt_rango que consiste unicamente en varios select, sin embargo mas abajo se puede ver un insert a la tabla OIVL sin embargo ese insert no está en mi procedimiento, parece que coincide con la ejecución de mi procedimiento y lo bota, con un transaction puedo controlar ese insert que no se encuentra en mi procedimiento?

    Saludos,


    Carlos Márquez
    San Pedro Sula
    Honduras

    viernes, 13 de octubre de 2017 11:02
  • Como mencionó Miguel mas arriba, una opción es utilizar set deadlock_priority .

    Y la otra opción, es bajar el nivel de aislamiento para tus consultas, ya que no son modificaciones de datos, pero hay que ver si tiene sentido traer datos que no están commiteados aún.

    Ref. ISOLATION LEVELS : https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql


    Mariano K.

    viernes, 13 de octubre de 2017 14:19
  • Saludos

    Veo un insert, cuantos registros están intentando meter a la vez en este insert?

    viernes, 13 de octubre de 2017 14:32
  • Hola, Mariano K.

    Exacto, la labora del procedimiento únicamente es devolver un set de datos, ahora bien, preferiría que mi select se ejecute después que la otra operación ya que ésta sí es mas crítica y es un Insert, únicamente lo que quiero es deje pasar el insert y luego mi procedimiento pero que no lo bote.

    A qué nivel de aislamiento debería de llevar mi procedimiento tomando en cuenta lo anterior? read uncommitted?

    Saludos,


    Carlos Márquez
    San Pedro Sula
    Honduras


    viernes, 13 de octubre de 2017 14:41
  • Hola, Enrique.

    El problema es que en el procedimiento en cuestión no hay Insert, el insert viene de otro lado, pero cuando coinciden entonces me bota el procedimiento.


    Carlos Márquez
    San Pedro Sula
    Honduras

    viernes, 13 de octubre de 2017 16:42
  • Otro lado?

    Podra ser que tengas un trigger que se este disparando? fuera de lo que ya te han dicho veria que tus estadisticas e indices esten al dia para ver que las operaciones se agilicen lo mas posible y dejar lo mínimo posible la ventana a que se de el deadlock.

    viernes, 13 de octubre de 2017 17:03
  • Coincido con Enrique, verificaría si hay Triggers y como están las estadísticas e índices. Y por último evitaría cambiar el nivel de aislamiento.

    Saludos.


    Mariano K.

    viernes, 13 de octubre de 2017 20:42