Еще раз о ловле и обработке изменений в реляционной БД

Часто возникает задача получения изменений в таблице БД за период для их обработки. Существуют такие основные варианты решения:

1. маркирование каждой измененной записи тем или иным образом,
2. регистрация каждого изменения в отдельной таблице с помощью триггера (см. подробнее Захват и обработка изменений в БД...),
3. получение изменений за период с помощью разности с более ранним снэпшотом (см. подробнее Передача изменений между БД: подход в духе KISS).

Если у вас есть выбор, какой из вариантов реализовать, то сделать этот выбор правильно помогут ответы на следующие вопросы:

1. Внесение изменений в таблицу полностью под вашим контролем?
2. У изменений будет только один обработчик или больше?
3. Записи в таблице удаляются (командой delete)?
4. Насколько большая эта таблица?

Рассмотрим эти вопросы для каждого варианта решения.

Маркирование измененной записи

1. Требует, чтобы внесение изменений было полностью под вашим контролем, в противном случае кто-то сможет изменять записи, не помечая их как измененные.
2. Позволяет работать с изменениями или одному или многим обработчикам, в зависимости от реализации.
3. Не позволяет регистрировать факты удаления записей и, следовательно, не позволяет их обрабатывать.
4. Позволяет работать с таблицами любого размера.

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

Чтобы изменения могли быть обработаны несколькими обработчиками, необходимо для пометки измененных записей использовать поле с непрерывно возрастающим значением – номером или датой-и-временем изменения. Тогда каждый обработчик должен после обработки запоминать максимальное обработанное значение, и при следующей обработке брать строки с большими значениями.

Регистрация изменений в отдельной таблице

1. Не требует, чтобы внесение изменений было полностью под вашим контролем, так как триггер поймает каждое изменение, кто бы ни был его источником.
2. Позволяет работать с изменениями или одному или многим обработчикам, в зависимости от того, как реализовано журналирование.
3. Позволяет регистрировать факты удаления записей (и значения полей удаляемых записей, если нужно) и обрабатывать их.
4. Позволяет работать с таблицами любого размера.

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

Получение изменений с помощью снэпшотов

1. Не требует, чтобы внесение изменений было полностью под вашим контролем, так как разность таблицы и снэпшота покажет каждое изменение, кто бы ни был его источником.
2. Позволяет работать с изменениями или одному или многим обработчикам, в зависимости от реализации.
3. Позволяет обрабатывать удаленные записи.
4. Не позволяет работать с очень большими таблицами, так как работа со снэпшотами становится очень дорогой и медленной.

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

Резюмируя,

сначала отмечу минусы.

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

Получение изменений с помощью снэпшотов становится очень дорогим (и неприемлемо медленным!) удовольствием, если таблица достаточно большая.

Теперь отмечу плюсы.

Маркирование измененной записи это практически "бесплатное" решение, так как реализуется при выполнении обычных команд insert и update приложениями и скриптами. И не требует создания триггеров или отдельных таблиц, как два других подхода.

Получение изменений с помощью снэпшотов чудовищно удобно, когда нужно получить изменения не в отдельной физической таблице БД, а в "таблице", возвращаемой сложным SQL запросом, включающим разные таблицы и вью.