Not generating random unique Id in a multithreaded environment for a SQL column having Bigint as datatype

Question

I have a SQL column with datatype as Bigint, so when I 'm generating a unique number using C# Random class in a multithreaded environment, I see there are duplicate values instead of having unique, I can see only system GUID is the only option to generate unique Id's, could you please help me to solve this problem.

private Object thisLock = new Object();  
public Random a = new Random(DateTime.Now.Ticks.GetHashCode());
private void NewNumber()
{
  lock (thisLock)  
  {  
    MyNumber = a.Next(0, 10);
  }
}

Why are you using the _hash code_ of the current Tick count for a random seed? — gunr2171, Aug 09 '21 at 17:02
@Mysterious288 what kind of database are you using? MS SQL, PostgreSQL, MySQL? — Steeeve, Aug 09 '21 at 17:12
Random does not generate uniques numbers. Prefer [incrementals](https://stackoverflow.com/questions/404040/how-do-you-like-your-primary-keys) or [GUIDs](https://dba.stackexchange.com/questions/264/guid-vs-int-which-is-better-as-a-primary-key). — , Aug 09 '21 at 17:14
Does this answer your question? [Generating random, unique values C#](https://stackoverflow.com/questions/14473321/generating-random-unique-values-c-sharp) — , Aug 09 '21 at 17:14
Please do not use `new Random(DateTime.Now.Ticks.GetHashCode())` - it's worse than `new Random()`. — Enigmativity, Aug 10 '21 at 00:32

score 2 · Answer 1 · edited Aug 09 '21 at 17:12

2

The Random class generates random values, not unique values. In your sample code the maximum number of unique integers is 10 (from 0 to 9). So if you called this method at least 11 times, you would be guaranteed one or more duplicates.

For a database you should use identity columns.

edited Aug 09 '21 at 17:12

gunr2171

16,104
25
61
88

answered Aug 09 '21 at 17:08

Steeeve

824
1
6
14

score 1 · Answer 2 · answered Aug 09 '21 at 19:16

Your code should normally run without error, and I did not find an error. The duplicate numbers may be due to their small range.

You can use the following class to generate random numbers between ranges. Returns a number from the desired range each time.

class UniqueRandom 
{
  private readonly List<int> _currentList;
  private readonly Random _random = new Random();

  public UniqueRandom(IEnumerable<int> seed) 
  {
    _currentList = new List<int>(seed);
  }

  public int Next()
  {
    if (_currentList.Count == 0) 
    {
      throw new ApplicationException("No more numbers");
    }

    int i = _random.Next(_currentList.Count);
    int result = _currentList[i];
    _currentList.RemoveAt(i);
    return result;
  }
}

create instance from UniqueRandom class and call Next() method in NewNumber() method

UniqueRandom u = new UniqueRandom(Enumerable.Range(0, 10));
private Object thisLock = new Object();  
public Random a = new Random(DateTime.Now.Ticks.GetHashCode());
private void NewNumber()
{
  lock (thisLock)  
  {  
    MyNumber = u.Next();
  }
}

Theodor Zoulias · Answer 3 · 2021-08-09T18:30:23.157

0

The SQL Server Bigint type is equivalent to the C# Int64 (long) type. To generate random long values in C#, look at this question. To ensure that the random number is also unique, add a unique constraint to the relevant datatable column. In case the random value already exists in the database, catch the constraint-violation exception, and try again with a new random value.

Regarding how to use the Random class correctly in a multithreaded environment, check out this question: Is C# Random Number Generator thread safe?

edited Aug 09 '21 at 18:30

answered Aug 09 '21 at 18:08

Theodor Zoulias

34,835
7
69
104

while this is ok, generating a random number on the client side, trying through dbms, catching constraint-exceptions and retrying is fairly ineffective. If I would really need random bigints on the server side, I would write a stored procedure to accomplish that without roundtrips between server and client. I'm still waiting for an answer, what DBMS is here in use and if random numbers are really a requirement. – Steeeve Aug 09 '21 at 18:25
@Steeeve the probability of inserting a duplicate `Bigint` value in a casual database is so small, assuming that the random value is properly generated, that retrying the `INSERT` after getting a constraint-violation exception should be a fairly rare occurrence. I wouldn't be concerned about the performance implications too much. – Theodor Zoulias Aug 09 '21 at 18:39
ACK :) I would still implement it in a stored procedure. – Steeeve Aug 09 '21 at 18:42
@Steeeve I'm using SQL Server and I can't have stored proc because I'm using fluentd tool to get logs from the source and to push it to the DB. – Mysterious288 Aug 20 '21 at 17:02

Not generating random unique Id in a multithreaded environment for a SQL column having Bigint as datatype

3 Answers3