c# – Concurrency control in database insertion

Question:

I have a concurrency control problem when inserting data from a table. The scenario is as follows:

There is a table that records daily data as per user request, and this data cannot be duplicated. The current concurrency control checks if there is any record of this data in the table on that day, and if there is, blocks the insertion.

The problem is that this implementation is being inefficient because when two users click the button simultaneously, the verification is done simultaneously (resulting in "There is no data in the database") and the insertion too, creating duplicate data in the table.

How can I implement concurrency control without using a lock on the table, since this table is constantly used and a lock would probably slow down the transaction?

public void InserirFoo(Foo variavel, int id)
{
    var diaDeHoje = DateTime.Now;
    if (!VerificarInsercao(id, diaDeHoje))
    {
        contexto.FooDataSet.inserir(variavel);
        contexto.SaveChanges();
    }
}

private bool VerificarInsercao(int id, DateTime dataAtual)
{
    return contexto.FooDataSet.Any(e => e.id == id && e.dataInsercao == dataAtual); 
}

Answer:

Using transactional scope. So:

public void InserirFoo(Foo variavel, int id)
{
    var diaDeHoje = DateTime.Now;
    using (var scope = new TransactionScope()) 
    {
        if (!VerificarInsercao(id, diaDeHoje))
        {
            contexto.FooDataSet.inserir(variavel);
            contexto.SaveChanges();
        }

        scope.Complete();
    }
}

This avoids collision, as two transactions cannot be opened in the same operation at the same time.

Asynchronous support exists as of .NET 4.5.2:

public async Task InserirFoo(Foo variavel, int id)
{
    var diaDeHoje = DateTime.Now;
    using (var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled)) 
    {
        if (!VerificarInsercao(id, diaDeHoje))
        {
            contexto.FooDataSet.inserir(variavel);
            await contexto.SaveChangesAsync();
        }

        scope.Complete();
    }
}