ASP NET, NET Framework, MONO, SQL, Visual Studio | Professional Programs | Программист Еремин В.В.
(SQL) SQL (2010 год)

Реализация таймаута на динамически создаваемых SQL JOB, вызывающих SQL CLR сборку.

Так случилось, что я уже лет десять пишу софт с помощью динамического создания заданий в MS SQL. Вообще у меня почти в каждой софтине присутствуют десятки статических задач (причем в любом SQL сервере). Кое-что на тему статических задач (например для кеширования долгоиграющих запросов) я описал здесь - Выполнение периодических задач в ASP.NET.

Но в этой заметке я бы хотел остановиться именно на задачах, создаваемых динамически по ходу работы приложения. А таких приложений у меня десятки. Если попробовать вспомнить за последние 10 лет хотя бы некоторые, самые крупные проекты такого рода, которые нашли хоть какое-то отражение на моем сайте, то получается довольно внушительный списочек.


Еще раз повторю прописную истину, которая туго доходит до начинающих - в памяти хранить запланированные на будущее задачи нельзя, ибо сайт постоянно перезапускается. Вот например здесь SqlClr_IndexCryptoProtector - криптографическая защита индексов SQL-сервера с помощью SQL CLR сборки лежат скрины перезапуска IIS, в котором хостится одна моя софтина - крупнейшая социальная сеть России votpusk.ru. За три года - IIS перезапустился 50 тысяч раз. Это получается полный сброс IIS каждые полчаса. Хотя микрософт говорит о 20 часах в среднем. Но видимо для нагруженных сайтов утечки памяти достигуют критических значений раньше и сайт перезапускается чаще.

В принципе, альтернативная идея SQL JOB - это виндузовый шедулер. Но увы, гавнософт как всегда смошенничал - он обещал вместо новых и новых технологических авантюр довести до ума хоть что-нибудь. Например доделать NET Framework. Не в части развития его в бредовую сторону, а в части покрытия им существующего виндузового функционала. Это, увы, так и не было сделано, как и все остальное, что было обещано. Например сделать NET-обвязку вокруг основных Win-софтин, таких как стандартная виндузовая справка. Увы, доступ к виндузовому шедулеру на сегодня существует максимально кривой из всех возможных вариантов - через WMI. А это вообще не технология при наличии доступа к MS SQL.

Хотел бы заметить, что за много лет я прошел по этой технологии так далеко, как это возможно. Например, как вы понимаете, динамические SQL JOB хранятся в базе MSDB - а ее никто не бекапирует и не реплицирует (это практически невозможно - ибо как раз в ней и содержатся задачи, описывающие процесс репликацию). Соответственно при восстановлении конкретной прикладной базы - MSDB теряются. А с ней и все наши задачи, созданные пользователями. Кроме того, восстановление MSDB на другой сервер тоже невозможно, помимо настроек безопасности в SQL JOB там фигурируют имена сервера.

Итак, ниже я опишу ниже лишь часть полного механизма работы dynamic SQL JOB. Другая важная и обязательная часть, как вы поняли, включает в себя бекапирование всех нужных данных из MSDB в прикладную базу и последующее восстановление SQL JOB в MSDB при восстановлении прикладной базы из бекапа.


Описанная ниже технология, увы, обладает двумя недостатками:


Итак, в основе всей описанной технологии лежит моя процедура CreateScheduleJob:


   1:  ALTER  procedure [dbo].[CreateScheduleJob]
   2:    -- ВЫПОЛНЯЕМАЯ SQL_АГЕНТОМ SQL_ПРОЦЕДУРА ИЛИ SQL_СБОРКА       
   3:    @SQL_command as nvarchar(1000) = '', 
   4:    @schedule_start_date as int = 20060101,     -- соответствуют параметрам sp_add_jobschedule
   5:    @schedule_end_date as int   = 99991231,     -- соответствуют параметрам sp_add_jobschedule
   6:    @schedule_start_time as int = 0,            -- соответствуют параметрам sp_add_jobschedule
   7:    @schedule_end_time as int   = 235959,       -- соответствуют параметрам sp_add_jobschedule
   8:    @schedule_freq_type as int =4,              -- соответствуют параметрам sp_add_jobschedule
   9:    @schedule_freq_interval  as int= 1,         -- соответствуют параметрам sp_add_jobschedule
  10:    @schedule_freq_subday_type  as int= 4,      -- соответствуют параметрам sp_add_jobschedule
  11:    @schedule_freq_subday_interval as int = 1,  -- соответствуют параметрам sp_add_jobschedule
  12:    @schedule_freq_relative_interval as int = 0,-- соответствуют параметрам sp_add_jobschedule
  13:    @schedule_freq_recurrence_factor as int = 0,-- соответствуют параметрам sp_add_jobschedule
  14:    @delete as int = 3,                         -- соответствуют параметрам sp_add_job delete_level
  15:    @job_comment as nvarchar(1000) = '',
  16:    @FullReport as bit=0,
  17:    @Server_name as nvarchar(100) =  N'DEV\SQL2008',
  18:    @Category as nvarchar(100) =  N'__Assist_Timeout__'
  19:    as
  20:    declare @job_step_name nvarchar(100), @Job_Name as nvarchar(1100), @jobschedule_name nvarchar(100),@dbName sysname
  21:    select  @job_name=db_name()+ @Category + @job_comment + ' ' + cast(newid() as nvarchar(100)), @job_step_name =cast(newid() as nvarchar(100)), @jobschedule_name =  cast(newid() as nvarchar(100)),@dbName=db_name()
  22:    DECLARE @JobID BINARY(16), @JobID_Str as uniqueidentifier
  23:    --DECLARE @ReturnCode INT    
  24:    --SELECT  @ReturnCode = 0  
  25:    DECLARE @ReturnCode INT ,  @C_User sysname   
  26:    SELECT  @ReturnCode = 0 ,  @C_User=SUser_Sname()
  27:    --BEGIN TRANSACTION  
  28:     IF (SELECT COUNT(*) FROM msdb.dbo.syscategories  with (nolock) WHERE name = @Category) < 1 
  29:     -- Add the category
  30:     EXECUTE msdb.dbo.sp_add_category @name = @Category
  31:     If @FullReport=1 EXECUTE msdb.dbo.sp_help_category
  32:     SELECT @JobID = job_id FROM  msdb.dbo.sysjobs  with (nolock) WHERE (name = @job_name)       
  33:     IF (@JobID IS NULL)    
  34:       BEGIN  
  35:          -- Add the job
  36:          EXECUTE @ReturnCode = msdb.dbo.sp_add_job  @job_name , @owner_login_name = @C_User, @description=@job_comment , @category_name = @Category, @enabled = 0, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= @delete, @job_id = @JobID OUTPUT 
  37:          SELECT @JobID as [JobID], @job_name as [JOB_NAME]
  38:          IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  39:          If @FullReport=1 EXECUTE msdb.dbo.sp_help_job
  40:          -- Add the job steps
  41:          EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = @job_step_name , @command = @SQL_command, @database_name = @dbName, @server = '', @database_user_name = N'dbo', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
  42:          IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  43:          EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1 
  44:          IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  45:          If @FullReport=1 EXECUTE msdb.dbo.sp_help_jobstep @job_id = @JobID
  46:          -- Add the job schedules
  47:          EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = @jobschedule_name , @enabled = 1, @freq_type = @schedule_freq_type, @active_start_date = @schedule_start_date, @active_start_time = @schedule_start_time, @freq_interval = @schedule_freq_interval , @freq_subday_type = @schedule_freq_subday_type, @freq_subday_interval = @schedule_freq_subday_interval, @freq_relative_interval = @schedule_freq_relative_interval, @freq_recurrence_factor = @schedule_freq_recurrence_factor, @active_end_date = @schedule_end_date, @active_end_time = @schedule_end_time
  48:          IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  49:          If @FullReport=1 EXECUTE msdb.dbo.sp_help_jobschedule @job_id = @JobID
  50:          -- Add the Target Servers
  51:          EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = @Server_name 
  52:          IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  53:          If @FullReport=1 EXECUTE msdb.dbo.sp_help_jobserver @job_id = @JobID
  54:          -- 
  55:          Select @JobID_Str=CAST(@JobID as uniqueidentifier)
  56:          EXECUTE msdb.dbo.sp_update_job @job_id = @JobID_Str , @enabled = 1 
  57:        END
  58:    QuitWithRollback:

Я публикую эту процедуру не первый раз. Просматривая свой сайт я нашел различные ее варианты пятилетней давности и старше (для SQL2000). Но сейчас я сделал тест, когда одновременно я создал сотню запросов - и сервер повис! Чертовы микрософтовцы не поставили with Nolock в самых безобидных отборах в sp_verify_job и sp_update_job. Поэтому этот вариант я публикую без транзакции. Как вы понимаете, в реальности задача создается и исчезает сама после завершения таймаута и описанная ситуация возможна только в очень нагруженных сайтах. Опять же, или убирать транзакцию или переписывать этот тупизм микрософтовских индусов с With (nolock). В этом проекте в связи с ограниченным временем на задачку я решил просто убрать транзакцию.


Теперь пойдем на уровень выше. Задание создается процой SetTimeout, которая представляет собой небольшую обвязку над довольно универсальной процой CreateScheduleJob и позволяет указывать всего два параметра - через сколько минут вызвать процедуру таймаута и по какой записи в базе.


   1:  ALTER Procedure [dbo].[SetTimeout]
   2:  @i integer,
   3:  @minute integer
   4:  as
   5:  BEGIN
   6:  Declare @Timeout   as Datetime
   7:  Declare @StartDate as nvarchar(8)
   8:  Declare @StartTime as nvarchar(6)
   9:  Declare @StartCMD  as nvarchar(200)
  10:  Declare @Comment   as nvarchar(8)
  11:  SELECT  @Timeout = DateAdd(MI,@minute,GETDATE())
  12:  SELECT  @Comment = cast(@i as nvarchar(20))
  13:  SELECT  @StartCMD='exec airts.dbo.TimeoutEvent @i=' + cast(@i as nvarchar(20))
  14:  SELECT  @StartTime=cast(YEAR(@Timeout) as nvarchar(4))+
  15:          RIGHT('0'+cast(MONTH(@Timeout) as nvarchar(2)),2)+
  16:          RIGHT('0'+cast(DAY(@Timeout) as nvarchar(2)),2)
  17:  SELECT  @StartTime=RIGHT('0'+cast(DATEPART(HH,@Timeout) as nvarchar(2)),2)+
  18:          RIGHT('0'+cast(DATEPART(MI,@Timeout) as nvarchar(2)),2)+
  19:          RIGHT('0'+cast(DATEPART(SS,@Timeout) as nvarchar(2)),2)
  20:  EXEC    [dbo].[CreateScheduleJob]
  21:          @SQL_command = @StartCMD,
  22:          @schedule_start_date = @StartDate,
  23:          @schedule_end_date = 99991231,
  24:          @schedule_start_time = @StartTime,
  25:          @schedule_end_time = 235959,
  26:          @schedule_freq_type = 1,
  27:          @schedule_freq_interval = 1,
  28:          @schedule_freq_subday_type = 0,
  29:          @schedule_freq_subday_interval = 0,
  30:          @schedule_freq_relative_interval = 0,
  31:          @schedule_freq_recurrence_factor = 1,
  32:          @job_comment = @Comment,
  33:          @delete=3,
  34:          @FullReport = 1,
  35:          @Server_name = 'DEV\SQL2008'
  36:  END    

Соответственно, где-то в коде сайта стоит вызов этой процедуры:


   1:              'установили таймаут (запустили задание, которое вызовет Assist_RET.ashx
   2:              Dim SetTimeout As New VBNET2009.ExecSQL_RDR("i", RequestNumber, "minute", Math.Min(E1.Timelimit,40))
   3:              SetTimeout.ExecSQL("SetTimeout", Data.CommandType.StoredProcedure)

Вот собственно и все. Теперь разберемся что делает задание, которое запустится через установленное время. В моем случае - это вызов процедуры TimeoutEvent, которая должна сообщить сайту, что за прошедшие 40 минут клиент так и не оплатил своей пластиковой картой свой заказанный товар.


   1:  ALTER procedure [dbo].[TimeoutEvent]
   2:  @i integer
   3:  as
   4:  Declare @ClientResponseDate datetime
   5:  select  @ClientResponseDate=ClientResponseDate from airts.dbo.Request where i=@i 
   6:  if     (@ClientResponseDate is NULL) BEGIN
   7:          update airts.dbo.Request set [Timeout]=GETDATE() where i=@i
   8:          --клиенту шлюза еще ничего не сообщали
   9:             DECLARE @Result nvarchar(max), @URL nvarchar(500)
  10:          SELECT  @URL='http://airts-admin.asp-net.ru/Assist_RET.ashx?order_idp=' + cast(@i as nvarchar(20))
  11:          --SELECT  @URL='http://airts-admin.asp-net.ru/?order_idp=' + cast(@i as nvarchar(20))
  12:          SELECT  airts.dbo.WebRequest(@URL)
  13:  END

В принципе, в каких-то проектах можно ограничится просто установкой какого-то флага в базе своего сайта (если это просто интернет-магазин), но в моем случае я еще и должен перейти со своего сайта на сайт клиента (потому что у меня сервис для интернет-магазинов). И я должен сообщить о таймауте оплаты не только своему сайту, но и сайту, заказавшему у меня оплату. И функционал Assist_RET весьма непрост - и мне надо выполнить сначала его, а потом уже уйти кроссом на конечный сайт интернет-магазина, потребителя услуг моего сайта - которому я им сообщу о несостоявшейся оплате.

Понятно, что такие переходы по сайтам реализуются SQL CLR только сборками. Если бы мне надо было сразу уйти на некий внешний (или просто выполнить реквест на свой сайт) - эта сборка бы состояла буквально из одной-единственной строки кода - строки 13 в коде ниже (такая сборка у меня опубликована тут).

Ну а с моим более сложным функционалом исполнения при таймауте - сначала кода своего сайта, а потом (по окончании работы хандлера Assist_RET на своем сайте) автоматический редирект на сайт конечного интернет-магазина (с сообщением ему о несостоявшейся оплате) сборка будет выглядеть вот так:


   1:  Imports System
   2:  Imports System.Data
   3:  Imports System.Data.SqlClient
   4:  Imports System.Data.SqlTypes
   5:  Imports Microsoft.SqlServer.Server
   6:   
   7:  'ALTER DATABASE airts SET TRUSTWORTHY ON
   8:  'CREATE ASSEMBLY [WebRequest] FROM 0x4D5A90000300000004000000FF... WITH PERMISSION_SET = EXTERNAL_ACCESS
   9:  Partial Public Class UserDefinedFunctions
  10:      <Microsoft.SqlServer.Server.SqlFunction()> _
  11:      Public Shared Function WebRequest(ByVal URL As String) As SqlString
  12:          Try
  13:              Dim HTML As String = GetRequest(URL)
  14:              'запрос по HTTP http://airts-admin.asp-net.ru/Assist_RET.ashx?order_idp=52 вернет
  15:              '
  16:              '<html>
  17:              '<head>
  18:              '<meta http-equiv='Content-Type' content='text/html; charset=UTF-8'>
  19:              '</head>
  20:              '<body onload='submit();'> 
  21:              '<form name='vbnet2009' action='http://airts.asp-net.ru/err.aspx' method='post' accept-charset='UTF-8'>
  22:              '<input type='hidden' name='Retcode' value='10' />
  23:              '<input type='hidden' name='Order_number' value='227' />
  24:              '<input type='hidden' name='Message' value='201' />
  25:              '<input type='hidden' name='TicketNumber' value='52' />
  26:              '<input type='image' src='images/wait.gif' style='border-width:0px;' />
  27:              '</form>
  28:              '<script language='javascript' type='text/javascript'>
  29:              'function submit() {
  30:              'document.forms[0].submit();
  31:              '}
  32:              '</script>
  33:              '</body>
  34:              '</html>
  35:              '
  36:              If HTML.Contains("vbnet2009") Then
  37:                  'vbnet2009 - соглашение об именах, включающее нижеследующий алгоритм
  38:                  Dim RedirectURL As String = ""
  39:                  Dim InputNames As New System.Collections.ArrayList
  40:                  Dim InputValues As New System.Collections.ArrayList
  41:                  Dim Items As String() = HTML.Split("'")
  42:                  For i As Integer = 0 To Items.Length - 1
  43:                      If Items(i).Contains("action") Then
  44:                          RedirectURL = Items(i + 1)
  45:                      End If
  46:                      If Items(i).Contains("name") Then
  47:                          InputNames.Add(Items(i + 1))
  48:                      End If
  49:                      If Items(i).Contains("value") Then
  50:                          InputValues.Add(Items(i + 1))
  51:                      End If
  52:                  Next
  53:                  '
  54:                  If InputNames.Count = InputValues.Count + 1 And RedirectURL <> "" Then
  55:                      'в InputNames(0) на 1 больше элемент чем в InputValues (name='vbnet2009')
  56:                      Dim POST_Data As New Text.StringBuilder
  57:                      If InputValues.Count = 5 Then
  58:                          POST_Data.Append((InputNames(1)) & "=" & (InputValues(0)) & "&")
  59:                          POST_Data.Append((InputNames(2)) & "=" & (InputValues(1)) & "&")
  60:                          POST_Data.Append((InputNames(3)) & "=" & (InputValues(2)) & "&")
  61:                          POST_Data.Append((InputNames(4)) & "=" & (InputValues(3)) & "&")
  62:                          POST_Data.Append((InputNames(5)) & "=" & (InputValues(4)))
  63:                      ElseIf InputValues.Count = 4 Then
  64:                          POST_Data.Append((InputNames(1)) & "=" & (InputValues(0)) & "&")
  65:                          POST_Data.Append((InputNames(2)) & "=" & (InputValues(1)) & "&")
  66:                          POST_Data.Append((InputNames(3)) & "=" & (InputValues(2)) & "&")
  67:                          POST_Data.Append((InputNames(4)) & "=" & (InputValues(3)))
  68:                      ElseIf InputValues.Count = 3 Then
  69:                          POST_Data.Append((InputNames(1)) & "=" & (InputValues(0)) & "&")
  70:                          POST_Data.Append((InputNames(2)) & "=" & (InputValues(1)) & "&")
  71:                          POST_Data.Append((InputNames(3)) & "=" & (InputValues(2)))
  72:                      ElseIf InputValues.Count = 2 Then
  73:                          POST_Data.Append((InputNames(1)) & "=" & (InputValues(0)) & "&")
  74:                          POST_Data.Append((InputNames(2)) & "=" & (InputValues(1)))
  75:                      ElseIf InputValues.Count = 1 Then
  76:                          POST_Data.Append((InputNames(1)) & "=" & (InputValues(0)))
  77:                      Else
  78:                          POST_Data.AppendLine()
  79:                      End If
  80:                      '
  81:                      PostRequestStr1(RedirectURL & "?" & POST_Data.ToString, POST_Data.ToString)
  82:                      Return RedirectURL & "?" & POST_Data.ToString
  83:                  End If
  84:              End If
  85:          Catch ex As Exception
  86:              Return "Error: " & ex.Message
  87:          End Try
  88:      End Function
  89:   
  90:      Public Shared Function GetRequest(ByVal URL As String) As String
  91:          Try
  92:              'запрос по HTTP 
  93:              Dim Request As Net.HttpWebRequest = CType(System.Net.WebRequest.Create(URL), Net.HttpWebRequest)
  94:              Request.AllowAutoRedirect = True
  95:              Dim Response As Net.WebResponse = Request.GetResponse()
  96:              Dim Reader As New System.IO.StreamReader(Response.GetResponseStream(), System.Text.Encoding.Default)
  97:              Dim HTML As String = Reader.ReadToEnd
  98:              Reader.Close()
  99:              Return HTML
 100:          Catch ex As Exception
 101:              Return "Error: " & ex.Message
 102:          End Try
 103:      End Function
 104:   
 105:      'Запрос странички методом POST (молча, ошибки обрабатываются извне этого кода) 
 106:      ' БЕЗ ОЖИДАНИЯ ОТВЕТА !
 107:      Public Shared Sub PostRequestStr1(ByVal URL As String, ByVal POST_Data As String)
 108:          Dim byteArray As Byte()
 109:          byteArray = System.Text.Encoding.UTF8.GetBytes(POST_Data)
 110:          '========== System.NotSupportedException The URI prefix is not recognized.
 111:          Dim request As Net.HttpWebRequest = Net.HttpWebRequest.Create(URL)
 112:          request.Method = "POST"
 113:          request.ContentType = "application/x-www-form-urlencoded"
 114:          request.ContentLength = byteArray.Length
 115:          '========== System.Net.WebExceptionStatus.Timeout Unable to connect to the remote server
 116:          Dim POST_Stream As IO.Stream = request.GetRequestStream()
 117:          POST_Stream.Write(byteArray, 0, byteArray.Length)
 118:          POST_Stream.Close()
 119:      End Sub
 120:   
 121:  End Class

Как видите, на этой страничке я не только описал технологию Dynamic SQL Job (ограничившись процедурой TimeoutEvent), но и описал прикладное применение этой технологии (на более высоком уровне софта) в одном из своих проектов. В целом этот мой проект содержит около 100 тысяч строк кода, и я как видите, по прежнему стараюсь из каждого реально существующего коммерческого проекта показать хотя бы один процент интересного кода.

Надеюсь, публикация технологического решения таймаута была полезной.



Комментарии к этой страничке ( )
ссылка на эту страничку: http://www.vb-net.ru/SqlJobTimeout/index.htm
<На главную>  <В раздел ASP>  <В раздел NET>  <В раздел SQL>  <В раздел Разное>  <Написать автору>  < Поблагодарить>