Conditions fails when 2 user are doing the delete action

Question

My problem is pretty straightforward, I Have a page where Admins can Manage the list of authorized admin but my problem is that there should always be at least 1 Admin left in the database so I Wrote This simple if that checks the number of Admins in the database should be higher than 1 before deleting

Here is my controller delete action

 public ActionResult DeleteAdmin(int idAdmin)
    {

        using (InscriptionFormationEntities dbm = new InscriptionFormationEntities())
        {
            Administrateurs admin = dbm.Administrateurs.FirstOrDefault(x => x.id == idAdmin);
            if(admin.NomLogin == Session["utilisateur"].ToString())
            {
                ModelState.AddModelError("Current User", "You can't delete Yourself");
            }
            if(dbm.Administrateurs.ToList().Count <= 1)
            {
                ModelState.AddModelError("LastAdmin", "At least 1 admin must be left");
            }
            if (ModelState.IsValid)
            {
                dbm.Administrateurs.Remove(admin);
                dbm.SaveChanges();
            }
            List<Administrateurs> ListeAdmin = dbm.Administrateurs.ToList();
            return RedirectToAction("Utilisateurs", "Admin");
        }
    }

This code Works perfectly fine expect in one case : if there are 2 Admin left and they try to delete eachother at the same time they will be able to. I Tried to change where I put the condition(for instance just before the delete) but still the condition always returns false. So I was wondering if there was a way to prevent this like defining that only 1 instance can delete at a time or something like that.

EDIT

I looked into this question: How can I lock a table on read, using Entity Framework?

but it dosen't solve my problem because i don't want to lock on the read but on the delete instead, because if I lock the read only 1 person will be able to access that page at a time ( it could work in a worse case scenario but I hope there is a better solution)

Possible duplicate of [How can I lock a table on read, using Entity Framework?](https://stackoverflow.com/questions/13404061/how-can-i-lock-a-table-on-read-using-entity-framework) — Gusman, Aug 15 '17 at 13:36
You need make lock only on delete process - to check count and delete. All other time table will be not locked. — Vitaliy Smolyakov, Aug 15 '17 at 14:46

score 1 · Accepted Answer · answered Aug 16 '17 at 09:43

If you are willing to use stored procedures you could use sp_getapplock. This works like a mutex in the stored procedure. So in the protected code section you could do a record count and delete knowing that another call to the procedure will not execute that code until the lock is released.

I have added an example procedure, ignore the set @msg and raiserror statements these are just there to display status, the important bit starts at the Exec statement.

Run the procedures in two separate tabs, they will both start but the second will wait 15 seconds to acquire a lock and then continue if it gets it.

exec critical_rhz '00:00:20' simulates a process that will take 20 seconds

CREATE PROC dbo.critical_rhz  @wait_duration varchar(30) = '00:01:00' -- default one minute
/* Performs a task in a critical section of code that can only be run
   by one session or transaction at a time. 
   The task is simulated by a WAIT 
   The raiseerror() with nowait is just for real time status displays */
AS
declare @rc int = 0 -- return code
  , @msg varchar(2000)
set @msg = convert(varchar,getdate(), 114) + ' critical_section_worker starting'
raiserror (@msg, 0, 1) with nowait 
Begin Try
Begin tran
set @msg= convert(varchar,getdate(), 114) + ' requesting lock'
raiserror (@msg, 0, 1) with nowait
Exec @rc = sp_getapplock @Resource='CriticalSectionWorker' -- the resource to be locked
     , @LockMode='Exclusive'  -- Type of lock
     , @LockOwner='Transaction' -- Transaction or Session
     , @LockTimeout = 15000 -- timeout in milliseconds, 15 seconds
set @msg= convert(varchar,getdate(), 114) + ' sp_getapplock returned ' + convert(varchar(30), @rc) + ' -- '
  + case when @rc < 0 then 'Could not obtain the lock'  else 'Lock obtained'  end
raiserror (@msg, 0, 1) with nowait
 if @rc >= 0 begin
  set @msg= convert(varchar,getdate(), 114) + ' got lock starting critical work '
  raiserror (@msg, 0, 1) with nowait
waitfor delay @wait_duration -- Critical Work simulated by waiting
  commit tran -- will release the lock
  set @msg= convert(varchar,getdate(), 114) + ' work complete released lock' 
  raiserror (@msg, 0, 1) with nowait
  end 
 else begin
  rollback tran
  set @rc = 50000
end
end try
begin catch
 set @msg = 'ERROR: ' + ERROR_MESSAGE() + ' at ' 
        + coalesce(ERROR_PROCEDURE(), '')
        + coalesce (' line:' + convert(varchar(30), ERROR_LINE()), '')
 RAISERROR (@msg, 0, 1) with nowait -- ensure the message gets out        
 if @@Trancount > 1 rollback tran
raiserror (@msg, 16, 1)
end catch
return @rc
GO

Conditions fails when 2 user are doing the delete action

1 Answers1