# Getting a serial number from shared table

I have a table that keeps track of a global serial number shared between several tables. This is not the PK or unique id for each table. It is like a case identifier. Every time users create a new entity we need to give them a unique serial number from this row. My worries is what happens if 2 users hit the method for incrementing this counter at the exact same time. Would this solution be enough?

Also this code would be run from 2 servers using the same database.

public string GetIdentifier()
{
int retries = 0;
bool success = false;

string newId;

do
{
try
{
var tracker = Trackers.Find(1);
newId = tracker.Increment();
SaveChanges();
success = true;

}
catch (DbUpdateConcurrencyException)
{
if (++retries == 5)
throw;
}
catch (Exception)
{
//do some logging
throw;
}

} while (!success);

return newId;
}

• Which language is this? C#? Please add the suitable tag. – mheinzerling Oct 12 '17 at 9:23
• My first guess: Let the database handle this for you. Databases are usually designed for concurrency and are pretty good in such tasks. Do you have some kind of auto increment columns? – mheinzerling Oct 12 '17 at 9:26
• This used to be implemented as a database trigger but I try to make it without triggers (and stored procedures) – Jepzen Oct 12 '17 at 9:50
• Your identity is a string. Why don't you use guids? – Gert Arnold Oct 12 '17 at 12:00
• I've rolled your changes back, since they invalidate the reviews. What you may and may not do after receiving answers. – Zeta Oct 20 '17 at 7:35

You propably won't ever get the effect that 2 people create the same unique identifier at the same time. Still, it could possibly happen, most databases will handle this. If you create your db with unique values only in your table, it will automatically give you an error when 2 values are identic.

• Lets say the table holds the value 4. Then If two processes hit the table at the same time and gets 4 add +1 and try to update the table. The first process that finishes will update the table to 5 and return 5. The last process will find out is hat dirty data and should not return 5. Instead it should re-read the data , add 1, update table and return 6. I dont think that is done "automatically" – Jepzen Oct 19 '17 at 9:06
• OP worries about 2 processes hitting the same time creating a duplicate value wich is not hte case. Like you mentioned it will increase the counter and 2 new values are created, so it kinda handles this automatically, because the db does not allow to run 2 processes at the same time. – Thomas Oct 19 '17 at 9:51
• @Thomas process A read current value (4), process B read current value (4). They're running sequentially (not at the same time). Now process A increment the value to 5 and store the new value. Also process B is doing the same. Again SEQUENTIALLY. Both processes have the value 5 (and this is wrong). DB helps you only if table is fully locked (and from OP code I assume this is the case). – Adriano Repetti Oct 19 '17 at 9:53
• @Jepzen A and B try to store value 4. A succeeds. B fails because the value is defined as unique and 4 already exists. B's thread catches the error, increments the value to 5, retries the save. This is such a fringe case for concurrency that the slightly inefficient algorithm is negligible due to it hardly occurring in the first place. If you want unique values in the DB, you should configure the DB column to only allow unique values. If you don't, you're making it possible for your business rules to be violated. – Flater Oct 19 '17 at 13:45
• @Jepzen: In short, you're wrongly equating a DbUpdateConcurrencyException to be equivalent to storing the same value twice, which is not necessarily the case. E.g. no such exception is encountered if two concurrent threads have a slight time difference between them, yet those threads may still have been saving the same value to the DB. Here's a link with an answer that relates to handling unique constraint violations (look for SQL exception number 2627 in the code) – Flater Oct 19 '17 at 13:51

You do not need a flag to exit the loop when you can simply return from function. Also the retry counter may be simplified with a simple for.

To put everything together:

for (int i=0; i <= MaximumNumberOfRetries; ++i)
{
try
{
var tracker = Trackers.Find(1);
var newId = tracker.Increment();

SaveChanges();
return newId;
}
catch (DbUpdateConcurrencyException)
{
}
catch (Exception)
{
//do some logging
throw;
}
}


I Introduced a GetRandomDelay() function, in the (maybe rare) case that three threads/processes/servers are processing a batch of insertions then it's better to de-synchronize them (otherwise the one that will first wait will maybe wait until all the others finished).

What you're currently doing is something like this:

UPDATE Trackers
SET SerialNumber = @NewValue
WHERE SerialNumber = @OldValue


It's what EF does in your case and it works because you're updating the field to @NewValue only if the existing value is what you expected @OldValue (what you read). If someone else was updating the DB then value changed and you should retry (just check the number of affected rows as result of this command, EF throws DbUpdateConcurrencyException).

There is, however, another easier way which is atomic on the DB and it will take care of concurrency for you (because of the write lock acquired on the table when writing):

UPDATE Trackers
SET SerialNumber = SerialNumber + 1
OUTPUT INSERTED.SerialNumber


In this case result of your query is the new serial number to use. Note that you have to write some SQL code (or a stored procedure) for this, it's not something you can (AFAIK) do directly from EF but using Database.SqlQuery().