Log reading to infer counter

Question

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

This number has the following structure: YEAR + COUNTER.

It just so happens that every time I create a new request I take the last request added to the database and from it I can infer the Number of the new request.

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

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

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

Answer 1

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

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

Answer 2

1. You have to update the comic schematics, and change the field number table Requests for

   • primary key
   • auto increment - this depends on BD. In SQL Server, keyword is used Identity

2. Add attribute [DatabaseGenerated(DatabaseGeneratedOption.Identity) to' property Number in the code.

   public class Request
   {
       [Key]
       [DatabaseGenerated(DatabaseGeneratedOption.Identity)
       public int Number {get; set;}
   }
   

Thus, the BD will deal with auto-incrementing the field. And plot the properties ACID, there will be no concurrency problems.

---

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

Now, I haven’t worked with databases for some time, but I recommend the following:

• Create a table (e.g., "Numbercounter") with a single row and column (e.g., "Count")

  ╔═══════╗
  ║ Count ║
  ╠═══════╣
  ║  2010 ║
  ╚═══════╝
  

• When you want to increment & get the next number, use a stored process that:
  1. Increments the value of Count. Example in "pseudo-code"/"pseudo-query": INSERT INTO NumberCounter(Count) values((Select Count from NumberCounter) + 1);
  2. Uses a lock in the table, to prevent other increments from being made in parallel
  3. Returns the current value of Count.

I hope it helps.

Read more: Understanding Locking in SQL Server

Answer 3

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

1- create the main entity, in this case: Request

public class Request
{
    public Guid RequestId { get; set; } = Guid.NewGuid();

    public int Number { get; set; }

    // demais propriedades necessárias
}

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

3- Na Action of Controller, I call Obter requestcounter() to obtain the number.

public ActionResult Create(Request request)
{
    if (ModelState.IsValid)
    {
        request.Number = ObterRequestCounter();

        _repository.Adicionar(request);

        return RedirectToAction("Index");
    }

    return View(request);
}

4- I create the private method where I add a Requestcounter with the Requestcounterid generated by me (when instant) and then I make the query using it to recover 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, no matter how many users will make an addition to Request at the same time. With the Requestcounterid of Requestcounter generated in the code (before going to the bank), I can recover which was the Number automatically generated by the bank without competition.

In my case, the table of Requestcounter is historical, but if my client at some future time decides that old records should be erased, I can do so using the property Dth for that reason.

That’s it.