Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Согласованность данных  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
Прошу совета по такому вопросу.
Допустим, в OLTP системе есть 3 таблицы: Product, Category, Manufacturer.

CREATE TABLE dbo.Manufacturer(
	ManufacturerId int NOT NULL,
	ManufacturerName nvarchar(100) NOT NULL,
 CONSTRAINT PK_Manufacturer PRIMARY KEY CLUSTERED (ManufacturerId ASC))

CREATE TABLE dbo.Category(
	CategoryId int NOT NULL,
	CategoryName nvarchar(255) NOT NULL
 CONSTRAINT PK_Category PRIMARY KEY CLUSTERED (CategoryId ASC))
 
CREATE TABLE dbo.Product(
	ProductId int,
	ManufacturerId int NOT NULL,
	CategoryId int NOT NULL,
	CONSTRAINT PK_Product PRIMARY KEY NONCLUSTERED (ProductId ASC))


И внешние ключи.
ALTER TABLE dbo.Product  WITH CHECK ADD  CONSTRAINT FK_Product_Category FOREIGN KEY(CategoryId)
	REFERENCES dbo.Category (CategoryId)

ALTER TABLE dbo.Product  WITH CHECK ADD  CONSTRAINT FK_Product_Manufacturer FOREIGN KEY(ManufacturerId)
	REFERENCES dbo.Manufacturer (ManufacturerId)

Периодически с этих таблиц происходит обновление хранилища данных.
Делается всё это в одной транзакции с уровнем SNAPSHOT, так что данные согласованы.
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION TR1

UPDATE ...
FROM dbo.Category
-- 10 rows

UPDATE ...
FROM dbo.Manufacturer
-- 10 rows

UPDATE ...
FROM dbo.Product
-- 1000000 rows

COMMIT

В комментариях я указал примерные числа обновляемых строк.
Как видим, в последнем UPDATE число обновляемых строк в тысячи раз больше, чем в первых двух.
Иногда случаются откаты транзакции после часа работы, поэтому решил обновлять таблицу dbo.Product
порциями в отдельных транзакциях.
Есть отдельная таблица для регистрации обновлённых записей, где фиксируется событие обновления, которую я не показываю.
UPDATE ...
FROM dbo.Category
-- 10 rows

UPDATE ...
FROM dbo.Manufacturer
-- 10 rows

WHILE EXISTS(SELECT * FROM dbo.ProductChanged ...)
BEGIN
	BEGIN TRANSACTION TR1
	UPDATE ...
	FROM dbo.Product
	-- 1000 rows
	
	UPDATE dbo.ProductChanged ...
	...
	COMMIT
END

Возник вопрос целостности данных, ведь исходные таблицы продолжают обновляться и может наступить момент,
когда у какого-то ProductId будет CategoryId, которого нет на момент старта обновления.

1 вариант - использовать в каждой таблице поле типа rowversion или datetime для согласования данных.

Кто может предложить другую идею?
3 сен 14, 16:41    [16530527]     Ответить | Цитировать Сообщить модератору
 Re: Согласованность данных  [new]
Crimean
Member

Откуда:
Сообщений: 13148
зачем вам снапшот для обновления? или вы обновляете на основании каких-то вычиток?
зачем вам такие объемные обновления?
в чем кроется первопричина "несогласованности" данных?
3 сен 14, 16:50    [16530580]     Ответить | Цитировать Сообщить модератору
 Re: Согласованность данных  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
Crimean
зачем вам снапшот для обновления?

Чтобы получить данные из трёх таблиц на определенный момент времени.
Crimean
зачем вам такие объемные обновления?

Это ежесуточные обновления, выполняемые джобом из 36 шагов, поэтому совет запускать чаще обновления не очень подходит. Там ещё много другого.
Crimean
в чем кроется первопричина "несогласованности" данных?

Допустим, на момент старта процедуры есть запись в dbo.Product
ProductId ManufacturerId CategoryId
3127520576563452

прошли обновления таблиц в хранилище
UPDATE ...
FROM dbo.Category
-- 10 rows

UPDATE ...
FROM dbo.Manufacturer
-- 10 rows

Затем начала обновляться таблица dbo.Product
порциями по 10000 записей в каждой транзакции.
Когда дело дошло до вышеупомянутой строки, то там уже стоит
ProductId ManufacturerId CategoryId
31275205765777777

а значения 777777 при обновлении хранилища из dbo.Category в целевой таблице (в хранилище) нет.
3 сен 14, 17:02    [16530676]     Ответить | Цитировать Сообщить модератору
 Re: Согласованность данных  [new]
invm
Member

Откуда: Москва
Сообщений: 9633
Jovanny,

Database snapshot не рассматриваете?
3 сен 14, 17:36    [16530941]     Ответить | Цитировать Сообщить модератору
 Re: Согласованность данных  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
invm,

Нет, база больше 1 терабайта, и сервер не слишком мощный.
3 сен 14, 17:46    [16531032]     Ответить | Цитировать Сообщить модератору
 Re: Согласованность данных  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8316
автор
Чтобы получить данные из трёх таблиц на определенный момент времени.

... необходимо оставлять эти данные в таблицах с пометкой даты или разделить таблицу на две части - архив и свежие данные. При обновлении/удалении старые записи откладывать в архив (опять таки с пометкой даты операции).
3 сен 14, 18:21    [16531282]     Ответить | Цитировать Сообщить модератору
 Re: Согласованность данных  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Jovanny
invm,

Нет, база больше 1 терабайта, и сервер не слишком мощный.


это не проблема. получите "свое" состояние данных без транзакции, накладных там мало
но я не очень уверен что вы корректно поставили задачу
пока все выглядит достаточно странно
3 сен 14, 18:52    [16531441]     Ответить | Цитировать Сообщить модератору
 Re: Согласованность данных  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
Владислав Колосов
... необходимо оставлять эти данные в таблицах с пометкой даты

Это я уже озвучил.
Jovanny
1 вариант - использовать в каждой таблице поле типа rowversion или datetime для согласования данных.

Владислав Колосов
или разделить таблицу на две части - архив и свежие данные.

Вообще-то это таблицы-справочники. Так что делить на свежие и устаревшие данные я как-то не очень представляю.
4 сен 14, 09:47    [16533161]     Ответить | Цитировать Сообщить модератору
 Re: Согласованность данных  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
Crimean
но я не очень уверен что вы корректно поставили задачу
пока все выглядит достаточно странно

Так а что странного? Есть набор таблиц, обновляемые постоянно, связанные внешними ключами. Нужно их отобразить (реплицировать, синхронизировать) с похожими (не аналогичными) таблицами в другой базе, тоже связанными внешними ключами.
Мы синхронизировали dbo.Category и dbo.Manufacturer, начинаем синхронизировать dbo.Product небольшими транзакциями, за это время появились новые записи во всех трёх таблицах, и когда начинаем синхронизировать эти новые строки из dbo.Product, то в целевой базе не будет новых ManufacturerId и CategoryId. Поймаем исключение "The statement conflicted with the FOREIGN KEY".
4 сен 14, 10:05    [16533215]     Ответить | Цитировать Сообщить модератору
 Re: Согласованность данных  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
Итак, пока видятся такие варианты.
1. Использовать в каждой таблице поле типа rowversion или datetime.
2. Делать SNAPSHOT базы.
3. SNAPSHOT - репликация.
4. Синхронизация через триггеры (возрастает вероятность блокировок).

2-й и 3-й из-за больших размеров базы и собственно таблиц не очень привлекательны.
Пока что 1-й вариант самый приемлемый.

Хотя в принципе можно рассмотреть
Владислав Колосов
разделить таблицу на две части - архив и свежие данные

Отслеживать изменения, скажем через CDC, и брать старые версии данных оттуда.
Такой себе ISOLATION LEVEL SNAPSHOT на уровне таблицы.
4 сен 14, 10:17    [16533300]     Ответить | Цитировать Сообщить модератору
 Re: Согласованность данных  [new]
invm
Member

Откуда: Москва
Сообщений: 9633
Jovanny,

1. Как уже писал - database snapshot. Если редакция сервера позволяет. Ваши аргументы против, говорят, что вы просто не в курсе что это.
2. Справочники синхронизировать также фрагментами на основе фрагментов из dbo.Product.
4 сен 14, 10:24    [16533340]     Ответить | Цитировать Сообщить модератору
 Re: Согласованность данных  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
Jovanny

2. Делать SNAPSHOT базы.

2-й ... из-за больших размеров базы и собственно таблиц не очень привлекательны.



Снапшот базы не делает копию базы, а использует ntfs sparse file под капотом. Только часть страниц данных при "искуственном" crash recovery при создании снапшота будет записаны в sparse файлы (страницы в которых были изменения сделанные активными на момент создания снапшота транзакциями). В дальнейшем в sparse файлы будут записываться только измененные с начала создания снапшота страницы. Не думаю что у вас быстро вырастет снапшот до неприемлемых размеров. Или у вас нагрузка такова, что...?
4 сен 14, 10:28    [16533365]     Ответить | Цитировать Сообщить модератору
 Re: Согласованность данных  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
churupaha
записываться только измененные с начала создания снапшота страницы


понимать как - записываться будет ИСХОДНОЕ содержимое до изменения.
4 сен 14, 10:34    [16533394]     Ответить | Цитировать Сообщить модератору
 Re: Согласованность данных  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8316
Jovanny,

автор
Вообще-то это таблицы-справочники. Так что делить на свежие и устаревшие данные я как-то не очень представляю.


Справочники дополняются, но не изменяются по миллиону в сутки записей. Это уже не справочники, в таком случае.
4 сен 14, 10:51    [16533517]     Ответить | Цитировать Сообщить модератору
 Re: Согласованность данных  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
Владислав Колосов
Справочники дополняются, но не изменяются по миллиону в сутки записей. Это уже не справочники, в таком случае.

По смыслу таки справочники. Есть ещё и таблицы фактов.
4 сен 14, 11:19    [16533703]     Ответить | Цитировать Сообщить модератору
 Re: Согласованность данных  [new]
Crimean
Member

Откуда:
Сообщений: 13148
а что, нельзя сделать обновления "большой" таблицы порциями и после получения порции "для мержа" - по полученной порции до-актуализировать справочники? оверхед небольшой будет. а все накрученное вокруг ради "согласованности" внезапно окажется не нужным
4 сен 14, 11:45    [16533886]     Ответить | Цитировать Сообщить модератору
 Re: Согласованность данных  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
Crimean
а что, нельзя сделать обновления "большой" таблицы порциями и после получения порции "для мержа" - по полученной порции до-актуализировать справочники? оверхед небольшой будет. а все накрученное вокруг ради "согласованности" внезапно окажется не нужным

Та вот на этом и остановился, хотя там тоже не всё просто, обновления делаются по нетривиальному алгоритму с гармонизацией и полным сканом таблиц.
4 сен 14, 12:08    [16534061]     Ответить | Цитировать Сообщить модератору
 Re: Согласованность данных  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
invm
Jovanny,

1. Как уже писал - database snapshot. Если редакция сервера позволяет. Ваши аргументы против, говорят, что вы просто не в курсе что это.

churupaha
Или у вас нагрузка такова, что...?

Я в курсе, что это. Запустил для пробы снепшот базы - делался 25 минут.
Не знаю, у кого как, то для меня многовато.
4 сен 14, 12:13    [16534094]     Ответить | Цитировать Сообщить модератору
 Re: Согласованность данных  [new]
случайно заглянул
Guest
Хранилище лучше все-таки заполнять из Stage-области, а не непосредственно из OLTP. В Stage-область пишете сырые данные, а потом спокойно обрабатываете данные и записываете их в хранилище в своем ETL-процессе.
Stage-областью может быть и database snapshot, почему нет? Но можно наверное и оптимизировать заполнение Stage-области, исходя из ваших бизнес-задач.
А долго или нет... ну это вопррос философский. Но бесплатного ничего не бывает.
4 сен 14, 12:27    [16534180]     Ответить | Цитировать Сообщить модератору
 Re: Согласованность данных  [new]
invm
Member

Откуда: Москва
Сообщений: 9633
Jovanny
Запустил для пробы снепшот базы - делался 25 минут.
Значит была существенная транзакционная активность на момент создания снепшота.
4 сен 14, 12:39    [16534301]     Ответить | Цитировать Сообщить модератору
 Re: Согласованность данных  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
Jovanny
invm
Jovanny,

1. Как уже писал - database snapshot. Если редакция сервера позволяет. Ваши аргументы против, говорят, что вы просто не в курсе что это.

churupaha
Или у вас нагрузка такова, что...?

Я в курсе, что это. Запустил для пробы снепшот базы - делался 25 минут.
Не знаю, у кого как, то для меня многовато.


Откатывалась "длинная" незакоммиченная транзакци(я/и)? Так ведь можно подобрать время для создания снапшота базы.
4 сен 14, 12:42    [16534324]     Ответить | Цитировать Сообщить модератору
 Re: Согласованность данных  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
churupaha
Откатывалась "длинная" незакоммиченная транзакци(я/и)?

Нет, это обычная нагрузка. Первые 10 дней месяца такая нагрузка круглосуточно.
4 сен 14, 13:02    [16534470]     Ответить | Цитировать Сообщить модератору
 Re: Согласованность данных  [new]
alexdr
Member

Откуда:
Сообщений: 251
В теме многобукф... сорри, что влез, не особо вникая, но если я правильно понял задачу:
Jovanny
в OLTP системе есть 3 таблицы: Product, Category, Manufacturer.
...
Периодически с этих таблиц происходит обновление хранилища данных.

т. е. необходимость раз в сутки заполнить хранинище данными из OLTP, то весь изложенный в треде подход представляется субоптимальным.

Уточните, правильно ли я понял задачу?
4 сен 14, 14:44    [16535259]     Ответить | Цитировать Сообщить модератору
 Re: Согласованность данных  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4807
Исходите из того, что данные могут оказаться не целостными и будут исправлены при следующей загрузке. Называется отложенная загрузка связных сущностный. В хранилище такое допустимо.
4 сен 14, 14:54    [16535342]     Ответить | Цитировать Сообщить модератору
 Re: Согласованность данных  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
alexdr
Уточните, правильно ли я понял задачу?
Правильно.
4 сен 14, 15:06    [16535432]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить