Transações incompletas podem armazenar grande número de bloqueios e causar bloqueio

Quando uma transação não é concluída porque uma consulta expira ou porque o lote é cancelado no meio de uma transação sem emitir uma instrução COMMIT ou ROLLBACK para concluir a transação, a transação é deixada aberta e continuam todos os bloqueios adquiridos durante essa transação a ser mantido. Transações subseqüentes executadas sob a mesma conexão são tratadas como transações aninhadas, para que todos os bloqueios adquiridos nessas transações concluídas não são liberados. Esse problema se repete todas as transações executadas da mesma conexão até que um ROLLBACK é executado. Como resultado, um grande número de bloqueios é mantido, os usuários são bloqueados e as transações são perdidos, que resulta em dados que seja diferentes da esperada.


O exemplo a seguir ilustra como os bloqueios não são liberados como resultado de uma transação aberta incompleta:
Abra o SQL Server Query Analyzer e execute o seguinte lote mas cancelar a transação antes que ela conclua:

Begin Tran
Update autores set state = 'CA'
waitfor delay "00:02:00" --Cancela o comando
Commit Tran

Exibir os bloqueios mantidas pelo executando o seguinte comando:
sp_lock

você verá que os bloqueios são mantidos para a tabela autores .

Da mesmo processo de identificação do servidor (SPID), executar o próximo lote:
Begin Tran
Update tituloautores set au_ord = 0
Commit Tran -  Transação Completa

Exibir os bloqueios mantidas pelo executando o seguinte comando:
sp_lock

você verá que, embora a última transação é concluída, bloqueios são mantidos nas tabelas de autores e títuloautores . O motivo é que a primeira transação não foi concluída e quando a transação segunda foi executada da mesma conexão, ele foi tratado como uma transação aninhada.

Você pode exibir a contagem de transação, verificando @@ trancount variável global emitindo a instrução a seguir:
select @@trancount

esta consulta retorna 1, que indica que uma transação pendente.

Quaisquer outras transações que são executadas a partir desta conexão são tratadas como aninhados. Bloqueios continuar acumular e não são liberados até que um ROLLBACK é executada, quais reversões para a transação mais externa ou para um ponto de salvamento.
Continuar com o exemplo, você pode ver como uma reversão pode causar uma transação a ser negado ao executar a transação a seguir da mesma conexão concluída:

Begin Tran
Update titles set royalty = 0
Rollback

A reversão traz o lote de volta para a transação externa, embora não haja uma transação concluída (2) em títuloautores . A reversão na transação concluída ocorre porque a transação concluída é tratada como uma transação aninhada.

Para evitar esse tipo de problema, verificar após cada transação para saber se a transação foi concluída usando a instrução a seguir:
If @@trancount > 0 rollback

fonte : microsft

abraço a todos ...