Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
 Заблокировать таблицу от удаления до конца транзакции  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 449
Доброго времени суток!
Есть хранимка

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 раз. Так что не пинайте сильно.
16 мар 18, 19:42    [21262496]     Ответить | Цитировать Сообщить модератору
 Re: Заблокировать таблицу от удаления до конца транзакции  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36696
Ну, вставьте эксклюзивный апплок в начале процедуры сериализовав таким образом ее выполнение.
16 мар 18, 19:58    [21262533]     Ответить | Цитировать Сообщить модератору
 Re: Заблокировать таблицу от удаления до конца транзакции  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 449
О чем вы, Сергей Алексеевич?
Да и не нужно мне блокировать таблицу в начале хранимки, нужно только в начале транзакции
16 мар 18, 20:27    [21262592]     Ответить | Цитировать Сообщить модератору
 Re: Заблокировать таблицу от удаления до конца транзакции  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 449
Я так понимаю вы имели в виду sys.sp_getapplock чтобы реализовать синглтон.
Но это совсем не то, что мне нужно.
16 мар 18, 21:04    [21262673]     Ответить | Цитировать Сообщить модератору
 Re: Заблокировать таблицу от удаления до конца транзакции  [new]
invm
Member

Откуда: Москва
Сообщений: 9127
Шамиль Фаридович
Заблокировать в начале транзакции tbGroup (желательно не всю, а только те строки, что есть в #tbGroup)
Как я себе это представляю - нарисовал выше. Вот правильно ли?
Представляете правильно. Нарисовали не совсем правильно - желаемый результат не гарантирован.
Чтобы максимально приблизится к "желательно не всю, а только те строки, что есть в #tbGroup", у tblGroup должен быть индекс по Id и запрос написан так:
delete tg
from #tbGroup tg
where
 not exists(select 1 from tbGroup with (updlock, rowlock) where id = tg.id)
option
 (loop join);
Плюс в этом запросе придется обеспечить сканирование #tblGroup в порядке id, иначе можете получить дедлок.
16 мар 18, 23:28    [21262931]     Ответить | Цитировать Сообщить модератору
 Re: Заблокировать таблицу от удаления до конца транзакции  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 449
invm, спасибо за ответ
invm
Плюс в этом запросе придется обеспечить сканирование #tblGroup в порядке id, иначе можете получить дедлок.

1. На #tbGroup есть кластерный PK по полю Id. Больше индексов нет. Этого будет достаточно?

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

?
17 мар 18, 00:06    [21263049]     Ответить | Цитировать Сообщить модератору
 Re: Заблокировать таблицу от удаления до конца транзакции  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7403
Шамиль Фаридович,

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


Ну и что?

DELETE 
FROM tbGroup tg 
WHERE ...

MERGE 15 штук ...


это прекрасно переживёт.
19 мар 18, 13:49    [21267698]     Ответить | Цитировать Сообщить модератору
 Re: Заблокировать таблицу от удаления до конца транзакции  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 449
Владислав Колосов,
не переживет именно MERGE (во временнных таблицах будут ссылки на не существующие в tbGroup записи
invm дал ответ на мой первый вопрос.

Сейчас меня интересует
<подсказка для блокировки по диапазону ключей индекса>)
19 мар 18, 15:08    [21268119]     Ответить | Цитировать Сообщить модератору
 Re: Заблокировать таблицу от удаления до конца транзакции  [new]
msLex
Member

Откуда:
Сообщений: 7734
Шамиль Фаридович
<подсказка для блокировки по диапазону ключей индекса

holdlock
19 мар 18, 15:42    [21268301]     Ответить | Цитировать Сообщить модератору
 Re: Заблокировать таблицу от удаления до конца транзакции  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 449
Наколько я понял из статьи уровни гранулярности, блокировка по отдельным строкам и по диапазону ключей индекса - это одно и то же. Т.о. если я хочу заблокировать только те строки, что относятся к определенной организации, то я должен выполнить следующее:

select count(*) 
from tbGroup with (INDEX(IX_tbGroup_Orgid), updlock, rowlock)
where OrgId = @OrgId

?
19 мар 18, 16:10    [21268551]     Ответить | Цитировать Сообщить модератору
 Re: Заблокировать таблицу от удаления до конца транзакции  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Шамиль Фаридович,

и подумать о эскалации
19 мар 18, 16:32    [21268726]     Ответить | Цитировать Сообщить модератору
 Re: Заблокировать таблицу от удаления до конца транзакции  [new]
invm
Member

Откуда: Москва
Сообщений: 9127
Шамиль Фаридович
блокировка по отдельным строкам и по диапазону ключей индекса - это одно и то же
Не совсем. Но для вашей задачи можете так считать.

При ограничении OrgId = @OrgId можете написать вот так:
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.
19 мар 18, 17:23    [21269052]     Ответить | Цитировать Сообщить модератору
 Re: Заблокировать таблицу от удаления до конца транзакции  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 449
invm
В худшем случае (если не будет выбран NL для соединения таблиц) - получите блокировку всех строк в tbGroup для OrgId = @OrgId

, а в лучшем, блокировку только тех, что есть в #tbGroup - красивое решение!
TaPaK
и подумать о эскалации

Отсюда мне стало известно, что порог укрупнения блокировок по умолчанию равен 5000 строк. Т.о. для топ-50 моих организаций сервер будет пытаться автоматически укрупнить блокировку до уровня таблицы.

1. Можно ли увеличить этот порог для одной (можно и всех) таблиц в рамках одной транзакции скажем до 100 000?
(для меня важным моментом является возможность параллельной работы 2 пользователей из разных организаций)
2. При каком количестве строк (а наверное занимаемых ими страниц) лучше сразу указывать PAGELOCK вместо ROWLOCK?
19 мар 18, 18:22    [21269313]     Ответить | Цитировать Сообщить модератору
 Re: Заблокировать таблицу от удаления до конца транзакции  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Шамиль Фаридович,

а где вы там "5000 строк" увидели ?
и да отключение эскалации там описано
19 мар 18, 18:24    [21269324]     Ответить | Цитировать Сообщить модератору
 Re: Заблокировать таблицу от удаления до конца транзакции  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
ну и все эти ваши танцы, особенно с длинной транзакцией, в итоге приведут вас к дедлокам.
19 мар 18, 18:25    [21269328]     Ответить | Цитировать Сообщить модератору
 Re: Заблокировать таблицу от удаления до конца транзакции  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 449
TaPaK,
я увидел там 5000 блокировок.
Если у меня в таблице #tbGroup 5001 строка и стоит tbGroup with (rowlock),
разве это не вызовет попытку укрупнения блокировки до уровня таблицы?
TaPaK
и да отключение эскалации там описано

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

Поэтому видимо придется остановится на отключении эскалации:
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 засунуть внутрь транзакции.
19 мар 18, 19:19    [21269441]     Ответить | Цитировать Сообщить модератору
 Re: Заблокировать таблицу от удаления до конца транзакции  [new]
msLex
Member

Откуда:
Сообщений: 7734
Шамиль Фаридович
Хотя по идее можно SET LOCK_ESCALATION = DISABLE засунуть внутрь транзакции.

Это заблокирует таблицу вообще до конца транзакции
Причем даже для селекта, и даже с nolock-м
19 мар 18, 19:36    [21269467]     Ответить | Цитировать Сообщить модератору
 Re: Заблокировать таблицу от удаления до конца транзакции  [new]
invm
Member

Откуда: Москва
Сообщений: 9127
Шамиль Фаридович,

У вас конкурентный доступ к ресурсу с несовместимыми блокировками. Откуда тут взяться эскалации?
Лучше уберите rowlock и позвольте серверу самому управлять гранулярностью блокировок.
19 мар 18, 19:48    [21269491]     Ответить | Цитировать Сообщить модератору
 Re: Заблокировать таблицу от удаления до конца транзакции  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7403
Шамиль Фаридович,

все равно не понял. Вас merge попытается из таблицы #tbGroup вернуть записи в tbGroup, которые были удалены другими процессами?
20 мар 18, 11:17    [21270674]     Ответить | Цитировать Сообщить модератору
 Re: Заблокировать таблицу от удаления до конца транзакции  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
invm
Шамиль Фаридович,

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


это здесь у него конкурентный доступ с несовместимыми блокировками?
delete tg
from #tbGroup tg
where
 not exists(select 1 from tbGroup with (updlock, rowlock) where id = tg.id)
option
 (loop join);
20 мар 18, 11:19    [21270681]     Ответить | Цитировать Сообщить модератору
 Re: Заблокировать таблицу от удаления до конца транзакции  [new]
invm
Member

Откуда: Москва
Сообщений: 9127
TaPaK
это здесь у него конкурентный доступ с несовместимыми блокировками?
Да.
20 мар 18, 11:50    [21270830]     Ответить | Цитировать Сообщить модератору
 Re: Заблокировать таблицу от удаления до конца транзакции  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
invm
TaPaK
это здесь у него конкурентный доступ с несовместимыми блокировками?
Да.

ну ок
20 мар 18, 11:58    [21270868]     Ответить | Цитировать Сообщить модератору
 Re: Заблокировать таблицу от удаления до конца транзакции  [new]
invm
Member

Откуда: Москва
Сообщений: 9127
TaPaK,

Вообще-то, при updlock любая конкурентная блокировка на таблице будет препятствовать эскалации.
20 мар 18, 12:37    [21271028]     Ответить | Цитировать Сообщить модератору
 Re: Заблокировать таблицу от удаления до конца транзакции  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
invm
TaPaK,

Вообще-то, при updlock любая конкурентная блокировка на таблице будет препятствовать эскалации.

а откуда она возьмётся?
20 мар 18, 12:38    [21271033]     Ответить | Цитировать Сообщить модератору
 Re: Заблокировать таблицу от удаления до конца транзакции  [new]
invm
Member

Откуда: Москва
Сообщений: 9127
TaPaK
а откуда она возьмётся?
Мы рассматриваем работу в однопользовательском режиме?
20 мар 18, 12:52    [21271096]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить