Добро пожаловать в форум, Guest >> Войти | Регистрация | Поиск | Правила | | В избранное | Подписаться | ||
Все форумы / Microsoft SQL Server |
![]() ![]() |
Топик располагается на нескольких страницах: [1] 2 вперед Ctrl→ все |
Roust_m Member Откуда: Сидней Сообщений: 1169 |
Добрый день, Есть такая задача, обеспечить параллельную выборку данных двумя (или более) пользователями, чтобы они не выбрали одни и те же записи для обработки. Пользователь 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] Ответить | Цитировать Сообщить модератору |
court Member Откуда: Сообщений: 2269 |
Roust_m, как минимум, эти два запроса
можно объединить в один 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] Ответить | Цитировать Сообщить модератору |
Roust_m Member Откуда: Сидней Сообщений: 1169 |
court, Хорошо, а как по поводу уровня изоляции? Он достаточный для получения нужного результата? Также не является ли он избыточным? |
23 окт 19, 09:49 [22000480] Ответить | Цитировать Сообщить модератору |
TaPaK Member Откуда: Kiev Сообщений: 6801 |
Roust_m,
readpast |
||
23 окт 19, 09:57 [22000490] Ответить | Цитировать Сообщить модератору |
Roust_m Member Откуда: Сидней Сообщений: 1169 |
Как-то не очень надежно. А вдруг там записи залочены по другой причине (другой операции) и ни один из пользователей их не получит. |
||||
23 окт 19, 10:10 [22000509] Ответить | Цитировать Сообщить модератору |
TaPaK Member Откуда: Kiev Сообщений: 6801 |
repeatable read не обеспечивает праллельную обработку |
||||
23 окт 19, 10:15 [22000519] Ответить | Цитировать Сообщить модератору |
invm Member Откуда: Москва Сообщений: 9683 |
Roust_m, Как быть, если один хочет (1, 2, 3), а другой, например, (2, 3)? |
23 окт 19, 10:27 [22000541] Ответить | Цитировать Сообщить модератору |
L_argo Member Откуда: Сообщений: 1454 |
Пусть юзеры вызывают ХП. А ХП заносит сделанный выбор в табличку. Другой юзер тоже заносит свой выбор, но только то, чего еще там нет. Потом селектит только свое вставленное. Логику можно сделать более сложной. |
23 окт 19, 10:33 [22000552] Ответить | Цитировать Сообщить модератору |
invm Member Откуда: Москва Сообщений: 9683 |
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] Ответить | Цитировать Сообщить модератору |
Ftt330 Member Откуда: Сообщений: 7 |
Очередь. |
||
23 окт 19, 10:47 [22000564] Ответить | Цитировать Сообщить модератору |
Roust_m Member Откуда: Сидней Сообщений: 1169 |
Если тот, кто хочет 1,2,3 пришел первым, то второй ничего не получает и должен выбрать что-то другое. |
||
24 окт 19, 00:55 [22001319] Ответить | Цитировать Сообщить модератору |
Roust_m Member Откуда: Сидней Сообщений: 1169 |
Примерно тоже самое я и пытаюсь сделать. Юзеры заносят выборку в свою таблицу и в общей таблице помечают то что они выбрали, чтобы другие это же не взяли. Просто надо разрулить ситуацию когда они почти одновременно обращаются за выборкой. |
||
24 окт 19, 00:57 [22001320] Ответить | Цитировать Сообщить модератору |
Roust_m Member Откуда: Сидней Сообщений: 1169 |
Не совсем понятно. В какой форме? |
||||
24 окт 19, 00:58 [22001321] Ответить | Цитировать Сообщить модератору |
Roust_m Member Откуда: Сидней Сообщений: 1169 |
Я не совсем понимаю как это работает. Залочить надо таблицу stg_student (желательно не всю, а минимально необходимые записи), после чего выбрать из нее нужные записи и пометить их как выбранные, чтобы следующий пользователь не взял себе те же самые записи. |
||
24 окт 19, 01:37 [22001325] Ответить | Цитировать Сообщить модератору |
L_argo Member Откуда: Сообщений: 1454 |
|
||
24 окт 19, 08:45 [22001361] Ответить | Цитировать Сообщить модератору |
Roust_m Member Откуда: Сидней Сообщений: 1169 |
Это будет реализовано в одной хранимой процедуре, но запускаться она будет многими пользователями. Почему не будет коллизий? |
||||
24 окт 19, 08:59 [22001365] Ответить | Цитировать Сообщить модератору |
invm Member Откуда: Москва Сообщений: 9683 |
Если есть справочник школ, то можно еще и так 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] Ответить | Цитировать Сообщить модератору |
L_argo Member Откуда: Сообщений: 1454 |
Шутки ради можно индекс уникальный поставить, чтобы вставить одну и ту же ID два раза было в принципе нельзя. :) |
||
24 окт 19, 10:12 [22001394] Ответить | Цитировать Сообщить модератору |
TaPaK Member Откуда: Kiev Сообщений: 6801 |
т.е. пусть валится в ошибки это нормально |
||||
24 окт 19, 10:15 [22001397] Ответить | Цитировать Сообщить модератору |
Владислав Колосов Member Откуда: Сообщений: 8485 |
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] Ответить | Цитировать Сообщить модератору |
Владислав Колосов Member Откуда: Сообщений: 8485 |
В первом запросе для наглядности лучше указать set val = 1. |
24 окт 19, 12:06 [22001527] Ответить | Цитировать Сообщить модератору |
Roust_m Member Откуда: Сидней Сообщений: 1169 |
Владислав Колосов, Спасибо, попробую |
25 окт 19, 05:58 [22002173] Ответить | Цитировать Сообщить модератору |
invm Member Откуда: Москва Сообщений: 9683 |
Если есть справочник школ, решается так: 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] Ответить | Цитировать Сообщить модератору |
Roust_m Member Откуда: Сидней Сообщений: 1169 |
Даже если объединить два запроса внутри транзакции как предложил 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] Ответить | Цитировать Сообщить модератору |
TaPaK Member Откуда: Kiev Сообщений: 6801 |
Roust_m,
а точнее сделать всё иначе, то почему-то не работает... |
||
28 окт 19, 10:25 [22003947] Ответить | Цитировать Сообщить модератору |
Топик располагается на нескольких страницах: [1] 2 вперед Ctrl→ все |
Все форумы / Microsoft SQL Server | ![]() |