是的，您想得太多.让SQL Server管理锁.

Yes, you are overthinking. Let SQL Server manage the locks.

create procedure [GetPageID]
  @SpaceRequested int
as
begin 
  set nocount on;

  begin tran;

  update top (1) pages
  set
    SpaceAvailable -= @SpaceRequested,
    SpaceOccupied += @SpaceRequested
  output
    inserted.PageID
  where
    SpaceAvailable > @SpaceRequested
  order by PageID asc;

  commit tran;

end;

上面的内容也可以用两步编写的方式显示(如果您更喜欢它，或者您的SQL Server版本足够旧):

The above can also be written in a two-step fashion you're showing in your question (in case you prefer it or your SQL Server version is old enough):

create procedure [GetPageID]
  @SpaceRequested int
as
begin 
  set nocount on;

  begin tran;

  declare @page_id int;

  select top (1) @page_id = PageID
  from pages with (updlock, rowlock)
  where SpaceAvailable > @SpaceRequested
  order by PageID asc;

  update Pages
  set
    SpaceAvailable = SpaceAvailable - @SpaceRequested,
    SpaceOccupied = SpaceOccupied + @SpaceRequested
  where
    PageID = @page_id;

  commit tran;

  select @page_id;

end;