Cancelando consulta do SQL Server com CancellationToken

Eu tenho um procedimento armazenado de longa duração no SQL Server que meus usuários precisam ser capazes de cancelar. Eu escrevi um pequeno aplicativo de teste da seguinte forma que demonstra que o método SqlCommand.Cancel() funciona muito bem:

  private SqlCommand cmd; private void TestSqlServerCancelSprocExecution() { TaskFactory f = new TaskFactory(); f.StartNew(() => { using (SqlConnection conn = new SqlConnection("connStr")) { conn.InfoMessage += conn_InfoMessage; conn.FireInfoMessageEventOnUserErrors = true; conn.Open(); cmd = conn.CreateCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "dbo.[CancelSprocTest]"; cmd.ExecuteNonQuery(); } }); } private void cancelButton_Click(object sender, EventArgs e) { if (cmd != null) { cmd.Cancel(); } } 

Ao chamar cmd.Cancel() , posso verificar se o procedimento armazenado subjacente interrompe a execução essencialmente imediatamente. Dado que eu uso o padrão async / await bastante pesadamente no meu aplicativo, eu esperava que os methods asynchronouss no SqlCommand que usam os parâmetros do CancellationToken funcionassem igualmente bem. Infelizmente, descobri que chamar Cancel() no CancellationToken fez com que o manipulador de events InfoMessage não fosse mais chamado, mas o procedimento armazenado subjacente continuou a ser executado. Meu código de teste para a versão assíncrona segue:

  private SqlCommand cmd; private CancellationTokenSource cts; private async void TestSqlServerCancelSprocExecution() { cts = new CancellationTokenSource(); using (SqlConnection conn = new SqlConnection("connStr")) { conn.InfoMessage += conn_InfoMessage; conn.FireInfoMessageEventOnUserErrors = true; conn.Open(); cmd = conn.CreateCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "dbo.[CancelSprocTest]"; await cmd.ExecuteNonQueryAsync(cts.Token); } } private void cancelButton_Click(object sender, EventArgs e) { cts.Cancel(); } 

Estou faltando alguma coisa em como o CancellationToken deve funcionar? Estou no .NET 4.5.1 e no SQL Server 2012, caso isso seja importante.

EDIT: Eu reescrevi o aplicativo de teste como um aplicativo de console no caso do contexto de synchronization foi um fator e vejo o mesmo comportamento – a invocação de CancellationTokenSource.Cancel() não interrompe a execução do procedimento armazenado subjacente.

EDIT: Aqui está o corpo do procedimento armazenado que estou chamando no caso que importa. Ele insere registros e imprime resultados em intervalos de um segundo para facilitar a visualização das tentativas de cancelamento.

 WHILE (@loop <= 40) BEGIN DECLARE @msg AS VARCHAR(80) = 'Iteration ' + CONVERT(VARCHAR(15), @loop); RAISERROR (@msg,0,1) WITH NOWAIT; INSERT INTO foo VALUES (@loop); WAITFOR DELAY '00:00:01.01'; SET @loop = @loop+1; END; 

Depois de analisar o que seu procedimento armazenado está fazendo, parece que está bloqueando o cancelamento de alguma forma.

Se você mudar

 RAISERROR (@msg,0,1) WITH NOWAIT; 

para remover a cláusula WITH NOWAIT , o cancelamento funciona conforme o esperado. No entanto, isso impede que os events do InfoMessage sejam triggersdos em tempo real.

Você poderia acompanhar o progresso do procedimento armazenado de longa duração de outra maneira ou registrar-se para o cancelamento do token e chamar cmd.Cancel() já que você sabe que funciona.

Uma outra coisa a notar, com o .NET 4.5, é possível usar Task.Run vez de instanciar um TaskFactory .

Então aqui está uma solução de trabalho:

 private CancellationTokenSource cts; private async void TestSqlServerCancelSprocExecution() { cts = new CancellationTokenSource(); try { await Task.Run(() => { using (SqlConnection conn = new SqlConnection("connStr")) { conn.InfoMessage += conn_InfoMessage; conn.FireInfoMessageEventOnUserErrors = true; conn.Open(); var cmd = conn.CreateCommand(); cts.Token.Register(() => cmd.Cancel()); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "dbo.[CancelSprocTest]"; cmd.ExecuteNonQuery(); } }); } catch (SqlException) { // sproc was cancelled } } private void cancelButton_Click(object sender, EventArgs e) { cts.Cancel(); } 

No meu teste, eu tive que envolver ExecuteNonQuery em uma Task para que o cmd.Cancel() funcionasse. Se eu usasse ExecuteNonQueryAsync , mesmo sem passar um token, o sistema bloquearia em cmd.Cancel() . Não tenho certeza porque esse é o caso, mas envolver o método síncrono em uma tarefa fornece um uso semelhante.