Competition control in database insertion

Question

Competition control in database insertion

Navigation

#1 by (10 votes)
#2 by (6 votes)
#3 by (1 votes)

10

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 according to the user request, and this data can not be duplicated. The current concurrency control checks if there is any record of this data in the table that day, and if it does, it blocks the insertion.

The problem is that this implementation is inefficient because when two users simultaneously click 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 the use of a lock on the table, since this table is constantly used and a lock would probably make the transaction slower?

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); 
}

sql-server c# .net entity-framework webforms

asked by anonymous 12.09.2014 / 15:39

3 answers

6

As I understand it, roughly you're experiencing race condition .

In a race condition you get to do an operation and get some result. During the process or just at the end of it, something changes and this state is no longer valid, but your program thinks it is. That is, when you run Any on FooDataSet , it does not find anything with those features but when you run SaveChanges , there is already a data in the database with those characteristics, placed by another process that had started the execution earlier or was faster for some reason.

I do not know the technology you're using but I can tell you the secret is to let the database handle this. Have it recorded and check whether the operation was successful or not. Of course, the database must be structured in such a way that duplicate information can not be inserted. I do not know and I doubt that there is another possibility to solve this.

This article can help you.

12.09.2014 / 17:19

1

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

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

12.09.2014 / 16:18

What is the purpose of empty command blocks or which do not belong to any command? Negative variance in R? Floating-Point Error Propagation

score 10 · Accepted 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 prevents collision, since two transactions can not 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();
    }
}