Translate following C# synchronization logic to Stored Procedure

Question

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...

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;
}

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?

GSerg · Accepted Answer · 2018-08-15T14:06:53.233

2

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;

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;

edited Aug 15 '18 at 14:06

answered Mar 06 '12 at 15:44

GSerg

76,472
17
159
346

Thanks for your answer, but why have you modified my current logic, you think it was incorrect? I am not expert in SP so I am just asking out of curiosity, it looks like you are doing select and update both in one statement? – Akash Kava Mar 06 '12 at 15:49
I did not modify the logic, I modified implementation. You are doing many unnecessary things, these were removed. The only select is from `@res` table, the update statement doesn't select anything, though it returns the page ID. Now that I wrote this, I realised I could simply output to the client without the temp table. – GSerg Mar 06 '12 at 15:53
Does this also mean that I dont need stored procedure if I am encapsulating same logic within a TransactionScope? – Akash Kava Mar 07 '12 at 08:44
@AkashKava No, it's better to have a stored procedure. LINQ or EF wouldn't put an update lock when fetching data for you to compare/amend later. There's a [check on update](http://stackoverflow.com/questions/330053/how-much-overhead-does-update-check-have-for-linq-updates), that is, it will check at the moment of the update that the row hasn't changed since the read operation, and only update if it has not, but this way you will have to rerun read/update cycle multiple times until it succeeds, whereas a stored proc will succeed first time. – GSerg Mar 07 '12 at 10:27

Translate following C# synchronization logic to Stored Procedure

1 Answers1

Linked