Log reading to infer counter

0

I have a template called Request and this template has a column called Number.

This Number has the following structure: YEAR + COUNTER.

It happens that whenever I'm going to create a new request I get the last request added in the database and from it I can infer the% > request .

For example, if my last request inserted has Number then the next request will have number = 20140001 .

The problem occurs when two requests are generated simultaneously. It turns out that two or more processes can create requests . Soon in each process the bank is accessed, I check the last request and generate a new one. It turns out that concurrently processes take the same request and consequently generate number = 20140002 equal.

I'm using Entity Framework 5 and MVC 4. How to solve this?

    
asked by anonymous 06.02.2014 / 19:01

3 answers

1
  • You need to update the DB schema, and change the number field from the Requests table to

    • primary key
    • auto increment - this depends on the DB. In SQL Server, the keyword Identity
  • Add attribute [DatabaseGenerated(DatabaseGeneratedOption.Identity) to 'property Number in code.

    public class Request
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)
        public int Number {get; set;}
    }
    
  • So, BD will try to self-boost the field. And thanks to the ACID properties, there will be no concurrency problems.

    If you do not want to use autoincrement, I think you will have to implement your own mechanism, and this is no simple task ...

    Now, I have not worked with databases for some time, but I recommend the following:

    • Create a table (eg "NumberCounter") with a single row and a single column (eg "Count")

      ╔═══════╗
      ║ Count ║
      ╠═══════╣
      ║  2010 ║
      ╚═══════╝
      
    • When you want to increment & get the next number, use a stored procedure that:
    • Increases the value of Count. Example in "pseudo-code" / "pseudo-query": INSERT INTO NumberCounter(Count) values((Select Count from NumberCounter) + 1);
    • Use a lock in the table to prevent other increments from being made in parallel
    • Returns the current value of Count.

    I hope it helps.

    Read more:   Understanding Locking in SQL Server

        
    06.02.2014 / 19:50
    1

    If you are using SQL Server 2012 you should use a sequence . The value must be retrieved by the time the form is loaded.

    If your bank is not SQL Server 2012 , an option to simulate sequence is in article .

        
    20.02.2014 / 14:59
    0

    To deal with this competition problem, I create the entity in question and another control entity. So:

    I create the parent entity, in this case: Request

    public class Request
    {
        public Guid RequestId { get; set; } = Guid.NewGuid();
    
        public int Number { get; set; }
    
        // demais propriedades necessárias
    }
    

    I create a number control entity, type: RequestCounter

    public class RequestCounter
    {
        public Guid RequestCounterId { get; set; } = Guid.NewGuid();
    
        public int Number { get; set; } // no banco este campo será autoincrement
    
        public DateTime Dth { get; set; } = DateTime.Now;
    }
    

    Controller , I call GetRequestCounter () to get the number.

    public ActionResult Create(Request request)
    {
        if (ModelState.IsValid)
        {
            request.Number = ObterRequestCounter();
    
            _repository.Adicionar(request);
    
            return RedirectToAction("Index");
        }
    
        return View(request);
    }
    

    I create the private method where I add a RequestCounter with the RequestCounterId generated by me (when instantiated) and then query using Number .

    private int ObterRequestCounter()
    {
        var requestCounter = new RequestCounter();
    
        _repository.AdicionarRequestCounter(requestCounter);
    
        var newNumber = _repository.ObterRequestCounterPorId
                                    (requestCounter.RequestCounterId).Number;
    
        // concateno o ano com o novo número e depois retorno para número.  
        return Convert.ToInt32(DateTime.Now.Year + newNumber.ToString());
    }
    

    Because of step 4, it does not matter how many users add Request at the same time. With the RequestCounterId of the RequestCounter generated in the code (before going to the bank), I can recover what was the Number automatically generated by the bank without competition.

    In my case, the RequestCounter table looks like history, but if my client at some point in the future decides that old records should be deleted, I can do this using the Dth property for this.

    That's it.

        
    06.10.2018 / 22:30