Я пытаюсь реализовать функцию «отмены» на страницах с потенциально длительными запросами или процессами выбора. Индексация и другие оптимизации были сделаны, чтобы свести к минимуму эту возможность, но во время более высокой загрузки сервера некоторое ожидание в сложных процессах все еще возможно.
Когда процесс запущен, отображается модальный счетчик занятости с кнопкой «Отмена». Если пользователь решит остановить процесс, я хочу использовать следующие шаги:
У меня есть тестовая хранимая процедура:
BEGIN
DECLARE @SpID varchar(10) = CONVERT(varchar(10), @@SPID);
RAISERROR(99999, 10, 1, @SpID) WITH NOWAIT;
WAITFOR DELAY '00:00:15'
END
Идея состоит в том, чтобы выполнить следующее...
SPID
из @@SPID
RAISERROR
, чтобы создать сообщение об отсутствии ошибок, содержащее SPID
WAITFOR
функция приложения, вызвавшая хранимую процедуру, извлекает сообщение с SPID
SPID
другой хранимой процедуре, чтобы убить этот процесс (KILL позаботится о закрытии соединения и откате действий с данными))На данный момент я понимаю, что сообщение из этой настройки может быть получено вызывающим кодом ASP.NET C#, но я не могу найти пример, который будет извлекать сообщение, в то же время позволяя продолжить выполнение вызывающего кода (с ExecuteNonQuery
, ExecuteReader
или ExecuteScalar
).
/* CommentTest Procedure */
CREATE PROCEDURE CommentTest
AS
BEGIN
DECLARE @SpID varchar(10) = CONVERT(varchar(10), @@SPID);
RAISERROR(99999, 10, 1, @SpID) WITH NOWAIT;
WAITFOR DELAY '00:00:30'
END
private string _spID;
private string RunCommentTestWaitForSP()
{
_spID = "";
try
{
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlCommand cmd = new SqlCommand("CommentTest", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
// this is where I'm stuck...
cmd.ExecuteNonQuery();
_spID = "The message returned from RAISERROR...";
}
}
}
catch (Exception ex)
{
log.Error("RunCommentTestWaitForSP exception: " + ex.ToString() + " / " + ex.Message);
log.Error("RunCommentTestWaitForSP stack trace: " + ex.StackTrace);
}
return spID;
}
private void LinkButtonCancelProcess_Click(object sender, EventArgs e)
{
RunCommentTestWaitForSP();
if (_spID.Length > 0 && "IsNumeric") {
"call function to run sp_KillSpID"
}
}
CREATE PROCEDURE [dbo].[sp_KillSpID]
@SpID varchar(5)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @KillStatement varchar(10) = 'KILL ' + @SpID;
EXEC(@KillStatement);
END
Хорошая мысль: использование этого префикса — плохая привычка, которая появилась, когда я присоединился к моей нынешней команде. Спасибо!
Вам нужно обработать событие InfoMessage в объекте SqlConnection:
private string RunCommentTestWaitForSP()
{
try
{
using (SqlConnection conn = new SqlConnection(connStr))
using (SqlCommand cmd = new SqlCommand("CommentTest", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
conn.InfoMessage += ReceiveSQLInfo;
conn.Open();
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
log.Error($"RunCommentTestWaitForSP exception: {ex} / {ex.Message}");
log.Error($"RunCommentTestWaitForSP stack trace: {ex.StackTrace}");
}
}
private void ReceiveSQLInfo(object sender, SqlInfoMessageEventArgs e)
{
//sender will be the "conn" connection above
var spID = e.Message;
// Here you could open a new connection
// (we expect the existing connection is still busy)
// and use it to kill the spID
}
Просто имейте в виду, что функция ExecuteNonQuery()
не завершится, пока не завершится хранимая процедура, то есть только после завершения кода WAIT FOR DELAY
. Поэтому метод LinkButtonCancelProcess_Click()
также не будет делать то, что вы ожидаете, поскольку блок if ()
не будет достигнут до тех пор, пока хранимая процедура не будет завершена в любом случае.
Кроме того, в зависимости от того, как вызывается C# RunCommentTestWaitForSP()
, компьютер может быть не в состоянии обрабатывать какие-либо сообщения о событиях, пока он не завершится.
Это полезно понять, мне было интересно, есть ли в отношениях между вызываемым SP и вызывающим кодом асинхронный хук при использовании RAISERROR. Возможно, мне нужно выбрать другую дорожку внутри кода кнопки отмены и вызвать sp_who, чтобы получить список и (каким-то образом) определить, какой процесс я должен убить.
Обратите внимание, что // we expect the existing connection is still busy
также потенциально очень опасен. SQL Server не принимает никаких специальных мер предосторожности, чтобы предотвратить быстрое повторное использование идентификаторов SPID, поэтому, если время пойдет не так, и в зависимости от прав вашей учетной записи, вы можете в конечном итоге убить невинный процесс. @fourells5: рассмотрите возможность предоставления экземпляра SqlCommand
и вызова .Cancel()
на нем (это можно сделать из другого потока). Это по-прежнему потенциально включает проблемы с синхронизацией, но, по крайней мере, их можно исправить. .Cancel()
не гарантируется быстрая работа, но и KILL
тоже.
Примечание: вы не должны использовать префикс
sp_
для своих хранимых процедур. Microsoft зарезервировала этот префикс для собственного использования (см. Именование хранимых процедур) , и вы рискуете столкнуться с конфликтом имен в будущем. Это также плохо сказывается на производительности ваших хранимых процедур. Лучше просто избегатьsp_
и использовать что-то другое в качестве префикса или вообще не использовать префикс!