Журнал изменений базы данных
От: afkos  
Дата: 11.02.20 10:14
Оценка:
Добрый день!

Разрабатываем такую специфическую систему средних размеров для управления данными, для промышленности, в основном.
Столкнулись с необходимостью иметь в БД какой-то журнал со сведениями об изменениях различных сущностей,
чтобы внешние системы с которыми настроена интеграция (штатная или силами клиента) могли понимать какие сущности удалены, изменены, добавлены в системе, для того чтобы их подтянуть себе.

Первое что приходит в голову — табличка в бд:
TABLE EventLog (eventId, eventType, entityId, ... )
— Храним только тип события и идентификатор сущности, сами изменения не хранятся
— Источник изменений (БД) добавляет в неё события в тригерах или еще как-то, внутри транзакций — таким образом мы сразу имеем атомарность изменений.
— Читатели вычитывают новые записи по eventId.
— Читателей может быть несколько, поэтому записи из таблицы при чтении не удаляются — чтобы другие читатели могли подключиться с любой точки синхронизации и получить изменения.
— Удалять записи предполагается в сервисной функции за заведомо устаревшие периоды времени.

С записью кажется все в порядке, а вопрос — как правильно из него читать:

Допустим есть внешняя система — назовем её читатель,
она по расписанию или по событию вычитывает все новые записи из таблицы
select .. from EventLog where eventId > @lastEventId -- СУБД у нас версионник, мы получим все новые записи, кроме еще не зафиксированных транзакций.
где @lastEventId — это идентификатор последней записи, которую он прочитал до этого (по сути @lastEventId — точка синхронизации)

Такая таблица была бы для нас удобна и для других задач: инвалидация кешей, синхронизация полнотекстовой базы.

НО при этом возможна следующая ситуация:

в EventLog Max(eventId) = 10
Транзакция А — добавляет запись eventId = 11
Транзакция Б — добавляет запись eventId = 12
Транзакция Б — фиксирует транзакцию
Читатель:
— выполняет запрос select from EventLog where eventId > 10,
— получает запись eventId = 12,
— запоминает @lastEventId = 12
Транзакция А — фиксирует транзакцию (eventId = 11)

Проблема: Читатель больше никогда не узнает о записи с eventId = 11. т.к его @lastEventId = 12

Читать из EventLog с ожиданием на блокировке в этом сценарии — крайне нежелательно.
Использовать внешнюю очередь в текущей архитектуре затруднительно

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

Можно ли что-то сделать в предложенной схеме?
Буду очень благодарен любым советам!
Re: Журнал изменений базы данных
От: Pavel Dvorkin Россия  
Дата: 11.02.20 10:44
Оценка:
Здравствуйте, afkos, Вы писали:

A>Можно ли что-то сделать в предложенной схеме?


Еще одна таблица, в которую заносятся записи после фиксации трансакции , в ней transactionId, а от нее one-to-many на элементы трансакции. Брать в порядке transactionId и получать ее eventId.

Транзакция А — добавляет запись eventId = 11
Транзакция Б — добавляет запись eventId = 12
Транзакция Б — фиксирует транзакцию и записывает transactionId = 100 со ссылкой на eventdId = 12
Читатель:
— выполняет запрос select from TranscactionLog where transactionId > 99,
— получает запись transcationId = 100 и по ней eventId = 12,
— запоминает @lastTransactionId = 100
Транзакция А — фиксирует транзакцию и записывает transactionId = 101 со ссылкой на eventdId = 11
Читатель:
— выполняет запрос select from TranscactionLog where transactionId > 100,
— получает запись transcationId = 101 и по ней eventId = 11,
— запоминает @lastTransactionId = 101

Иными словами, очередь не из event, а из завершенных transaction, ссылающихся на свои event
With best regards
Pavel Dvorkin
Re: Журнал изменений базы данных
От: vsb Казахстан  
Дата: 11.02.20 10:56
Оценка:
1. Сделать ограничение на длительность транзакции, при синхронизации делать запрос в пределах этой длительности. В данном случае удобней какой-нибудь change_timestamp, а не id (ну или мерять время в идентификаторах, тоже вариант). Например если известно, что дольше часа ничего не висит, значит синхронизироваться до часа.

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

3. В этой же базе ведётся табличка, куда insert триггером вставляет новую запись, а при импорте вы транзакцией эту запись удаляете. Тогда ничего не потеряется. Самый надёжный вариант, но с накладными расходами.
Отредактировано 11.02.2020 10:56 vsb . Предыдущая версия .
Re[2]: Журнал изменений базы данных
От: afkos  
Дата: 11.02.20 12:39
Оценка:
Здравствуйте, Pavel Dvorkin, Вы писали:

PD>Еще одна таблица, в которую заносятся записи после фиксации трансакции , в ней transactionId, а от нее one-to-many на элементы трансакции. Брать в порядке transactionId и получать ее eventId.


PD>Транзакция А — добавляет запись eventId = 11

PD>Транзакция Б — добавляет запись eventId = 12
PD>Транзакция Б — фиксирует транзакцию и записывает transactionId = 100 со ссылкой на eventdId = 12
PD>Читатель:
PD>— выполняет запрос select from TranscactionLog where transactionId > 99,
PD>— получает запись transcationId = 100 и по ней eventId = 12,
PD>— запоминает @lastTransactionId = 100
PD>Транзакция А — фиксирует транзакцию и записывает transactionId = 101 со ссылкой на eventdId = 11
PD>Читатель:
PD>— выполняет запрос select from TranscactionLog where transactionId > 100,
PD>— получает запись transcationId = 101 и по ней eventId = 11,
PD>— запоминает @lastTransactionId = 101

PD>Иными словами, очередь не из event, а из завершенных transaction, ссылающихся на свои event


Спасибо за ответ!

В таком варианте возможен Race Condition на вставку в таблицу TransactionLog:
если происходит вставка одновременно, transactionId=10 может вставиться в результате раньше чем transactionId=9,
и читатель соответственно получит transactionId=10, а запись с 9 уже не прочитает.

Достаточно маловероятно, но синтетический тест показывает что это возможно, я бы спал спокойнее, если бы знал, что в системе не может возникнуть подобных артефактов
Вероятность такого события может повышаться при пиковых нагрузках на SQL Server.

Кроме того, т.к. вставка в таблицу TransactionLog происходит вне транзакции, теоретически она может быть не выполнена в результате сбоя, тогда произошедшие события будут потеряны.
Re[2]: Журнал изменений базы данных
От: afkos  
Дата: 11.02.20 12:42
Оценка:
Здравствуйте, vsb, Вы писали:

vsb>1. Сделать ограничение на длительность транзакции, при синхронизации делать запрос в пределах этой длительности. В данном случае удобней какой-нибудь change_timestamp, а не id (ну или мерять время в идентификаторах, тоже вариант). Например если известно, что дольше часа ничего не висит, значит синхронизироваться до часа.


vsb>2. Если нужно синхронизировать всё, то вытаскиваем с предыдущего периода тот же час дополнительно. При этом в нашей базе сохраняем информацию про загруженные ID, чтобы не грузить дубликаты и загружаем то, что в прошлый раз недогрузили.


Спасибо! Думал над таким вариантом, не нравится его "не системность", надеюсь что найдется вариант лучше, но этот кажется рабочим.

vsb>3. В этой же базе ведётся табличка, куда insert триггером вставляет новую запись, а при импорте вы транзакцией эту запись удаляете. Тогда ничего не потеряется. Самый надёжный вариант, но с накладными расходами.

Триггер на таблицу EventLog? Не могли бы раскрыть подробнее?
Re: Журнал изменений базы данных
От: kov_serg Россия  
Дата: 11.02.20 12:50
Оценка:
Здравствуйте, afkos, Вы писали:

A>НО при этом возможна следующая ситуация:


A>в EventLog Max(eventId) = 10

A>Транзакция А — добавляет запись eventId = 11
A>Транзакция Б — добавляет запись eventId = 12
A>Транзакция Б — фиксирует транзакцию
A>Читатель:
A>- выполняет запрос select from EventLog where eventId > 10,
A>- получает запись eventId = 12,
A>- запоминает @lastEventId = 12
A>Транзакция А — фиксирует транзакцию (eventId = 11)

A>Проблема: Читатель больше никогда не узнает о записи с eventId = 11. т.к его @lastEventId = 12

Что мешает делать синхронизацию по блочно
select count(1) from table where id>=id1 and id<=id2
и смотреть не изменилось ли кол-во записей с последнего захода.
Если да то дозабирать, используя дихотомию и подходящий размер окна.

A>Буду очень благодарен любым советам!

обратите внимание на тригеры.
Re[3]: Журнал изменений базы данных
От: vsb Казахстан  
Дата: 11.02.20 12:51
Оценка:
Делаем таблицу EventLogExportStatus (externalSystemId, eventLogEventId). При вставке записи в EventLog кто-то (триггер или тот же, кто вставляет запись, не суть) вставляет записи в EventLogExportStatus по одной для каждой системы, которой требуется экспорт. Далее эти системы делают select EventLog.* from EventLogExportStatus join EventLog on EventLogExportStatus.eventLogEventId = EventLog.eventId where EventLogExportStatus.externalSystemId = 123 (123 это id текущей системы) и для каждой загруженной записи delete from EventLogExportStatus where externalSystemId = 123 and eventLogEventId = 789 (789 это id только что загруженной записи).

Вместо delete можно флажок переключать, тогда записи будут копиться, но можно какую-то дополнительную полезную информацию записывать вроде даты-времени экспорта этой записи. С нужными индексами работать будет быстро.
Отредактировано 11.02.2020 12:53 vsb . Предыдущая версия . Еще …
Отредактировано 11.02.2020 12:52 vsb . Предыдущая версия .
Re: Журнал изменений базы данных
От: wildwind Россия  
Дата: 11.02.20 12:53
Оценка:
Здравствуйте, afkos, Вы писали:

A>Проблема: Читатель больше никогда не узнает о записи с eventId = 11. т.к его @lastEventId = 12


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

A>Читать из EventLog с ожиданием на блокировке в этом сценарии — крайне нежелательно.

Как и для любой таблицы, если читателей/писателей (в таблицу-лог) несколько, какие-то блокировки неизбежны.
Re[4]: Журнал изменений базы данных
От: wildwind Россия  
Дата: 11.02.20 12:58
Оценка:
Здравствуйте, vsb, Вы писали:

vsb>Делаем таблицу EventLogExportStatus (externalSystemId, eventLogEventId). При вставке записи в EventLog кто-то (триггер или тот же, кто вставляет запись, не суть) вставляет записи в EventLogExportStatus по одной для каждой системы, которой требуется экспорт. Далее эти системы делают select EventLog.* from EventLogExportStatus join EventLog on EventLogExportStatus.eventLogEventId = EventLog.eventId where EventLogExportStatus.externalSystemId = 123 (123 это id текущей системы) и для каждой загруженной записи delete from EventLogExportStatus where externalSystemId = 123 and eventLogEventId = 789 (789 это id только что загруженной записи).


Зачем так сложно, одной таблицы достаточно.
Re[5]: Журнал изменений базы данных
От: vsb Казахстан  
Дата: 11.02.20 14:05
Оценка:
Здравствуйте, wildwind, Вы писали:

vsb>>Делаем таблицу EventLogExportStatus (externalSystemId, eventLogEventId). При вставке записи в EventLog кто-то (триггер или тот же, кто вставляет запись, не суть) вставляет записи в EventLogExportStatus по одной для каждой системы, которой требуется экспорт. Далее эти системы делают select EventLog.* from EventLogExportStatus join EventLog on EventLogExportStatus.eventLogEventId = EventLog.eventId where EventLogExportStatus.externalSystemId = 123 (123 это id текущей системы) и для каждой загруженной записи delete from EventLogExportStatus where externalSystemId = 123 and eventLogEventId = 789 (789 это id только что загруженной записи).


W>Зачем так сложно, одной таблицы достаточно.


Если систем на экспорт несколько, то недостаточно (ну или колонку для каждой создавать, не очень расширяемый вариант на мой взгляд). Но можно и в исходную таблицу всё засунуть, конечно.
Отредактировано 11.02.2020 14:05 vsb . Предыдущая версия .
Re[6]: Журнал изменений базы данных
От: wildwind Россия  
Дата: 11.02.20 14:24
Оценка:
Здравствуйте, vsb, Вы писали:

vsb>Если систем на экспорт несколько, то недостаточно (ну или колонку для каждой создавать,


Не колонку, строку.
Re[3]: Журнал изменений базы данных
От: Gurney Великобритания www.kharlamov.biz
Дата: 11.02.20 15:23
Оценка:
Здравствуйте, afkos, Вы писали:

A>В таком варианте возможен Race Condition на вставку в таблицу TransactionLog:

A>если происходит вставка одновременно, transactionId=10 может вставиться в результате раньше чем transactionId=9,
A>и читатель соответственно получит transactionId=10, а запись с 9 уже не прочитает.
Чтобы данные не терялись, вам нужно использовать уровень изоляции транзакций ISOLATION_SERIALIZEABLE
Возможно в вашей базе он подругому называется. Но производительность будет печальная.

Pick your poison...
Re: Журнал изменений базы данных
От: AleksandrN Россия  
Дата: 11.02.20 15:31
Оценка:
Здравствуйте, afkos, Вы писали:

A>Добрый день!


A>Разрабатываем такую специфическую систему средних размеров для управления данными, для промышленности, в основном.

A>Столкнулись с необходимостью иметь в БД какой-то журнал со сведениями об изменениях различных сущностей,
A>чтобы внешние системы с которыми настроена интеграция (штатная или силами клиента) могли понимать какие сущности удалены, изменены, добавлены в системе, для того чтобы их подтянуть себе.

A>Проблема: Читатель больше никогда не узнает о записи с eventId = 11. т.к его @lastEventId = 12


Сохраняй время последнего изменения записи, а внешние системы будут запрашивать записи, которые обновились после предыдущего запроса.
Отредактировано 11.02.2020 15:57 AleksandrN . Предыдущая версия .
Re[4]: Журнал изменений базы данных
От: afkos  
Дата: 12.02.20 04:42
Оценка:
Здравствуйте, vsb, Вы писали:

vsb>Делаем таблицу EventLogExportStatus (externalSystemId, eventLogEventId). При вставке записи в EventLog кто-то (триггер или тот же, кто вставляет запись, не суть) вставляет записи в EventLogExportStatus по одной для каждой системы, которой требуется экспорт. Далее эти системы делают select EventLog.* from EventLogExportStatus join EventLog on EventLogExportStatus.eventLogEventId = EventLog.eventId where EventLogExportStatus.externalSystemId = 123 (123 это id текущей системы) и для каждой загруженной записи delete from EventLogExportStatus where externalSystemId = 123 and eventLogEventId = 789 (789 это id только что загруженной записи).


vsb>Вместо delete можно флажок переключать, тогда записи будут копиться, но можно какую-то дополнительную полезную информацию записывать вроде даты-времени экспорта этой записи. С нужными индексами работать будет быстро.


Понял.

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

Вариант рабочий, как делать понятно — нужно подумать о применимости, спасибо.

Хотелось бы еще проработать вариант с исходной схемой, когда читатели не известны системе, и журнал сохраняется.
Re[2]: Журнал изменений базы данных
От: afkos  
Дата: 12.02.20 04:46
Оценка:
Здравствуйте, AleksandrN, Вы писали:

AN>Здравствуйте, afkos, Вы писали:


A>>Добрый день!


A>>Разрабатываем такую специфическую систему средних размеров для управления данными, для промышленности, в основном.

A>>Столкнулись с необходимостью иметь в БД какой-то журнал со сведениями об изменениях различных сущностей,
A>>чтобы внешние системы с которыми настроена интеграция (штатная или силами клиента) могли понимать какие сущности удалены, изменены, добавлены в системе, для того чтобы их подтянуть себе.

A>>Проблема: Читатель больше никогда не узнает о записи с eventId = 11. т.к его @lastEventId = 12


AN>Сохраняй время последнего изменения записи, а внешние системы будут запрашивать записи, которые обновились после предыдущего запроса.


В этом случае имеем те же проблемы, время изменения фиксируется внутри транзакции, а доступно другим транзакциям оно становится через некоторое время, когда транзакция фиксирует изменения.
Re: Журнал изменений базы данных
От: Sinclair Россия https://github.com/evilguest/
Дата: 12.02.20 04:54
Оценка:
Здравствуйте, afkos, Вы писали:

A>Добрый день!

Ну, первый вопрос — такой: а насколько часто вносятся изменения?
Просто очевидное решение — это потребовать table-level update lock на EventLog. То есть транзакция Б не сможет начать вставлять записи в EventLog до того, как завершится транзакция А, и линейный порядок сохранится.
Это выгодно, если у вас очень много читателей, и мало писателей.
Уйдемте отсюда, Румата! У вас слишком богатые погреба.
Re: Журнал изменений базы данных
От: Sinclair Россия https://github.com/evilguest/
Дата: 12.02.20 05:00
Оценка:
Здравствуйте, afkos, Вы писали:
A>Первое что приходит в голову — табличка в бд:
Проверьте, как ведёт себя родная реализация: https://www.sqlshack.com/creating-a-sql-server-audit-using-sql-server-change-tracking/
Судя по документации, сервер как раз гарантирует корректное упорядочение change versions в случае конкурирующих транзакций:
https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/work-with-change-tracking-sql-server?view=sql-server-ver15
Уйдемте отсюда, Румата! У вас слишком богатые погреба.
Re: Журнал изменений базы данных
От: akasoft Россия  
Дата: 12.02.20 07:05
Оценка:
Здравствуйте, afkos, Вы писали:

A>в EventLog Max(eventId) = 10

A>Транзакция А — добавляет запись eventId = 11
A>Транзакция Б — добавляет запись eventId = 12
A>Транзакция Б — фиксирует транзакцию

Что позволяет зафиксировать eventId = 12? Как транзакция Б выбирает 12, ведь и для А, и для Б Max(eventId) = 10? Они обе должны совать 11, кто первый просунул, того и тапки.
А кто второй, инкрементирует eventId и пробует просунуть.
... << RSDN@Home 1.0.0 alpha 5 rev. 0>>
Re[2]: Журнал изменений базы данных
От: afkos  
Дата: 12.02.20 12:48
Оценка:
Здравствуйте, Sinclair, Вы писали:

S>Ну, первый вопрос — такой: а насколько часто вносятся изменения?

S>Просто очевидное решение — это потребовать table-level update lock на EventLog. То есть транзакция Б не сможет начать вставлять записи в EventLog до того, как завершится транзакция А, и линейный порядок сохранится.
S>Это выгодно, если у вас очень много читателей, и мало писателей.

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

С table-level update lock мы фактически ограним число одновременно выполняющихся (изменяющих) транзакций до 1, увы, это не приемлимо в нашем случае.

S>Проверьте, как ведёт себя родная реализация: https://www.sqlshack.com/creating-a-sql-server-audit-using-sql-server-change-tracking/

S>Судя по документации, сервер как раз гарантирует корректное упорядочение change versions в случае конкурирующих транзакций:
S>https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/work-with-change-tracking-sql-server?view=sql-server-ver15

Спасибо, ознакомился
Мы не можем использовать специфичные механизмы СУБД, т.к. поддерживаем сразу несколько СУБД: SQLServer, PostgreSql, Oracle, но полезно разобраться как там решаются те же проблемы.
Вот что я выяснил:

запрос новых изменений висит на блокировке, если какая-то транзакция изменила отслеживаемую таблицу, но не зафиксировала изменения
select * from CHANGETABLE(CHANGES eventLog, @lastVersion) c

Таким образом исходная проблема конечно решается, видимо, это самое надежное поведение

Любопытно, что изменения сделанные в транзакции, не видны через CHANGETABLE даже внутри этой самой транзакции, что наталкивает на мысль, что номер версии таблицы увеличивается не в момент изменения, а в момент фиксации транзакции.
Такое поведение обеспечивается движком SQL Server.

Пока что пришел к нескольким решениям:

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

2. Читаем из одной непрерывной таблицы,
при этом можно разделить внешние системы на два класса:
а) синхронизируются с высокой частотой (раз в секунду, например) — не могут блокироваться на чтении новых событий
такие системы будут помнить о возможных пропущенных записях, и отслеживать их каким-то образом в будущем до определенного таймаута.
это скорее всего наша внутренняя система, мы сможем написать надежную реализацию для неё.

б) синхронизируются редко, раз в день или реже
запрашивают новые события с блокировкой на чтении, если есть не зафиксированные транзакции (select for update для MVCC, или просто select для SQLServer)
соответственно, можно не беспокоиться о пропущенных событиях

Спасибо всем участникам!
Буду рад новым идеям
Re[2]: Журнал изменений базы данных
От: afkos  
Дата: 12.02.20 12:59
Оценка:
Здравствуйте, akasoft, Вы писали:

A>Здравствуйте, afkos, Вы писали:


A>>в EventLog Max(eventId) = 10

A>>Транзакция А — добавляет запись eventId = 11
A>>Транзакция Б — добавляет запись eventId = 12
A>>Транзакция Б — фиксирует транзакцию

A>Что позволяет зафиксировать eventId = 12? Как транзакция Б выбирает 12, ведь и для А, и для Б Max(eventId) = 10? Они обе должны совать 11, кто первый просунул, того и тапки.

A>А кто второй, инкрементирует eventId и пробует просунуть.

Инкремент eventId обеспечивается СУБД, identity колонка.
СУБД умеет его инкрементировать в разных транзакциях без блокировок.
Каждой транзакции достается свой eventId, и при записи в таблицу они не пересекаются по ключу, в идеале могут не мешая друг другу вставить запись в таблицу.
Подождите ...
Wait...
Пока на собственное сообщение не было ответов, его можно удалить.