Unique code based on category - a sort of auto_increment 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.
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.