Can database transaction help block read until read from another transaction is finished?

Question

In my code I have a situation similar to this:

using(var transaction = DbContext.Database.BeginTransaction(IsolationLevel.Serializable)
{
    var record = DbContext.SomeTable.FirstOrDefault(t => t.id = someId);
    if(record == null)
    {
        DbContext.SomeTable.Add(new SomeTanle(...);
    }
}

I tested it by doing a pair of concurrent calls to the API endpoint that calls above code and I saw that from time to time same record is created two times. I suppose that probably there is no transaction isolation level that blocks select in one transaction until second transaction with select on same table is finished. I mean in this case there is a risk that both calls will go through the select statement without waiting waiting for other transaction to finish. Therefore, only way to avoid inconsistent data is to use lock on the code level. Am I right?

Answer 1

Your assumption from this code is that you want to do a lock on the table. Therefore, this cannot be done with IsolationLevel. It is better to put Lock on the method body to make sure that it does not get enter more than one thread at the moment. You can also leave the record in a static variable so you don't have to read from the database and lock it every time. Like the following code:

private static readonly Object obj = new Object();
private static SomeTable someTable=null;
using(var transaction = DbContext.Database.BeginTransaction(IsolationLevel.Serializable)
{

if(someTable==null){
lock (obj)
{ 

    var record = DbContext.SomeTable.FirstOrDefault(t => t.id = someId);
SomeTable=record;
    if(record == null)
    {
        DbContext.SomeTable.Add(new SomeTanle(...);
    }
}
}
}