ReSQL.ru
     
powered by simpleCommunicator - 2.0.51     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Как избежать блокировки?
25 сообщений из 40, страница 1 из 2
  1  все
Как избежать блокировки?
    #40060762
Ролг Хупин
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ролг Хупин
Участник
Есть процедура, делающая простой апдейт, типа такой:

Код: sql
1.
2.
3.
4.
5.
6.
7.
CREATE PROCEDURE [dbo].[test] 
@id int
AS
BEGIN
update t set m=m+1 where id=@id
END
GO



Блокировка возникает, когда два приложения одновременно вызывают эту процедуру.

Как обойти блокировку, выбор жертвы?т.е. чтобы апдейты в обеих процедурах выполнились
...
Рейтинг: 0 / 0
08.04.2021, 16:12
| Ответить | Цитировать | Написать  
Как избежать блокировки?
    #40060768
felix_ff
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
felix_ff
Участник
Ролг Хупин,

какие индексы есть на таблице?
...
Рейтинг: 0 / 0
08.04.2021, 16:30
| Ответить | Цитировать | Написать  
Как избежать блокировки?
    #40060791
Ролг Хупин
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ролг Хупин
Участник
felix_ff
Ролг Хупин,

какие индексы есть на таблице?


по id - кластерный, первичный ключ

Если не одновременно вызвается срабатывает быстро и оба и больше вызовов, но если в один момент - имею блокировку.

Что сделать - апп лок, пусть все по очереди ждут и последовательно выполняются?
...
Рейтинг: 0 / 0
08.04.2021, 17:41
| Ответить | Цитировать | Написать  
Как избежать блокировки?
    #40060795
felix_ff
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
felix_ff
Участник
Ролг Хупин,

а вы под "блокировкой" понимаете именно deadlock или просто одна сессия ждет завершения апдейта другой?

если deallock то граф приложите пожалуйста.
...
Рейтинг: 0 / 0
08.04.2021, 17:53
| Ответить | Цитировать | Написать  
Как избежать блокировки?
    #40060797
H5N1
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
H5N1
Участник
Ролг Хупин,

судя по всему так косноязычно имеется виду взимоблокировка. ее не должно быть. первая транзакция лочит, другая ждет. нужен граф дедлока, из него будет ясно что происходит.
как бонус можно попробовать IL serializable, может поможет выстроить последовательно транзакции. на дефолтном IL read committed mssql есть оптимизации, например на RC можно прочитать залоченное значение из индекса, но вроде перед нами не тот случай.
...
Рейтинг: 0 / 0
08.04.2021, 17:56
| Ответить | Цитировать | Написать  
Как избежать блокировки?
    #40060822
Ролг Хупин
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ролг Хупин
Участник
felix_ff
Ролг Хупин,

а вы под "блокировкой" понимаете именно deadlock или просто одна сессия ждет завершения апдейта другой?

если deallock то граф приложите пожалуйста.


да, сервер определяет как дедлок, определяет жертву и разрешает конфликт.

Но хотелось бы понять, как я должен изменить процедуру, чтобы такой фигни не было.
...
Рейтинг: 0 / 0
08.04.2021, 20:18
| Ответить | Цитировать | Написать  
Как избежать блокировки?
    #40060832
Mr. X
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Mr. X
Гость
Ролг Хупин,

Код процедуры как бы реальный, тогда и индекс исходя из этого кода должен быть по id, m - ключевые поля. Поможет или нет - нужно пробовать.
...
Рейтинг: 0 / 0
08.04.2021, 21:52
| Ответить | Цитировать | Написать  
Как избежать блокировки?
    #40060853
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
Ролг Хупин,

"как я должен изменить процедуру"
Для этого необходимо получить граф взаимоблокировки.
https://www.yandex.ru/search/?lr=39&offline_search=1&text=как получить граф взаимоблокировки sql server
...
Рейтинг: 0 / 0
08.04.2021, 23:11
| Ответить | Цитировать | Написать  
Как избежать блокировки?
    #40060890
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
Ролг Хупин
Но хотелось бы понять, как я должен изменить процедуру, чтобы такой фигни не было.
Обновление одной записи по PK - куда уж менять? :-)

Дедлок тут может быть только из за наличия индексов, или из за других действий в длинной внешней транзакции.
Если внешней транзакции нет, то можно попробовать убрать индексы (но они же нужны, убрать может не получиться)
Если убрать индексы нельзя, то единственный способ решения проблемы - ловить ошибку дедлока и перезапускать эту процедуру.
...
Рейтинг: 0 / 0
09.04.2021, 08:06
| Ответить | Цитировать | Написать  
Как избежать блокировки?
    #40060950
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
alexeyvg
Ролг Хупин
Но хотелось бы понять, как я должен изменить процедуру, чтобы такой фигни не было.
Обновление одной записи по PK - куда уж менять? :-)

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

Возможно, просто из-за конкуренции за страницу.
Можно, например, запретить страничные блокировки на таблице.
Или вообще оставить только строчные.
От ситуации зависит.
Пусть граф дедлока покажет.
...
Рейтинг: 0 / 0
09.04.2021, 11:28
| Ответить | Цитировать | Написать  
Как избежать блокировки?
    #40060981
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
uaggster
Возможно, просто из-за конкуренции за страницу.
Хм ,а что, возможен дедлок при доступе к одной странице?
Это из за роу-блокировок?
...
Рейтинг: 0 / 0
09.04.2021, 12:42
| Ответить | Цитировать | Написать  
Как избежать блокировки?
    #40060982
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
uaggster
Пусть граф дедлока покажет.
Это да, вообще, о дедлоках глупо вообще начинать говорить без графа, зачем терять время...
...
Рейтинг: 0 / 0
09.04.2021, 12:42
| Ответить | Цитировать | Написать  
Как избежать блокировки?
    #40061011
Ролг Хупин
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ролг Хупин
Участник
uaggster
alexeyvg
пропущено...
Обновление одной записи по PK - куда уж менять? :-)

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

Возможно, просто из-за конкуренции за страницу.
Можно, например, запретить страничные блокировки на таблице.
Или вообще оставить только строчные.
От ситуации зависит.
Пусть граф дедлока покажет.


Файл с дедлоком прицепил
...
Рейтинг: 0 / 0
09.04.2021, 14:12
| Ответить | Цитировать | Написать  
Как избежать блокировки?
    #40061023
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
Код: sql
1.
UPDATE x2 SET [order] =[order] +1 WHERE [order]>=@order AND id<>@child_id AND p_id2= @new_p_id AND @child_id<>dbo.NullID() option (loop join);


?
...
Рейтинг: 0 / 0
09.04.2021, 14:29
| Ответить | Цитировать | Написать  
Как избежать блокировки?
    #40061026
felix_ff
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
felix_ff
Участник
Ролг Хупин,

у вас там борьба за ключи индекса и к тому же некластерного IX_x2_p_id2.

у него ключ как я понимаю колонка p_id2?

и к тому же вы слукавили :-)

запросы вида
Код: sql
1.
UPDATE x2 SET [order] =[order] +1 WHERE [order]>=@order AND id<>@child_id AND p_id2= @new_p_id AND @child_id<>dbo.NullID();



отличаются от
Код: sql
1.
update t set m=m+1 where id=@id




собственно в плане сканирование IX_x2_p_id2?
...
Рейтинг: 0 / 0
09.04.2021, 14:30
| Ответить | Цитировать | Написать  
Как избежать блокировки?
    #40061027
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
alexeyvg
Ролг Хупин
Но хотелось бы понять, как я должен изменить процедуру, чтобы такой фигни не было.
Обновление одной записи по PK - куда уж менять? :-)

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


+<keylock id="lock693736b600" associatedObjectId="72060033870331904" mode="X" indexname="IX_x2_p_id2" objectname="db.dbo.x2" dbid="5" hobtid="72060033870331904">

-<keylock id="lock5ff9f96980" associatedObjectId="72060033870331904" mode="U" indexname="IX_x2_p_id2" objectname="db.dbo.x2" dbid="5" hobtid="72060033870331904">
Именно оно.
...
Рейтинг: 0 / 0
09.04.2021, 14:32
| Ответить | Цитировать | Написать  
Как избежать блокировки?
    #40061028
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
felix_ff
запросы вида
Код: sql
1.
UPDATE x2 SET [order] =[order] +1 WHERE [order]>=@order AND id<>@child_id AND p_id2= @new_p_id AND @child_id<>dbo.NullID();


отличаются от
Код: sql
1.
update t set m=m+1 where id=@id

Мда, и что мы тут обсуждали??? :-)
...
Рейтинг: 0 / 0
09.04.2021, 14:36
| Ответить | Цитировать | Написать  
Как избежать блокировки?
    #40061031
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
uaggster
+<keylock id="lock693736b600" associatedObjectId="72060033870331904" mode="X" indexname="IX_x2_p_id2" objectname="db.dbo.x2" dbid="5" hobtid="72060033870331904">

-<keylock id="lock5ff9f96980" associatedObjectId="72060033870331904" mode="U" indexname="IX_x2_p_id2" objectname="db.dbo.x2" dbid="5" hobtid="72060033870331904">
Именно оно.
Пусть тогда перезапускает апдэйт.
...
Рейтинг: 0 / 0
09.04.2021, 14:38
| Ответить | Цитировать | Написать  
Как избежать блокировки?
    #40061032
felix_ff
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
felix_ff
Участник
alexeyvg
felix_ff
запросы вида
Код: sql
1.
UPDATE x2 SET [order] =[order] +1 WHERE [order]>=@order AND id<>@child_id AND p_id2= @new_p_id AND @child_id<>dbo.NullID();


отличаются от
Код: sql
1.
update t set m=m+1 where id=@id

Мда, и что мы тут обсуждали??? :-)


 

хрустальные шары разогревали
...
Рейтинг: 0 / 0
09.04.2021, 14:39
| Ответить | Цитировать | Написать  
Как избежать блокировки?
    #40061035
Ролг Хупин
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ролг Хупин
Участник
felix_ff
alexeyvg
пропущено...
Мда, и что мы тут обсуждали??? :-)


 

хрустальные шары разогревали


упрощал, щадил читателей, убирал всякую фигню
...
Рейтинг: 0 / 0
09.04.2021, 14:47
| Ответить | Цитировать | Написать  
Как избежать блокировки?
    #40061038
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
Можно для update попробовать сразу xlock выставлять, хотя это может сказаться на производительности - будут неоправданные ожидания там, где обновление не требуется.
...
Рейтинг: 0 / 0
09.04.2021, 14:47
| Ответить | Цитировать | Написать  
Как избежать блокировки?
    #40061040
Ролг Хупин
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ролг Хупин
Участник
uaggster
Код: sql
1.
UPDATE x2 SET [order] =[order] +1 WHERE [order]>=@order AND id<>@child_id AND p_id2= @new_p_id AND @child_id<>dbo.NullID() option (loop join);


?


Т.е. решение в этом случае: сделать (loop join) ?
...
Рейтинг: 0 / 0
09.04.2021, 14:49
| Ответить | Цитировать | Написать  
Как избежать блокировки?
    #40061046
felix_ff
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
felix_ff
Участник
Ролг Хупин
uaggster
Код: sql
1.
UPDATE x2 SET [order] =[order] +1 WHERE [order]>=@order AND id<>@child_id AND p_id2= @new_p_id AND @child_id<>dbo.NullID() option (loop join);


?


Т.е. решение в этом случае: сделать (loop join) ?


что там у вас с планом запроса для этой процедуры?

у вас проблема в том что при операции апдейта блокируется несколько ключей индекса IX_x2_p_id2 U-блокировкой которая впоследствии будет сконвертирована в X

получается что у вас предикат: p_id2= @new_p_id возвращает или несколько строк, или не используется index_seek а вместо этого идет скан индекса., или там range scan.

кароче план еще желательно продемонстрируйте
...
Рейтинг: 0 / 0
09.04.2021, 15:01
| Ответить | Цитировать | Написать  
Как избежать блокировки?
    #40061047
Ролг Хупин
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ролг Хупин
Участник
Ролг Хупин
uaggster
Код: sql
1.
UPDATE x2 SET [order] =[order] +1 WHERE [order]>=@order AND id<>@child_id AND p_id2= @new_p_id AND @child_id<>dbo.NullID() option (loop join);


?


Т.е. решение в этом случае: сделать (loop join) ?



Не совсем понял
авторUsing OPTION(LOOP JOIN) appears to allow the query optimizer to join the tables using the nested loops in which ever order it deems is best.

Но у меня одна таблица, что оптимизатор будет джойнить?
...
Рейтинг: 0 / 0
09.04.2021, 15:02
| Ответить | Цитировать | Написать  
Как избежать блокировки?
    #40061052
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
Ролг Хупин,

UPDATE x2 WITH (PAGLOCK, XLOCK)
...
Рейтинг: 0 / 0
09.04.2021, 15:10
| Ответить | Цитировать | Написать  
25 сообщений из 40, страница 1 из 2
  1  все
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Как избежать блокировки?
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=46&msg=40061032&tid=1684827]:
 0ms
get settings:
 8ms
get forum list:
 13ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 67ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 59ms
get tp. blocked users:
 1ms
others: 12ms
total:  176ms
созд. / загр.: 176ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]