How to Lock a Table on Read, Using Entity Framework

Lock table before inserting in EF Core 5

Thank you for the suggestions from the comments!

I've tried using transactions with the IsolationLevel set to Serializable, but then I've realized this would lock too many tables (the example from the question is a dummy one, the actual implementation is more complex).

I agree that sometimes it might be easier to have this kind of logic in the database, but adding a stored procedure for this will kind of break the current consistency and most probably leave the door open for other stored procedures. I'm not saying stored procedures are bad, just that in my current situation, even if it is a bit harder/more complex to achieve this without them, I believe it's worth it for consistency reasons.

The solution I've ended with

I've ended splitting the flow in 2 steps as follows:

// step 1
var newOrder = /* logic for creating the new order */;
_ordersRepository.Add(newOrder);
_ordersRepository.SaveChanges(); // insert into the db

// step 2
var orders = _ordersRepository.GetAllBy(userId, date); // get the orders from the db
var totalAmount = orders.Sum(o => o.Amount); 
if(totalAmount < MaximumAmount) {
  newOrder.IsApproved = true;
}
_ordersRepository.Update(newOrder); 
_ordersRepository.SaveChanges(); // update the new order

Step 1 just creates the new order and inserts it into the database, the IsApproved flag being left to the default which is false.
Step 2 performs the daily limit validation and if the check passes, the IsApproved flag to true.

I know it's not an actual solution, but a workaround. Locking a table might have a too big performance impact, especially if the given table is used by multiple app features. With this solution, even if there was an issue in the Step 2, the order will be left with IsApproved=false so it won't have any impact and the user can either try again later, or somebody from support can handle it.

Is EF can lock table?

No Transaction Isolation Level does that. The easiest way to do that is with an Application Lock. An Application Lock is like a global Mutex that can allow only a single client computer to run a piece of code. Note that this code doesn't have to be database code. You could use an Application Lock to give a client exclusive access to a file, or other resource.

Like this:

using (var tran = db.Database.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))
{
    var lockName = "MyLock";
    db.Database.ExecuteSqlCommand($"exec sp_getapplock '{lockName}', 'exclusive'");

    // do stuff

    tran.Commit();
}

Locking tables in Entity Framework

It's not clear to me why you would want this, but if you really want to lock the whole table you could:

Turn off row and page locking so that everything escalates to a table lock

Example adapted from here:

ALTER INDEX [MyIndexName] ON [dbo].[MyTableName] SET ( ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = OFF)

Note: this assumes your application solely "owns" these tables -- wouldn't want to apply this and break some other app

Set your queries to use Serializable isolation level

Example adapted from here:

TransactionOptions topt = new TransactionOptions();   
topt.IsolationLevel = System.Transactions.IsolationLevel.Serializable;
using (var tran = new TransactionScope(TransactionScopeOption.Required, topt)) {
   //do stuff
}

ORACLE SQL - Lock a table until a task finishes [Using EF Core]

As far as I can tell, in Oracle you can't block readers from reading the table. Why wouldn't they be able to read "old" value, because it is valid until you "calculate" the new value? I don't know how to prevent them to do so. Truncate the table (so that it is empty)?

Anyway: one option is to

use a function (because you want to return new value to caller) ...
... which is an autonomous transaction (otherwise you can't perform DML within) ...
... that uses
- SELECT ... FOR UPDATE to lock row for updates by other users
- LOCK in case table is empty; you might not need it because you already have some data in the table

Here's a sample function; see if it helps.

FUNCTION f_token (par_id IN NUMBER)
   RETURN NUMBER
IS
   PRAGMA AUTONOMOUS_TRANSACTION;
   l_dummy  VARCHAR2 (1);
   l_token  NUMBER := 1;                 -- suppose default token value is "1"
BEGIN
       -- FOR UPDATE will "lock" row for updates by other users
       SELECT 'x'
         INTO l_dummy
         FROM token b
        WHERE b.id = par_id
   FOR UPDATE OF b.token;

   -- here you'd call another web services etc. and acquire new value into L_TOKEN

   UPDATE token b
      SET b.token = l_token
    WHERE b.id = par_id;

   COMMIT;                                         -- commit releases the lock
   RETURN (l_token);
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      -- in case table is empty, lock it so that nobody can do anything with it, except you
      LOCK TABLE token IN EXCLUSIVE MODE;

      INSERT INTO token (id, token)
           VALUES (par_id, l_token);

      COMMIT;                                      -- commit releases the lock
      RETURN (l_token);
END f_token;