将以下C#同步逻辑转换为存储过程 [英] Translate following C# synchronization logic to Stored Procedure

问题描述

我遵循Entity Framework Logic，并且我想转换为存储过程，我曾尝试在存储过程中使用排他锁，但这会导致大量超时...

I have following Entity Framework Logic, and I want to translate to Stored Procedure, I have tried using exclusive lock in Stored Procedure but it results in lots of timeouts...

将Page视为具有4列的某种硬盘

Think of Page as some sort of Hard disk which has 4 columns

Pages
   PageID
   SpaceAvailable
   SpaceOccupied
   TotalSpace

并且我需要在页面的可用空间中分配我的对象，如果对象不适合，它将获得下一个可用页面.

and I need to allocate my objects in the pages as the space is available, if object can not fit, it will get the next available page.

// 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#中相同的东西将正确且快速地运行，唯一的问题是，我必须将其移至存储的"作为数据库的过程现在将为多个客户端提供服务.

Following is Stored Procedure I have written but it results in lots of timeout as I have set 5 seconds for timeout, where else same thing runs correctly and quite fast in C#, the only problem is, I have to move this to Stored Procedure as database will now serve multiple clients.

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

我对存储过程了解不多，但是我需要以锁定模式分配页面，以便没有页面被过度填充.

I dont know much about Stored Procedures, but I need to allocate pages in locked mode so that no page will be over filled.

我是否想过? 即使我正在事务中运行，我仍然需要锁定吗?

AM I OVER THINKING ? Even if I am running in transaction do I still need locking?

推荐答案

是的，您想得太多.让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;

这篇关于将以下C#同步逻辑转换为存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！