Как отслеживать изменения в СУБД при конкурентной записи?

Мне нужно реализовать синхронизацию данных в распределённой системе, учитывая, что в таблицы с данными происходит конкурентная запись. Экспорт из основной БД должен читать только изменённые (и вставленные) строки.

Обычный совет в таком случае: "используй триггеры", помечай в триггере строки timestamp'ом изменения данных или монотонно возрастающей числовой последовательностью (ревизией). И сообщай удалённой системе этот timestamp/ревизию.

Но проблема с конкурентной записью в том, когда происходит коммит. Ниже описан пример:
[время 00:00] Транзакция A начинает запись большой пачки данных; помечает строки timestamp [00:00].
[время 00:02]      Транзакция B начинает запись маленькой пачки данных; помещает строки timestamp [00:02].
[время 00:03]      Транзакция B заканчивает запись и происходит коммит.
[время 00:10]            Начинается экспорт в удалённую систему. Уровень изоляции - ReadCommitted, 
                         поэтому только данные из транзакции B попадают в выборку для экспорта, timestamp 00:02.
[время 00:15] Транзакция A заканчивает запись и происходит коммит. 
              Удалённая система никогда не получит эти данные(!)


Я хочу, чтобы строки из Транзакции А были в БД помечены как более поздние, чем строки в Транзакции B.
Ведь закоммитились они позже: доказательство тому - в момент экспорта по факту в БД их ещё не было (как раз благодаря изоляции).

Мне нравится подход с Change tracking в MSSQL, он эту ситуация способен решить.
Но я держу в голову скорый переход на PostgreSQL, и мне нужно придумать общее решение.

Как правильно решать подобную задачу?

Оригинал моего вопроса на StackOverflow - здесь.
  • Вопрос задан
  • 1146 просмотров
Пригласить эксперта
Ответы на вопрос 3
Melkij
@Melkij
PostgreSQL DBA
Возьмите pgq и пишите в очередь перед коммитом как найти нужные данные. Или триггером сразу при записи в таблицы шлите сообщения через pgq.
При экспорте соответственно читаете сообщения из pgq. Всё.

Или напишите или возьмите какой-нибудь готовый logical decoding. Сможете читать вообще поток репликации

В общем случае в общем-то нужна всё равно очередь. Из транзакции в таблички очереди пишете метки какие данные изменились, читатель читает эту метки и получает все изменения независимо от времени коммита.
Ответ написан
@younghacker
Навскидку я бы сделал маленькую табличку "очередь репликации" и тригерами из других таблиц в неё заносил бы данные.
ID - записи первичный ключ
TimeStamp - время внесения изменений (возможно избыточное, непонятно будут ли изменяться записи уже изменённые)
TableID - идентификатор таблицы источника изменений
RecordID - идентификатор записи из таблицы источника
md5sum - контрольная сумма изменённой записи (всех полей) опциональное.

На этой табличке тригера генерируют события.
Внесено изменение - генерируете событие NEW_WORK_FOR_REPLICATOR_! ;)

Событие запускает обработчик (репликатор) который выгребает из этой таблицы все данные. И реплицирует все записи подряд из таблиц которые здесь зарегистрировались. При успешном экспорте запись в таблице удаляется. Если скорость изменений в базе небольшая то и таблица будет небольшая. Основную часть времени будет пустовать.

Получится что транзакция по успешному завершению создаст запись в этой вспомогательной таблице.
Запись сгенерирует событие. На событие прибежит репликатор экспортирует запись источник и если всё окей - прибъёт запись в этой таблице. Количество записей в ней вернётся в норму. Для застаревших записей в репликаторе можно сделать сборщик мусора который использует контрольную сумму которая позволит проверить есть ли эта запись в целевой таблице при экспорте если в какой-то момент до удаления записи вдруг произошла остановка базы источника либо репликатор завис или был убит (в жизни всегда есть место oomkill). При этом сборщик мусора сделает запись в бортовой журнал для отчётности. :)

Можно также обойтись и без событий. Пустая таблица селектится быстро поэтому можно мониторить часто. А если в таблице есть записи - значит есть работа для репликатора. Тогда не придётся писать логику игнора событий во время работы репликатора.

Транзакции - ReadCommited.
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы