ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Заблокировать таблицу от удаления до конца транзакции
25 сообщений из 67, страница 1 из 3
  1  все
Заблокировать таблицу от удаления до конца транзакции
    #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:42
| Ответить | Цитировать | Написать  
Заблокировать таблицу от удаления до конца транзакции
    #39616127
Гавриленко Сергей Алексеевич
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гавриленко Сергей Алексеевич
Участник
Ну, вставьте эксклюзивный апплок в начале процедуры сериализовав таким образом ее выполнение.
...
Рейтинг: 0 / 0
16.03.2018, 19:58
| Ответить | Цитировать | Написать  
Заблокировать таблицу от удаления до конца транзакции
    #39616131
Шамиль Фаридович
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Шамиль Фаридович
Участник
О чем вы, Сергей Алексеевич?
Да и не нужно мне блокировать таблицу в начале хранимки, нужно только в начале транзакции
...
Рейтинг: 0 / 0
16.03.2018, 20:27
| Ответить | Цитировать | Написать  
Заблокировать таблицу от удаления до конца транзакции
    #39616137
Шамиль Фаридович
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Шамиль Фаридович
Участник
Я так понимаю вы имели в виду sys.sp_getapplock чтобы реализовать синглтон.
Но это совсем не то, что мне нужно.
...
Рейтинг: 0 / 0
16.03.2018, 21:04
| Ответить | Цитировать | Написать  
Заблокировать таблицу от удаления до конца транзакции
    #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
16.03.2018, 23:28
| Ответить | Цитировать | Написать  
Заблокировать таблицу от удаления до конца транзакции
    #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
17.03.2018, 00:06
| Ответить | Цитировать | Написать  
Заблокировать таблицу от удаления до конца транзакции
    #39616794
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
Шамиль Фаридович,

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

Ну и что?

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

MERGE 15 штук ...



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

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


holdlock
...
Рейтинг: 0 / 0
19.03.2018, 15:42
| Ответить | Цитировать | Написать  
Заблокировать таблицу от удаления до конца транзакции
    #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:10
| Ответить | Цитировать | Написать  
Заблокировать таблицу от удаления до конца транзакции
    #39616919
TaPaK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TaPaK
Участник
Шамиль Фаридович,

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

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

а где вы там "5000 строк" увидели ?
и да отключение эскалации там описано
...
Рейтинг: 0 / 0
19.03.2018, 18:24
| Ответить | Цитировать | Написать  
Заблокировать таблицу от удаления до конца транзакции
    #39617013
TaPaK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TaPaK
Участник
ну и все эти ваши танцы, особенно с длинной транзакцией, в итоге приведут вас к дедлокам.
...
Рейтинг: 0 / 0
19.03.2018, 18:25
| Ответить | Цитировать | Написать  
Заблокировать таблицу от удаления до конца транзакции
    #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:19
| Ответить | Цитировать | Написать  
Заблокировать таблицу от удаления до конца транзакции
    #39617045
msLex
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
msLex
Участник
Шамиль ФаридовичХотя по идее можно SET LOCK_ESCALATION = DISABLE засунуть внутрь транзакции.
Это заблокирует таблицу вообще до конца транзакции
Причем даже для селекта, и даже с nolock-м
...
Рейтинг: 0 / 0
19.03.2018, 19:36
| Ответить | Цитировать | Написать  
Заблокировать таблицу от удаления до конца транзакции
    #39617053
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
Шамиль Фаридович,

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

все равно не понял. Вас merge попытается из таблицы #tbGroup вернуть записи в tbGroup, которые были удалены другими процессами?
...
Рейтинг: 0 / 0
20.03.2018, 11:17
| Ответить | Цитировать | Написать  
Заблокировать таблицу от удаления до конца транзакции
    #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:19
| Ответить | Цитировать | Написать  
Заблокировать таблицу от удаления до конца транзакции
    #39617377
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
TaPaKэто здесь у него конкурентный доступ с несовместимыми блокировками?Да.
...
Рейтинг: 0 / 0
20.03.2018, 11:50
| Ответить | Цитировать | Написать  
Заблокировать таблицу от удаления до конца транзакции
    #39617380
TaPaK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TaPaK
Участник
invmTaPaKэто здесь у него конкурентный доступ с несовместимыми блокировками?Да.
ну ок
...
Рейтинг: 0 / 0
20.03.2018, 11:58
| Ответить | Цитировать | Написать  
Заблокировать таблицу от удаления до конца транзакции
    #39617418
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
TaPaK,

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

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


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