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

Откуда:
Сообщений: 18
Всем добрый день!
возник вопрос, никак не могу найти ответ. на сервере SQL 2005
Смысл такой, выполняется процедура отката некой многосоставной бизнес-задачи (пусть будет "комплексная задача"). (многосоставная, это значит что внутри нее есть много мелких подзадач, пусть это будут роллбэки "простых задач"). Во время выполнения отката комплексной задачи, возникают блокировки для почти всех остальных пользователей, которые делают в этот момент одну из простых задач. как только сложная транзакция закрывается, остальных отпускает.

вот пример запроса из простой задачи, который зависает :
delete from table with (rowlock index = i1) where spid = @@spid. 

при этом зависает как с хинтом nolock , так и вообще без хинтов.

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

еще могу добавить, что лочащая транзакция может выглядеть как множественное вложение более мелких транзакций, по ощущениям от 2 до 5 штук, либо одноуровневых, либо вложенных. Может можно это как то трассировать, специальным образом наладить профайлер?
код анализу не поддается, за один роллбэк исполняется ~ 200 процедур, и некоторые из них еще в курсорах. (результат трейса миллион строк)

есть еще одна мысль, это анализировать лочащие транзакции, по поиску, например,
begin tran
по тексту исполняемых процедур, и сделать там например,
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
, но повторюсь, без понимания почему и самое главное где лочится ресрсурс\таблица это вряд ли имеет смысл.

пытаюсь смоделировать ситуацию, но пока плохо получается, кроме tablockx внутри комплексной транзакции не получается заставить ждать ее пока она закроется. может у кого то получится сделать модель, буду благодарен
26 май 14, 13:41    [16073282]     Ответить | Цитировать Сообщить модератору
 Re: Лочащий запрос внутри сложной транзакции  [new]
Glory
Member

Откуда:
Сообщений: 104751
F-22SO
при этом зависает как с хинтом nolock , так и вообще без хинтов.

1. Как вы себе представляете удаление с nolock ???
2. Он не зависает. Он ожидает. А чего ожидает, вот это и выясните
26 май 14, 14:20    [16073616]     Ответить | Цитировать Сообщить модератору
 Re: Лочащий запрос внутри сложной транзакции  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8823
Вероятно Вы начинаете транзакцию - и пошли пахтать эти 200 процедур. Измените бизнес-логику так, чтобы все изменения накатывались финальным аккордом, а не растянуто по времени. Добейтесь минимального времени существования транзакции и народ Вас полюбит.
26 май 14, 14:59    [16073937]     Ответить | Цитировать Сообщить модератору
 Re: Лочащий запрос внутри сложной транзакции  [new]
F-22SO
Member

Откуда:
Сообщений: 18
Владислав Колосов
Измените бизнес-логику так, чтобы все изменения накатывались финальным аккордом, а не растянуто по времени

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

что касается
Glory
2. Он не зависает. Он ожидает. А чего ожидает, вот это и выясните


то я сегодня не смог повторить живой ситуации, (из-за глюкавости приложения) может ближе к вечеру, я найду чего же ждет этот ждущий запрос
26 май 14, 17:46    [16075279]     Ответить | Цитировать Сообщить модератору
 Re: Лочащий запрос внутри сложной транзакции  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8823
Проблема же в том, что Вы не вылечите плохую архитектуру никакими средствами. У Вас постоянно будут блокировки.
Можно лишь посоветовать изменить уровень изоляции транзакций процедур на READ UNCOMMITTED, если бизнес-логика это позволяет.
26 май 14, 17:53    [16075306]     Ответить | Цитировать Сообщить модератору
 Re: Лочащий запрос внутри сложной транзакции  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Владислав Колосов
Проблема же в том, что Вы не вылечите плохую архитектуру никакими средствами. У Вас постоянно будут блокировки.
Можно лишь посоветовать изменить уровень изоляции транзакций процедур на READ UNCOMMITTED, если бизнес-логика это позволяет.
Ну, RCSI может помочь, и менять толком ничего не надо.
26 май 14, 17:54    [16075313]     Ответить | Цитировать Сообщить модератору
 Re: Лочащий запрос внутри сложной транзакции  [new]
F-22SO
Member

Откуда:
Сообщений: 18
Glory
1. Как вы себе представляете удаление с nolock ???
2. Он не зависает. Он ожидает. А чего ожидает, вот это и выясните

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

2. и самое интересное.

запрос, который чего-то ожидает. смотрел вот здесь
select * from sys.dm_tran_locks  where spid = @spid -- подвисший 

у него request_status = WAIT, по OBJECT_NAME(resource_associated_entity_id) выдает именно нашу table , по которой не отрабатывает простой запрос delete from table with (rowlock index = i1) where spid = @@spid.

то есть мы ждем эту таблицу. что тут может быть еще интересно resource_type = OBJECT; request_mode = IX , request_type = LOCK, request_status = WAIT; request_owner_type = TRANSACTION.
так же два последних поля request_owner_lockspace_id и lock_owner_address заполнены данными. сильно подозреваю, что это ссылка на нехороший план, который я так упорно ищу. но вот куда это ссылается не понятно.

почитал что такое request_mode = IX , это Блокировка с намерением монопольного доступа. (навевает на мысль, что где то внутри кривого функционала есть что то вроде delete from table и забыли написать where spid = @@spid тем самым заблокировав именно мои записи)

теперь подвесивший запрос
select * from sys.dm_tran_locks  where spid = @spid -- подвесивший 

у этого же запроса есть своя запись с этим же resource_associated_entity_id , но у него вот такая картина:
resource_type = OBJECT; request_mode = X; request_type = LOCK; request_status =GRANT; request_reference_count =2256

X = Монопольная блокировка. данные может тут не много не красиво представил, но есть выгруженные в excel.

вопрос, куда копать дальше? как найти часть запроса, которая положила монопольный доступ на таблицу.
26 май 14, 19:35    [16075786]     Ответить | Цитировать Сообщить модератору
 Re: Лочащий запрос внутри сложной транзакции  [new]
F-22SO
Member

Откуда:
Сообщений: 18
Гавриленко Сергей Алексеевич
Владислав Колосов
Проблема же в том, что Вы не вылечите плохую архитектуру никакими средствами. У Вас постоянно будут блокировки.
Можно лишь посоветовать изменить уровень изоляции транзакций процедур на READ UNCOMMITTED, если бизнес-логика это позволяет.
Ну, RCSI может помочь, и менять толком ничего не надо.

что такое RCSI я не понял, что касается архитектуры, готов часами соглашаться с Вами, что ее нужно менять, и все что в моих силах я делаю, уж поверьте.
поменять массово на на READ UNCOMMITTED не получится это точно - бизнес логика что ни на есть самая капризная к точности.
26 май 14, 19:39    [16075796]     Ответить | Цитировать Сообщить модератору
 Re: Лочащий запрос внутри сложной транзакции  [new]
F-22SO
Member

Откуда:
Сообщений: 18
боюсь ввел кого то в заблуждение, чтобы быть максимально точным подвисший запрос выглядит так:
delete table from table WITH (nolock INDEX=I1)   
where SPID = @@spid 


где I1 это некластерный индекс по полю SPID
26 май 14, 19:44    [16075812]     Ответить | Цитировать Сообщить модератору
 Re: Лочащий запрос внутри сложной транзакции  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
F-22SO
что такое RCSI я не понял

Читать про READ_COMMITTED_SNAPSHOT. Если, конечно, у вас писатели мешают читателям, а не писатели друг другу.

F-22SO
Может можно это как то трассировать, специальным образом наладить профайлер?
Есть в профайлере событие Errors And Warnings: Blocked Process Report.

И заодно еще помониторьте эскалации блокировок.
26 май 14, 19:51    [16075834]     Ответить | Цитировать Сообщить модератору
 Re: Лочащий запрос внутри сложной транзакции  [new]
F-22SO
Member

Откуда:
Сообщений: 18
сделал так, два коннекта, первый вот что делает
drop table ##Stat
create table ##Stat (
dat datetime
)
BEG:
delete table1 from table1 WITH (rowlock INDEX=i1)   
where SPID = @@spid  
insert into ##stat select getDate()
declare @Date datetime
select @date =dateadd(ms,200,getdate())
waitfor time @Date

GOTO BEG

а второй занимается откатом многосоставной операции.

затем, настроил профайлер на процедуру отката многосоставной операции.

все запустил.

основная мысль была такая, "если на таблицу table1 наложится блокировка, то insert из цикла , см . запрос выше не будет отрабатывать, и тем самым я зафиксирую точное время последнего инсерта, и с точностью до 200 мс. анализируя лог профайла, я определю место, где накладыватся блокировка."
взялся с воодушевлением, и провел штук 5 ть итераций, результат - почти в каждый раз блокировка возникала в разных местах. как такое может быть - я не понимаю.
сложилось ощущение, что сервер накладывает на таблицу блокировку после определенного состояния сервера, которое так или иначе возникает при выполнении сложной транзакции. то ли какой то кэш закончился то ли количество циклов, или еще что то ... какие будут мысли?
29 май 14, 10:28    [16089436]     Ответить | Цитировать Сообщить модератору
 Re: Лочащий запрос внутри сложной транзакции  [new]
Glory
Member

Откуда:
Сообщений: 104751
F-22SO
сложилось ощущение, что сервер накладывает на таблицу блокировку после определенного состояния сервера, которое так или иначе возникает при выполнении сложной транзакции.

Вы это увидели в трассировке ? Или у себя в голове ?

И где в вашем скрипте общая транзакция то ?

F-22SO
какие будут мысли?

Читать в хелпе про Locking in the Database Engine
29 май 14, 10:34    [16089472]     Ответить | Цитировать Сообщить модератору
 Re: Лочащий запрос внутри сложной транзакции  [new]
F-22SO
Member

Откуда:
Сообщений: 18
Гавриленко Сергей Алексеевич
Читать про READ_COMMITTED_SNAPSHOT. Если, конечно, у вас писатели мешают читателям, а не писатели друг другу.

почитал - почтиал, пробовать не стал, в голове не обозначился единственный верный путь, нужно классическое решение, а эти настройки к чему приведут не известно.
Гавриленко Сергей Алексеевич
Есть в профайлере событие Errors And Warnings: Blocked Process Report.
И заодно еще помониторьте эскалации блокировок

пробовал, но ничего хорошего пока не нашел, профайл не на миллион строк, а 44 млн. в итоге. слишком жестоко. мысль ясна, спасибо, но пока результата не добился, может руки кривые, не отрицаю.
29 май 14, 10:37    [16089493]     Ответить | Цитировать Сообщить модератору
 Re: Лочащий запрос внутри сложной транзакции  [new]
Glory
Member

Откуда:
Сообщений: 104751
F-22SO
профайл не на миллион строк, а 44 млн. в итоге. слишком жестоко.

А вы что выбирали трассировать то ? Вообще все события всех соединений с начала времен ?
29 май 14, 10:39    [16089505]     Ответить | Цитировать Сообщить модератору
 Re: Лочащий запрос внутри сложной транзакции  [new]
F-22SO
Member

Откуда:
Сообщений: 18
Glory,
нет конечно, усливия такие: отдельная тестовая база

1) у меня в QA один цикл, который раз в 200 мс опрашивает таблицу table1 на предмет delete table1 from table1 with (rowlock index = i1) where spid = @@spid , и сразу потом делает пометку в какое время он отработал в таблицу ##stat

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

2)профайлер на 1 spid, который взял из пункта 2. события :
errors and warnings:
Blocked process reports
performance:
showplantext, showplanall
stored procedures:
sp completed
sp starting
tsql:
sql stmt completed
sql stmt starting
29 май 14, 10:51    [16089596]     Ответить | Цитировать Сообщить модератору
 Re: Лочащий запрос внутри сложной транзакции  [new]
Glory
Member

Откуда:
Сообщений: 104751
F-22SO
1) у меня в QA один цикл, который раз в 200 мс опрашивает таблицу table1 на предмет delete table1 from table1 with (rowlock index = i1) where spid = @@spid , и сразу потом делает пометку в какое время он отработал в таблицу ##stat

И что - это разве одна транзакция ?
F-22SO
errors and warnings:
Blocked process reports
performance:
showplantext, showplanall
stored procedures:
sp completed
sp starting
tsql:
sql stmt completed
sql stmt starting

И как вы тут увидели события, связанные с блокировками ?
29 май 14, 10:53    [16089613]     Ответить | Цитировать Сообщить модератору
 Re: Лочащий запрос внутри сложной транзакции  [new]
F-22SO
Member

Откуда:
Сообщений: 18
а потом сижу и смотрю момент, когда цикл
drop table ##Stat create table ##Stat (dat datetime)
BEG:
delete table1 from table1 WITH (rowlock INDEX=i1)   
where SPID = @@spid  
insert into ##stat select getDate()
declare @Date datetime
select @date =dateadd(ms,200,getdate())
waitfor time @Date
GOTO BEG

заблокировался, т.е. ситуация с блокировкой повторилась. а именно, по sys.dm_tran_locks и sys.dm_exec_requests именно такая как на рабочей базе в момент блокировки.
29 май 14, 10:56    [16089644]     Ответить | Цитировать Сообщить модератору
 Re: Лочащий запрос внутри сложной транзакции  [new]
F-22SO
Member

Откуда:
Сообщений: 18
Glory
F-22SO
1) у меня в QA один цикл, который раз в 200 мс опрашивает таблицу table1 на предмет delete table1 from table1 with (rowlock index = i1) where spid = @@spid , и сразу потом делает пометку в какое время он отработал в таблицу ##stat

И что - это разве одна транзакция ?
F-22SO
errors and warnings:
Blocked process reports
performance:
showplantext, showplanall
stored procedures:
sp completed
sp starting
tsql:
sql stmt completed
sql stmt starting

И как вы тут увидели события, связанные с блокировками ?


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

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

я событий не увидел связанных с блокировкой, но я увидел текст запроса, который работал примерно в это время с точностью до 200 мс. а время я взял из таблицы ##stat , т.е. взял время провала в ##stat, потом пошел в лог профайла, и посмотрел что делал
29 май 14, 11:02    [16089689]     Ответить | Цитировать Сообщить модератору
 Re: Лочащий запрос внутри сложной транзакции  [new]
Glory
Member

Откуда:
Сообщений: 104751
F-22SO
это вообще лакмусовая бумажка, когда этот цикл останавливается - я понимаю, что произошла блокировка

Офигеть. А пацаны то не знают.
Пользуются всяким там мониторингом.
29 май 14, 11:03    [16089710]     Ответить | Цитировать Сообщить модератору
 Re: Лочащий запрос внутри сложной транзакции  [new]
F-22SO
Member

Откуда:
Сообщений: 18
да, ситуация такова, что у нас нет прав на профайл на рабочей базе, так что приходится придумывать такие сложности как средство мониторинга. хотя на тестовой базе есть права на профайл, а вот привычкеа осталась. тем не менее. прошу не ругать сильно.
29 май 14, 11:07    [16089736]     Ответить | Цитировать Сообщить модератору
 Re: Лочащий запрос внутри сложной транзакции  [new]
Glory
Member

Откуда:
Сообщений: 104751
F-22SO
что у нас нет прав на профайл на рабочей базе

Ну тогда и не занимайтесь фигней.

F-22SO
так что приходится придумывать такие сложности как средство мониторинга.

Создайте тогда лучше свой сервер баз данных
29 май 14, 11:09    [16089755]     Ответить | Цитировать Сообщить модератору
 Re: Лочащий запрос внутри сложной транзакции  [new]
F-22SO
Member

Откуда:
Сообщений: 18
правильно я понимаю, что моя проблема в том, что я не могу нормально настроить профайлер, и если все сделать круто и правильно - то я увижу конкретное место, которое наложило X блокировку на таблицу table1, а идеи с "левым мониторингом" не катят?

тем не менее странно почему описанная конструкция не состоятельна. ведь по сути все должно работать, есть момент времени когда цикл остановился. смотри на текст запроса, исправляй. Но разные итерации показывают в пределах времени блокировки +-1 сек совершенно разные процедуры.
29 май 14, 11:17    [16089854]     Ответить | Цитировать Сообщить модератору
 Re: Лочащий запрос внутри сложной транзакции  [new]
Glory
Member

Откуда:
Сообщений: 104751
F-22SO
что моя проблема в том, что я не могу нормально настроить профайлер, и если все сделать круто и правильно -

Ваша проблема
- отсутствие знаний о том, что такое блокировки, что такое транзакции и как они связаны
- отсутствие прав на мониторинг блокировок и транзакций
29 май 14, 11:20    [16089880]     Ответить | Цитировать Сообщить модератору
 Re: Лочащий запрос внутри сложной транзакции  [new]
F-22SO
Member

Откуда:
Сообщений: 18
суровый диагноз, но знать всего не возможно, иначе я бы не обращался на форум. На данный момент активно пытаюсь восполнить пробел. Буду рад, если кто то поделится.
что касается прав на мониторинг , то они есть на тестовой базе. и в данном случае считаю пол дела сделано - я добился повторения ситуации как минимум на тестовой базе (блокировка поторяется). тут я могу экспериментировать сколько хочу.
29 май 14, 11:29    [16089978]     Ответить | Цитировать Сообщить модератору
 Re: Лочащий запрос внутри сложной транзакции  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
F-22SO
то я увижу конкретное место, которое наложило X блокировку на таблицу table1
Вряд ли вы найдете это место. Потому что, вероятнее всего, у вас там эскалация блокировок из-за:
F-22SO
за один роллбэк исполняется ~ 200 процедур, и некоторые из них еще в курсорах. (результат трейса миллион строк)

И мониторить эскалацию вам уже советовали.
29 май 14, 11:44    [16090097]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить