DEV Community

loading...

Discussion on: Generate a unique random code to store in DB

mmj profile image
MMJ Author

I don't think it's a matter of locks, nor "unique ID systems" here.
Checking the DB for duplicates can be a a way to ensure uniqueness basing on type of DB and type of application.
But I appreciate your point.
Thanks.

Thread Thread
darkain profile image
Vincent Milum Jr

The locks have to deal with the race condition you've introduced in the example code. What happens when there are two active connections to the database at the same time? For example, two separate web requests coming in at the same time. If they both generate the same "unique" ID at the same time, they'll both check to see if the ID exist, which it doesn't yet for either. Then the first goes to insert, and then the second goes to insert. The database lock would be a single mutex to prevent this very condition from happening. Also, standard unit testing usually doesn't test for these types of scenarios either, since they're generally single-instanced, single-threaded. Its just something I want readers to be aware of when building systems like this, and potential issues that'll arise over time.

Thread Thread
mmj profile image
MMJ Author

Your point sure have to be clear. I agree.

Thread Thread
mmj profile image
MMJ Author • Edited

You gave me something to think about...

Let me ask you this: being in a MySql DB and setting the field unique, instead of looking for the code in the table, what about using a try-catch inserting the code and, if the error code refers to duplicate entry, do that again?
Something like this:

do {

  $res = false;
  $codeError = '';
  $code = generateCode(10);

  try {
    $stmt = $mysqli->prepare("INSERT INTO tablename (code) VALUES (?)");
    $stmt->bind_param("s", $code);
    $res = $stmt->execute();
  }
  catch (exception $e) {
    $codeError = $e->getCode();
    echo "code with value {$code} not inserted <br>";
  }

} while ($codeError === 1062);

if ($res) {
  echo "code with value {$code} inserted";
}
Enter fullscreen mode Exit fullscreen mode
Thread Thread
darkain profile image
Vincent Milum Jr

So a few things here to consider.

Firstly, if you're dealing with a single database instance, this will work. If you're dealing with a Galera instance, this should work. If you're dealing primary-replica style replication, this will also work. However, if you're doing primary-primary replication with writes to multiple nodes, this could still potentially lead to a race condition, so it is best to know what your replication topology is.

Secondly, using random data for keys works in small to medium scale (hundreds, thousands, maybe even a few million rows), but once you get beyond that, range locking within the database engine for the key starts to become a performance concern.

It is also important to take into consideration the data size. Only 10 characters will eventually fill up. And the closer to full you get, the longer it is going to take to find a unique value. Something to keep in mind as services scale upwards in ways not thought about at creation time. For instance, YouTube published articles about increasing their view counter on their videos from a 32-bit integer to a 64-bit integer, because Gangnam Style broke the 2 billion views mark.

str_shuffle is also listed as not cryptographically secure. These types of pseudo random generators are usually designed to be fast, and as such, may not even be able to produce every single possible value in a given set of constraints, so you may be hitting those collisions even sooner.

Thread Thread
mmj profile image
MMJ Author

Interesting points here, thanks!

Forem Open with the Forem app