Реализация таймаута на динамически создаваемых SQL JOB, вызывающих SQL CLR сборку.
Так случилось, что я уже лет десять пишу софт с помощью динамического создания заданий в MS SQL. Вообще у меня почти в каждой софтине присутствуют десятки статических задач (причем в любом SQL сервере). Кое-что на тему статических задач (например для кеширования долгоиграющих запросов) я описал здесь - Выполнение периодических задач в ASP.NET.
Но в этой заметке я бы хотел остановиться именно на задачах, создаваемых динамически по ходу работы приложения. А таких приложений у меня десятки. Если попробовать вспомнить за последние 10 лет хотя бы некоторые, самые крупные проекты такого рода, которые нашли хоть какое-то отражение на моем сайте, то получается довольно внушительный списочек.
- В 2003-м году я писал для iile.ru задачку по контролю исполнительской дисциплины. Директор на совещании устанавливает своему сотруднику какой-то срок по какой-то задачке. А потом кадровику в нужный срок приходит напоминание проверить исполнение.
- В 2005-м году я делал систему интернет-магазинов по торговле электроникой digitalshop.ru. Из общего перечня задач, которые я решал для этой компании - шестой пункт как раз это приложение. Это была исключительно удачная софтина. Каждый работник этой компании работает как бы в Аське (моей разработки). Только работает эта аська только по сотрудникам (находящимся в интрасети и экстрасети) и управляется эта аська директором фирмы. Он например может создать напоминание, которое выпрыгивает у сотрудника перед носом каждую минуту и блокирует работу, пока сотрудник не отпишется что задача выполнена.
- В 2006-м году я делал систему для ведения учета задач программиста. На рабочем столе висят полупрозрачные иконки задач и некоторые из них начинают противно моргать, когда сроки подходят к концу. Одна из версий основана на виндузовом шедулере задач, а вторая как раз на динамических SQL JOB
- В 2007-м году я делал helpdesk систему управления инцидентами для MS - она была устроена точно так же - есть предельные сроки разрешения инцидентов. О том что они истекают надо напоминать.
- В 2008-м году я написал софтину для тестирования знаний для РЖД, в принципе у меня особых описаний этой софтины на сайте не сохранилось - только осталась инструкция для начинающих как готовить Excel-отчеты в натуральном Excel-формате - Формирование Excel-отчетов. И если клиентская часть времени контроллировалась таймаутом странички в яваскрипте, то серверная, понятное дело - именно SQL-заданием.
- В 2009-м году я написал для компании gisis.ru софтину для менеджеров. Которая должна была напоминать менеджерам о сроках всяких промежуточных этапов всяких договоров. Те менеджер загружает свой очередной договор на сайт и отмечает чтобы каждого например 29-го числа месяца ему приходило письмо-напоминание о том, что надо подписывать какие-то квартальные акты. И чтобы в письме были вложены все нужные для этого договора бумажки. Хотя вот эту мою последнюю софтину по каким-то административным причинам не внедрили.
- И вот в 2010-м году мне в очередной раз потребовалось написать сайт, который я не вижу, как сделать иначе - кроме как с использованием Dynamic SQL JOB. Здесь задача состоит в реализации таймаута в платежном шлюзе. То есть человеку дается например 40 минут чтобы он оплатил покупку пластиковой картой. Не уложился - покупка считается неоплаченной. Ну действительно, нельзя же вечно держать какой-то заказанный товар забронированным и недоступным другим покупателям.
Еще раз повторю прописную истину, которая туго доходит до начинающих - в памяти хранить запланированные на будущее задачи нельзя, ибо сайт постоянно перезапускается. Вот например здесь 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 при восстановлении прикладной базы из бекапа.
Описанная ниже технология, увы, обладает двумя недостатками:
- Она не работает на бесплатном MS SQL Express. Я описывал эту проблему многократно на своем сайте. Эта технология - уже не ерундовина для студенческих лабораторных работ, это серьезная технология для промышленного софта. И минимальная версия MS SQL, которая поддерживает эту технологию стоит от 3 тысяч долларов. Не нравится - уходите на PostgreSQL. Там существует 400 пакетов с дополнительным функционалом, а функционал SQL JOB входит даже в самую минимальную конфигурацию.
- Эта технология нарушает безопасность сервера. В принципе задания получается создавать нормально если вы являетесь не только членом SQL-роли SYSADMIN, но даже и даете права DBO свеому логину на базу MSDB. Это серьезная дыра в безопасности. Исправить ее можно ручной корректировкой двух процедур sp_verify_job и sp_update_job - просто внесите туда SID своего логина. Чтобы ваш @owner_sid обрабатывался как например системный SID 0x010100000000000512000000. Иначе система безопасности MS SQL будет создавать вот такие триггера и не будет вам позволять запустить созданную вами задачу.
Итак, в основе всей описанной технологии лежит моя процедура 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.vb-net.com/Assist_RET.ashx?order_idp=' + cast(@i as nvarchar(20))
11: --SELECT @URL='http://airts-admin.vb-net.com/?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.vb-net.com/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.vb-net.com/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 тысяч строк кода, и я как видите, по прежнему стараюсь из каждого реально существующего коммерческого проекта показать хотя бы один процент интересного кода.
Надеюсь, публикация технологического решения таймаута была полезной.
)
|
|