DEV Community

loading...

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

Collapse
mmj profile image
MMJ Author

Thanks for this comment. As I pointed out in the post this piece of code is not that efficient in large scale application and, you are right, also not for cases where you need a "secure" code, so I agree with you. This is suitable for small application and in the case you need/want to choose which characters your code will be made of.
Anyway, having said that, the uniqueness is granted by the second part of code I posted, so I don't think this can be an issue in any way, no matter how big the things are scaled up. Or am I missing something?

Collapse
darkain profile image
Vincent Milum Jr

There are no locks on the database, so it has a potential race condition there. Additionally, if this were a multi-primary database (like Galera), those locks cannot be guaranteed anyways. Its usually better to just let existing dedicated unique ID systems to their things, like auto-increment primary keys. They have a lot of consideration in their implementation to ensure uniqueness, down to how they handle thread locking and mutexes.

Thread Thread
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