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

Откуда:
Сообщений: 26
 		Есть 2 процедурки, это отчеты. Характер процедурок одинаковый
- запуск процедуры пересчета документов - это реальный пересчет реальных документов с записью данных в реальные таблицы.
- выборка данных во времянки
- Изменение данных во времянках
- результирующий вывод.
Процедуры могут выполняться достаточно долго (до 10 мин.), так вот, хотелось бы чтобы на время выполнения этих процедур они не блокировали бы таблицы.
Пробовал хинты nolock и SET TRANSACTION ISOLATION LEVEL SNAPSHOT вначале процедуры, не помогло.
В такие вопросы никогда не влазил, чтение документации в голове оставило много каши.
Использую MSSQL Server 2008R2.
Как реализовать такое и на что нужно обратить внимание?
1 окт 14, 17:09    [16645951]     Ответить | Цитировать Сообщить модератору
 Re: Две процедурки блокирующие работу  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
RIPer
Пробовал хинты nolock и SET TRANSACTION ISOLATION LEVEL SNAPSHOT вначале процедуры, не помогло.


при изменении данных блокироваться изменяемые данные будут - от этого вы никак не избавитесь.
а SET TRANSACTION ISOLATION LEVEL SNAPSHOT - это в _читающих_ коннектах надо делать. тогда блокировка не будет мешать чтению.
1 окт 14, 17:21    [16646038]     Ответить | Цитировать Сообщить модератору
 Re: Две процедурки блокирующие работу  [new]
хмхмхм
Guest
автор
хотелось бы чтобы на время выполнения этих процедур они не блокировали бы таблицы

А они их действительно блокируют?
У вас же в процедурах только:
автор
выборка данных во времянки


Время этих выборок какое? Какие накладываются блокировку на таблицу (какой уровень изоляции транзакции включен, какие хинты в запросах выборок)?
1 окт 14, 17:27    [16646090]     Ответить | Цитировать Сообщить модератору
 Re: Две процедурки блокирующие работу  [new]
RIPer
Member

Откуда:
Сообщений: 26
daw
RIPer
Пробовал хинты nolock и SET TRANSACTION ISOLATION LEVEL SNAPSHOT вначале процедуры, не помогло.


при изменении данных блокироваться изменяемые данные будут - от этого вы никак не избавитесь.
а SET TRANSACTION ISOLATION LEVEL SNAPSHOT - это в _читающих_ коннектах надо делать. тогда блокировка не будет мешать чтению.

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

Один селект будет всегда блокировать другой если они по одной таблице?
1 окт 14, 17:32    [16646124]     Ответить | Цитировать Сообщить модератору
 Re: Две процедурки блокирующие работу  [new]
RIPer
Member

Откуда:
Сообщений: 26
хмхмхм
автор
хотелось бы чтобы на время выполнения этих процедур они не блокировали бы таблицы

А они их действительно блокируют?
У вас же в процедурах только:
автор
выборка данных во времянки


Время этих выборок какое? Какие накладываются блокировку на таблицу (какой уровень изоляции транзакции включен, какие хинты в запросах выборок)?


Блокируют, даже delete по таблице ждет окончания процедуры. По умолчанию в базе стоит READ COMMITTED, пробовал и с nolock и без него.

А есть ли какой нибудь лог где можно увидеть кто что блокирует?
1 окт 14, 17:37    [16646165]     Ответить | Цитировать Сообщить модератору
 Re: Две процедурки блокирующие работу  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
RIPer
то есть
- запуск процедуры пересчета документов - это реальный пересчет реальных документов с записью данных в реальные таблицы
нужно выносить из процедуры, или разделить процедуру на 2 транзакции с разным уровнем изоляции? Изменение то как раз делается не долго и не всегда.

Один селект будет всегда блокировать другой если они по одной таблице?


ну, раз не долго и не всегда, то можно вынести, тогда. и запускать только когда надо, а не каждый раз.
"разделить процедуру на 2 транзакции с разным уровнем изоляции" смысла нет особого - разве что эти ваши 2 процедурки друг другу меньше будут мешать при одновременном запуске.
один селект блокировать другой не будет.
1 окт 14, 17:38    [16646172]     Ответить | Цитировать Сообщить модератору
 Re: Две процедурки блокирующие работу  [new]
RIPer
Member

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

В процедуре отчета проверяется нужно ли делать пересчет, и если нужно то он запускается, хотя пересчет может запускаться и из другого места. Но процедура отчета блокируют другую, похожую, или другой запрос, даже если не запускался пересчет.
1 окт 14, 17:47    [16646247]     Ответить | Цитировать Сообщить модератору
 Re: Две процедурки блокирующие работу  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8488
 		Если грязное чтение не помогает, значит Вы недоговариваете, того, что процедура выполняет изменение данных в длиииинной транзакции.
1 окт 14, 17:52    [16646275]     Ответить | Цитировать Сообщить модератору
 Re: Две процедурки блокирующие работу  [new]
RIPer
Member

Откуда:
Сообщений: 26
Владислав Колосов
Если грязное чтение не помогает, значит Вы недоговариваете, того, что процедура выполняет изменение данных в длиииинной транзакции.

Хм, надо будет попробовать исключить вызов пересчета...
Вот только что лучше оставить nolock или snapshot?
1 окт 14, 17:57    [16646329]     Ответить | Цитировать Сообщить модератору
 Re: Две процедурки блокирующие работу  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8488
 		Снапшот - хорошо, но для него требуется богатырская производительность tempdb :) readuncommitted может прочесть данные, которые затем будут выброшены или будут добавлены после получения первых данных. Смотрите по задаче.
1 окт 14, 18:07    [16646390]     Ответить | Цитировать Сообщить модератору
 Re: Две процедурки блокирующие работу  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4822
RIPer
Пробовал хинты nolock и SET TRANSACTION ISOLATION LEVEL SNAPSHOT вначале процедуры, не помогло.


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

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

Также может вам рассмотреть варианты обновления частями, порциями или инкрементально.

Посмотрите, что такое Lock Escalation
1 окт 14, 18:41    [16646621]     Ответить | Цитировать Сообщить модератору
 Re: Две процедурки блокирующие работу  [new]
RIPer
Member

Откуда:
Сообщений: 26
a_voronin
RIPer
Пробовал хинты nolock и SET TRANSACTION ISOLATION LEVEL SNAPSHOT вначале процедуры, не помогло.


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

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

Также может вам рассмотреть варианты обновления частями, порциями или инкрементально.

Посмотрите, что такое Lock Escalation

В том то и дело что такая минимизация есть, в процедуре расчета в конце есть транзакция, которая меняет реальные данные, тупо delete одно, insert другое. Но если процедура отчета длится 1000 сек и нет никаких хинтов и изменения уровня изоляции, то другая процедура отчета, которая берет данные их тех же таблиц вместо обычных 60 сек выполняется 500 и в процессах показывается что она блокируется первой. Хотя транзакция по изменению реальных данных делается достаточно быстро. Отсюда я сделал вывод что блокирует вторую процедурку именно селект из первой.

В общем получается гадание на кофейной гуще, все же нужны данные о том кто кого и когда блокирует, чтобы четче понимать в какие моменты процедуры происходит эта блокировка.
2 окт 14, 09:41    [16648321]     Ответить | Цитировать Сообщить модератору
 Re: Две процедурки блокирующие работу  [new]
RIPer
Member

Откуда:
Сообщений: 26
Владислав Колосов
Снапшот - хорошо, но для него требуется богатырская производительность tempdb :) readuncommitted может прочесть данные, которые затем будут выброшены или будут добавлены после получения первых данных. Смотрите по задаче.

Хм, это отчет, и мне все равно что будет с данными потом. И я так понимаю что с readuncommitted все будет быстрее?
2 окт 14, 09:43    [16648328]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить