Generating unique code based on a category

[Riteman]

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.<br/>
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.

[diafol]

Why are you doing this? What.s wrong with a regular users table with a field for district?

[pritaeas]

If you use a composite PK in your users table, your auto increment will work as expected.

[Riteman]

@diafol This is the client requirement

[Taywin]

Your issue is very common in concurrent programming.

One possible solution is to lock the table right when a user attempt to add a new data. The work part inside locking table must be as little as you can, or you would create a bottle neck.

Another solution is to redesign your tables. However, I have no idea what your client requirements are, so I can't suggest this solution...