Geek asks and answers

How to implement a secure transaction, random sequence in MySQL

Therefore, my application should allow users to generate random alphanumeric codes such as A6BU31, 38QV3B, R6RK7T. Currently, they consist of 6 characters, while I and O are not used (so we got 34 ^ 6 possibilities). These codes are then printed and used for something else.

Now I have to make sure that many users can "reserve" up to 100 codes per request, so user A may want to get 50 codes, user B wants to generate 10, and so on. These codes must be unique to all users, so user A and user B cannot both receive the code ABC123.

My current approach (using PHP and MySQL) is to have two InnoDB tables for this:

  • One (the "repository") contains a large list of pre-generated codes (since the likelihood of collisions increases over time, and I don’t want to go into the try-insert-if-fail-try-another-code approach). The repository contains only codes and an automatically increasing identifier (so I can sort them, see below).
  • Another table stores reserved keys (i.e. code + user owner).

Whenever the user wants to reserve N keys, I planned to do the following

BEGIN;
INSERT INTO revered_codes (code,user_id)
  SELECT code FROM repository WHERE 1 ORDER BY id LIMIT N;
DELETE FROM repository WHERE 1 ORDER BY id LIMIT N;
COMMIT;

This should work, but I'm not sure. Looks like I'm creating a WTF solution.

, . , , . , , reserved_codes, - , WTFy.

, INSERT reserved_codes.

, , - MySQL? , reserved_codes , , .

: , , (reserved_codes ). ( , ~ 1mio ).

+3
1

, , :

UPDATE repository SET user_id = ? WHERE user_id IS NULL LIMIT N;

. :

  • user_id
  • , .
+3

More articles:


All Articles