Обеспечение последовательного (согласно очереди) доступа к таблице-справочнику

@Jack Famous · Регистрация: 10.05.2021

Студворк — интернет-сервис помощи студентам

Приветствую!

Вводные:
• некоторый блок кода (БК) начал повторятся в разных ХП (П1, П2).
• я хочу его оформить в отдельную ХП (ОП: отдельная процедура).
• в каждой ХП БК работает с временными таблицами (ВТ) #tmp внутри этой ХП и обращается к единой справочной таблице Dict.
• для работы БК нужно 3 поля (условные названия): ID Int, Str VarChar (50), Ins VarChar(1).

БК делает 3 основных действия:
1. проверяет ID и Str по Dict.
2. при нахождении, заполняет Ins значением из Dict.
3. при ненахождении заполняет Dict этими ненайденными (пополняет словарь).

При вызове ОП из П1/П2, ОП "видит" ВТ, созданные в П1/П2.
На этом я и хочу построить логику.

То есть, ОП будет без параметров. Работать будет с #TempName из вызывающей ХП, с полями, имена которых известны.
Вопрос в том, как, в этом случае, правильно разрулить многопоточность?

Что именно нужно обеспечить: в ситуации, когда есть несколько параллельных вызовов П1 и/или П2, та ХП, что первой вызвала ОП и, в ней, "дошла" до получения данных из Dict, должна блокировать Dict (например, от других экземпляров ХП), пока не закончит работу с ней.

Надеюсь, более-менее понятно объяснил

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

Достаточно ли будет просто обернуть блок получения данных из Dict и вставки новых данных (если они есть) в Dict — в явную транзакцию (Begin Tran … Commit)?
Будет ли, в таком случае, очередной экземпляр ОП ждать, пока Dict "освободится", чтобы получить из него самые "свежие" данные?

И правильно ли я понимаю, что каждый экземпляр ОП будет работать со своим экземляром #tmp из вызывающей ХП? И разруливать тут ничего не нужно.

@Jack Famous · 14.10.2024, 15:04 **[ТС]**

Нашёл статью Serializable vs. Snapshot Isolation Level
Я так понимаю, что достаточно указать SET TRANSACTION ISOLATION LEVEL SERIALIZABLE перед явной транзакцией в ХПс.

Но вот, чего я не понимаю:
• как это повлияет на уровень изоляции БД и/или сервера? нужно ли отключать сериализацию после работы ХПс, чтобы не поломать существующую логику?
• если верить справке, то это аналогично HOLDLOCK во всех Select'ах явной транзакции?

Пока выглядит, как более непонятный мне способ блокировки. Не выглядит быстрее. Возможно, надёжнее, чем sp_getapplock, но только для ситуаций параллельного чтения/изменения ВНЕ ХПс. Что неплохо, но на уровне TABLOCKХ или TABLOCK+HOLDLOCK, если я правильно понимаю.
Прошу более опытных (в сравнении со мной) пользователей внести ясность.

UPD:

Сообщение от invm

Такие коллизии разруливаются инструкцией merge с хинтом serializable на целевую таблицу. Никакие дополнительные приседания не требуются.

Сообщение от Jack Famous

Я так понимаю, что достаточно указать SET TRANSACTION ISOLATION LEVEL SERIALIZABLE перед явной транзакцией в ХПс.

невнимателен был. Вы про указание, а я про глобальный параметр написал.

Получается так:
если я использую sp_getapplock, то это позволит создать очередь только внутри ХПс. То есть. не даст повесить очередную блокировку, пока висит какая-то другая. Это быстро работает и выполняет мою задачу.
Но позволит ли эта блокировка изменить данные справочника в другом (не в ХПс) месте?

Если да, то тогда, для надёжности и "правильности" нужно использовать хинты/указания TABLOCK+HOLDLOCK, TABLOCKX или SERIALIZABLE.
Я не понимаю до конца разницу в них, но, видимо, сериализация является более "правильным" подходом.
Непонятно, насколько это ресурсозатратно, ведь при для сериализации блокируются только те ключи, которые были затронуты ранее. То есть, это нужно где-то хранить и проверять.
В этом плане, подход "заблокировать таблицу целиком при первом запросе и до окончания явно прописанной тразакции"(которая включает возможную вставку данных в Справочник), кажется более быстрым.

Жду комментариев

@PaulWist · 14.10.2024, 16:14

Сообщение от Jack Famous

как это повлияет на уровень изоляции БД и/или сервера?

Никак, каждое соединение выполняется со своим TIL.

Сообщение от Jack Famous

нужно ли отключать сериализацию после работы ХПс, чтобы не поломать существующую логику?

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

Сообщение от Jack Famous

если верить справке, то это аналогично HOLDLOCK во всех Select'ах явной транзакции?

Да, правильно.

Сообщение от Jack Famous

то тогда, для надёжности и "правильности" нужно использовать хинты/указания TABLOCK+HOLDLOCK, TABLOCKX или SERIALIZABLE.

Если надо дать одновременно читать данные из одной и той же таблицы, но не давать изменять, добавлять , то TABLOCK+HOLDLOCK.

Если надо запретить одновременно чтение/модификацию части данных таблички, то SERIALIZABLE (за исключением случая select * from t).

Если надо запретить одновременно чтение/модификацию всех данных таблички, то TABLOCKX.

@Jack Famous · 14.10.2024, 16:40 **[ТС]**

PaulWist, огромное спасибо за развёрнутый ответ!

Последнее уточнение/закрепление, на всякий случай: при явном объявлении транзакции (Begin Tran … Commit/RollBack) хинты (TABLOCK+HOLDLOCK, TABLOCKX или SERIALIZABLE), наложенные при первом обращении к таблице (внутри этой явной транзакции), сохранятся (сохраняют блокировку) до явного окончания транзакции Commit/RollBack?

@PaulWist · 15.10.2024, 09:05

Сообщение от Jack Famous

Последнее уточнение/закрепление,

Давайте разделим вопрос на 2 части.

1. Соединение устанавливается с определённым TIL, его можно поменять на другой, например с SERIAZABLE на RC, тогда все инструкции, ХП, ф-ии будут выполняться с TIL RC.

Кликните здесь для просмотра всего текста

T-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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
use tempdb
go
 
create table t1 (f1 int primary key, f2 char(4000) default '1');
go
 
create table t2 (f1 int primary key, f2 char(4000) default '2');
go
 
 
insert into t1 (f1)
select top 2 row_number() over (order by 1/0) 
 from master.dbo.spt_values
 
insert into t2 (f1)
select top 2 row_number() over (order by 1/0) 
 from master.dbo.spt_values
 
 
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
-- DBCC USEROPTIONS
begin tran
select 'begin tran SERIALIZABLE - начали транзакцию'
-- DBCC USEROPTIONS
    select * from t1 with (tablock) where f1 <= 1 
 
    SELECT *  
    from 
    (select N'SERIALIZABLE t1 (tablock) -  блокировка установилась') t1(a)
    outer apply (
    select 
    resource_type ,
       object_name(resource_associated_entity_id)  as TableName ,
       request_mode ,
       request_type ,
       request_status
    FROM sys.dm_tran_locks where resource_database_id = 2) t2
 
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
select 'устанавливаем TIL READ COMMITTED на табличку t2 (tablock)'
-- DBCC USEROPTIONS
 
select * from t2 with (tablock) where f1 > 1 
 
SELECT *  
from 
(select N'Осталась блокировка только t1 (tablock) - блокировки t2 нет, TIL = READ COMMITTED') t1(a)
outer apply (
select 
resource_type ,
       object_name(resource_associated_entity_id)  as TableName ,
       request_mode ,
       request_type ,
       request_status
FROM sys.dm_tran_locks where resource_database_id = 2) t2
 
 rollback

2.
Когда для транзакции изменяется уровень изоляции, ресурсы, которые считываются после изменения, защищаются в соответствии с правилами нового уровня. Ресурсы, которые считываются до изменения, остаются защищенными в соответствии с правилами предыдущего уровня. Например, если для транзакции уровень изоляции изменяется с READ COMMITTED на SERIALIZABLE, то совмещаемые блокировки, полученные после изменения, будут удерживаться до завершения транзакции.

Если инструкция SET TRANSACTION ISOLATION LEVEL использовалась в хранимой процедуре или триггере, то при возврате управления из них уровень изоляции будет изменен на тот, который действовал на момент их вызова. Например, если уровень изоляции REPEATABLE READ устанавливается в пакете, а пакет затем вызывает хранимую процедуру, которая меняет уровень изоляции на SERIALIZABLE, при возвращении хранимой процедурой управления пакету, настройки уровня изоляции меняются назад на REPEATABLE READ.

Ну и далее по тексту.

@Jack Famous · 15.10.2024, 09:44 **[ТС]**

PaulWist, большое вам спасибо!

Новые блоги и статьи Все статьи Все блоги /
Символьное дифференцирование igorrr37 13.02.2026 / * Логарифм записывается как: (x-2)log(x^2+2) - означает логарифм (x^2+2) по основанию (x-2). Унарный минус обозначается как ! */ #include <iostream> #include <stack> #include <cctype>. . .	Камера Toupcam IUA500KMA Eddy_Em 12.02.2026 Т. к. у всяких "хикроботов" слишком уж мелкий пиксель, для подсмотра в ESPriF они вообще плохо годятся: уже 14 величину можно рассмотреть еле-еле лишь на экспозициях под 3 секунды (а то и больше),. . .	И ясному Солнцу zbw 12.02.2026 И ясному Солнцу, и светлой Луне. В мире покоя нет и люди не могут жить в тишине. А жить им немного лет.	«Знание-Сила» zbw 12.02.2026 «Знание-Сила» «Время-Деньги» «Деньги -Пуля»
SDL3 для Web (WebAssembly): Подключение Box2D v3, физика и отрисовка коллайдеров 8Observer8 12.02.2026 Содержание блога Box2D - это библиотека для 2D физики для анимаций и игр. С её помощью можно определять были ли коллизии между конкретными объектами и вызывать обработчики событий столкновения. . . .	SDL3 для Web (WebAssembly): Загрузка PNG с прозрачным фоном с помощью SDL_LoadPNG (без SDL3_image) 8Observer8 11.02.2026 Содержание блога Библиотека SDL3 содержит встроенные инструменты для базовой работы с изображениями - без использования библиотеки SDL3_image. Пошагово создадим проект для загрузки изображения. . .	SDL3 для Web (WebAssembly): Загрузка PNG с прозрачным фоном с помощью SDL3_image 8Observer8 10.02.2026 Содержание блога Библиотека SDL3_image содержит инструменты для расширенной работы с изображениями. Пошагово создадим проект для загрузки изображения формата PNG с альфа-каналом (с прозрачным. . .	Установка Qt-версии Lazarus IDE в Debian Trixie Xfce volvo 10.02.2026 В общем, достали меня глюки IDE Лазаруса, собранной с использованием набора виджетов Gtk2 (конкретно: если набирать текст в редакторе и вызвать подсказку через Ctrl+Space, то после закрытия окошка. . .

@Jack Famous 932 / 365 / 43 Регистрация: 10.05.2021 Сообщений: 1,564 Записей в блоге: 10

	Обеспечение последовательного (согласно очереди) доступа к таблице-справочнику 11.10.2024, 09:47. Показов 2073. Ответов 24 Метки нет (Все метки) Приветствую! Вводные: • некоторый блок кода (БК) начал повторятся в разных ХП (П1, П2). • я хочу его оформить в отдельную ХП (ОП: отдельная процедура). • в каждой ХП БК работает с временными таблицами (ВТ) #tmp внутри этой ХП и обращается к единой справочной таблице Dict. • для работы БК нужно 3 поля (условные названия): ID Int, Str VarChar (50), Ins VarChar(1). БК делает 3 основных действия: 1. проверяет ID и Str по Dict. 2. при нахождении, заполняет Ins значением из Dict. 3. при ненахождении заполняет Dict этими ненайденными (пополняет словарь). При вызове ОП из П1/П2, ОП "видит" ВТ, созданные в П1/П2. На этом я и хочу построить логику. То есть, ОП будет без параметров. Работать будет с #TempName из вызывающей ХП, с полями, имена которых известны. Вопрос в том, как, в этом случае, правильно разрулить многопоточность? Что именно нужно обеспечить: в ситуации, когда есть несколько параллельных вызовов П1 и/или П2, та ХП, что первой вызвала ОП и, в ней, "дошла" до получения данных из Dict, должна блокировать Dict (например, от других экземпляров ХП), пока не закончит работу с ней. Надеюсь, более-менее понятно объяснил Если можно, то хотелось бы обойтись без явных (ручками) блокировок. Явные транзакции допускаются. Достаточно ли будет просто обернуть блок получения данных из Dict и вставки новых данных (если они есть) в Dict — в явную транзакцию (Begin Tran … Commit)? Будет ли, в таком случае, очередной экземпляр ОП ждать, пока Dict "освободится", чтобы получить из него самые "свежие" данные? И правильно ли я понимаю, что каждый экземпляр ОП будет работать со своим экземляром #tmp из вызывающей ХП? И разруливать тут ничего не нужно. 0

@Jack Famous 932 / 365 / 43 Регистрация: 10.05.2021 Сообщений: 1,564 Записей в блоге: 10
	14.10.2024, 16:40 [ТС]
	PaulWist, огромное спасибо за развёрнутый ответ! Последнее уточнение/закрепление, на всякий случай: при явном объявлении транзакции (Begin Tran … Commit/RollBack) хинты (TABLOCK+HOLDLOCK, TABLOCKX или SERIALIZABLE), наложенные при первом обращении к таблице (внутри этой явной транзакции), сохранятся (сохраняют блокировку) до явного окончания транзакции Commit/RollBack? 0

@Jack Famous 932 / 365 / 43 Регистрация: 10.05.2021 Сообщений: 1,564 Записей в блоге: 10
	15.10.2024, 09:44 [ТС]
	PaulWist, большое вам спасибо! 0