Entity Framework - Transaction Scope pessimistic locking

Question

I've been trying to implement pessimistic locking of EF contexts using Transaction Scope.

Models:

User

Room
    ICollection<User> Users
    int Capacity

Controller:

public void List()
{
    using(var context = new MyDBContext())
    {
        // Let's pretend that this method grabs list of users from room and creates list of their names and then dumbs them
        foreach(var item in context.Rooms.First().GetUserNames()) 
            Console.WriteLine(item);
    }
}

public void Join()
{
    using(var context = new MyDBContext())
    {
        var room = context.Rooms.First();
        if(room.Users.Count >= room.Capacity)
            throw new Exception("No room");

        using(var transaction = new Transaction(
                TransactionScopeOption.Required,
                new TransactionOptions
                {
                    IsolationLevel = IsolationLevel.RepeatableRead
                }
            ))
        {
            room = context.Rooms.First(); // Fetch again to get latest version

            if(room.Users.Count >= room.Capacity)
                throw new Exception("No room");

            room.Users.Add(CurrentUser);

            context.SaveChanges();
            transaction.Complete();
        }
    }
}

Now I'm looking for approval that behaviour described below should work and in case it won't, I'd be glad to hear feedback or advices to make it possible.

Expected behaviour:

Let's assume we've got 3 threads: A - calls Join() first; B - calls List(); C - calls Join() second.

Room.Capacity is 1.

This is what I'm expecting to happen: A calls Join(). Method checks if it can join - looks like yes, so it enters TransactionScope. At this time, B calls List() - because A didn't finish Join() yet, B thinks that there are no users in the room. Important thing here is that B won't wait until A finishes transaction, but will access last commited version of row. Now, C is calling Join(). Because A is still working on transaction, C's initial test for room succeeds, so it enters TransactionScope as well. But because A is not done yet, C should wait for it now. A finishes transaction, number of users in room is now 1. A unlocks transaction and C enters it. It fetches Room model again, tests capacity just to find out it can't join, so throws exception.

What I thing may broke:

I'm not sure about IsolationLevel.RepeatableRead. In docs, it's said that Volatile data can be read but not modified during the transaction. New data can be added during the transaction.. On the one hand, I'm hoping that this will allow List() method to read row as is and won't wait for TransactionScope to finish. Also, it should lock only row, not table, so adding new Rooms is possible. But I'm worried that it will not block lock C's TransactionScope when A is still running. And lastly, i'm not sure if 'Volatile data can be read but not modified during the transaction' applies to TransactionScope as well.

To sum it up: please, tell me if my code will behave as expected and if not, what tweaks should I do in order to achieve that? Thanks in advance.

Entity Framework - Transaction Scope pessimistic locking

0 Answers0