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

Откуда: Чехия
Сообщений: 487
День добрый,

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

Предистория (если кому интересно):
Мы используем довольно специфический продукт Martki EDM (CADIS) на базе MS SQL 2014. Это тулза микс ETL + WEB UI RAD, технически довольно похожа на нашу 1С, но для финансовой сферы.
На продакшене начали возникать дедлоки, и чем больше новых пользователей и джобов тем больше. Поиск проблемы осложняется тем что Маркит компилирует почти всё в зашифрованные порцедуры и множеством внутренних таблиц.
Конечно когда создавалось приложения все были в курсе про дедлоки, но много лет приложение жило без них.
Есть конечно мысль создать дополнительные, более строгие семафоры, чем те что у нас есть сейчас, но тогда это сильно ударит по многопоточности приложения.

Я слышал что уже в SQL 2005 появилось версионирование строк на подобие того что есть в Оракла, которая позволяет избежать дедлока при чтении, т.е. база не блокирует чтение вообще никогда. Есть ли оно?
1 мар 17, 13:22    [20253919]     Ответить | Цитировать Сообщить модератору
 Re: Версионирование строк как в Оракле или как бороться с дедлоками  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Skulll
День добрый,

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

Предистория (если кому интересно):
Мы используем довольно специфический продукт Martki EDM (CADIS) на базе MS SQL 2014. Это тулза микс ETL + WEB UI RAD, технически довольно похожа на нашу 1С, но для финансовой сферы.
На продакшене начали возникать дедлоки, и чем больше новых пользователей и джобов тем больше. Поиск проблемы осложняется тем что Маркит компилирует почти всё в зашифрованные порцедуры и множеством внутренних таблиц.
Конечно когда создавалось приложения все были в курсе про дедлоки, но много лет приложение жило без них.
Есть конечно мысль создать дополнительные, более строгие семафоры, чем те что у нас есть сейчас, но тогда это сильно ударит по многопоточности приложения.

Я слышал что уже в SQL 2005 появилось версионирование строк на подобие того что есть в Оракла, которая позволяет избежать дедлока при чтении, т.е. база не блокирует чтение вообще никогда. Есть ли оно?

RCSI включите если бизнес-логика позволяет.

автор
зашифрованные порцедуры
что останавливает расшифровать?
1 мар 17, 13:31    [20253963]     Ответить | Цитировать Сообщить модератору
 Re: Версионирование строк как в Оракле или как бороться с дедлоками  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 32174
Skulll
Я слышал что уже в SQL 2005 появилось версионирование строк на подобие того что есть в Оракла, которая позволяет избежать дедлока при чтении, т.е. база не блокирует чтение вообще никогда. Есть ли оно?
Да.
1 мар 17, 13:31    [20253965]     Ответить | Цитировать Сообщить модератору
 Re: Версионирование строк как в Оракле или как бороться с дедлоками  [new]
o-o
Guest
TF 1222 включите и получите/изучите свои дедлоки,
прежде чем что-то включать.
1 мар 17, 13:42    [20254020]     Ответить | Цитировать Сообщить модератору
 Re: Версионирование строк как в Оракле или как бороться с дедлоками  [new]
MacaronicTragedy
Member

Откуда:
Сообщений: 99
можно включить read commited snapshot isolation, т.е. все транзакции с изоляций типа read commited будут выполняться в режиме версионности строк. модификаций кода не должно потребоваться, однако это все равно имеет свои недостатки.

дедлоки можно посмотреть запросом

select event_data,dateadd(HH, 3, event_data.value('(event/@timestamp)[1]', 'datetime')) AS [TIMESTAMP] from
(
select cast(event_data as XML) as event_data from
(
SELECT target_data = CONVERT(XML, target_data)
FROM sys.dm_xe_session_targets t
INNER JOIN sys.dm_xe_sessions s
ON t.event_session_address = s.address
WHERE t.target_name = 'event_file'
AND s.name = 'system_health'
) t1
CROSS APPLY t1.target_data.nodes('//EventFileTarget/File') FileEvent ( FileTarget )
CROSS APPLY sys.fn_xe_file_target_read_file(FileEvent.FileTarget.value('@name','nvarchar(256)'), NULL,NULL,NULL ) t2
) t3
where t3.event_data.value('event[@name="xml_deadlock_report"][1]', 'varchar(max)') is not null
ORDER BY TIMESTAMP desc
1 мар 17, 13:50    [20254058]     Ответить | Цитировать Сообщить модератору
 Re: Версионирование строк как в Оракле или как бороться с дедлоками  [new]
Skulll
Member

Откуда: Чехия
Сообщений: 487
TaPaK
Skulll
День добрый,

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

Предистория (если кому интересно):
Мы используем довольно специфический продукт Martki EDM (CADIS) на базе MS SQL 2014. Это тулза микс ETL + WEB UI RAD, технически довольно похожа на нашу 1С, но для финансовой сферы.
На продакшене начали возникать дедлоки, и чем больше новых пользователей и джобов тем больше. Поиск проблемы осложняется тем что Маркит компилирует почти всё в зашифрованные порцедуры и множеством внутренних таблиц.
Конечно когда создавалось приложения все были в курсе про дедлоки, но много лет приложение жило без них.
Есть конечно мысль создать дополнительные, более строгие семафоры, чем те что у нас есть сейчас, но тогда это сильно ударит по многопоточности приложения.

Я слышал что уже в SQL 2005 появилось версионирование строк на подобие того что есть в Оракла, которая позволяет избежать дедлока при чтении, т.е. база не блокирует чтение вообще никогда. Есть ли оно?

RCSI включите если бизнес-логика позволяет.


Спасибо, это вроде бы то, что нужно, начну копать в этом направлении. Может вы знаете, оно работает точно так же как в Оракле?

TaPaK
Skulll
зашифрованные порцедуры
что останавливает расшифровать?

Во первых их тысячи, и дедлоки появляются в разных местах. во вторых админы отобрали у нас доступ на запуск любых приложений даже в дев среде, т.е. мы не можешь запустить приложение которые расшифровывает, но мы боримся с этим...
1 мар 17, 13:54    [20254077]     Ответить | Цитировать Сообщить модератору
 Re: Версионирование строк как в Оракле или как бороться с дедлоками  [new]
Skulll
Member

Откуда: Чехия
Сообщений: 487
MacaronicTragedy
можно включить read commited snapshot isolation, т.е. все транзакции с изоляций типа read commited будут выполняться в режиме версионности строк. модификаций кода не должно потребоваться, однако это все равно имеет свои недостатки.

дедлоки можно посмотреть запросом

select event_data,dateadd(HH, 3, event_data.value('(event/@timestamp)[1]', 'datetime')) AS [TIMESTAMP] from
(
select cast(event_data as XML) as event_data from
(
SELECT target_data = CONVERT(XML, target_data)
FROM sys.dm_xe_session_targets t
INNER JOIN sys.dm_xe_sessions s
ON t.event_session_address = s.address
WHERE t.target_name = 'event_file'
AND s.name = 'system_health'
) t1
CROSS APPLY t1.target_data.nodes('//EventFileTarget/File') FileEvent ( FileTarget )
CROSS APPLY sys.fn_xe_file_target_read_file(FileEvent.FileTarget.value('@name','nvarchar(256)'), NULL,NULL,NULL ) t2
) t3
where t3.event_data.value('event[@name="xml_deadlock_report"][1]', 'varchar(max)') is not null
ORDER BY TIMESTAMP desc


Спасибо за запрос, у меня пока нет прав запустить его даже на деве, но попробую выбить права.
Недостатки в смысле пониженной производительности, или что-то еще?
1 мар 17, 13:56    [20254088]     Ответить | Цитировать Сообщить модератору
 Re: Версионирование строк как в Оракле или как бороться с дедлоками  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 32174
Skulll
Во первых их тысячи, и дедлоки появляются в разных местах. во вторых админы отобрали у нас доступ на запуск любых приложений даже в дев среде, т.е. мы не можешь запустить приложение которые расшифровывает, но мы боримся с этим...
А вы какое в этом принимаете участие?
Продукт сторонний, администрируете тоже не вы...
Если вы пользователи, то просто ставьте задачу "что бы ошибок больше не было", и всё, а разработчики с админами пусть решают эти вопросы.
1 мар 17, 13:57    [20254092]     Ответить | Цитировать Сообщить модератору
 Re: Версионирование строк как в Оракле или как бороться с дедлоками  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Skulll,

автор
Недостатки в смысле пониженной производительности, или что-то еще?

больше нагрузки на tempdb, понятное дело что понижение общей скорости из-за снимков (но я лично серьёзных изменений не замечал) почитайте вообщем статьи :)
1 мар 17, 14:03    [20254124]     Ответить | Цитировать Сообщить модератору
 Re: Версионирование строк как в Оракле или как бороться с дедлоками  [new]
Skulll
Member

Откуда: Чехия
Сообщений: 487
Skulll
MacaronicTragedy
можно включить read commited snapshot isolation, т.е. все транзакции с изоляций типа read commited будут выполняться в режиме версионности строк. модификаций кода не должно потребоваться, однако это все равно имеет свои недостатки.

дедлоки можно посмотреть запросом

select event_data,dateadd(HH, 3, event_data.value('(event/@timestamp)[1]', 'datetime')) AS [TIMESTAMP] from
(
select cast(event_data as XML) as event_data from
(
SELECT target_data = CONVERT(XML, target_data)
FROM sys.dm_xe_session_targets t
INNER JOIN sys.dm_xe_sessions s
ON t.event_session_address = s.address
WHERE t.target_name = 'event_file'
AND s.name = 'system_health'
) t1
CROSS APPLY t1.target_data.nodes('//EventFileTarget/File') FileEvent ( FileTarget )
CROSS APPLY sys.fn_xe_file_target_read_file(FileEvent.FileTarget.value('@name','nvarchar(256)'), NULL,NULL,NULL ) t2
) t3
where t3.event_data.value('event[@name="xml_deadlock_report"][1]', 'varchar(max)') is not null
ORDER BY TIMESTAMP desc


Спасибо за запрос, у меня пока нет прав запустить его даже на деве, но попробую выбить права.
Недостатки в смысле пониженной производительности, или что-то еще?

Немного ошибся, нет прав на деве, но есть на проде. Сейчас скрипт ничего не показывает, а мы (команда разработки) не мониторим продакшен обычно, так что видимо придется включить TF 1222 как советует коллега выше.
1 мар 17, 14:06    [20254133]     Ответить | Цитировать Сообщить модератору
 Re: Версионирование строк как в Оракле или как бороться с дедлоками  [new]
Skulll
Member

Откуда: Чехия
Сообщений: 487
alexeyvg
Skulll
Во первых их тысячи, и дедлоки появляются в разных местах. во вторых админы отобрали у нас доступ на запуск любых приложений даже в дев среде, т.е. мы не можешь запустить приложение которые расшифровывает, но мы боримся с этим...
А вы какое в этом принимаете участие?
Продукт сторонний, администрируете тоже не вы...
Если вы пользователи, то просто ставьте задачу "что бы ошибок больше не было", и всё, а разработчики с админами пусть решают эти вопросы.

Мы что-то типа программисты 1С и за продукт тоже отвечаем. И когда случаются дедлоки, то все 3 участника сваливают друг на друга эту проблему - команда разработки (мы), админы, и Markit EDM
1 мар 17, 14:10    [20254151]     Ответить | Цитировать Сообщить модератору
 Re: Версионирование строк как в Оракле или как бороться с дедлоками  [new]
invm
Member

Откуда: Москва
Сообщений: 9913
Skulll
И когда случаются дедлоки, то все 3 участника сваливают друг на друга эту проблему - команда разработки (мы), админы, и Markit EDM
И этим вы будете заниматься до скончания веков. Даже намониторив кучу дедлоков и выяснив их причины, вы вряд ли добьетесь от разработчиков продукта решения проблемы.
Самый простой выход - отлавливать дедлок на стороне приложения и повторять транзакцию через некоторое время.
1 мар 17, 14:19    [20254187]     Ответить | Цитировать Сообщить модератору
 Re: Версионирование строк как в Оракле или как бороться с дедлоками  [new]
MacaronicTragedy
Member

Откуда:
Сообщений: 99
Skulll
Спасибо за запрос, у меня пока нет прав запустить его даже на деве, но попробую выбить права.
Недостатки в смысле пониженной производительности, или что-то еще?


к тому, что сказал тапак добавлю, что в ряде ситуаций может измениться логика работы запросов.
в этой статье есть пример такого поведения:
https://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/
в пункте 3, вроде.


по поводу того, что запрос ничего не выводит - это странно. по идее, в extended evens - system health session пишется информация по дедлокам по умолчанию. этот запрос должен выводить все эти строки. у меня на 12м и 14м сиквелях работает.
1 мар 17, 14:41    [20254286]     Ответить | Цитировать Сообщить модератору
 Re: Версионирование строк как в Оракле или как бороться с дедлоками  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
MacaronicTragedy,

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

как и для любого уровня изоляции.
1 мар 17, 14:44    [20254299]     Ответить | Цитировать Сообщить модератору
 Re: Версионирование строк как в Оракле или как бороться с дедлоками  [new]
MacaronicTragedy
Member

Откуда:
Сообщений: 99
TaPaK
MacaronicTragedy,
как и для любого уровня изоляции.


ага.
просто этот мне показался самым подлым. в том плане, что его часто описывают, как безопасный, не требующий изменений в коде, да и по описанию от него не ждешь подлянок, когда впервые сталкиваешься.
1 мар 17, 14:47    [20254321]     Ответить | Цитировать Сообщить модератору
 Re: Версионирование строк как в Оракле или как бороться с дедлоками  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
MacaronicTragedy
TaPaK
MacaronicTragedy,
как и для любого уровня изоляции.


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

:) чем короче транзакции тем счастливее живёт RSCI, а вообще "если бизнес-логика позволяет."
1 мар 17, 14:54    [20254359]     Ответить | Цитировать Сообщить модератору
 Re: Версионирование строк как в Оракле или как бороться с дедлоками  [new]
Skulll
Member

Откуда: Чехия
Сообщений: 487
TaPaK
MacaronicTragedy,

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

как и для любого уровня изоляции.


Без RCSI первая сессия будет просто ждать комита второй сессии, а если вторая сессия не закомитела эти данные и пытается селектить незакомиченные данные первой сессии, тогда получим дедлок. Как я понял, если RCSI то дедлока не будет, вторая сессия просто прочитает старые но еще закомиченные данные. Но вот есть апдейт, тогда опять будет дедлок, но уже очень редко может произойти. Так по крайней мере в Оракле.

У нас система не совсем реалтайм, больше пакетная обработка с небольшими апдейтами от пользователей. Так что все добавление RCSI не должно что-то существенно изменить.
1 мар 17, 16:53    [20254885]     Ответить | Цитировать Сообщить модератору
 Re: Версионирование строк как в Оракле или как бороться с дедлоками  [new]
Skulll
Member

Откуда: Чехия
Сообщений: 487
TaPaK
MacaronicTragedy
пропущено...


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

:) чем короче транзакции тем счастливее живёт RSCI, а вообще "если бизнес-логика позволяет."


На сколько я понял по-умолчанию в SQL Servre 2014 автокоммит, т.е. поймать дедлок невозможно если запускать по одному DML. Или иными словами в SQL Servre 2014, если не менять автокоммит, то невозможно поймать дедлок.
Единственное объяснение почему мы ловим дедлоки, это зашифрованные процедуры которые используют транзации, другого объяснения я не могу найти.
1 мар 17, 17:01    [20254905]     Ответить | Цитировать Сообщить модератору
 Re: Версионирование строк как в Оракле или как бороться с дедлоками  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Skulll,

автор
Без RCSI первая сессия будет просто ждать комита второй сессии, а если вторая сессия не закомитела эти данные и пытается селектить незакомиченные данные первой сессии, тогда получим дедлок. Как я понял, если RCSI то дедлока не будет, вторая сессия просто прочитает старые но еще закомиченные данные. Но вот есть апдейт, тогда опять будет дедлок, но уже очень редко может произойти. Так по крайней мере в Оракле.

- RCSI разводит читателей и писателей. Все всегда смогут всё прочитать. дедлоков на апдейте ? есть ньюанс именно со SNAPSHOT ISOLATION но о нём сейчас речь не идёт
1 мар 17, 17:02    [20254914]     Ответить | Цитировать Сообщить модератору
 Re: Версионирование строк как в Оракле или как бороться с дедлоками  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Skulll
TaPaK
пропущено...

:) чем короче транзакции тем счастливее живёт RSCI, а вообще "если бизнес-логика позволяет."


автор
На сколько я понял по-умолчанию в SQL Servre 2014 автокоммит, т.е. поймать дедлок невозможно если запускать по одному DML. Или иными словами в SQL Servre 2014, если не менять автокоммит, то невозможно поймать дедлок.
Единственное объяснение почему мы ловим дедлоки, это зашифрованные процедуры которые используют транзации, другого объяснения я не могу найти.

На сколько я понял по-умолчанию в SQL Servre 2014 автокоммит, т.е. поймать дедлок невозможно если запускать по одному DML. Или иными словами в SQL Servre 2014, если не менять автокоммит, то невозможно поймать дедлок.
Единственное объяснение почему мы ловим дедлоки, это зашифрованные процедуры которые используют транзации, другого объяснения я не могу найти.

хех, у вас системный подход к спихиванию проблем :) вы сам придумываете кто виноват и натягиваете свою сову на их глобус :)
1 мар 17, 17:06    [20254926]     Ответить | Цитировать Сообщить модератору
 Re: Версионирование строк как в Оракле или как бороться с дедлоками  [new]
MacaronicTragedy
Member

Откуда:
Сообщений: 99
Skulll
На сколько я понял по-умолчанию в SQL Servre 2014 автокоммит, т.е. поймать дедлок невозможно если запускать по одному DML. Или иными словами в SQL Servre 2014, если не менять автокоммит, то невозможно поймать дедлок.
Единственное объяснение почему мы ловим дедлоки, это зашифрованные процедуры которые используют транзации, другого объяснения я не могу найти.


если явно указать begin transaction, то автокоммита не будет. попробуй дергать процедуры, явно указав перед ними начало транзакции. только потом после тестов не забудь rollback выполнить или commit
1 мар 17, 17:20    [20254975]     Ответить | Цитировать Сообщить модератору
 Re: Версионирование строк как в Оракле или как бороться с дедлоками  [new]
o-o
Guest
Skulll
На сколько я понял по-умолчанию в SQL Servre 2014 автокоммит, т.е. поймать дедлок невозможно если запускать по одному DML.

экая фигня.
если на таблице есть хотя бы один индекс,
любой DML на ней затрагивает уже 2 сущности: саму таблицу и этот индекс.
и обновляются они тоже не одновременно, но разумеется в 1 транзакции,
будь это 100 раз автокоммит.
теперь достаточно влезть читателю, просматривающему таблицу и ее индекс в обратном порядке,
и дедлок готов.

а ведь еще могут быть просто таблицы, связанные по FK, с каскадностью.
и читать по одной таблице никто не обязан, а джойн на связанных таблицах это вообще само собой разумеется
1 мар 17, 17:46    [20255048]     Ответить | Цитировать Сообщить модератору
 Re: Версионирование строк как в Оракле или как бороться с дедлоками  [new]
o-o
Guest
желающие могут воспроизвести:
Deadlock with a single table
1 мар 17, 18:00    [20255090]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить