Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Change Tracking: Объясните, как такое получается.  [new]
ShIgor
Member

Откуда: Нижний Новгород
Сообщений: 2204
Имеем, sql2014 express, база 1С, на одной таблице настроен CT без отслеживания изменений по полям.
Все работает, все хорошо в 99.99% случаев.
Но иногда возникает такая ситуация: при запросе из
select ct.sys_change_version, ct.sys_change_operation, ct._IDRRef, sct.commit_ts, sct.xdes_id, sct.commit_time
from 
  changetable(changes _DocumentXXX, 456236) ct
  inner join sys.dm_tran_commit_table sct on ct.sys_change_version = sct.commit_ts

получаю 2 записи:
sys_change_versionsys_change_operation_IDRRefcommit_tsxdes_idcommit_time
456237U0x828494C6911BAE8E11E889E85F56DDB845623735667992018-07-17 17:46:05.683
456237U0x828494C6911BAE8E11E889E85F56DDB845623735668002018-07-17 17:46:05.683


отличаются они только полем xdes_id
достучаться до конкретных изменений можно только через DAC, поэтому для решения уж никак не подходит.
Но смотрим, что произошло:
select * from sys.change_tracking_539148966 where sys_change_xdes_id between 3566799 and 3566800

sys_change_xdes_idsys_change_xdes_id_seqsys_change_operationk__IDRRef_00000001
356679928375U0x828494C6911BAE8E11E889E91273AB1B
356680028376U0x828494C6911BAE8E11E889E85F56DDB8


и видим что xdes_id = 3566799 не может иметь никакого отношения к commit_ts = 456237, т.к. PK разные!!!
да и
select ct.sys_change_version, ct._IDRRef from changetable(version _Document126, (_IDRRef), (0x828494C6911BAE8E11E889E91273AB1B)) ct

sys_change_version_IDRRef
4562910x828494C6911BAE8E11E889E91273AB1B

совсем другая...

Как так???

от MS кроме https://support.microsoft.com/en-us/help/3083381/duplicate-key-rows-from-the-sys-syscommittab-table-in-sql-server
ничего не нашел, но оно вроде к моей проблеме отношения не имеет.
18 июл 18, 18:51    [21582285]     Ответить | Цитировать Сообщить модератору
 Re: Change Tracking: Объясните, как такое получается.  [new]
ShIgor
Member

Откуда: Нижний Новгород
Сообщений: 2204
ShIgor,

Ну разобрался вроде, поэтому сам себе и отвечу.

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

Дальше нам нужно понять что-же такое commit_ts (оно же sys_change_version) и xdes_id.
это поля из sys.dm_tran_commit_table
грубо:
xdes_id - внутр идентификатор для транзакции
commit_ts - последовательный номер коммита для транзакции (CSN Commit Sequence Number)

есть еще commit_csn - это тоже самое что и commit_ts, только нумерация instance-wide (на моих данных значения разные, а поведение одинаковое и дальше рассматривать его не буду)

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

Другая часть запроса это функция CHANGETABLE с параметром changes - возвращает список записей (PK) для которых были изменения и (важно) последняя операция и последний номер коммита для каждой измененной записи.

Ну а дальше понятно - связав обе сущности можно получить умножение записей. CHANGETABLE не обращает внимание на то, что в одном коммите могут оказаться несколько транзакций и для каждого измененного PK он вернет один и тот же commit_ts. Самый худший вариант в этом случае декартово произведение.

Переводя на мою ситуацию, если бы commit_ts был бы последний на момент запроса, я получил бы ответ:
sys_change_versionsys_change_operation_IDRRefcommit_tsxdes_idcommit_time
456237U0x828494C6911BAE8E11E889E85F56DDB845623735667992018-07-17 17:46:05.683
456237U0x828494C6911BAE8E11E889E85F56DDB845623735668002018-07-17 17:46:05.683
456237U0x828494C6911BAE8E11E889E91273AB1B45623735667992018-07-17 17:46:05.683
456237U0x828494C6911BAE8E11E889E91273AB1B45623735668002018-07-17 17:46:05.683


Самое печальное, что без DAC корректную связь между ними получить невозможно.

кстати, вот один из примеров с этой засадой:
http://yabele.blogspot.com/2015/07/mssql-change-tracking-how-can-i-get.html
19 июл 18, 14:39    [21585239]     Ответить | Цитировать Сообщить модератору
 Re: Change Tracking: Объясните, как такое получается.  [new]
Владислав Колосов
Member

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

судя по справке, это - временнАя метка каждой транзакции (номер версии).

автор
все хорошо в 99.99% случаев

Хорошо для чего?
20 июл 18, 01:52    [21586901]     Ответить | Цитировать Сообщить модератору
 Re: Change Tracking: Объясните, как такое получается.  [new]
ShIgor
Member

Откуда: Нижний Новгород
Сообщений: 2204
Владислав Колосов,

хорошо - в смысле все работает, и отслеживается как надо.
но этот метод получения commit_time приводит к дублированию записей и, соответственно, к дополнительной последующей обработке, нагрузке и т.п.
20 июл 18, 09:30    [21587269]     Ответить | Цитировать Сообщить модератору
 Re: Change Tracking: Объясните, как такое получается.  [new]
Владислав Колосов
Member

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

хм, я полагал, что информации о версии, которую возвращает changetable() и CHANGE_TRACKING_CURRENT_VERSION() достаточно для инкрементного запроса при синхронизации данных.
23 июл 18, 17:07    [21595002]     Ответить | Цитировать Сообщить модератору
 Re: Change Tracking: Объясните, как такое получается.  [new]
ShIgor
Member

Откуда: Нижний Новгород
Сообщений: 2204
Владислав Колосов,

ну, СТ используется не только для синхронизации.
для аудита, например, этих данных недостаточно.
23 июл 18, 18:57    [21595372]     Ответить | Цитировать Сообщить модератору
 Re: Change Tracking: Объясните, как такое получается.  [new]
Владислав Колосов
Member

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

я намекаю на то, что вы используете инструмент не по его назначению, а потом жалуетесь ;)
24 июл 18, 13:34    [21597900]     Ответить | Цитировать Сообщить модератору
 Re: Change Tracking: Объясните, как такое получается.  [new]
ShIgor
Member

Откуда: Нижний Новгород
Сообщений: 2204
Владислав Колосов,

странные умозаключения.
24 июл 18, 15:25    [21598491]     Ответить | Цитировать Сообщить модератору
 Re: Change Tracking: Объясните, как такое получается.  [new]
ДонАтелло
Member

Откуда:
Сообщений: 29
ShIgor
грубо:
xdes_id - внутр идентификатор для транзакции
commit_ts - последовательный номер коммита для транзакции (CSN Commit Sequence Number)

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


Не очень понятно как Вы пришли к такому выводу. Точнее кажется, что Вы пришли к такому выводу исключительно из определений этих двух полей. На самом деле, Вы, просто, констатируете факт того, что в sys.dm_tran_commit_table, каким то чудом оказалось две записи с одним commit_ts. Ибо в каком-либо другом контексте фраза "...в один коммит могут влезть несколько разных транзакций" звучит пугающе.

ShIgor
Ну а дальше понятно - связав обе сущности можно получить умножение записей. CHANGETABLE не обращает внимание на то, что в одном коммите могут оказаться несколько транзакций и для каждого измененного PK он вернет один и тот же commit_ts.


Немного некорректно. У Вас две таблицы в запросе и Вы соединяете их. Первая - changetable(...) работает честно и возвращает одну запись. Она не обязана ничего знать о второй таблице sys.dm_tran_commit_table в которой данные уже задвоены к моменту выполнения.

ShIgor
Переводя на мою ситуацию, если бы commit_ts был бы последний на момент запроса, я получил бы ответ:


Тоже непонятно. При чём тут последний коммит или нет?

Что касается конкретно Вашей ситуации, то было опрометчиво писать такой запрос, не зная ключей в таблице sys.syscommittab (это подложенная под представление sys.dm_tran_commit_table таблица). Если не знаете ключей - то нужно перестраховываться чем-то типа

В целом - тема интересная
5 июл 19, 17:54    [21921774]     Ответить | Цитировать Сообщить модератору
 Re: Change Tracking: Объясните, как такое получается.  [new]
ДонАтелло
Member

Откуда:
Сообщений: 29
Прошу прощения. Нечаянно отправил недописанное сообщение. Продолжу:

Что касается конкретно Вашей ситуации, то было опрометчиво писать такой запрос, не зная ключей в таблице sys.syscommittab (это подложенная под представление sys.dm_tran_commit_table таблица). Если не знаете ключей - то нужно перестраховываться чем-то типа
cross apply (select top 1 * from sys.dm_tran_commit_table sct where sct.commit_ts = ct.sys_change_version)


В целом - случай интересный. Если у кого-то есть возможность воспроизвести то же самое поведение, то интересно было бы глянуть на результат запроса
select top 1 from sys.dm_tran_commit_table sct where sct.xdes_id in (3566799, 3566800)


Т.е. исследовать откуда же появились две транзакции с одним commit_ts.

Я работаю над универсально обёрткой вокруг CT или CDC. Связано это с тем, что CDC доступен не на всех редакциях SQL. С CDC всё достаточно просто, там LSN и SeqNO из журнала транзакций. Оба numeric(25). В CT же Майкрософт ввёл новые характеристики, обозначающие версию данных sys_change_version тот же самый... bigint. А Sequance вообще спрятали, через функции её не достать, только в таблицах из под DAC.
Т.е. мне интересно внутреннее устройство CT и хотелось бы доковырять этот пример для лучшего понимания внутренностей и засад.
Спасибо.
5 июл 19, 18:06    [21921779]     Ответить | Цитировать Сообщить модератору
 Re: Change Tracking: Объясните, как такое получается.  [new]
ShIgor
Member

Откуда: Нижний Новгород
Сообщений: 2204
ДонАтелло,

я скорее всего не совсем последовательно изложил свою мысль.
"в один коммит укладываются несколько транзакций" - это надо понимать как: коммит - это commit_ts (или sys_change_version) и никакого отношения этот коммит не имеет к настоящим коммитам транзакций. просто система СТ видимо имеет определенную дискретность и под одной sys_change_version (т.к. она сквозная для БД) в таблице sys.dm_tran_commit_table могут быть много записей и это логично, т.к. если сделать любую массовую операцию изменения/удаления над таблицей то транзакция будет одна, sys_change_version у всех будет одна, а в sys.dm_tran_commit_table запишется столько записей сколько затронуто одной транзакцией. опять же если транзакция явная и затрагивает несколько таблиц на которые "натравлен" CT, то все они окажутся под одной sys_change_version. На практике - под одной sys_change_version могут быть несколько операций вставки, удаления, изменения совершенно несвязанных таблиц, которые даже логически не могут быть в одной транзакции или наоборот, операции в одной таблице, но записи не имеют отношения одна к другой, т.е. скорее всего сформированы в разных местах и разными пользователями но в одно время. Отсюда и получается некая дискретность сбора информации системой CT. У меня, видимо, не настолько сильно нагруженная система, поэтому дублирование, с которого я и начал тему, встречается не часто.
На данный момент я игнорирую эту ситуацию. Но почему MS не посчитала нужным добавить время в changetable чтоб не городить весь этот огород. Хотя их тоже понять можно - зачем усложнять систему ненужной для данной работы информацией.
13 авг 19, 00:40    [21947600]     Ответить | Цитировать Сообщить модератору
 Re: Change Tracking: Объясните, как такое получается.  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 6707
ДонАтелло,

автор
Я работаю над универсально обёрткой вокруг CT или CDC.


Как-то настораживает. Там всё, что нужно, уже обернуто в функции.
13 авг 19, 12:20    [21947936]     Ответить | Цитировать Сообщить модератору
 Re: Change Tracking: Объясните, как такое получается.  [new]
ДонАтелло
Member

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

Думаю Вы ошибаетесь насчёт sys.dm_tran_commit_table. В её описании чётко написано:
Displays one row for each transaction that is committed for a table that is tracked by SQL Server change tracking
Т.е. одна строка на транзакцию для таблицы. Ничего про строки данных тут нет. Да и было бы странно, если бы системная таблица распухала от информации о действиях над каждой строчкой.
Информация об изменениях в строках, о которой Вы пишете, содержится в специальных таблицах, которые именуются как change_tracking_N, где N - это object_id пользовательской таблицы, для которой включён CT. Вы можете их увидеть в sys.internal_tables.
К сожалению, обратится к этим таблицам можно только через DAC. Там есть ценная информация, недоступная через штатные функции для работы с CT. Например Sequance Value, которое, как раз, и есть постоянно увеличивающаяся величина при корректировке одной отдельно взятой строчки и который легко доступен для CDC при работе со штатными функциями (__$seqval).
Посему, мне кажется, Ваша гипотеза насчёт природы задвоений в sys.dm_tran_commit_table не состоятельна. Причина в чём то другом. Но у меня, к сожалению, не получается добиться воспроизведения Вашей ситуации.
21 авг 19, 18:34    [21954751]     Ответить | Цитировать Сообщить модератору
 Re: Change Tracking: Объясните, как такое получается.  [new]
ДонАтелло
Member

Откуда:
Сообщений: 29
Владислав Колосов,

Ну насчёт "всего нужного" я бы поспорил. В предыдущем посте я упомянул про sequance value, например.
Но обёртка то нужна не для CT как такового, и не для CDC как такового, обёртка нужна вокруг их обоих, для того, чтобы можно было пользоваться любой из них, не обращая внимания на разную реализацию их интерфейсов.
Этим приходится заниматься, поскольку CDC явно выигрышнее (по крайней мере для наших целей), ибо асинхронный, но доступен он только для Enterprise (по крайней мере для 2008R2). А нашу базу могут развернуть и на Express, там доступен только CT.
21 авг 19, 18:38    [21954755]     Ответить | Цитировать Сообщить модератору
 Re: Change Tracking: Объясните, как такое получается.  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 6707
ДонАтелло,

СТ нужен для загрузки данных в разнородные источники по значению ключа.
Соответственно, никакие сиквенсы там не нужны, нужен список ключей и операций, записи по которым вы должны извлечь и передать получателю, который на месте проводит синхронизацию своей локальной базы самыми свежими изменениями. Это целевое назначение системы.
21 авг 19, 19:08    [21954773]     Ответить | Цитировать Сообщить модератору
 Re: Change Tracking: Объясните, как такое получается.  [new]
ShIgor
Member

Откуда: Нижний Новгород
Сообщений: 2204
ДонАтелло,

Все, что Вы описали сейчас мной было описано в самом начале, в т.ч. и про DAC тоже.

ладно, визуально думаю будет понятнее

К сообщению приложен файл. Размер - 121Kb
22 авг 19, 17:26    [21955790]     Ответить | Цитировать Сообщить модератору
 Re: Change Tracking: Объясните, как такое получается.  [new]
ДонАтелло
Member

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

Да, извините. Не перечитал и уже позабылось. Освежил немного и получаем:
Ваш вывод:
автор
из вышесказанного получается, что в один коммит могут влезть несколько разных транзакций и совсем необязательно от одной таблицы на которых настроен CT.

Не совсем согласен с формулировкой и, особенно в части про то, настроен ли CT на этих таблицах или нет, но исходя из туманных определений полей
автор
commit_ts bigint A monotonically increasing number that serves as a database-specific timestamp for each committed transaction.
xdes_id bigint A database-specific internal ID for the transaction.

приходится делать вывод о том что "commited transaction" в общем случае может относится как один ко многим к "internal ID for the transaction", т.е. тут две разные транзакции имеются в виду.
Исходя из сказанного и помня что в sys.syscommittab уникальный ключ создан именно по полю xdes_id, а не по полю commit_ts, о чём соответствует ошибка в упомянутой Вами статье
автор
"Cannot insert duplicate key row in object 'sys.syscommittab' with unique index 'si_xdes_id'. The duplicate key value is (KeyValue).

то правильнее было бы писать запрос типа:
...
from
	sys.change_tracking_734625660 as CT
	inner join sys.dm_tran_commit_table as T on
		T.xdes_id = CT.sys_change_xdes_id

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

В сухом остатке меня интересовало то, что остаётся за кадром. Что заставляет SQL сервер в рамках одной "commited transaction", вдруг, инкрементировать "internal ID for the transaction"? Я легко могу добиться изменения N таблиц в одной транзакции, я могу добиться разного типа изменений в одной транзакции (например оператор MERGE может дать сразу и I и U и D и всё в одной транзакции), но у меня никогда не складывалось Вашей ситуации.
Я надеялся получить ответ из логов, но изучение выхлопа от fn_dblog ничего не даёт. В её выхлопе всего два поля упоминают xdes, это [Max XDESID] и [Master XDESID]. Они там, кстати, записываются в другом формате. В таком же как поле [Transaction ID] (0000:00000000). Но их исследование ничего не даёт, у меня они пустые, по крайней мере в тех записях, которые касаются исследуемой таблицы. Возможно, в Вашем случае, они, как раз, будут непустыми и можно что-то раскопать. Например у меня xdes = 31358819. cast(31358819 as varbinary) = 0x01DE7F63. Первой записью от запроса
select [Max XDESID], [Master XDESID], * from fn_dblog(null, null) where [Transaction ID] = '0000:01de7f63' order by [Current LSN] desc

вижу:
автор
Operation = LOP_COMMIT_XACT, Context = LCX_TRACKED_XDES

что намекает, что по некоему коммиту SQL отследил какой-то xdes.
У Вас на руках два xdes.
Как выглядит лог двух этих транзакций, выведенный в порядке возрастания или убывания [Current LSN]?
Что в поле AllocUnitName, совпадает с изменяемой таблицей?
Сколько Вы насчитаете внутри обычных транзакций (операции LOP_BEGIN_XACT и LOP_COMMIT_XACT)? Подозреваю, что всего одну. А если так - то самое интересное - это то что же происходит на изломе [Transaction ID]?
22 авг 19, 20:09    [21955946]     Ответить | Цитировать Сообщить модератору
 Re: Change Tracking: Объясните, как такое получается.  [new]
ShIgor
Member

Откуда: Нижний Новгород
Сообщений: 2204
ДонАтелло
У Вас на руках два xdes.Как выглядит лог двух этих транзакций, выведенный в порядке возрастания или убывания [Current LSN]?

N-ое число записей (видел и 600 и 50, разное в общем) с возрастающем LSN у которых первая часть одинаковая, вторых частей 2, и третья часть монотонно возрастающая в рамках 2 части.

ДонАтелло
Что в поле AllocUnitName, совпадает с изменяемой таблицей?

да, совпадает, практически все N записей, за исключением LOP_BEGIN_XACT, LOP_COMMIT_XACT у которых нет в этом поле ничего и записей связанных с таблицей change_tracking_XXX измененной таблицы

ДонАтелло
Сколько Вы насчитаете внутри обычных транзакций (операции LOP_BEGIN_XACT и LOP_COMMIT_XACT)? Подозреваю, что всего одну.

2 пары (очень редко бывает больше), сначала начинается одна, где-то посередине начинается вторая, обе закончились одновременно в конце, но четкого деления по второй части LSN нет, вторая транзакция начинается еще в рамках одного и того же номера второй части LSN. Номера самих транзакций необязательно последовательные, что странно..

ДонАтелло
А если так - то самое интересное - это то что же происходит на изломе [Transaction ID]?

Излома нет как такового. Судя по LSN, вторая транзакция начинается примерно с середины первой в порядке возрастания LSN и до конца обе транзакции перемежаются между собой, но зависимости нет никакой, как будто вторая транзакция вкраплена в первую. А бывает и просто последовательно одна и вторая, но обязательно в конце два коммита, причем сначала коммит второй транзакции, потом первой.
23 авг 19, 23:22    [21956821]     Ответить | Цитировать Сообщить модератору
 Re: Change Tracking: Объясните, как такое получается.  [new]
ДонАтелло
Member

Откуда:
Сообщений: 29
Забыл подписаться на ветку и опять заработался и забыл про неё.
Извините.

Честно говоря, не очень понял вот это:
ShIgor
... с возрастающем LSN у которых первая часть одинаковая, вторых частей 2, и третья часть монотонно возрастающая в рамках 2 части.


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


Если у Вас остались ещё силы, то остаются три вопроса:
1. Эти две транзакции пересекаются по изменяемым данным? Нужно проанализировать что у них в полях Page ID, Lock Information и т.д.
2. Если да, то какой же у вас уровень изоляции, что над таблицей успешно работают две транзакции одновременно, меняющие одни и те же данные одновременно. Может у Вас там SNAPSHOT?
3. Нет возможности опознать что делается в этих двух транзакциях? Делают ли эти две транзакции одно и то же? Т.е. это один и тот же фрагмент кода, в некой процедуре, но запущенной, допустим, двумя пользователями из разных сессий. Какое-то узкое место, которое как-то оптимизировали (тем же SNAPSHOTом например). Или смешались действия двух фрагментов совсем разного кода?

Собственно, если на 1ый и 2ой вопрос ответ "да", то придётся признать, что SQL выводит всё верно. Ну, по крайней мере, объяснимо: "Вот это CT изменение было произведено сразу двумя транзакциями".
9 сен 19, 23:23    [21967354]     Ответить | Цитировать Сообщить модератору
 Re: Change Tracking: Объясните, как такое получается.  [new]
ShIgor
Member

Откуда: Нижний Новгород
Сообщений: 2204
ДонАтелло,

1. и "да", и "нет". т.е. бывают транзакции над одной строкой (I и U), а бывают и не связанные между собой в т.ч. и по типу операции над данными и по источникам возникновения (компы/пользователи).
2. обе опции "Allow Snaphot Isolation" и "Is Read Committed Snapshot On" в "true"
3. сам вопрос аналогичен первому и ответ соответствующий: бывает разное.
12 сен 19, 14:51    [21969535]     Ответить | Цитировать Сообщить модератору
 Re: Change Tracking: Объясните, как такое получается.  [new]
ДонАтелло
Member

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

Ну тогда, более-менее, понятно. Со снапшотами у вас, практически, версионная СУБД. Как точно при этом работает CT это уже надо глубоко нырять в тех. документацию. Где б её ещё взять, хотят тут на форуме есть товарищ уровня бог, лазящий по исходникам sqlserver, но его мы звать не будем :). Но ваш кейс вполне объясним. Раз две сессии не испытывают никаких проблем с работой с одними и теми же строками, то CT вполне себе может зарегистрировать их транзакции в одной своей. Как с этим бороться - тоже, в принципе, мы выяснили.

Спасибо за интересный случай. У нас, кстати, тоже снапшоты включены, но надо сильнее постараться чтобы поймать такой кейс, видимо. В общем, будем иметь в виду.
12 сен 19, 15:00    [21969551]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить