Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Синхронизация доступа к строкам таблицы из разных сессий  [new]
FreeBard
Member

Откуда:
Сообщений: 223
 		Есть хранимая процедура, которая запускается с высокой частотой из разных сессий.

Процедура принимает номер группы считает количество записей в группе и добавляет новую строку с этим количеством увеличенным на 1 (в целом count можно заменить на поиск максимального значения по полю cnt, сути задачи не меняет)

CREATE PROCEDURE [dbo].[P]
  @group_Id  int

AS
	DECLARE @cnt int;
	
	SELECT  @cnt = COUNT(*) + 1 FROM dbo.T WHERE [Group_Id] = @group_Id;

	INSERT INTO T([Group_Id], Cnt, spid) 
		VALUES(@group_Id, @cnt, @@spid);

RETURN (1)
GO


Процедура работает с таблицей T
create table T
(
	group_id int
	,cnt int
        ,spid int
)


На таблице висит триггер на инсерт, который делает задержку (необходимость триггера не обсуждается)
CREATE TRIGGER [dbo].[T (After Insert)]
  ON [dbo].[T]
  AFTER INSERT
AS
  WAITFOR DELAY '00:00:00.100'
  RETURN
GO



В таблице накапливаются примерно такие данные

group_id cnt spid
7 1 59
7 2 59
7 3 59
7 4 59
2 1 59
2 2 59
2 3 59

Проблема возникает когда процедура одновременно запускается из разных сессий
Получаю примерно такие данные
group_id cnt spid
7 1 59
7 2 59
7 3 59
7 3 61
7 4 59
2 1 59
2 2 59
2 2 58
2 3 59

Очевидно проблема c синхронизацией кода select+insert.
Когда первая процедура посчитала количество строк в группе, но еще не завершила инсерт(триггер создает задержку), другая процедура завершает подсчет и не успев посчитать добавленную первой процедурой запись тоже делает insert.

Смотрел в сторону sp_getapplock, аналог критической секции для потоков. Применил для кода select+insert
Он решает проблему, но с одним большим НО. Он распространяет свое действие для всех вызовов процедуры. Это резко сказывается на скорости обработки данных.

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

Возможно ли как-то решить проблему? Возможно как-то поиграть с уровнями изоляции?
11 мар 16, 10:55    [18918807]     Ответить | Цитировать Сообщить модератору
 Re: Синхронизация доступа к строкам таблицы из разных сессий  [new]
PaulYoung
Member

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

почитайте про блокировки, например тут
11 мар 16, 11:06    [18918860]     Ответить | Цитировать Сообщить модератору
 Re: Синхронизация доступа к строкам таблицы из разных сессий  [new]
FreeBard
Member

Откуда:
Сообщений: 223
 		PaulYoung,

Я не прошу читательский билет) Я прошу совета.
Механизм блокировок я в целом понимаю.
Не понимаю как его здесь применить.

Единственное что приходит в голову запускать select c уровнем изоляции uncommited read, что бы на чтение не влияла задержка триггера
11 мар 16, 11:17    [18918928]     Ответить | Цитировать Сообщить модератору
 Re: Синхронизация доступа к строкам таблицы из разных сессий  [new]
leov
Member

Откуда: С-Петербург
Сообщений: 616
 		FreeBard,

если не нужны дубли то надо уникуй делать
инсерт+селект не должен блокировать лишнее при наличии правильных индексов
соответственно и тормозов не должно быть
но даже и инсерт+селект тут ничего не гарантирует
я тут нарывался на обсуждения что между ними вполне может кто-то влезть


а вообще по моему задача как-то криво поставлена
я бы автоинкрементное поле добавил
а если уж нужен именно такой cnt как вы хотите
то его можно получить и при выборке
ну или проапдейтить все махом
11 мар 16, 11:23    [18918953]     Ответить | Цитировать Сообщить модератору
 Re: Синхронизация доступа к строкам таблицы из разных сессий  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8492
 		FreeBard,

создайте последовательность и используйте её для нумерации групп.
11 мар 16, 11:33    [18918996]     Ответить | Цитировать Сообщить модератору
 Re: Синхронизация доступа к строкам таблицы из разных сессий  [new]
invm
Member

Откуда: Москва
Сообщений: 9687
FreeBard
Он распространяет свое действие для всех вызовов процедуры
sp_getapplock распространяет свое действие на ресурс, который указан в вызове.
Если вы этот ресурс сделаете зависимым от @group_Id, то решите свою проблему.
11 мар 16, 11:38    [18919012]     Ответить | Цитировать Сообщить модератору
 Re: Синхронизация доступа к строкам таблицы из разных сессий  [new]
FreeBard
Member

Откуда:
Сообщений: 223
leov
FreeBard,

если не нужны дубли то надо уникуй делать
инсерт+селект не должен блокировать лишнее при наличии правильных индексов
соответственно и тормозов не должно быть
но даже и инсерт+селект тут ничего не гарантирует
я тут нарывался на обсуждения что между ними вполне может кто-то влезть


а вообще по моему задача как-то криво поставлена
я бы автоинкрементное поле добавил
а если уж нужен именно такой cnt как вы хотите
то его можно получить и при выборке
ну или проапдейтить все махом


Я специально максимально упростил задачу, что бы выделить главное. Т.е. Задача поставлена условно.
Интересует как раз как сделать что бы никто не влез между селектом и инсертом.
11 мар 16, 11:46    [18919058]     Ответить | Цитировать Сообщить модератору
 Re: Синхронизация доступа к строкам таблицы из разных сессий  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8492
 		FreeBard,

Вам не нужен селект, выше я написал. как поступить.
11 мар 16, 11:59    [18919125]     Ответить | Цитировать Сообщить модератору
 Re: Синхронизация доступа к строкам таблицы из разных сессий  [new]
FreeBard
Member

Откуда:
Сообщений: 223
invm
FreeBard
Он распространяет свое действие для всех вызовов процедуры
sp_getapplock распространяет свое действие на ресурс, который указан в вызове.
Если вы этот ресурс сделаете зависимым от @group_Id, то решите свою проблему.


	EXEC  sp_getapplock @Resource= @group_Id
         , @LockMode='Exclusive'
         , @LockOwner='Transaction'
         , @LockTimeout = 15000



Спасибо, то что нужно!
11 мар 16, 12:01    [18919136]     Ответить | Цитировать Сообщить модератору
 Re: Синхронизация доступа к строкам таблицы из разных сессий  [new]
FreeBard
Member

Откуда:
Сообщений: 223
 		Владислав Колосов,

Спасибо, тоже вариант.
Думал о сиквенсах, но в реальной задаче это количество еще обвешивается бизнес логикой (номер должен расти в пределах даты и тд.) и сиквенс не совсем подходит. К тому же если его использовать для всех групп, то в рамках группы в нумерации будут дыры.
11 мар 16, 12:11    [18919176]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить