Database Mail: Почтовые рассылки прямо из Microsoft SQL Server

[@lair]

За использование WAITFOR TIME в «реальной жизненной ситуации» надо бить. Что, агента в MS SQL нет?

[@CodeDriller]

Вы правы, надежнее использовать не WAITFOR TIME, а создать для этого отдельный Job в SQL Agent, хоть это и более трудоемко. Надежнее потому (я поясняю для читателей), что запрос в режиме ожидания WAITFOR TIME будет сброшен при аварийной перезагрузке сервера, а Job в SQL Agent будет выполнен.

[@lair]

То есть сам факт многочасового ожидания в запущенном скрипте вас никак не смущает? Учитывая, что у скриптов бывает таймаут.

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

[@CodeDriller]

Бог с вами, никакой блокировки таблицы пользователей здесь и в помине быть не может. И никакого таймаута для обычных (не remote) скриптов в SQL Server тоже нет, если вы только сами его не установите.

[@lair]

Вы хотите сказать, что курсор не накладывает никаких блокировок на таблицу, по которой он ходит?

[@qw1]

Может, он сделал

ALTER DATABASE [mysite] SET ALLOW_SNAPSHOT_ISOLATION ON;

и забыл о блокировках при чтении как о страшном сне

[@CodeDriller]

В данной ситуации на время ожидания WAITFOR TIME таблица не блокируется, ни на чтение, ни на запись.

[@lair]

На чтение — верю. А за счет чего она не блокируется на запись? Не может быть двойных чтений и пропущенных записей?

(может вы еще скажете, что и schema lock на нее не ставится?)

[@CodeDriller]

Если вы не верите про запись — можете проверить сами. Точно я не смогу ответить на ваш вопрос, за счет чего. Это вопрос реализации. Я как-то взял посмотреть «Microsoft SQL Server 2008 Internals», и даже там реализация динамических курсоров освещена не была. Видимо, это составляет технологическую тайну Microsoft. Но, вообще говоря, если верить предположениям, что в динамических курсорах начальный SQL-запрос с минимальными изменениями выполняется на каждый FETCH, то остальное домыслить несложно. Соответственно такой подход в обычной ситуации позволяет поддерживать в курсоре актуальный порядок записей. /Перенес на ветку ниже/

[@lair]

Актуальный порядок записей — это как?

Возьмем банальный пример: вот у вас идут записи 1, 2, 5, 6 (по id; эта ситуация выглядит надуманной только до тех пор, пока вы сортируете записи по id и id числовой, стоит вам сделать сортировку по имени, или взять id в виде guid — она сразу станет реальной), вы только что обработали запись 2 и попали в ожидание. В его время пользователь добавил запись 3 (у нас же нет блокировки на запись, правда?). Какая запись выберется после ожидания, 3 или 5?

Окей, предположим, вам повезло, выбралась 3, вы ее обработали, затем выбрали и обработали 5, снова стоите в ожидании. В это время пользователь добавляет запись 4. Какая запись выбретеся после ожидания, 4 или 6?

[@CodeDriller]

Первый вопрос: выберется запись 3, потому что после 2 SQL Server снова сделает полный SELECT и указатель курсора (в документации это называется positioning marker) укажет на запись, следующую за 2. Второй вопрос: выберется запись 6, потому что она следует за 5.

То есть порядок будет действительно актуальным: все завершенные транзакции будут доступны в курсоре в режиме реального времени, а если установлен уровень изоляции «read uncommitted», то даже и незавершенные.

[@lair]

Поздравляю вас, вы только что продолбали одно письмо, которое надо было отправить.

[@CodeDriller]

Прошу прощения, не понял…?

[@lair]

Добавленная запись с id 4.

[@CodeDriller]

Ну нет, рассылка была предназначена для тех, кто целый год не был на сайте, а не тех, кто зарегистрировался 0,87 секунды назад. :-)

[@lair]

Осталось убедить в этом заказчика. Не говоря уже о том, что это для id работает только сценарий с добавлением, а для всех остальных полей — и изменение тоже.

Собственно, это наглядная проблема блокировок и их отсутствия.

[@CodeDriller]

Изменения полей отражаются в динамическом курсоре тоже в режиме реального времени.

А если вам по какой-то причине будет нужна блокировка, вы можете вызвать ее искусственно.

[@lair]

Вот только вы про пересортировку записей забыли. «В реальном времени».

[@CodeDriller]

И пересортировка в режиме реального времени будет, на каждый FETCH. Какой вы ORDER для курсора укажете, такая и будет сортировка.

[@lair]

После чего вы (если не будет блокировок) получите двойные отправки и пропущенные письма.

[@CodeDriller]

Я повторюсь. Вы сами выбираете поля для сортировки. Если вы собираетесь во время выполнения скрипта переприсваивать id пользователей, то вы можете либо сортировать по другому полю, либо вызвать блокировку искусственно. Всё в вашей власти. Хотя переприсваивать идентифицирующие поля я бы вам не рекомендовал — это очень плохая практика.

[@lair]

Я повторюсь: вы отчетливо не понимаете последствий того, что делаете, решая задачу максимально в лоб. Это опасно.

[@CodeDriller]

//

[@BelAnt]

В данном случае динамические курсоры не к месту. Да и вообще большинство задач можно выполнить без их использования. Возможно поэтому функционал курсоров довольно скудно освещается со стороны Microsoft.

Советую вам поближе познакомиться с возможностями службы SQL Agent, в совокупности с SSIS.

[@BelAnt]

+ полезно хранить информацию о том, что и кому вы отправляли.

[@CodeDriller]

Эта информация сохраняется автоматически. Я писал выше в статье, отправленные письма можно посмотреть таким SQL-запросом:

SELECT * FROM msdb.dbo.sysmail_sentitems

[@CodeDriller]

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

Информации насчет курсоров достаточно (лучший, на мой взгляд, источник — книга «SQL Server Query Performance Tuning Distilled»), просто Microsoft не раскрывает детали внутренней реализации курсоров, видимо, считая эту информацию секретной. Может быть, я соберусь еще написать отдельную статью о курсорах и блокировках.

Насчет ознакомления с SQL Agent — благодарю, мэтр, это бесценный совет. :-)

[@BelAnt]

SQL Agent, в совокупности с SSIS. позволяют автоматизировать абсолютно любые процессы.

В данном случае я бы делал рассылку на регулярной основе. Каждый день формируется очередь на отправку уведомлений и сохраняется в реляционную таблицу. В удобное время запускаем job который проходит по необработанным записям в очереди с TOP 1 и отправляет письмо.

Такой механизм является более универсальным и позволяет наладить отправку различных уведомлений. Шаблоны писем храним в отдельной таблице.

[@CodeDriller]

А, понятно. С SSIS я действительно не знаком, поэтому не представляю, как это там делается. При возможности ознакомлюсь. Спасибо. Вариант принимается.

[@CodeDriller]

Если вы не верите про запись — можете проверить сами. Точно я не смогу ответить на ваш вопрос, за счет чего. Это вопрос реализации. Я как-то взял посмотреть «Microsoft SQL Server 2008 Internals», и даже там реализация динамических курсоров освещена не была. Видимо, это составляет технологическую тайну Microsoft. Но, вообще говоря, если верить предположениям, что в динамических курсорах начальный SQL-запрос с минимальными изменениями выполняется на каждый FETCH, то остальное домыслить несложно. Соответственно такой подход в обычной ситуации позволяет поддерживать в курсоре актуальный порядок записей.

[@CodeDriller]

Извините, пожалуйста, не в ту ветвь запостил ответ.