0

I have to create a unique code to every user registering. The thing is that the unique code needs to be generated based on which city the user is from. So the tables I have are:

CREATE TABLE `microreg`.`users` ( `user_id` INT NOT NULL
AUTO_INCREMENT , `user_code` CHAR(10) NOT NULL , PRIMARY KEY (`user_id`),
UNIQUE (`user_code`));

user_id is for unique identification of users in DB.
user_code is the readable code generated for the use of users. They access their account with that unique code.

CREATE TABLE `microreg`.`districts` ( `district_id` INT NOT NULL
AUTO_INCREMENT , `district_name` VARCHAR(50) NOT NULL , `district_code`
CHAR(4) NOT NULL , PRIMARY KEY (`district_id`), UNIQUE (`district_code`));`

This contains as about 20K districts with unique code assigned.

say,

Trichy      ITRY
Coimbatore  ICBE
Kolkata     IKOL
Delhi       IDEL
...
New York    UNYK
Washington  UWDC

So, when user registers with Trichy as district, he will be generated with

user_id   : 1
user_code : ITRY000001

second user from same city

user_id   : 2
user_code : ITRY000002

another one from New York

user_id   : 3
user_code : UNYK000001

user_code is unique with respect to district. [User with id 1 and 3 are not same]

So far, I fetched the maximum value of the selected district of the user

SELECT RIGHT(MAX(user_code),6) FROM `users` WHERE user_code LIKE 'ITRY%';

and then increment it by 1 and then add it back with district_code appended to it.

INSERT INTO `microreg`.`users` (`user_id`, `user_code`) VALUES (NULL, 'ITRY000003');

This method works perfectly when there is only one user updating at a time. But this started creating problem when multiple users concurrently started registering. Since unique is set, two users with same user_code was generated and creates overhead.

Previously, for another application, I created unique tables for every code (like district here) and added the user to that table and then appended back to original table (like users here). In that case I had only 4 types of code (like district here). But for this app, I have got about 20K districts. I couldn't get a spark for this. (For information, I use PHP with mysqli for this application) Thanks.

2 Answers 2

1

The select max and increment does not scale if you have multiple writers. My rule of thumb is if you need to lock tables for routine operations, you should seriously question if you have the correct solution.

Is there a requirement that the user code to be monotonically increasing by 1? I don't think MySQL has sequences other than the auto_increment facility. I'm assuming you don't want to expose the user_id column.

If there is no requirement for increment by 1, I offer two solutions.

  1. Create a table whose sole purpose is to generate a sequence using the auto_increment faculty. It has two columns, user_id and seqno (that one is the auto increment). You would insert a row with the user_id and get the seqno for that user. Concatenation with the district to generate the user code. You can delete the row after you created the user.

  2. Hash the user_id and concatenation with the district.

I'm partial to the second solution for the simplicity.

1

Assuming you are using an ACID compliant engine (i.e. InnoDB) the "get max, add one, then insert" method should work if wrapped in a transaction.

I would query the requirement for the user code to be a specific item though given you have a user ID which is presumably an auto-incrementing integer. If there is no need for codes to be sequential within a region (i.e. it is OK to have ITRY000003 and ITRY000042 but nothing in between because users 4 through 41 are in other places) then you could just have the user code be a derived value constructed from [district_code]+PadWithZeros([user_id]). This means you don't have to worry about generating the extra code (which I assume is needed for display purposes mainly?). You may still need to store it if the users could move between districts but you'd like to keep their original code if they do. If it isn't required that the code be static if the user moves districts then you don't need to store it: it can be derived when needed.

5
  • Yeah am using InnoDB, client of mine needs ITRY000003, ITRY000004 sequentially. Irrespective of user changing from one district to other district, the code is permanent once generated.
    – Thamilhan
    Oct 30, 2015 at 11:37
  • And I haven't worked well with transaction. If it locks the table till the transaction is complete, I can very well go with it. But as far as I have in my knowledge I knew that transaction will process all queries successfully or rollbacks all changes.
    – Thamilhan
    Oct 30, 2015 at 11:39
  • 1
    If you have the transaction isolation level set to SERIALIZABLE it will definitely lock the data so each action will not interfere with each other. The default REPEATABLE READ may result in deadlock errors (where two or more processes get a read lock then try get a write lock - none of them can get the write lock until the other drop the read lock but they won't until they get the write lock and complete). Oct 30, 2015 at 12:40
  • how about using LOCK TABLES users WRITE and then UNLOCK TABLES?
    – Thamilhan
    Oct 30, 2015 at 12:52
  • To avoid deadlock in the case of "read, increment, write" the relevant write locks need to be present at the point of the read, not just around the write. Transactions with the isolation level set to serializable will ensure this. If you are locking things manually then you need to request and release the locks similarly. Oct 30, 2015 at 14:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.