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

Откуда:
Сообщений: 18
День добрый!
Сразу скажу - это мой первый опыт решения проблем с взаимоблокировками... глаза разбегаются в разные стороны :) Прошу снисходительности!

Ситуация выглядит так:
Клиентское приложение (MS ACCESS 2010) запускает на выполнение хранимую процедуру на MS SQL Server 2008 R2

В этой процедуре делается следующее:
delete from dbo._tmp where Пользователь = suser_sname() ;
-- тут куча инструкций которые динамически формируют строку запроса в переменную @SQL
-- сама строка @SQL представляет из себя инструкцию INSERT INTO _tmp(Пользователь, Ключ) SELECT suser_sname(), Ключ FROM Данные
exec(@SQL);

Затем клиентское приложение читает представление в MS SQL.
Строка запроса представления примерно такая: select * from Данные inner join Данные.Ключ = _tmp.Ключ where Пользователь = suser_sname()

Суть всего процесса следующая:
В ACCESS надо передать данные, что бы они там динамически изменялись. Но для каждого запрашивающего пользователя существуют различные ограничения на просмотр тех или иных строк данных. Поэтому предварительно запускается хранимая процедура, которая формирует временную таблицу с ключами записей, которые доступны пользователю. Эта времменная таблица задействована в представлении, которое и читает клиент с условием Пользователь = suser_sname()

Наверняка решение кому-то сильно не понравится. Другого пока не могу придумать в связке с MS ACCESS
Понятное дело, что пользователей много. Часто возникают взаимоблокировки запросов, потому что пользователи уверенно пытаются прочитать и записать данные как во временную таблицу так и в таблицу с данными.

Сам вопрос:
Посоветуйте какие ключи и опции лучше использовать в инструкциях DELETE и INSERT данной ситуации, что бы избежать или минимизировать взаимоблокировки.
Суда по всему блокируются инструкции DELETE и INSERT когда другие пользователи читают представление, основанное на таблице _tmp

Спасибо!
5 сен 19, 17:38    [21964835]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки - помогите начинающему  [new]
invm
Member

Откуда: Москва
Сообщений: 8750
Покажите граф взаимоблокировки в виде xml или xdl.
5 сен 19, 17:43    [21964838]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки - помогите начинающему  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36381
Взаимоблокировки -- это когда 1205 ошибка.
А судя по тому, что тут написано, вам в форум по Access разбираться с тем, как он работает с сервером.
5 сен 19, 17:44    [21964840]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки - помогите начинающему  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 6711
Соколов Валерий,

здесь читайте : https://docs.microsoft.com/ru-ru/sql/relational-databases/security/row-level-security?view=sql-server-2016
5 сен 19, 18:03    [21964851]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки - помогите начинающему  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 6711
Можно организовать приблизительно так, как это описано в статье, с помощью функций и представлений.
5 сен 19, 18:04    [21964852]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки - помогите начинающему  [new]
Соколов Валерий
Member

Откуда:
Сообщений: 18
Небольшое пояснение: Задача омрачена тем, что нужно не просто организовать разный доступ к записям, а фактически исполняемая процедура - это процедура фильтрации/поиска - т.е. в нее передается множество параметров.


Граф постараюсь отловить завтра
5 сен 19, 19:26    [21964897]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки - помогите начинающему  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2296
Соколов Валерий,

При том количестве данных что вы предоставили вам можно посоветовать только отапливать ошибку 1205 на клиенте и перезапускать процедуру.
5 сен 19, 22:26    [21964968]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки - помогите начинающему  [new]
PsyMisha
Member

Откуда: другая столица
Сообщений: 334
Соколов Валерий,

я еще так же рекомендую скачать и установить на экземпляр хранимую процедурку sp_WhoIsActive
http://whoisactive.com/downloads/who_is_active_v11_16.zip

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

Не раз спасала
6 сен 19, 09:14    [21965076]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки - помогите начинающему  [new]
PsyMisha
Member

Откуда: другая столица
Сообщений: 334
PsyMisha,

http://whoisactive.com/docs/19_whyblocked/
6 сен 19, 09:17    [21965077]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки - помогите начинающему  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 6711
Соколов Валерий,

и процедуру не можете изменить?
6 сен 19, 18:00    [21965569]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки - помогите начинающему  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2296
PsyMisha,

Какое отношение блокировки имеют к дедлокам?
6 сен 19, 21:55    [21965756]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки - помогите начинающему  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34487
Mind
PsyMisha,

Какое отношение блокировки имеют к дедлокам?


Блокировки , в конечном итоге - причина deadlock-ов
7 сен 19, 08:18    [21965843]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки - помогите начинающему  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34487
Соколов Валерий,

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

Потому что СУБД уже не в состоянии сериализовать это автоматически,
если ты данные копируешь, связь с исходными теряется.


Можно было бы переделать все это на другой сценарий работы,
Взял, забрал данные, а потом вместо delete/insert делаешь их merge с оригинальными данными.
Может это поможет.

Но конечно надо в делали влезать чтобы что-то сказать более детально.


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

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

Раз блокировка в аварийных случаях тоже только руками.

Параллельная работа пользователей при этом будет невозможна
7 сен 19, 08:30    [21965844]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки - помогите начинающему  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34487
Соколов Валерий,

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

Это лучший вариант, но тебе он естественно не понравится
7 сен 19, 08:35    [21965846]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки - помогите начинающему  [new]
aleks222
Member

Откуда:
Сообщений: 733
MasterZiv
Аксесс в жопу, делаешь нормальный клиент - сервер,


Чтобы написать клиента, сравнимого с Access, - надо очень сильно напрячься.
Ни тебе, ни тредстартеру такое не по силам.

Поэтому не надо на Access пенять, если руки кривые.
7 сен 19, 09:27    [21965849]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки - помогите начинающему  [new]
Сруль.
Member

Откуда:
Сообщений: 112
автор
Часто возникают взаимоблокировки запросов, потому что пользователи уверенно пытаются прочитать и записать данные как во временную таблицу так и в таблицу с
данными.

Было, что-то похожее. Чем похожее?
Нельзя было двум юзерам, одновременно, пулять одну и ту же
процедуру. Там не велась борьба с блокировками-этого требовала постановка задачи.
Я дал решение, которое до сих пор работает(примерно пол-года), один раз, где-то на втором месяце,
дала козла, но об этом позже.
Во первых, ваша таблица не временная, а вспомогательная. Разница ясна, правда.
Я использовал полу-временную: с двумя решётками.
Суть такая, процедура начиналась с проверки, существует ли вспомогательная таблица?
Если да, она уходила в бесконечный while(1=1) где первой командой стоял waitfor,
я тормозил выполнение на 5 мили-секунд, сейчас не помню, этот промежуток получился
эксперементально, снова проверка, если таблицы нет, комманда exit-cиловой выход
из цикла и вперёд-создание таблицы, понеслась алгоритма. Последняя комманда-удаление
вспомогательной таблицы.
С небольшим ожиданием юзеры мирятся до сих пор.
Суть козла была в том, что как-то раз вспом. таблица не стёрлась.
Может килл, может кто-то улучшил процедуру и она перестала работать.
Я с трудом вспомнил о чём речь, нашёл виновницу торжества и удалил.
С тех пор всё тикает.
9 сен 19, 12:38    [21966754]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки - помогите начинающему  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6526
Сруль.
автор
Часто возникают взаимоблокировки запросов, потому что пользователи уверенно пытаются прочитать и записать данные как во временную таблицу так и в таблицу с
данными.

Было, что-то похожее. Чем похожее?
Нельзя было двум юзерам, одновременно, пулять одну и ту же
процедуру. Там не велась борьба с блокировками-этого требовала постановка задачи.
Я дал решение, которое до сих пор работает(примерно пол-года), один раз, где-то на втором месяце,
дала козла, но об этом позже.
Во первых, ваша таблица не временная, а вспомогательная. Разница ясна, правда.
Я использовал полу-временную: с двумя решётками.
Суть такая, процедура начиналась с проверки, существует ли вспомогательная таблица?
Если да, она уходила в бесконечный while(1=1) где первой командой стоял waitfor,
я тормозил выполнение на 5 мили-секунд, сейчас не помню, этот промежуток получился
эксперементально, снова проверка, если таблицы нет, комманда exit-cиловой выход
из цикла и вперёд-создание таблицы, понеслась алгоритма. Последняя комманда-удаление
вспомогательной таблицы.
С небольшим ожиданием юзеры мирятся до сих пор.
Суть козла была в том, что как-то раз вспом. таблица не стёрлась.
Может килл, может кто-то улучшил процедуру и она перестала работать.
Я с трудом вспомнил о чём речь, нашёл виновницу торжества и удалил.
С тех пор всё тикает.

очередной изобретатель sp_getapplock

автор
Я использовал полу-временную: с двумя решётками.
ох
9 сен 19, 12:56    [21966777]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки - помогите начинающему  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2296
TaPaK


автор
Я использовал полу-временную: с двумя решётками.
ох
Чур меня. Сгинь нечистая!
9 сен 19, 23:14    [21967349]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки - помогите начинающему  [new]
defragmentator
Member

Откуда:
Сообщений: 20420
aleks222
MasterZiv
Аксесс в жопу, делаешь нормальный клиент - сервер,


Чтобы написать клиента, сравнимого с Access, - надо очень сильно напрячься.
Ни тебе, ни тредстартеру такое не по силам.

Поэтому не надо на Access пенять, если руки кривые.


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

PS: А в чём проблема написать клиента на Delphi или C# ?
14 сен 19, 13:24    [21970873]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки - помогите начинающему  [new]
aleks222
Member

Откуда:
Сообщений: 733
defragmentator
aleks222
пропущено...


Чтобы написать клиента, сравнимого с Access, - надо очень сильно напрячься.
Ни тебе, ни тредстартеру такое не по силам.

Поэтому не надо на Access пенять, если руки кривые.


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

PS: А в чём проблема написать клиента на Delphi или C# ?


Не надо читать надписи на заборах.
14 сен 19, 21:19    [21971012]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки - помогите начинающему  [new]
defragmentator
Member

Откуда:
Сообщений: 20420
aleks222
defragmentator
пропущено...


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

PS: А в чём проблема написать клиента на Delphi или C# ?


Не надо читать надписи на заборах.


Это документация MS. Если в самописном клиенте можно как угодно управлять блокировками, то в Access вывел на экран табличку - и привет, ждите все, пока я по экрану похожу.

https://support.office.com/ru-ru/article/Свойство-recordlocks-Блокировка-записей-6ca29bbb-8824-4671-8087-97fe0568019a
автор
Все записи в базовой таблице или запросе блокируются, если форма открыта в представлении формы или режиме таблицы, когда просматривается или печатается отчет либо выполняется запрос. Хотя пользователям доступна возможность чтения записей, никто из них не может редактировать, добавлять или удалять записи, пока форма не будет закрыта, печать отчета не будет завершена или выполнение запроса не закончится.
14 сен 19, 23:04    [21971044]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки - помогите начинающему  [new]
aleks222
Member

Откуда:
Сообщений: 733
defragmentator
aleks222
пропущено...


Не надо читать надписи на заборах.


Это документация MS. Если в самописном клиенте можно как угодно управлять блокировками, то в Access вывел на экран табличку - и привет, ждите все, пока я по экрану похожу.

https://support.office.com/ru-ru/article/Свойство-recordlocks-Блокировка-записей-6ca29bbb-8824-4671-8087-97fe0568019a
автор
Все записи в базовой таблице или запросе блокируются, если форма открыта в представлении формы или режиме таблицы, когда просматривается или печатается отчет либо выполняется запрос. Хотя пользователям доступна возможность чтения записей, никто из них не может редактировать, добавлять или удалять записи, пока форма не будет закрыта, печать отчета не будет завершена или выполнение запроса не закончится.


Научись читать по-ангельски, что-ли.
И заканчивай нести бред.
15 сен 19, 14:30    [21971145]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки - помогите начинающему  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 29049
defragmentator
Это документация MS. Если в самописном клиенте можно как угодно управлять блокировками, то в Access
Вы задаёте вопросы в терминах приложений MS Office, и цитируете документацию оттуда же, на форуме, который к MS Office не имеет никакого отношения.
И ваши "блокировки", "взаимоблокировки" тут воспринимаются совсем по другому.

Конечно, ваши "взаимоблокировки" не имеют никакого отношения к "взаимоблокировкам" в MSSQL; у вас это фичи, механизмы, состояния того клиентского приложения, которым вы пользуетесь (Access), и проблемы с ним следует решать на форуме по этому клиенту.
15 сен 19, 17:14    [21971211]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки - помогите начинающему  [new]
aleks222
Member

Откуда:
Сообщений: 733
alexeyvg
defragmentator
Это документация MS. Если в самописном клиенте можно как угодно управлять блокировками, то в Access
Вы задаёте вопросы в терминах приложений MS Office, и цитируете документацию оттуда же, на форуме, который к MS Office не имеет никакого отношения.
И ваши "блокировки", "взаимоблокировки" тут воспринимаются совсем по другому.

Конечно, ваши "взаимоблокировки" не имеют никакого отношения к "взаимоблокировкам" в MSSQL; у вас это фичи, механизмы, состояния того клиентского приложения, которым вы пользуетесь (Access), и проблемы с ним следует решать на форуме по этому клиенту.


MS Access клиент именно MS SQL.
И блокировки, которые он использует - это блокировки именно MS SQL.
Только бред "Все записи в базовой таблице или запросе блокируются" не имеет к действительности НИКАКОГО отношения.
MS Access может блокировать все записи "если так пожелает разработчик", но по-умолчанию НИЧЕГО не блокирует.
Именно это и написано в цитируемой ссылке.
15 сен 19, 18:42    [21971230]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить