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

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'

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

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

-- тестовая таблица
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

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

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

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

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

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