Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Как избежать блокировки? / 25 сообщений из 40, страница 1 из 2
  1  все
08.04.2021, 16:12
    #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:30
    #40060768
felix_ff
felix_ff
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как избежать блокировки?
Ролг Хупин,

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

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


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

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

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

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

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

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

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

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


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

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

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

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

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

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

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

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

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


Файл с дедлоком прицепил
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.04.2021, 14:29
    #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:30
    #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:32
    #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:36
    #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:38
    #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:39
    #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:47
    #40061035
Ролг Хупин
Ролг Хупин
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как избежать блокировки?
felix_ff
alexeyvg
пропущено...
Мда, и что мы тут обсуждали??? :-)


 

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


упрощал, щадил читателей, убирал всякую фигню
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.04.2021, 14:47
    #40061038
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как избежать блокировки?
Можно для update попробовать сразу xlock выставлять, хотя это может сказаться на производительности - будут неоправданные ожидания там, где обновление не требуется.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.04.2021, 14:49
    #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, 15:01
    #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:02
    #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:10
    #40061052
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как избежать блокировки?
Ролг Хупин,

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


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