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.

0

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 menas 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.

  • 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. – Thamilan Oct 30 '15 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. – Thamilan Oct 30 '15 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). – David Spillett Oct 30 '15 at 12:40
  • how about using LOCK TABLES users WRITE and then UNLOCK TABLES? – Thamilan Oct 30 '15 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. – David Spillett Oct 30 '15 at 14:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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