Concurrency control in database insertion

Question

I have a competition control problem in entering data from a table. The scenario is as follows::

There is a table that records daily data according to user requests, and this data cannot be duplicated. The current competition control checks if there is any record of these data in the table on that day, and if it exists, blocks the insertion.

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

How can I implement a 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 1

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 prevents collision as two transactions cannot be opened in the same operation at the same time.

Asynchronous support exists from . 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();
    }
}

Answer 2

From what I understand, roughly you’re experiencing what’s called race condition.

In a race condition you start an operation and get some result. During the process or at the very end of it, something changes and this state is no longer valid, but your program thinks it is. That is, when you run the Any in the FooDataSet, he finds nothing with those characteristics but when he executes the SaveChanges, there is already a data in the database with those characteristics, placed by another process that had started the execution before or was faster for some reason.

I’m not familiar with the technology you’re using, but I can tell you the secret is to let the database handle it. Have it recorded and checked whether the operation was successful or not. Of course, the database needs to be structured in such a way that duplicate information cannot be entered. I don’t know and I doubt there’s any other way to solve this.

That article can help you.

Answer 3

You can use the lock of C#, basically it blocks that someone else accesses the code that is "LOCKADO" while someone is already there.

object lockObject = new object();
public void DemoLock()
{
    lock (lockObject)
    {
        InsereRegistrosNaTabela();
    }
}