Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Заблокировать таблицу от удаления до конца транзакции / 25 сообщений из 67, страница 1 из 3
  1  все
16.03.2018, 19:42
    #39616123
Шамиль Фаридович
Шамиль Фаридович
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Заблокировать таблицу от удаления до конца транзакции
Доброго времени суток!
Есть хранимка

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
CREATE #tbGroup

MERGE tbGroup -- в 2 стороны: INSERT, DELETE
...

INSERT #tbGroup
FROM tbGRoup
WHERE ...

--пляски с использованием tbGroup.Id и не только

--сохраняем данные из кучи временных таблиц, у которых в том числе есть ссылки на tbGroup.Id, в постоянные
BEGIN TRAN
--ЗАБЛОКИРОВАТЬ tbGroup от удаления строк
DELETE tg -- + каскадное удаление из всех дочерних временных таблиц
    FROM #tbGroup tg 
    LEFT JOIN tbGroup g WITH(UPDLOCK) 
        ON g.Id = tg.Id
    WHERE g.Id IS NULL

MERGE 15 штук ...

DELETE 
FROM tbGroup tg 
WHERE ...

MERGE 15 штук ...

END TRAN



Поменять порядок в ней сейчас будет крайне тяжело.

Проблема в том, что эта хранимка может выполняться параллельно и к моменту объявления транзакции в tbGroup может уже не быть части строк, что были на момент заполнения #tbGroup - но они и не нужны. Я могу внутри транзакции каскадно удалить записи из #tbGroup и дочерних временных таблиц. Проблема в том, что и во время выполнения транзакции конкурентами могут быть удалены записи из tbGroup (вообще-то они тоже не нужны)

И тут 2 пути:
1. В каждой из 30 MERGE инструкций делать проверку на существование строки в tbGroup (и чет мне уже не кажется это решение не совсем правильным)
2. Заблокировать в начале транзакции tbGroup (желательно не всю, а только те строки, что есть в #tbGroup)
Как я себе это представляю - нарисовал выше. Вот правильно ли?

Про уровни изоляции и блокировки читал, но в реале использовал 1 раз. Так что не пинайте сильно.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.03.2018, 19:58
    #39616127
Гавриленко Сергей Алексеевич
Гавриленко Сергей Алексеевич
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Заблокировать таблицу от удаления до конца транзакции
Ну, вставьте эксклюзивный апплок в начале процедуры сериализовав таким образом ее выполнение.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.03.2018, 20:27
    #39616131
Шамиль Фаридович
Шамиль Фаридович
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Заблокировать таблицу от удаления до конца транзакции
О чем вы, Сергей Алексеевич?
Да и не нужно мне блокировать таблицу в начале хранимки, нужно только в начале транзакции
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.03.2018, 21:04
    #39616137
Шамиль Фаридович
Шамиль Фаридович
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Заблокировать таблицу от удаления до конца транзакции
Я так понимаю вы имели в виду sys.sp_getapplock чтобы реализовать синглтон.
Но это совсем не то, что мне нужно.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.03.2018, 23:28
    #39616165
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Заблокировать таблицу от удаления до конца транзакции
Шамиль ФаридовичЗаблокировать в начале транзакции tbGroup (желательно не всю, а только те строки, что есть в #tbGroup)
Как я себе это представляю - нарисовал выше. Вот правильно ли?Представляете правильно. Нарисовали не совсем правильно - желаемый результат не гарантирован.
Чтобы максимально приблизится к "желательно не всю, а только те строки, что есть в #tbGroup", у tblGroup должен быть индекс по Id и запрос написан так:
Код: sql
1.
2.
3.
4.
5.
6.
delete tg
from #tbGroup tg
where
 not exists(select 1 from tbGroup with (updlock, rowlock) where id = tg.id)
option
 (loop join);

Плюс в этом запросе придется обеспечить сканирование #tblGroup в порядке id, иначе можете получить дедлок.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.03.2018, 00:06
    #39616178
Шамиль Фаридович
Шамиль Фаридович
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Заблокировать таблицу от удаления до конца транзакции
invm, спасибо за ответ
invmПлюс в этом запросе придется обеспечить сканирование #tblGroup в порядке id, иначе можете получить дедлок.
1. На #tbGroup есть кластерный PK по полю Id. Больше индексов нет. Этого будет достаточно?

2. #tbGroup может быть достаточно большой, и гонять tbGroup (сейчас 2M записей) вложенными циклами не самый лучший вариант.
В tbGroup есть проиндексированное поле OrgId.
Можно в этом случае инициировать блокировку не конкретных строк, а диапазона ключей, как-то так:
Код: sql
1.
2.
3.
select count(*) 
from tbGroup with (updlock, <подсказка для блокировки по диапазону ключей индекса>)
where OrgId = @OrgId


?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.03.2018, 13:49
    #39616794
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Заблокировать таблицу от удаления до конца транзакции
Шамиль Фаридович,

авторво время выполнения транзакции конкурентами могут быть удалены записи из tbGroup

Ну и что?

Код: sql
1.
2.
3.
4.
5.
DELETE 
FROM tbGroup tg 
WHERE ...

MERGE 15 штук ...



это прекрасно переживёт.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.03.2018, 15:08
    #39616858
Шамиль Фаридович
Шамиль Фаридович
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Заблокировать таблицу от удаления до конца транзакции
Владислав Колосов,
не переживет именно MERGE (во временнных таблицах будут ссылки на не существующие в tbGroup записи
invm дал ответ на мой первый вопрос.

Сейчас меня интересует
Код: sql
1.
<подсказка для блокировки по диапазону ключей индекса>)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.03.2018, 15:42
    #39616870
msLex
msLex
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Заблокировать таблицу от удаления до конца транзакции
Шамиль Фаридович
Код: sql
1.
<подсказка для блокировки по диапазону ключей индекса


holdlock
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.03.2018, 16:10
    #39616902
Шамиль Фаридович
Шамиль Фаридович
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Заблокировать таблицу от удаления до конца транзакции
Наколько я понял из статьи уровни гранулярности , блокировка по отдельным строкам и по диапазону ключей индекса - это одно и то же. Т.о. если я хочу заблокировать только те строки, что относятся к определенной организации, то я должен выполнить следующее:

Код: sql
1.
2.
3.
select count(*) 
from tbGroup with (INDEX(IX_tbGroup_Orgid), updlock, rowlock)
where OrgId = @OrgId


?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.03.2018, 16:32
    #39616919
TaPaK
TaPaK
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Заблокировать таблицу от удаления до конца транзакции
Шамиль Фаридович,

и подумать о эскалации
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.03.2018, 17:23
    #39616970
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Заблокировать таблицу от удаления до конца транзакции
Шамиль Фаридовичблокировка по отдельным строкам и по диапазону ключей индекса - это одно и то жеНе совсем. Но для вашей задачи можете так считать.

При ограничении OrgId = @OrgId можете написать вот так:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
with tg as
(
 select top (cast(0x7fffffff as int)) id from #tbGroup order by id
)
delete tg
from
 tg left join
 tbGroup g with (updlock, index = IX_tbGroup_Orgid) on g.OrgId = @OrgId and g.id = tg.id;


В худшем случае (если не будет выбран NL для соединения таблиц) - получите блокировку всех строк в tbGroup для OrgId = @OrgId.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.03.2018, 18:22
    #39617007
Шамиль Фаридович
Шамиль Фаридович
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Заблокировать таблицу от удаления до конца транзакции
invmВ худшем случае (если не будет выбран NL для соединения таблиц) - получите блокировку всех строк в tbGroup для OrgId = @OrgId
, а в лучшем, блокировку только тех, что есть в #tbGroup - красивое решение!
TaPaKи подумать о эскалации
Отсюда мне стало известно, что порог укрупнения блокировок по умолчанию равен 5000 строк. Т.о. для топ-50 моих организаций сервер будет пытаться автоматически укрупнить блокировку до уровня таблицы.

1. Можно ли увеличить этот порог для одной (можно и всех) таблиц в рамках одной транзакции скажем до 100 000?
(для меня важным моментом является возможность параллельной работы 2 пользователей из разных организаций)
2. При каком количестве строк (а наверное занимаемых ими страниц) лучше сразу указывать PAGELOCK вместо ROWLOCK?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.03.2018, 18:24
    #39617010
TaPaK
TaPaK
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Заблокировать таблицу от удаления до конца транзакции
Шамиль Фаридович,

а где вы там "5000 строк" увидели ?
и да отключение эскалации там описано
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.03.2018, 18:25
    #39617013
TaPaK
TaPaK
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Заблокировать таблицу от удаления до конца транзакции
ну и все эти ваши танцы, особенно с длинной транзакцией, в итоге приведут вас к дедлокам.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.03.2018, 19:19
    #39617036
Шамиль Фаридович
Шамиль Фаридович
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Заблокировать таблицу от удаления до конца транзакции
TaPaK,
я увидел там 5000 блокировок.
Если у меня в таблице #tbGroup 5001 строка и стоит tbGroup with (rowlock),
разве это не вызовет попытку укрупнения блокировки до уровня таблицы?
TaPaKи да отключение эскалации там описано
Мне нужно не отключение, а перенастройка эскалации, причем желательно для одной таблицы в рамках одной транзакции.
Я так понимаю, увеличить порог укрупнения только для текущего соединения навряд ли получится, а тем более для одной таблицы

Поэтому видимо придется остановится на отключении эскалации:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
BEGIN TRY

 ALTER TABLE tbGroup SET LOCK_ESCALATION = DISABLE 
 BEGIN TRAN
 ...
 END TRAN 
 ALTER TABLE tbGroup SET LOCK_ESCALATION = AUTO

END TRY
BEGIN CATCH
 ...
 ALTER TABLE tbGroup (SET LOCK_ESCALATION = AUTO
END CACTH



Хотя по идее можно SET LOCK_ESCALATION = DISABLE засунуть внутрь транзакции.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.03.2018, 19:36
    #39617045
msLex
msLex
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Заблокировать таблицу от удаления до конца транзакции
Шамиль ФаридовичХотя по идее можно SET LOCK_ESCALATION = DISABLE засунуть внутрь транзакции.
Это заблокирует таблицу вообще до конца транзакции
Причем даже для селекта, и даже с nolock-м
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.03.2018, 19:48
    #39617053
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Заблокировать таблицу от удаления до конца транзакции
Шамиль Фаридович,

У вас конкурентный доступ к ресурсу с несовместимыми блокировками. Откуда тут взяться эскалации?
Лучше уберите rowlock и позвольте серверу самому управлять гранулярностью блокировок.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.03.2018, 11:17
    #39617334
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Заблокировать таблицу от удаления до конца транзакции
Шамиль Фаридович,

все равно не понял. Вас merge попытается из таблицы #tbGroup вернуть записи в tbGroup, которые были удалены другими процессами?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.03.2018, 11:19
    #39617339
TaPaK
TaPaK
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Заблокировать таблицу от удаления до конца транзакции
invmШамиль Фаридович,

У вас конкурентный доступ к ресурсу с несовместимыми блокировками. Откуда тут взяться эскалации?
Лучше уберите rowlock и позвольте серверу самому управлять гранулярностью блокировок.

это здесь у него конкурентный доступ с несовместимыми блокировками?
Код: sql
1.
2.
3.
4.
5.
6.
delete tg
from #tbGroup tg
where
 not exists(select 1 from tbGroup with (updlock, rowlock) where id = tg.id)
option
 (loop join);
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.03.2018, 11:50
    #39617377
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Заблокировать таблицу от удаления до конца транзакции
TaPaKэто здесь у него конкурентный доступ с несовместимыми блокировками?Да.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.03.2018, 11:58
    #39617380
TaPaK
TaPaK
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Заблокировать таблицу от удаления до конца транзакции
invmTaPaKэто здесь у него конкурентный доступ с несовместимыми блокировками?Да.
ну ок
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.03.2018, 12:37
    #39617418
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Заблокировать таблицу от удаления до конца транзакции
TaPaK,

Вообще-то, при updlock любая конкурентная блокировка на таблице будет препятствовать эскалации.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.03.2018, 12:38
    #39617419
TaPaK
TaPaK
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Заблокировать таблицу от удаления до конца транзакции
invmTaPaK,

Вообще-то, при updlock любая конкурентная блокировка на таблице будет препятствовать эскалации.
а откуда она возьмётся?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.03.2018, 12:52
    #39617436
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Заблокировать таблицу от удаления до конца транзакции
TaPaKа откуда она возьмётся?Мы рассматриваем работу в однопользовательском режиме?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
  1  все
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Заблокировать таблицу от удаления до конца транзакции / 25 сообщений из 67, страница 1 из 3
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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