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

Откуда: Сидней
Сообщений: 1090
Добрый день,

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

Пользователь 1 выбирает студентов из школ 1,2 и 3. Пользователь 2 пытается выбрать студентов из школ 3,4 и 5. Поскольку пользователь 1 начал первым, он должен получить список студентов из школ 1, 2 и 3 и пометить их для обработки. Пользователь 2 должен получить студентов только из школ 4 и 5. При этом надо использовать минимально необходимый уровень изоляции. Понятно, что можно залочить всю таблицу и добиться результата, но это не приемлемо.

Я думаю сделать так:
1. Установить уровень изоляции repeatable read
2. Открыть транзакцию
3. Сохранить студентов из школ 1, 2 и 3 в таблицу-переменную
4. В той же транзакции проапдейтить записи студентов со статусом 'S' (чтобы другой пользователь эти записи не трогал)
5. Закомиттить транзакцию.

В это время второй пользователь попытается запустить тот-же код для школ 3,4 и 5. Он не сможет залочить нужные записи и его сессия будет ждать окончания транзакции первого пользователя. Когда первый пользователь закончит свою транзакцию, то студенты школы 3 уже будет помечены статусом 'S' и второй пользователь их не получит.

Подскажите, я мыслю в правильном направлении?

Примерный код ниже.

Спасибо.


DECLARE @json varchar(max) =
N'{
"schoolIds": [1, 2, 3]
}'

DECLARE @studentList TABLE (id bigint)

set transaction isolation level repeatable read
begin tran 
insert into @studentList(id)
select stg_id from stg_student stg 
join openjson(@json,'$.schoolIds') sc on stg.schoolId=value  and ISNULL(status,'') <> 'S' 

update [dbo].[stg_student] set status = 'S' 
from [dbo].[stg_student] s
join @studentList sl
on s.stg_id = sl.id

commit tran
set transaction isolation level read committed
23 окт 19, 09:19    [22000424]     Ответить | Цитировать Сообщить модератору
 Re: Параллельная выборка данных двумя или более пользователями  [new]
court
Member

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

как минимум, эти два запроса
Roust_m
insert into @studentList(id)
select stg_id from stg_student stg 
join openjson(@json,'$.schoolIds') sc on stg.schoolId=value  and ISNULL(status,'') <> 'S' 

update [dbo].[stg_student] set status = 'S' 
from [dbo].[stg_student] s
join @studentList sl
on s.stg_id = sl.id

можно объединить в один
update stg 
set stg.status = 'S' 
output inserted.stg_id into @studentList(id)
from stg_student stg 
join openjson(@json,'$.schoolIds') sc on stg.schoolId=value  and ISNULL(status,'') <> 'S'
23 окт 19, 09:43    [22000464]     Ответить | Цитировать Сообщить модератору
 Re: Параллельная выборка данных двумя или более пользователями  [new]
Roust_m
Member

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

Хорошо, а как по поводу уровня изоляции? Он достаточный для получения нужного результата? Также не является ли он избыточным?
23 окт 19, 09:49    [22000480]     Ответить | Цитировать Сообщить модератору
 Re: Параллельная выборка данных двумя или более пользователями  [new]
TaPaK
Member

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

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


readpast
23 окт 19, 09:57    [22000490]     Ответить | Цитировать Сообщить модератору
 Re: Параллельная выборка данных двумя или более пользователями  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1090
TaPaK
Roust_m,

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


readpast


Как-то не очень надежно. А вдруг там записи залочены по другой причине (другой операции) и ни один из пользователей их не получит.
23 окт 19, 10:10    [22000509]     Ответить | Цитировать Сообщить модератору
 Re: Параллельная выборка данных двумя или более пользователями  [new]
TaPaK
Member

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

пропущено...


readpast


Как-то не очень надежно. А вдруг там записи залочены по другой причине (другой операции) и ни один из пользователей их не получит.

repeatable read не обеспечивает праллельную обработку
23 окт 19, 10:15    [22000519]     Ответить | Цитировать Сообщить модератору
 Re: Параллельная выборка данных двумя или более пользователями  [new]
invm
Member

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

Как быть, если один хочет (1, 2, 3), а другой, например, (2, 3)?
23 окт 19, 10:27    [22000541]     Ответить | Цитировать Сообщить модератору
 Re: Параллельная выборка данных двумя или более пользователями  [new]
L_argo
Member

Откуда:
Сообщений: 1137
Пусть юзеры вызывают ХП.
А ХП заносит сделанный выбор в табличку. Другой юзер тоже заносит свой выбор, но только то, чего еще там нет.
Потом селектит только свое вставленное.

Логику можно сделать более сложной.
23 окт 19, 10:33    [22000552]     Ответить | Цитировать Сообщить модератору
 Re: Параллельная выборка данных двумя или более пользователями  [new]
invm
Member

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

В общем, вот вам "рыба", дальше допилите под свои требования:
begin tran 
insert into @studentList(id)
select stg_id from stg_student stg 
join openjson(@json,'$.schoolIds') sc on stg.schoolId=value  and ISNULL(status,'') <> 'S' 

declare @res nvarchar(255), @result int

declare res cursor local fast_forward for
 select N'school' + cast(id as nvarchar(10)) from @studentList

open res
while 1 = 1
 begin
  fetch next from res into @res
  if @@fetch_status <> 0
   break;

  exec @result = sp_getapplock @res, N'Exclusive', N'Transaction', -1
  if @result <> 0 -- ошибка
   begin
    ...
   end
 end
close res
deallocate res

update [dbo].[stg_student] set status = 'S' 
from [dbo].[stg_student] s
join @studentList sl
on s.stg_id = sl.id

commit tran
23 окт 19, 10:44    [22000561]     Ответить | Цитировать Сообщить модератору
 Re: Параллельная выборка данных двумя или более пользователями  [new]
Ftt330
Member

Откуда:
Сообщений: 7
Roust_m
Есть такая задача, обеспечить параллельную выборку данных двумя (или более) пользователями, чтобы они не выбрали одни и те же записи для обработки.

Очередь.
23 окт 19, 10:47    [22000564]     Ответить | Цитировать Сообщить модератору
 Re: Параллельная выборка данных двумя или более пользователями  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1090
invm
Roust_m,

Как быть, если один хочет (1, 2, 3), а другой, например, (2, 3)?


Если тот, кто хочет 1,2,3 пришел первым, то второй ничего не получает и должен выбрать что-то другое.
24 окт 19, 00:55    [22001319]     Ответить | Цитировать Сообщить модератору
 Re: Параллельная выборка данных двумя или более пользователями  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1090
L_argo
Пусть юзеры вызывают ХП.
А ХП заносит сделанный выбор в табличку. Другой юзер тоже заносит свой выбор, но только то, чего еще там нет.
Потом селектит только свое вставленное.

Логику можно сделать более сложной.


Примерно тоже самое я и пытаюсь сделать. Юзеры заносят выборку в свою таблицу и в общей таблице помечают то что они выбрали, чтобы другие это же не взяли. Просто надо разрулить ситуацию когда они почти одновременно обращаются за выборкой.
24 окт 19, 00:57    [22001320]     Ответить | Цитировать Сообщить модератору
 Re: Параллельная выборка данных двумя или более пользователями  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1090
Ftt330
Roust_m
Есть такая задача, обеспечить параллельную выборку данных двумя (или более) пользователями, чтобы они не выбрали одни и те же записи для обработки.

Очередь.


Не совсем понятно. В какой форме?
24 окт 19, 00:58    [22001321]     Ответить | Цитировать Сообщить модератору
 Re: Параллельная выборка данных двумя или более пользователями  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1090
invm
Roust_m,

В общем, вот вам "рыба", дальше допилите под свои требования:
begin tran 
insert into @studentList(id)
select stg_id from stg_student stg 
join openjson(@json,'$.schoolIds') sc on stg.schoolId=value  and ISNULL(status,'') <> 'S' 

declare @res nvarchar(255), @result int

declare res cursor local fast_forward for
 select N'school' + cast(id as nvarchar(10)) from @studentList

open res
while 1 = 1
 begin
  fetch next from res into @res
  if @@fetch_status <> 0
   break;

  exec @result = sp_getapplock @res, N'Exclusive', N'Transaction', -1
  if @result <> 0 -- ошибка
   begin
    ...
   end
 end
close res
deallocate res

update [dbo].[stg_student] set status = 'S' 
from [dbo].[stg_student] s
join @studentList sl
on s.stg_id = sl.id

commit tran


Я не совсем понимаю как это работает. Залочить надо таблицу stg_student (желательно не всю, а минимально необходимые записи), после чего выбрать из нее нужные записи и пометить их как выбранные, чтобы следующий пользователь не взял себе те же самые записи.
24 окт 19, 01:37    [22001325]     Ответить | Цитировать Сообщить модератору
 Re: Параллельная выборка данных двумя или более пользователями  [new]
L_argo
Member

Откуда:
Сообщений: 1137
Просто надо разрулить ситуацию когда они почти одновременно обращаются за выборкой.
Если это будет делать одна ХП, то коллизий не будет.
24 окт 19, 08:45    [22001361]     Ответить | Цитировать Сообщить модератору
 Re: Параллельная выборка данных двумя или более пользователями  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1090
L_argo
Просто надо разрулить ситуацию когда они почти одновременно обращаются за выборкой.
Если это будет делать одна ХП, то коллизий не будет.


Это будет реализовано в одной хранимой процедуре, но запускаться она будет многими пользователями. Почему не будет коллизий?
24 окт 19, 08:59    [22001365]     Ответить | Цитировать Сообщить модератору
 Re: Параллельная выборка данных двумя или более пользователями  [new]
invm
Member

Откуда: Москва
Сообщений: 9122
Roust_m
Залочить надо таблицу stg_student
Таблицу надо не лочить, а сериализовать к ней доступ по определенным критериям. Что данный код и делает на основе пользовательских блокировок.

Если есть справочник школ, то можно еще и так
begin tran 
insert into @studentList(id)
select stg_id from stg_student stg 
join openjson(@json,'$.schoolIds') sc on stg.schoolId=value  and ISNULL(status,'') <> 'S' 

declare @c int;
select
 @c = count(*)
from
 @studentList a join
 [Справочник школ] with (rowlock, updlock) b on b.id = a.id;

update [dbo].[stg_student] set status = 'S' 
from [dbo].[stg_student] s
join @studentList sl
on s.stg_id = sl.id

commit tran
24 окт 19, 10:06    [22001393]     Ответить | Цитировать Сообщить модератору
 Re: Параллельная выборка данных двумя или более пользователями  [new]
L_argo
Member

Откуда:
Сообщений: 1137
Roust_m
Это будет реализовано в одной хранимой процедуре, но запускаться она будет многими пользователями. Почему не будет коллизий?
Потому что эта процедура сначала вставляет только свое и потом читает только свое.

Шутки ради можно индекс уникальный поставить, чтобы вставить одну и ту же ID два раза было в принципе нельзя. :)
24 окт 19, 10:12    [22001394]     Ответить | Цитировать Сообщить модератору
 Re: Параллельная выборка данных двумя или более пользователями  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
L_argo
Roust_m
Это будет реализовано в одной хранимой процедуре, но запускаться она будет многими пользователями. Почему не будет коллизий?
Потому что эта процедура сначала вставляет только свое и потом читает только свое.

Шутки ради можно индекс уникальный поставить, чтобы вставить одну и ту же ID два раза было в принципе нельзя. :)

т.е. пусть валится в ошибки это нормально
24 окт 19, 10:15    [22001397]     Ответить | Цитировать Сообщить модератору
 Re: Параллельная выборка данных двумя или более пользователями  [new]
Владислав Колосов
Member

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

вот Вам демо пример реализации.

создаёте сессию 1, копируете пример:

-- тестовая таблица
create table tempdb.dbo.t1 (id int, val int default 0);
insert tempdb.dbo.t1 (id) values (1),(2),(3),(4),(5);

begin tran
select * from tempdb.dbo.t1 with (readpast); -- первый пользователь выбирает все доступные строки для принятия решения на редактирование
select * from tempdb.dbo.t1 with (updlock,readpast) where id in (1,3); -- забрать строки для редактирования
update tempdb.dbo.t1 with (readpast) set val = 2 where id in (1,3); -- обновить указанные строки

select * from tempdb.dbo.t1 with (readuncommitted);

-- Выполнить до этой строки, выполнить пакет второй сессии, просмотреть результаты, затем выполнить строки ниже.

rollback
drop table tempdb.dbo.t1


создаёте сессию 2, имитирующую. работу второго пользователя, та же последовательность запросов, что и для первого:

begin tran
select * from tempdb.dbo.t1 with (readpast); -- второй пользователь выбирает всё, что не выбрал первый для принятия решения на редактирование
select * from tempdb.dbo.t1 with (updlock,readpast) where id in (2,4); -- забрать строки для редактирования
update tempdb.dbo.t1 with (readpast) set val = 2 where id in (2,4); -- обновить указанные строки

select * from tempdb.dbo.t1 with (readuncommitted); -- просмотреть всю таблицу

rollback
24 окт 19, 12:03    [22001515]     Ответить | Цитировать Сообщить модератору
 Re: Параллельная выборка данных двумя или более пользователями  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7399
В первом запросе для наглядности лучше указать set val = 1.
24 окт 19, 12:06    [22001527]     Ответить | Цитировать Сообщить модератору
 Re: Параллельная выборка данных двумя или более пользователями  [new]
Roust_m
Member

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

Спасибо, попробую
25 окт 19, 05:58    [22002173]     Ответить | Цитировать Сообщить модератору
 Re: Параллельная выборка данных двумя или более пользователями  [new]
invm
Member

Откуда: Москва
Сообщений: 9122
Roust_m
Пользователь 1 выбирает студентов из школ 1,2 и 3. Пользователь 2 пытается выбрать студентов из школ 3,4 и 5. Поскольку пользователь 1 начал первым, он должен получить список студентов из школ 1, 2 и 3 и пометить их для обработки. Пользователь 2 должен получить студентов только из школ 4 и 5. При этом надо использовать минимально необходимый уровень изоляции. Понятно, что можно залочить всю таблицу и добиться результата, но это не приемлемо.
Каюсь, невнимательно прочитал задачу.
Если есть справочник школ, решается так:
begin tran

insert into @studentList(id)
select
 stg_id
from
 openjson(@json,'$.schoolIds') scl join
 [Справочник школ] sc with (updlock, readpast) on sc.schoolId = a.value join
 stg_student stg on stg.schoolId = sc.schoolId and ISNULL(stg.status,'') <> 'S' 

update [dbo].[stg_student] set status = 'S' 
from [dbo].[stg_student] s
join @studentList sl
on s.stg_id = sl.id

commit tran
25 окт 19, 10:54    [22002331]     Ответить | Цитировать Сообщить модератору
 Re: Параллельная выборка данных двумя или более пользователями  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1090
invm
Roust_m
Пользователь 1 выбирает студентов из школ 1,2 и 3. Пользователь 2 пытается выбрать студентов из школ 3,4 и 5. Поскольку пользователь 1 начал первым, он должен получить список студентов из школ 1, 2 и 3 и пометить их для обработки. Пользователь 2 должен получить студентов только из школ 4 и 5. При этом надо использовать минимально необходимый уровень изоляции. Понятно, что можно залочить всю таблицу и добиться результата, но это не приемлемо.
Каюсь, невнимательно прочитал задачу.
Если есть справочник школ, решается так:
begin tran

insert into @studentList(id)
select
 stg_id
from
 openjson(@json,'$.schoolIds') scl join
 [Справочник школ] sc with (updlock, readpast) on sc.schoolId = a.value join
 stg_student stg on stg.schoolId = sc.schoolId and ISNULL(stg.status,'') <> 'S' 

update [dbo].[stg_student] set status = 'S' 
from [dbo].[stg_student] s
join @studentList sl
on s.stg_id = sl.id

commit tran


Даже если объединить два запроса внутри транзакции как предложил court и установить режим изоляции как repeatable read, у меня не получилось разделить пользователей.

Пользователь 1:
DECLARE @json varchar(max) =
N'{
"schoolIds": [1, 2]
}'


DECLARE @studentList TABLE (id bigint)

set transaction isolation level repeatable read
begin tran 

update stg 
set stg.status = 'S' 
output inserted.stg_id into @studentList(id)
from stg_student stg 
join openjson(@json,'$.schoolIds') sc on stg.schoolId=value  and ISNULL(transferred_status,'') <> 'S'

waitfor delay '00:00:15'

commit tran
set transaction isolation level read committed


select ss.schoolId, sl.id from @studentList sl
join stg_student ss
on sl.id = ss.stg_id



Пользователь 2:
DECLARE @json varchar(max) =
N'{
"schoolIds": [2, 3]
}'
select ISJSON(@json)

DECLARE @studentList TABLE (id bigint)

set transaction isolation level repeatable read
begin tran 

update stg 
set stg.status = 'S' 
output inserted.stg_id into @studentList(id)
from stg_student stg 
join openjson(@json,'$.schoolIds') sc on stg.schoolId=value  and ISNULL(transferred_status,'') <> 'S'

waitfor delay '00:00:15'

commit tran
set transaction isolation level read committed


select ss.schoolId, sl.id from @studentList sl
join stg_student ss
on sl.id = ss.stg_id


Оба пользователя получают пересекающиеся данные.
28 окт 19, 10:22    [22003942]     Ответить | Цитировать Сообщить модератору
 Re: Параллельная выборка данных двумя или более пользователями  [new]
TaPaK
Member

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

автор
Даже если объединить два запроса внутри транзакции как предложил court и установить режим изоляции как repeatable read, у меня не получилось разделить пользователей.

а точнее сделать всё иначе, то почему-то не работает...
28 окт 19, 10:25    [22003947]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить