将以下C#同步逻辑转换为存储过程

本文关键字:转换 存储过程 同步 | 更新日期: 2023-09-27 18:27:00

我有以下实体框架逻辑,我想转换为存储过程,我尝试在存储过程中使用独占锁,但它会导致很多超时。。。

把Page想象成某种有4列的硬盘

Pages
   PageID
   SpaceAvailable
   SpaceOccupied
   TotalSpace

我需要在页面中分配我的对象,因为空间是可用的,如果对象不能容纳,它将获得下一个可用的页面。

// a static lock to prevent race condition
static object Locker = new Object(); 
long AllocateNewPage(MyContext context, int requestedSize){
   long pageID = 0;
   // what is T-SQL lock equaivalent?
   lock(Locker){
      using(TransactionScope scope = new TransactionScope()){
         var page = context.Pages
                        .Where(x=>x.SpaceAvailable>requestedSize)
                        .OrderBy(x=>x.PageID)
                        .First();
         page.SpaceOccupied = page.SpaceOccupied + requestedSize;
         page.SpaceAvailable = page.SpaceAvailable - requestedSize;
         context.SaveChanges();
         scope.Commit();
         pageID = page.PageID;
      }
   }
   return pageID;
}

下面是我写的存储过程,但它会导致很多超时,因为我设置了5秒的超时时间,否则同样的事情在C#中运行正确且非常快,唯一的问题是,我必须将其移动到存储过程中,因为数据库现在将为多个客户端服务。

CREATE procedure [GetPageID]
(
    @SpaceRequested int
)
AS
BEGIN
    DECLARE @DBID int
    DECLARE @lock int
    DECLARE @LockName varchar(20)
    SET @LockName = 'PageLock'
    BEGIN TRANSACTION
        -- acquire a lock
        EXEC @lock = sp_getapplock 
                            @Resource = @LockName, 
                            @LockMode = 'Exclusive', 
                            @LockTimeout = 5000
        IF @lock<>0 BEGIN
            ROLLBACK TRANSACTION
            SET @DBID = -1
            SELECT @DBID
            return 0
        END
        SET @DBID = coalesce((SELECT TOP 1 PageID 
                                  FROM Pages 
                                  WHERE SpaceAvailable > @SpaceRequested 
                                  ORDER BY PageID ASC ),0)
        UPDATE Pages SET 
            SpaceAvailable = SpaceAvailable - @SpaceRequested,
            SpaceOccupied = SpaceOccupied + @SpaceRequested
        WHERE PageID = @DBID
        EXEC @lock = sp_releaseapplock @Resource = @LockName
    COMMIT TRANSACTION
    SELECT @DBID
END

我对存储过程了解不多,但我需要在锁定模式下分配页面,这样页面就不会被过度填充。

我想得太多了吗即使我正在运行事务,我仍然需要锁定吗?

将以下C#同步逻辑转换为存储过程

是的,你想得太多了。让SQL Server管理锁。
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版本足够旧):

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;