Transacción incompleta puede contener gran número de bloqueos y provocar un bloqueo


Resumen


Cuando no se completa una transacción porque el tiempo de espera de una consulta o porque el lote se cancela en medio de una transacción sin que se emita una instrucción COMMIT o ROLLBACK para completar la transacción, la transacción se queda abierta y continuarán todos los bloqueos adquiridos durante esta transacción a realizarse. Transacciones posteriores que se ejecutan en la misma conexión se tratan como transacciones anidadas, por lo que no se liberan todos los bloqueos adquiridos en estas transacciones completadas. Este problema se repite con todas las transacciones que se ejecutan desde la misma conexión hasta que se ejecuta una instrucción ROLLBACK. Como resultado, se llevan a cabo un gran número de bloqueos, se bloquean los usuarios y las transacciones se pierden, qué resultados en datos que es diferentes de lo esperado.

Más información


En el ejemplo siguiente se muestra cómo los bloqueos no se liberan como resultado de una transacción abierta sin terminar:
  1. Abra el analizador de consultas de SQL Server y ejecute el siguiente lote pero cancelar la transacción antes de que finalice:
    Begin TranUpdate authors set state = 'CA'
    waitfor delay "00:02:00" --Cancel the command
    Commit Tran

  2. Ver los bloqueos mantenidos por ejecutando el siguiente comando:
    sp_lock
    Verá que los bloqueos se mantienen para la tabla authors .

  3. Desde el mismo servidor id (SPID), ejecute el siguiente lote:
    Begin TranUpdate titleauthor set au_ord = 0
    Commit Tran - Completed transaction.

  4. Ver los bloqueos mantenidos por ejecutando el siguiente comando:
    sp_lock
    Verá que, aunque la última transacción completada, se mantienen bloqueos en las tablas de los autores y la titleauthors . La razón es que la primera transacción no se completó y cuando la segunda transacción se ejecuta desde la misma conexión, se trató como una transacción anidada.

    Puede ver el recuento de transacciones comprobando la variable global @@trancount emitiendo la siguiente instrucción:
    select @@trancount
    Esta consulta devuelve 1, lo que indica que una transacción está pendiente.

    Más transacciones que se ejecutan desde esta conexión se tratan como anidado. Bloqueos continúan acumulándose y no se liberan hasta que se ejecuta una instrucción ROLLBACK, qué versiones anteriores a la transacción más externa o a un punto de almacenamiento.
Para continuar con el ejemplo, puede ver cómo una reversión puede causar una transacción completa que se va a negar mediante la ejecución de la siguiente transacción desde la misma conexión:
Begin TranUpdate titles set royalty = 0
Rollback

El rollback deshace el lote a la transacción más externa, incluso si hay una transacción completa (2) en titleauthors. La reversión de la transacción completa se produce porque la transacción completada se trata como una transacción anidada.

Para evitar este tipo de problemas, comprobar después de cada transacción para ver si la transacción es completa mediante la siguiente instrucción:
If @@trancount > 0 rollback

REFERENCIAS

Para obtener una descripción de cómo supervisar el bloqueo de secuencias de comandos SQL en SQL Server 7.0 o SQL Server 2000, consulte los artículos siguientes en Microsoft Knowledge Base:
251004 cómo supervisar los bloqueos de SQL Server 7.0

271509 cómo supervisar el bloqueo en SQL Server 2005 y SQL Server 2000